Elasticsearch Trade-offs: Not a Primary Database, vs PostgreSQL & MongoDB

01 · Positioning

Why Elasticsearch Is Not a Primary Database

Elasticsearch is optimized for search and analytics, not for being the single source of truth for transactional data.

It lacks multi-document ACID transactions, has near real-time (not immediate) consistency by design, and mapping changes typically require reindexing. These aren't bugs — they're consequences of design choices made to optimize for search speed and horizontal scale, which is a different goal than transactional correctness.

02 · Comparison

Elasticsearch vs PostgreSQL

PostgreSQL and Elasticsearch solve different problems well, and the difference is architectural, not just a feature checklist.

Transactions

PostgreSQL gives full ACID transactions across multiple tables/rows. Elasticsearch has no multi-document transactions — each document write is independent.

Query model

PostgreSQL excels at precise, relational queries (joins, exact filters, aggregations over structured data). Elasticsearch excels at ranked full-text search and fast faceted aggregations over denormalized documents.

Schema flexibility

PostgreSQL enforces a rigid schema with cheap ALTER TABLE for simple changes. Elasticsearch's mapping is far more rigid in practice — most non-trivial changes require reindexing.

03 · Comparison

Elasticsearch vs MongoDB

Both are document-oriented and horizontally scalable, but they optimize for different access patterns.

Primary use case

MongoDB is built as a general-purpose operational document database — CRUD, moderate ad-hoc querying. Elasticsearch is built specifically for search — text relevance ranking, faceting, and analytics.

Consistency

MongoDB offers tunable consistency with strong options (majority reads/writes) suitable as a system of record. Elasticsearch's near-real-time model is optimized for search freshness, not transactional guarantees.

04 · Core distinction

Search vs Database Queries

A database query asks a boolean question: does this row match? A search asks a ranking question: how well does this document match, relative to every other match?

This distinction explains almost every architectural difference between Elasticsearch and a relational database: the inverted index (built for term lookup, not row scans), BM25 scoring (ranking, not filtering), and analyzers (fuzzy matching, not exact comparison) all exist because "most relevant" is a fundamentally different question than "exactly matching".

05 · Trade-offs

Inverted Index Trade-offs

The same inverted index that makes search fast has real costs — in storage, in write amplification, and in mapping rigidity.

Storage overhead

Every analyzed text field maintains its own inverted index — indexing many large text fields multiplies storage compared to a relational table storing raw strings.

Write cost

Every indexed document must be analyzed and its terms inserted into the relevant inverted indexes — indexing is inherently more expensive per write than a simple relational row insert.

06 · Data modeling

Denormalization

Elasticsearch has no joins — data that would be normalized across tables in a relational database must be denormalized into self-contained documents.

A product document destined for search typically embeds its category name, brand name, and other lookup values directly, rather than storing foreign keys and joining at query time. This trades storage and update complexity (denormalized copies must be kept in sync) for query speed and simplicity.

07 · Trade-offs

Relevance vs Exact Matching

Optimizing for relevance (finding the 'best' matches) and optimizing for exactness (finding precisely the specified value) pull in different directions.

A match query on a text field is deliberately forgiving — it will match partial term overlaps, different word forms, and different orderings, then rank by how well each result fits. A term query on a keyword field is deliberately strict — it returns exactly what was asked for, nothing more, nothing less, unranked.

08 · Common mistakes

Common Mistakes

A short list of mistakes account for most of the confusing, hard-to-debug Elasticsearch issues teams encounter.

Wrong field type for the query

Using term on text or match on keyword — the analyzed/unanalyzed mismatch silently breaks matching.

Treating it as a system of record

Storing data only in Elasticsearch with no upstream source of truth makes mapping changes risky: the Reindex API can rebuild an index from _source, but only if _source was kept and complete, and there is no independent copy to fall back on if a reindex goes wrong.

Ignoring shard sizing upfront

Leaving primary shard count at the default and discovering months later that growing it means a reindex (or a split into a compatible shard count).

09 · FAQs & architecture

FAQs & Real-World Architectures

The most common real question isn't "is Elasticsearch good?" — it's "where does it fit next to the database I already have?"

"Can Elasticsearch replace my database?"

For most applications, no — pair it with a system of record and sync data in, rather than making Elasticsearch the only store.

"How does data get into Elasticsearch?"

Common patterns: dual writes from the application, Change Data Capture (CDC) from the database's write-ahead log, or events published to a stream (e.g. Kafka) that a consumer indexes.

Typical real-world architecture

Application → primary database (PostgreSQL/MongoDB) → CDC/event stream → indexing consumer → Elasticsearch, with the application querying Elasticsearch only for search/analytics endpoints and the primary database for everything transactional.

Key takeaways

01Elasticsearch is a search/analytics layer, not a transactional system of record — pair it with a primary database.
02vs PostgreSQL: precise relational queries and transactions vs ranked full-text search and fast faceted analytics.
03vs MongoDB: general-purpose operational document store vs a purpose-built search engine.
04The inverted index trades write cost and storage for dramatically faster, ranked reads.
05Denormalize documents at index time — there are no joins at query time.
06Sync data into Elasticsearch via CDC or event streams; never make it the only copy of important data.