PostgreSQL, MySQL, MongoDB and Elasticsearch/OpenSearch
Architect-level comparison of the most common data stores in Java stacks: strengths, indexing, scaling, consistency, operational concerns and when to pick each.
Comparison
| Aspect | PostgreSQL | MySQL (InnoDB) | MongoDB | Elasticsearch / OpenSearch |
|---|---|---|---|---|
| Model | Relational + JSONB, rich types, extensions | Relational | Documents (BSON) | Inverted index documents |
| Strength | Correctness, advanced SQL, extensions (PostGIS, pgvector, Timescale) | Simplicity, read scaling, huge ecosystem | Flexible schema, horizontal sharding built in | Full-text search, relevance, aggregations, logs |
| Transactions | Full ACID, serializable available | ACID | Multi-document transactions (with costs) | No multi-document transactions |
| Default isolation | Read committed | Repeatable read | Snapshot within transactions | n/a (near real-time refresh) |
| Scaling writes | Single primary; Citus or sharding layers | Single primary; Vitess | Native sharding | Shards per index |
| Typical role | System of record | System of record | Document-centric system of record | Secondary index, never the only copy |
[!VERSION] Elastic re-licensed Elasticsearch in 2021 (prompting the AWS-led OpenSearch fork) and added AGPL as an option in 2024. OpenSearch moved to the Linux Foundation in 2024.
PostgreSQL essentials
- Indexes: B-tree (default), GIN (JSONB, arrays, full-text), GiST/SP-GiST (geo, ranges), BRIN (huge append-only tables), partial and expression indexes.
- MVCC: updates create new row versions; VACUUM reclaims space; monitor bloat and autovacuum.
- Partitioning by range/list/hash for very large tables (e.g. by month).
- Read scaling via replicas; connection pooling (PgBouncer) for many clients.
CREATE INDEX CONCURRENTLY idx_orders_customer_recent ON orders (customer_id, created_at DESC) WHERE status <> 'CANCELLED';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 'c1' ORDER BY created_at DESC LIMIT 20;MongoDB essentials
Model around access patterns: embed data read together (order with its lines), reference large or shared data. Choose a shard key with high cardinality and even distribution; use majority write concern for durability.
Elasticsearch/OpenSearch essentials
- Index = shards (primary + replicas); choose shard size roughly in the tens of GB.
- Mappings:
text(analyzed) vskeyword(exact); define explicitly. - Near-real-time: documents searchable after refresh (default ~1 s).
- Feed from the system of record via CDC; reindex with aliases for zero-downtime mapping changes.
- Vector (k-NN) search supports semantic/hybrid retrieval.
Interview questions and answers
PostgreSQL or MongoDB for a new product catalog?
If attributes vary widely and data is read as whole documents by ID, both work: PostgreSQL with JSONB keeps relational capabilities and one less technology; MongoDB fits if the team prefers document modeling and needs native sharding at very large scale. Search should go to a search engine either way.
Why not use Elasticsearch as the primary database?
No multi-document transactions, eventual visibility (refresh), mapping changes require reindexing, and historically weaker durability guarantees. Use it as a derived read model.