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) vs keyword (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.