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.