Elasticsearch vs relational databases - Visual Side-by-Side Comparison

Concept Flow - Elasticsearch vs relational databases

Data Input

↓

Schema defined

↓

SQL Queries

↓

Joins, ACID

↓

Choose based on needs

Shows how data flows differently in relational databases and Elasticsearch, highlighting schema, query style, and features.

Execution Sample

Elasticsearch

SELECT * FROM users WHERE age > 30;

GET /users/_search
{
  "query": {
    "range": {"age": {"gt": 30}}
  }
}

Compares a SQL query in relational DB with an Elasticsearch JSON query for the same data condition.

Execution Table

Step	System	Input Query	Processing	Output Result
1	Relational DB	SELECT * FROM users WHERE age > 30;	Parse SQL, check schema, scan 'users' table, filter rows with age > 30	Rows with age > 30
2	Elasticsearch	GET /users/_search {"query": {"range": {"age": {"gt": 30}}}}	Parse JSON query, search index 'users', filter documents with age > 30	Documents with age > 30
3	Relational DB	Joins possible	Combine data from multiple tables	Joined rows
4	Elasticsearch	No joins	Denormalized data, no join support	Single documents only
5	Relational DB	ACID transactions	Ensure atomicity, consistency, isolation, durability	Reliable transactions
6	Elasticsearch	Eventual consistency	Distributed system, data may be temporarily inconsistent	Fast but eventual consistency
7	Relational DB	Schema fixed	Tables and columns predefined	Structured data
8	Elasticsearch	Schema flexible	Documents can have varying fields	Flexible data
9	Exit	N/A	Query processing ends	Results returned to user

💡 Query processing completes after filtering and returning matching data

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Query	N/A	SELECT * FROM users WHERE age > 30;	GET /users/_search {"query": {"range": {"age": {"gt": 30}}}}	Joins possible	No joins	Query processed
Data	Raw tables/documents	Filtered rows with age > 30	Filtered documents with age > 30	Joined rows	Single documents	Results ready
Consistency	N/A	ACID guaranteed	Eventual consistency	ACID guaranteed	Eventual consistency	Consistency state noted

Key Moments - 3 Insights

Why can't Elasticsearch perform joins like relational databases?

Elasticsearch stores data as independent documents without fixed schema, so it doesn't support joins (see execution_table rows 3 and 4). Instead, it uses denormalized data.

What does 'schema flexible' mean in Elasticsearch compared to relational databases?

Elasticsearch allows documents to have different fields without predefined tables, unlike relational DBs which require fixed tables and columns (see execution_table rows 7 and 8).

Why is Elasticsearch described as eventually consistent, unlike relational databases?

Elasticsearch is distributed and prioritizes speed, so data updates may take time to propagate, unlike relational DBs which ensure immediate consistency with ACID (see execution_table rows 5 and 6).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does Elasticsearch process the JSON query?

A Step 2

B Step 1

C Step 4

D Step 6

💡 Hint

Check the 'System' and 'Input Query' columns in execution_table row 2

According to variable_tracker, what is the consistency model after step 6?

A ACID guaranteed

B Eventual consistency

C No consistency

D Immediate consistency

💡 Hint

Look at the 'Consistency' row and the 'After Step 6' column in variable_tracker

If we add joins support to Elasticsearch, which execution_table step would change?

A Step 3

B Step 5

C Step 7

D Step 9

💡 Hint

Joins are discussed in execution_table rows 3 and 4

Concept Snapshot

Elasticsearch vs Relational DBs:
- Relational DBs use fixed schema tables and SQL queries.
- Elasticsearch stores flexible JSON documents and uses JSON queries.
- Relational DBs support joins and ACID transactions.
- Elasticsearch offers full-text search, scalability, and eventual consistency.
- Choose based on data structure and query needs.

Full Transcript

This visual execution compares Elasticsearch and relational databases. Data input flows into either system. Relational databases require a fixed schema and use SQL queries, supporting joins and ACID transactions for reliable consistency. Elasticsearch stores flexible JSON documents, uses JSON queries, and focuses on full-text search and scalability with eventual consistency. The execution table shows step-by-step how queries are processed differently, including filtering data and handling joins or consistency. Variable tracking highlights query, data, and consistency states across steps. Key moments clarify common confusions about joins, schema flexibility, and consistency models. The quiz tests understanding of query processing steps, consistency states, and join support. The snapshot summarizes key differences to help choose the right system for your needs.

Practice

1. Which of the following best describes the main use case for Elasticsearch compared to relational databases?

easy

A. Elasticsearch is optimized for fast full-text search and analytics.

B. Elasticsearch is mainly used for storing structured transactional data.

C. Elasticsearch uses SQL to manage complex relationships between tables.

