Elasticsearch vs Postgres Full-Text Search

This comparison comes up every time a product adds a search box. Postgres already holds the data and has full-text search built in: tsvector, tsquery, GIN indexes, language stemming and trigram matching. Elasticsearch is a separate distributed search engine built on Lucene, with BM25 relevance, analyzers, fuzzy matching, highlighting, aggregations and horizontal sharding. The real trade is not features versus no features. It is one system with transactional consistency versus two systems with a sync pipeline between them. The question that decides it is: is search a filter on your data, or is search the product? If users mostly find records they already know something about — an order number, a customer name, a ticket title — and relevance ranking is secondary, Postgres is enough and saves a whole system. If relevance, typo tolerance, facets and sub-100ms queries over tens of millions of documents are what users judge you on, you need a search engine, and you sign up for keeping it in sync.

The Verdict#

Default: Postgres full-text search until relevance quality or scale is a measured problem. Then Elasticsearch (or a compatible engine), fed from Postgres through CDC or an outbox, with Postgres remaining the source of truth.

🎯 Staff Move: "For the admin search over 5 million tickets I'd use Postgres: a generated tsvector column with a GIN index, plus pg_trgm for fuzzy name matches. It stays consistent with writes and adds no system. If we launch customer-facing search over the knowledge base, where ranking quality is the product, I'd add Elasticsearch fed by CDC and keep Postgres as the source of truth."

When the non-default wins:

• Elasticsearch on day one wins when search is the product from launch — a marketplace, a job board, a documentation site — and the team already knows how to run it. Retrofitting relevance onto a database search is slower than starting right.
• Postgres beyond "tens of millions" still wins when every query is scoped to a tenant: per-tenant partitions keep each search small, and transactional consistency stays free.
• Neither wins for logs and metrics at volume: aggregation-heavy, append-only data usually belongs in a columnar store.

At a Glance#

Numbers to bring:

How They Actually Differ#

1. One System vs Two: The Sync Pipeline Is the Real Cost#

With Postgres, the search index is just another index. INSERT the row and the tsvector (as a generated column) and its GIN entry commit atomically; a search one millisecond later finds it. Deletes and permission changes are instant.

With Elasticsearch, Postgres stays the source of truth and every change must flow to the search cluster. The safe patterns are CDC from the write-ahead log (logical decoding into Kafka, then an indexer) or a transactional outbox. Both work; both introduce lag, backfill jobs, a reindex procedure, and a class of bugs where search and the database disagree.

The hidden requirement in the two-system design: permission and deletion correctness. If a document is deleted or a user loses access, the search index lags by seconds to minutes during an incident. Production designs either filter results through Postgres at read time (hydrate by ID and re-check access) or accept and document the window.

🎯 Staff Insight: Adding Elasticsearch is not adding an index. It is adding a distributed system, a pipeline, a reindex runbook and a consistency window. Price those before you price the cluster.

2. Relevance: Rank Without Corpus Statistics vs BM25#

Postgres's ranking functions use term frequency, proximity and document length, but the documentation is explicit that they do not use any global information — there is no inverse document frequency, so a rare, meaningful term and a common one count alike. Ranking also requires reading the tsvector of every matching document, which the documentation warns can be I/O bound and slow when queries match many rows.

Elasticsearch scores with BM25, which uses corpus-wide term statistics so rare terms matter more, and gives you knobs: field boosts (title^3), function scores (recency, popularity), synonyms, phrase and proximity boosting, and rescoring of the top N with a costlier model. That is the gap users notice: Postgres returns matching documents; Elasticsearch returns the best matching documents first.

When Postgres's ranking is good enough: when results are few (selective filters cut matches to under a few thousand) and users scan them, or when the sort is by date or status rather than relevance. Extensions that add BM25 ranking to Postgres exist; they narrow the gap but are another component to evaluate and operate.

The Postgres tuning ladder before adding a search engine:

If the ladder is exhausted and users still complain about relevance, that is the measured signal to add a search engine.

3. Scaling and Isolation#

Postgres full-text search scales with the database: vertically, with read replicas, and by partitioning (per-tenant indexes work well because most queries are tenant-scoped). A GIN index on 10 million documents of a few KB each is comfortable on one node; heavy-match queries and ranking are the limit, not storage. The catch is contention: search queries run on the same machine as OLTP writes unless you route them to a replica.

Elasticsearch scales horizontally. An index is split into primary shards (fixed at creation; the split API or a reindex changes it), each replicated; queries fan out to one copy of every shard and merge. Elastic's guidance is shards of 10–50 GB and under 200 million documents each. Search load is isolated from the primary database by construction.

4. What Each Can Do That the Other Cannot#

Where Each One Breaks#

The production surprise for each:

• Postgres: it works beautifully until one common word matches half the table, and the query that took 5ms takes 8 seconds.
• Elasticsearch: the cluster is healthy and search is wrong. The sync pipeline dropped events during a deploy, and nobody reconciles counts.

Incident Sketch: The Search Index That Missed a Deploy#

t=0        Indexer deploy introduces a serialization bug for one document type
t=+1min    Indexer sends failures to its retry topic; retries fail the same way
t=+3h      Retry topic retention drops the oldest failed events
t=+2d      Customer reports a published article that search never returns
t=+2d+4h   Investigation finds 18,000 documents missing; full reindex from Postgres

