Skip to content

Elasticsearch vs PostgreSQL Search

1. Decision

Choose PostgreSQL Search (B-Tree, GIN, tsvector, pgvector) for simple full-text search, exact filtering, or vector similarity search when query volume is moderate and data already resides in PostgreSQL; choose Elasticsearch (or OpenSearch) when complex distributed full-text search, multi-field relevance scoring (BM25), autocomplete, aggregation analytics, or massive log search at petabyte scale are required.

2. 30-Second Answer

PostgreSQL provides native text search via tsvector and tsquery data types indexed with GIN (Generalized Inverted Index), alongside vector similarity search using pgvector (HNSW/IVFFlat). It avoids dual-write consistency issues and architectural overhead by keeping search strictly within ACID relational boundaries. Elasticsearch is a distributed, JSON-based search engine built on Apache Lucene, utilizing inverted indexes, BM25 relevance ranking, finite state transducers (FST) for autocomplete, and distributed shard sharding. Elasticsearch scales horizontally across dozens of nodes for high-throughput search and aggregations, but requires Change Data Capture (CDC) synchronization pipelines from the primary database.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
PostgreSQL Search E-commerce with simple search, exact text matching, zero architectural footprint addition Complex search relevance scoring, fuzzy multi-language stemming, petabyte log analytics Low (<10ms) Scale-up; Read Replicas Immediate ACID consistency Low
Elasticsearch Complex product search, fuzzy matching, faceted filtering, real-time log analytics Simple exact lookup queries, primary transactional data storage Very Low (<5ms point search) Horizontal linear scale-out (Primary & Replica Shards) Near Real-Time (NRT ~1s refresh interval); Eventual High (JVM heap tuning, shard split management)

4. Selection Rules

Requirement Best Choice Rationale / Example
Zero Additional Infrastructure Overhead PostgreSQL Search Leverage existing Postgres DB using native tsvector + GIN indexing
Advanced Relevance Scoring (BM25 / TF-IDF) Elasticsearch Tunable BM25 scoring across custom field boosts, language stemmers, and decay functions
High Performance Faceted Aggregations Elasticsearch Lucene doc-values provide sub-second multi-dimensional aggregation queries
Strict Zero Data Stale Read Guarantee PostgreSQL Search Immediate visibility within single transaction without refresh delay
Fuzzy Matching / Typo Tolerance / Autocomplete Elasticsearch Edge-ngram and Finite State Transducers (FST) handle sub-string completion
Large-Scale Log Analytics & Telemetry Elasticsearch Logstash/Fluentd ingestion directly into daily time-partitioned indices

5. Tradeoffs

  1. Transactional Simplicity vs Distributed Search Performance
  2. Gain (Postgres): No data sync lag, no split-brain search indexes, single system to back up.
  3. Lose (Postgres): CPU-intensive text matching degrades core transactional OLTP database performance.
  4. Apache Lucene Inverted Index vs PostgreSQL GIN Index
  5. Gain (Elasticsearch): Highly optimized per-segment inverted indexes with doc-values for fast sorting/aggregations.
  6. Lose (Elasticsearch): Significant memory footprint (JVM heap) and index build CPU overhead.
  7. Near Real-Time (NRT) vs Immediate ACID Visibility
  8. Gain (Elasticsearch): Decoupled search workload scales independently from database writes.
  9. Lose (Elasticsearch): Default 1-second segment refresh_interval means newly inserted data isn't immediately searchable.
  10. Fuzzy Search Capabilities vs Basic Stemming
  11. Gain (Elasticsearch): Advanced analyzer pipelines (CharFilters, Tokenizers, TokenFilters, Phonetics).
  12. Lose (Elasticsearch): Complex index mapping configuration management.
  13. Operational Overhead
  14. Gain (Postgres): Single operational database engine.
  15. Lose (Postgres): Poor horizontal scalability for multi-terabyte search indexes.

6. Interview Counter Questions

  1. Why shouldn't you use Elasticsearch as your primary transactional database?
  2. How does an Inverted Index work in Apache Lucene under the hood?
  3. How do you keep PostgreSQL primary data synchronized with Elasticsearch in real-time?
  4. What is the difference between match query and term query in Elasticsearch?
  5. How does Elasticsearch handle Autocomplete efficiently (Edge-Ngram vs Completion Suggester FST)?
  6. What causes Elasticsearch JVM Garbage Collection pauses and split-brain scenarios?
  7. How does BM25 scoring algorithm improve upon traditional TF-IDF?
  8. What is the role of Lucene doc_values in Elasticsearch sorting and aggregations?
  9. When would you choose PostgreSQL pgvector over Elasticsearch for hybrid search?
  10. How do you size and plan shard allocation for Elasticsearch indices to avoid over-sharding?

