What is the difference between Elasticsearch and a relational database?
Compare Elasticsearch and relational databases: schema, joins, ACID vs eventual consistency, and why they are often used together.
Expected Interview Answer
A relational database stores structured rows in tables with fixed schemas and excels at transactional integrity and joins, while Elasticsearch stores schema-flexible JSON documents in an inverted index optimized for fast full-text search, relevance ranking, and analytics at scale. In short, relational databases are the source of truth for transactions; Elasticsearch is a search and analytics layer.
Relational databases (like PostgreSQL or MySQL) enforce ACID transactions, normalize data across tables, and use SQL with joins, which is ideal for consistency-critical writes such as payments. Elasticsearch denormalizes data into documents, is eventually consistent and near real-time, and does not support cross-index joins the way SQL does. It shines at ranked text search and aggregations over huge datasets. Many systems use both: the database owns the data, and Elasticsearch indexes a copy for search.
- Clarifies transactional integrity vs search optimization
- Explains schema-fixed tables vs flexible JSON documents
- Highlights joins/SQL vs denormalized documents
- Covers ACID/strong consistency vs near real-time eventual consistency
- Frames them as complementary rather than competing
AI Mentor Explanation
A relational database is like the official scorebook that must be perfectly accurate and consistent for every run and wicket, since results depend on it. Elasticsearch is like the broadcast stats engine that copies those records and lets commentators instantly search patterns. You trust the scorebook for the truth, but you use the stats engine for fast, flexible queries during play.
Step-by-Step Explanation
Step 1
Compare the data model
Relational uses fixed-schema tables and rows; Elasticsearch uses flexible JSON documents.
Step 2
Compare querying
SQL with joins across tables vs denormalized documents and a query DSL with no cross-index joins.
Step 3
Compare consistency
Relational offers ACID transactions and strong consistency; Elasticsearch is near real-time and eventually consistent.
Step 4
Compare the optimization goal
Databases optimize correct transactional writes; Elasticsearch optimizes ranked search and aggregations.
Step 5
Decide how to combine them
Keep the database as source of truth and index a copy into Elasticsearch for search.
What Interviewer Expects
- Names ACID/transactions as a relational strength
- Explains inverted index and search focus of Elasticsearch
- Knows Elasticsearch lacks true cross-index joins
- Mentions eventual consistency and near real-time indexing
- Frames the two as complementary, not interchangeable
Common Mistakes
- Claiming Elasticsearch can replace a transactional database entirely
- Assuming Elasticsearch supports SQL joins like relational systems
- Ignoring that Elasticsearch is eventually consistent
- Treating denormalization in Elasticsearch as bad design rather than intended
Best Answer (HR Friendly)
“A relational database is best at storing important records accurately and safely, like payments or orders. Elasticsearch is best at searching through large amounts of data very quickly. Many companies use both together: the database keeps the official data, and Elasticsearch makes it searchable.”
Code Example
-- Relational (SQL)
SELECT * FROM products
WHERE name LIKE '%headphones%' AND price < 150;
-- Elasticsearch query DSL
GET /products/_search
{
"query": {
"bool": {
"must": { "match": { "name": "headphones" } },
"filter": { "range": { "price": { "lt": 150 } } }
}
}
}Follow-up Questions
- Why does Elasticsearch avoid cross-index joins?
- How would you keep Elasticsearch in sync with a relational database?
- What does eventual consistency mean for search results?
- When is denormalization the right choice?
- Can you run transactions in Elasticsearch?
MCQ Practice
1. Which system provides ACID transactions?
Relational databases guarantee ACID transactions; Elasticsearch is near real-time and eventually consistent.
2. How does Elasticsearch typically model related data compared to SQL?
Elasticsearch denormalizes data into documents because it lacks efficient cross-index joins.
3. A common architecture pattern is to:
The database stays authoritative while Elasticsearch indexes a copy for fast search and analytics.
Flash Cards
Core difference? — Relational = transactional source of truth with ACID and joins; Elasticsearch = search/analytics engine over denormalized documents.
Consistency model? — Relational is strongly consistent; Elasticsearch is near real-time and eventually consistent.
Joins? — SQL supports cross-table joins; Elasticsearch has no efficient cross-index joins, so data is denormalized.
How are they combined? — Database owns the data; a copy is indexed into Elasticsearch for search.
Continue Learning
Related Interview Questions
What is Elasticsearch and what problems does it solve?
easy
What is an inverted index and how does it power Elasticsearch search?
medium
What is the difference between Amazon RDS and DynamoDB?
medium
A user searches for a word that is plainly in the document and gets nothing back. How do you debug it?
medium