D. Elasticsearch is designed for batch processing of large datasets only.

Solution

1. Step 1: Understand Elasticsearch's purpose

   Elasticsearch is built for fast full-text search and analytics on large volumes of data.

2. Step 2: Compare with relational databases

   Relational databases focus on structured data and transactional integrity, not optimized for text search.

3. Final Answer:

   Elasticsearch is optimized for fast full-text search and analytics. -> Option A

4. Quick Check:

   Elasticsearch = fast text search [OK]

Hint: Elasticsearch = search and analytics, relational = structured data [OK]

Common Mistakes:

• Confusing Elasticsearch with relational database transactional features
• Thinking Elasticsearch uses SQL for queries
• Assuming Elasticsearch is only for batch processing

2. Which query language is primarily used to interact with relational databases?

easy

A. XPath

B. JSON Query DSL

C. SQL

D. GraphQL

Solution

1. Step 1: Identify relational database query language

   Relational databases use SQL (Structured Query Language) to manage and query data.

2. Step 2: Compare other options

   JSON Query DSL is for Elasticsearch, XPath is for XML, GraphQL is a query language for APIs.

3. Final Answer:

   SQL -> Option C

4. Quick Check:

   Relational DBs use SQL [OK]

Hint: Relational DBs use SQL, Elasticsearch uses JSON DSL [OK]

Common Mistakes:

• Confusing JSON Query DSL with SQL
• Thinking XPath or GraphQL are used for relational databases
• Mixing query languages between technologies

3. Given the following Elasticsearch query snippet, what is the main purpose of this query?

{"query": {"match": {"title": "Elasticsearch"}}}

medium

A. Update documents with the title 'Elasticsearch'.

B. Create a new document with title 'Elasticsearch'.

C. Delete documents where the title is exactly 'Elasticsearch'.

D. Retrieve documents where the title field contains the word 'Elasticsearch'.

Solution

1. Step 1: Analyze the query structure

   The query uses "match" on the "title" field with the term "Elasticsearch" which searches for documents containing that word.

2. Step 2: Understand Elasticsearch query actions

   Queries retrieve data; update, delete, and create require different API calls.

3. Final Answer:

   Retrieve documents where the title field contains the word 'Elasticsearch'. -> Option D

4. Quick Check:

   Match query = search documents [OK]

Hint: "match" queries search text fields in Elasticsearch [OK]

Common Mistakes:

• Thinking the query modifies data instead of searching
• Confusing match with exact match or update operations
• Assuming it deletes or creates documents

4. Identify the error in this SQL query compared to Elasticsearch query style:

SELECT * FROM products WHERE name = 'laptop'

medium

A. SQL uses JSON format, so this query is invalid.

B. Elasticsearch does not use SQL syntax; it uses JSON-based queries.

C. The query is missing a JOIN clause required in Elasticsearch.

D. The query should use double quotes around 'laptop' in SQL.

Solution

1. Step 1: Understand query languages

   SQL uses its own syntax; Elasticsearch uses JSON Query DSL, not SQL syntax.

2. Step 2: Identify the mismatch

   The given query is valid SQL but invalid for Elasticsearch which requires JSON format queries.

3. Final Answer:

   Elasticsearch does not use SQL syntax; it uses JSON-based queries. -> Option B

4. Quick Check:

   Elasticsearch queries = JSON, not SQL [OK]

Hint: Elasticsearch queries are JSON, not SQL [OK]

Common Mistakes:

• Assuming Elasticsearch supports SQL syntax natively
• Thinking JOINs are mandatory in Elasticsearch queries
• Confusing string quoting rules between SQL and JSON

5. You need to build a system that supports complex transactions with multiple table joins and strict data integrity. Which database type should you choose and why?

hard

A. Relational database, because it supports ACID transactions and joins.

B. Relational database, because it is optimized for full-text search.

C. Elasticsearch, because it stores data in JSON and is schema-less.

D. Elasticsearch, because it handles complex joins efficiently.

Solution

1. Step 1: Identify requirements

   The system needs complex transactions, multiple joins, and strict data integrity.

2. Step 2: Match requirements to database features

   Relational databases provide ACID transactions and support joins, ensuring data integrity.

3. Step 3: Compare Elasticsearch capabilities

   Elasticsearch is optimized for search and analytics, not transactional integrity or joins.

4. Final Answer:

   Relational database, because it supports ACID transactions and joins. -> Option A

5. Quick Check:

   Transactions + joins = Relational DB [OK]

Hint: Use relational DB for transactions and joins, Elasticsearch for search [OK]

Common Mistakes:

• Choosing Elasticsearch for transactional systems
• Confusing JSON storage with transactional support
• Assuming Elasticsearch supports complex joins