Skip to content

SQL LIKE vs Elasticsearch/OpenSearch

1. Decision

Choose SQL LIKE / ILIKE (or Postgres tsvector) for simple exact substring matching, low query volume, or small relational datasets where adding search infrastructure is unnecessary; choose Elasticsearch / OpenSearch for multi-field full-text search, fuzzy search, relevance scoring (BM25), faceted search filtering, and petabyte-scale real-time log analytics.

2. 30-Second Answer

SQL LIKE '%keyword%' executes an un-indexed full-table scan, reading every row tuple sequentially from disk (\(O(N)\) query complexity). Even leading-wildcard indexes fail B-Tree lookups, leading to severe disk I/O thrashing and database crashes under concurrent search traffic. Elasticsearch and OpenSearch are distributed, Lucene-based search engines utilizing Inverted Indexes. An inverted index maps every unique word to its containing document IDs (\(O(1)\) dictionary lookup). They provide advanced text analysis (stemming, tokenization, synonym expansion), BM25 relevance scoring, fuzzy typo tolerance, and sub-second faceted aggregations over billions of documents, but require asynchronous Change Data Capture (CDC) synchronization from the primary database.

3. Decision Matrix

Option Best For Avoid When Latency Scalability Consistency Operational Complexity
SQL LIKE / ILIKE Simple exact substring matches (WHERE email LIKE '%@gmail.com'), small tables (<50k rows), zero infrastructure overhead Full-text search, multi-field search, fuzzy matching, high-concurrency search traffic High (\(O(N)\) full table scan; seconds over large tables) Extremely Poor (Degrades exponentially with row count and concurrent requests) Immediate ACID consistency Low
Elasticsearch / OpenSearch E-commerce product search, fuzzy typo matching, autocomplete, faceted filtering, log analytics Simple exact key lookups, transactional system of record data storage Ultra-Low (<10ms inverted index lookup) Extreme Horizontal Scale (Distributed Shards & Replica Nodes) Near Real-Time (NRT ~1s segment refresh interval); Eventual High (JVM tuning, shard management, CDC pipelines)

4. Selection Rules

Requirement Best Choice Rationale / Example
Simple Exact Prefix Match on Small Table SQL LIKE 'admin%' B-Tree index handles trailing wildcards ('admin%') cleanly via index range scan
Complex Full-Text Search Across 10 Fields Elasticsearch / OpenSearch Search title, description, tags, and reviews in 1 request with BM25 relevance scoring
Fuzzy Search / Typo Tolerance ("iphone" \(\rightarrow\) "ifone") Elasticsearch / OpenSearch Levenshtein Edit Distance algorithms handle typos natively in Lucene analyzers
Zero Additional Infrastructure Overhead Postgres tsvector + GIN Native inverted index inside PostgreSQL handles full-text without external engines
Sub-Second Faceted Aggregation Filters Elasticsearch / OpenSearch Lucene doc_values compute multi-dimensional faceted counts (brand, price, size) in ms

5. Tradeoffs

  1. Full Table Disk Scan vs Inverted Index Lookups
  2. Gain (Search Engine): \(O(1)\) dictionary lookup instantly returns document IDs.
  3. Lose (Search Engine): High RAM consumption (JVM heap) storing inverted indexes and FST dictionaries.
  4. Transactional Integrity vs Dual-Write Synchronization Lag
  5. Gain (SQL): Immediate visibility within single ACID database transactions.
  6. Lose (SQL): Heavy LIKE queries starve OLTP CPU/RAM connection pools.
  7. Advanced Relevance Scoring (BM25) vs Boolean Matches
  8. Gain (Search Engine): Ranks results intelligently based on term frequency, inverse document frequency, and field boosts.
  9. Lose (Search Engine): Requires tuning custom analyzers, tokenizers, and field weights.
  10. Near Real-Time (NRT) Refresh vs Instant Commit
  11. Gain (Search Engine): Decouples heavy search workloads away from primary relational databases.
  12. Lose (Search Engine): Default 1-second refresh_interval means inserted items have a 1s index visibility delay.
  13. Operational Maintenance Complexity
  14. Gain (SQL): Single database engine to maintain.
  15. Lose (SQL): Cannot scale search throughput horizontally independently from the primary DB.

