Elasticsearch vs Built-In Database Full-Text Search: When to Use Which?
Compare Elasticsearch and built-in database full-text search: consistency, scale, ranking, and when to use each in an interview.
Expected Interview Answer
Use your relational database’s built-in full-text search when search is a secondary feature on data that already lives there and needs to stay transactionally consistent, and reach for Elasticsearch when search is a primary, high-scale feature needing advanced relevance tuning, faceting, or near-real-time analytics across large, frequently changing text.
Built-in database full-text search (PostgreSQL tsvector, MySQL FULLTEXT) keeps the index inside the same transaction as your data, so it is always consistent with writes and requires no extra infrastructure, but it offers fewer ranking algorithms, weaker fuzzy matching, and struggles to scale search-heavy workloads without impacting OLTP performance. Elasticsearch is a dedicated, horizontally scalable search engine with rich relevance scoring (BM25), faceted aggregations, and fast fuzzy/typo-tolerant search, but it runs as a separate system that must be kept in sync with the source database, introducing replication lag and operational overhead. The decision hinges on whether search scale and sophistication justify the added infrastructure and consistency complexity.
- Database FTS: no extra infrastructure, transactionally consistent
- Elasticsearch: superior relevance scoring and faceted search
- Elasticsearch: scales search load independently of the OLTP database
- Database FTS: simpler operations for moderate search needs
AI Mentor Explanation
A local cricket club keeping its own handwritten player index in the same clubhouse ledger as match results is fine for occasional lookups, since the index and the ledger never fall out of sync. A national cricket federation covering every club, though, builds a dedicated statistics bureau with its own staff and systems purely for fast, sophisticated searches across millions of records, accepting that the bureau’s copy lags the source ledgers by minutes. Database full-text search is the clubhouse ledger; Elasticsearch is the dedicated statistics bureau.
Step-by-Step Explanation
Step 1
Assess search as a primary or secondary feature
If search is occasional and secondary, built-in database FTS is usually sufficient.
Step 2
Evaluate scale and relevance needs
High query volume, faceting, fuzzy/typo-tolerant matching, or advanced ranking push toward Elasticsearch.
Step 3
Weigh consistency requirements
If search results must always reflect the very latest write, an external index adds unacceptable sync lag.
Step 4
Account for operational cost
Elasticsearch requires a separate cluster, sync pipeline (e.g. CDC), and monitoring — factor that into the decision.
What Interviewer Expects
- Clear articulation of the trade-off between consistency and search sophistication
- Awareness of the operational cost of running a separate search cluster
- Understanding of when built-in database FTS is genuinely sufficient
- Mention of sync mechanisms like change data capture (CDC) for keeping Elasticsearch current
Common Mistakes
- Assuming Elasticsearch is always superior regardless of scale
- Not mentioning the sync/consistency lag introduced by an external search index
- Ignoring the operational overhead of running and maintaining a search cluster
- Failing to note that built-in database FTS also supports ranking, just less sophisticated
Best Answer (HR Friendly)
“Built-in database full-text search is simplest when search is a small part of the app and needs to always match the latest data exactly, since it lives right alongside the data with no extra system. Elasticsearch makes sense once search becomes a major, high-traffic feature that needs advanced relevance ranking, fuzzy matching, or faceted filtering at scale, accepting a small delay in keeping the search index in sync.”
Code Example
-- Built-in database full-text search: always consistent with writes
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM Articles, plainto_tsquery('english', 'system design interview') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
-- Conceptual: syncing the same table to Elasticsearch via CDC
-- 1. A change-data-capture stream reads new/updated rows from Articles
-- 2. Each change is transformed into a JSON document
-- 3. The document is indexed into Elasticsearch, slightly behind the write
-- 4. Search queries then hit Elasticsearch for ranking, facets, and fuzzy matchFollow-up Questions
- How would you keep an Elasticsearch index in sync with a source-of-truth database?
- What is BM25 and how does it differ from PostgreSQL’s default ranking?
- How do you handle eventual consistency between the database and the search index in the UI?
- What are faceted search and aggregations, and why are they hard in plain SQL?
MCQ Practice
1. What is the primary trade-off of adding Elasticsearch alongside a relational database?
Elasticsearch offers richer, more scalable search but introduces a separate system that must be synced, adding lag and ops overhead.
2. When is built-in database full-text search usually the better choice?
Built-in FTS keeps the index in the same transaction as the data, ideal when consistency matters more than search sophistication.
3. What mechanism is commonly used to keep Elasticsearch synced with a source database?
CDC pipelines stream row-level changes from the source database into Elasticsearch to keep the search index reasonably current.
Flash Cards
When should you prefer built-in database FTS? — When search is secondary, volume is moderate, and results must match the latest write exactly.
When should you prefer Elasticsearch? — When search is a primary, high-scale feature needing advanced ranking, fuzzy matching, or facets.
What is the main downside of Elasticsearch? — It is a separate system requiring sync (CDC), introducing lag and operational overhead.
What ranking algorithm does Elasticsearch use by default? — BM25, a probabilistic relevance scoring function more advanced than typical database FTS ranking.