Detection that would have caught it: a dead-letter queue with an alert on growth, a daily reconciliation that compares counts and checksums per type between Postgres and the index, and a synthetic probe that creates a document and searches for it every minute. Owner: the search team owns the pipeline and the reconciliation job.

Incident Sketch: The Common Word That Slowed Postgres#

t=0        A newsletter links to a search for 'update'
t=+1min    Query matches 1.8M of 6M rows; ts_rank reads every match's tsvector
t=+2min    Each query takes 6-9s; 40 concurrent searches saturate the primary's I/O
t=+3min    Checkout writes slow; OLTP p99 from 20ms to 800ms
t=+10min   Statement timeout on search lowered to 500ms; search routed to a replica

Prevention: search on a replica from the start, a statement timeout for search queries, and ranking only the top few thousand candidates by a cheaper sort before computing ts_rank. Owner: platform for isolation, product team for query shape.

Cost and Operations#

Rough numbers: a modest production Elasticsearch deployment (3 masters, 6 data nodes, a few TB) costs from low thousands to over ten thousand dollars a month in cloud infrastructure, plus the pipeline and a share of someone's time. Postgres search on an existing cluster costs roughly the storage of the GIN index — often 20–50% of the indexed text — and a replica if you isolate it.

Assumptions: cloud list prices, replicas for availability, SSD-backed nodes; order of magnitude only.

🧭 Principal Insight: The decision is reversible in one direction only. Starting on Postgres and adding a search engine later is a well-trodden path. Starting with a search engine as a primary store, and discovering it was never a database, is a migration with data loss risk.

Switching Later#

Adding Elasticsearch, in order:

1. Define the index document from the read model users search, denormalized, with a version field taken from the source row.
2. Build the CDC or outbox pipeline with an idempotent indexer (upsert by ID, ignore older versions) and a dead-letter queue.
3. Backfill from Postgres into a new index behind an alias; start the live pipeline before the backfill finishes so nothing is missed.
4. Dual-read: serve from Postgres, query Elasticsearch in the background, compare result overlap and latency.
5. Switch reads behind a flag, keep the Postgres search path as a degraded fallback, and schedule the reconciliation job.

The one-way doors:

1. Making the search engine the source of truth. Keep it rebuildable from the database at all times; a full reindex should be a routine job, not a disaster recovery event.
2. Exposing search-engine query syntax to clients. Wrap it in your own API so you can change engines, analyzers or ranking.
3. Denormalization choices in the index (embedding author names, permissions). Changing them means reindexing everything.

How Real Companies Chose#

GitLab — Postgres for Basic Search, Elasticsearch for Advanced Search#

GitLab ships both. Its basic search uses other data sources, such as PostgreSQL data and Git data, while advanced search uses Elasticsearch or OpenSearch; administrators can fall back to basic search if the search cluster has problems, and GitLab.com itself runs advanced search on Elasticsearch (GitLab docs).

Staff insight: The product works without the search engine and gets better with it. Designing search as an optional, rebuildable layer over the database is exactly the posture to describe in an interview.

GitHub — Outgrowing a General-Purpose Search Engine for Code#

GitHub explained that general text search products had not served code search well: poor user experience, slow indexing and expensive hosting. Elasticsearch had taken months to index the corpus, and code needs punctuation, regular expressions and substring matching without stemming or stop words. GitHub built its own engine; the post cites 115 TB of code across 15.5 billion documents reduced to a 25 TB index (GitHub Blog).

Staff insight: Elasticsearch is tuned for natural-language relevance. When the query language is different — code, logs, exact substrings — a general engine can be the wrong tool at scale. Name the query semantics before naming the engine.

Uber — Moving Log Analytics off Elasticsearch#

Uber moved its logging platform from Elasticsearch to ClickHouse. It reported that more than 80% of queries were aggregations, which Elasticsearch was not optimized for; strict schemas caused type-conflict errors that dropped logs; and the setup needed 20+ clusters per region. After the move, a single ClickHouse node ingested about 300K logs per second and hardware cost dropped by more than half (Uber Engineering).

Staff insight: A search engine is the right tool for ranked text retrieval, not for aggregation-heavy analytics over logs. Saying "most of our queries are aggregations, so this belongs in a columnar store" is the kind of workload-first reasoning interviewers want.

Follow-Ups to Expect#

What to Say in the Interview#

"The deciding question is whether search is a filter or the product. For finding known records with relational filters, Postgres full-text search keeps one system and transactional consistency."

"Postgres ranks without corpus statistics, so once relevance quality is what users judge us on, I'd add Elasticsearch with BM25, synonyms and typo tolerance."

"Postgres stays the source of truth. Changes flow through CDC into an idempotent indexer, I alert on sync lag, and I can rebuild the index from the database at any time with an alias swap."

"For permissions, I'll re-check access when hydrating results from the database, so a lagging index can't leak a document someone just lost access to."

Related Guides#

• Elasticsearch — Lucene internals, sharding, relevance and operations in depth
• PostgreSQL — the database most search features start in
• Design a Search Engine — web-scale indexing and ranking
• Design Typeahead — autocomplete, where latency budgets are tightest
• Indexes — inverted indexes, GIN and B-trees from first principles
• Read-Heavy Systems — isolating read workloads from the primary
• Batch and Stream Pipelines — CDC, backfills and reconciliation
• ClickHouse vs Druid vs Pinot — when the "search" workload is really aggregation