6. Interview Counter Questions

  1. Why does SQL LIKE '%keyword%' bypass B-Tree indexes while LIKE 'keyword%' can use them?
  2. How does an Inverted Index in Apache Lucene work under the hood?
  3. How do you synchronize data between PostgreSQL and Elasticsearch without dual-writing in application code?
  4. What is the difference between tsvector + GIN index in PostgreSQL versus Elasticsearch?
  5. How does the Levenshtein Edit Distance algorithm implement Fuzzy Search in Lucene?
  6. Why is doc_values critical for sorting and aggregations in Elasticsearch?
  7. What is Near Real-Time (NRT) in Elasticsearch and what happens during a segment refresh_interval?
  8. How does BM25 scoring rank search results better than simple term counts?
  9. When would you choose PostgreSQL pgvector hybrid search over Elasticsearch?
  10. How do you handle schema changes (Mapping updates) in Elasticsearch without downtime?

7. Gold Standard Answers

  1. B-Tree indexes sort text from left-to-right. A trailing wildcard ('keyword%') performs a B-Tree range scan. A leading wildcard ('%keyword%') obscures the prefix, forcing a full table scan of all disk pages.
  2. An Inverted Index extracts words (terms) from documents via an Analyzer pipeline and maps each term to a Posting List containing document IDs, term frequencies, and byte offsets.
  3. Use Change Data Capture (CDC via Debezium reading Postgres WAL) streaming to Kafka, consumed by an indexing worker. Avoid dual-writing in app code due to partial failure risks.
  4. Postgres tsvector + GIN provides native in-database full-text search. Elasticsearch adds distributed auto-sharding, BM25 scoring, fuzzy matching, autocomplete FSTs, and faceted aggregations at petabyte scale.
  5. Levenshtein Distance calculates the minimum single-character edits (insertions, deletions, substitutions) to transform word A to word B. Lucene uses Automata over FSTs for fuzzy matching up to distance 2.
  6. Inverted indexes map terms to documents (fast search, slow sorting). doc_values is an on-disk columnar structure mapping documents to terms, allowing fast, memory-efficient sorting and aggregations.
  7. Documents written to memory buffer are periodically flushed to OS page cache as new Lucene segments every 1s (refresh_interval). Data becomes searchable (NRT) only after segment refresh.
  8. BM25 incorporates Term Frequency saturation (prevents repeated keyword spam from artificially boosting rank) and Document Length Normalization (penalizes long documents with accidental hits).
  9. Use pgvector for small-to-medium vector datasets (<10M vectors) requiring joint relational SQL queries (WHERE tenant_id = X AND cosine_distance < Y) inside single ACID transactions.
  10. Create a new index with updated mappings (products_v2), run the _reindex API in the background, and switch a zero-downtime Index Alias (products_live) from v1 to v2.

8. Common Mistakes

  • Running LIKE '%keyword%' queries on multi-million row production SQL databases under heavy concurrent traffic.
  • Dual-writing directly to Postgres and Elasticsearch inside HTTP handlers without CDC or outbox patterns.
  • Failing to use Index Aliases in Elasticsearch, forcing application code updates during index mapping reindexing.
  • Allocating more than 32GB JVM heap to Elasticsearch, disabling Compressed OOPs pointers and wasting RAM.
  • Expecting instant sub-millisecond visibility of newly inserted records in Elasticsearch without accounting for the 1s refresh_interval.

9. Keywords

SQL LIKE, ILIKE, Elasticsearch, OpenSearch, Inverted Index, Posting List, BM25, TF-IDF, Levenshtein Distance, Fuzzy Search, doc_values, Near Real-Time (NRT), refresh_interval, Debezium CDC, Index Alias, Reindex.

10. Cheat Sheet

  • Core Rule: Simple substring / prefix ('admin%') \(\rightarrow\) SQL; Multi-field full-text, fuzzy, faceted search \(\rightarrow\) Elasticsearch / OpenSearch.
  • Index Engine: SQL B-Tree (Row-oriented); Elasticsearch Inverted Index + Columnar doc_values.
  • Leading Wildcard Penalty: LIKE '%keyword%' forces an \(O(N)\) full table scan, thrashing DB disk I/O.
  • Sync Strategy: PostgreSQL WAL \(\rightarrow\) Debezium CDC \(\rightarrow\) Kafka \(\rightarrow\) Elasticsearch Indexer.
  • Zero-Downtime Reindexing: Always point app code to an Index Alias (products_live), never raw index names.
  • 32GB Heap Limit: Never allocate \(>32\text{GB}\) JVM heap to Elasticsearch to preserve Compressed OOPs pointers.

11. Related Decisions