7. Gold Standard Answers

  1. Elasticsearch lacks strict ACID transactions, foreign keys, multi-document locking, and rollback capabilities. Writing directly to Elasticsearch risks data corruption during split-brain partitions, and data is only searchable after segment refresh intervals.
  2. An inverted index maps every unique word (term) to a posting list containing document IDs where the term appears, along with term frequency and positional offsets, enabling sub-millisecond full-text lookups (\(O(1)\) dictionary lookup).
  3. Use Change Data Capture (CDC via Debezium reading Postgres WAL) to stream change events to Kafka, consumed by an indexing worker that updates Elasticsearch asynchronously. Avoid dual-writing directly in application HTTP endpoints.
  4. term query looks for exact binary matches in the inverted index without analyzing the search term. match query passes the input string through the field's analyzer pipeline (lowercasing, stemming, tokenization) before searching.
  5. Edge-Ngram tokenizes words into prefix fragments at index time ("phone" \(\rightarrow\) "p", "ph", "pho"), allowing standard inverted index matching. Completion Suggester uses in-memory Finite State Transducers (FST) for ultra-fast, microsecond prefix matching.
  6. Large aggregations or heap un-mapping fill the 32GB JVM heap limit, triggering Stop-The-World (STW) GC pauses. If node heartbeats fail during STW GC, master election misinterprets the pause as node death, causing cluster instability.
  7. BM25 incorporates term frequency saturation (preventing documents with 100 repeated keywords from unfairly dominating scores) and document length normalization (penalizing long documents containing accidental hits).
  8. Inverted indexes map terms to documents (fast search, slow sorting). doc_values is an on-disk, column-oriented data structure built at index time that maps documents to terms, allowing fast, memory-efficient sorting and aggregations.
  9. Choose pgvector when embedding dimensions fit in memory, dataset size is under ~10M vectors, and queries require strict joint filtering with relational SQL tables (WHERE tenant_id = X AND cosine_distance < Y).
  10. Aim for primary shard sizes between 20GB and 50GB. Rule of thumb: keep total count of shards on a node below 20 per GB of allocated JVM heap. Over-sharding wastes heap memory on Lucene segment metadata.

8. Common Mistakes

  • Writing directly to PostgreSQL and Elasticsearch inside an HTTP handler, creating inconsistent index state when Elasticsearch writes fail.
  • Over-sharding Elasticsearch by creating dozens of shards for tiny indices (<1GB), exhausting JVM heap memory.
  • Using PostgreSQL LIKE '%keyword%' queries in production, causing full table scans and disk I/O thrashing.
  • Allocating more than 32GB JVM heap to Elasticsearch, disabling Compressed Ordinary Object Pointers (Compressed OOPs) and wasting RAM.
  • Treating Elasticsearch as a primary data store without periodic snapshot backups to S3.

9. Keywords

Elasticsearch, PostgreSQL, Inverted Index, GIN Index, tsvector, Lucene, BM25, TF-IDF, doc_values, FST, Edge-Ngram, Completion Suggester, Debezium CDC, Near Real-Time (NRT), 32GB JVM Heap limit.

10. Cheat Sheet

  • Core Difference: Postgres Search = Relational exact/text search in ACID boundary; Elasticsearch = Distributed Lucene inverted-index search engine.
  • Index Engine: Postgres = GIN Index / B-Tree; Elasticsearch = Lucene Inverted Index + Columnar doc_values.
  • Consistency: Postgres = Immediate ACID visibility; Elasticsearch = Near Real-Time (NRT, 1s refresh interval).
  • Data Sync: Use CDC (Debezium + Kafka) to stream Postgres WAL changes to Elasticsearch. Never dual-write in application code.
  • Autocomplete: Use Edge-Ngram tokenization or Completion Suggester (FST) in Elasticsearch.
  • Heap Rule: Never set Elasticsearch JVM heap size above 32GB to maintain Compressed OOPs pointers.

11. Related Decisions