Complete Relational Database Architecture Visualizer

Master the SQL & Database Engine.

Interactive engines for B+ Tree index traversal, Composite Index Prefix rules, EXPLAIN cost plans, physical joins, ACID isolation anomalies, and analytical window functions.

Explore 20 Concepts SQL Playground

Database Physical Storage Engine

B+ Tree Index Traversal & Leaf Page Stepper

Step 1 of 4 • $O(\log N)$ Point Lookup

Search Target ID:

Root Page #1 (Disk Block: 0x01)8 KB Page

ptr_0 (< 30)[ Key: 30 ]ptr_1 (30 - 70)[ Key: 70 ]ptr_2 (> 70)

Branch Page #3 (Disk Block: 0x03)Internal Routing Page

ptr_0 (30 - 40)[ Key: 40 ]ptr_1 (40 - 60)[ Key: 60 ]ptr_2 (60 - 70)

Leaf Page #8 (Doubly-Linked with Page #7 and #9)Record Pointers

[ id: 41 ➔ Blk 11:1 ]⇄[ id: 45 ➔ Blk 12:4 ]⇄[ id: 52 ➔ Blk 14:2 ]

Step Narration:

45 > 30 and 45 <= 70. Follow pointer between 30 and 70 to Branch Page #3.

Query Optimization Rules

Composite Index & Leftmost Prefix Rule Evaluator

Index: ON users(country, status, created_at)

Active SQL Query:Index Scan (3 cols)

SELECT * FROM users WHERE country = 'US' AND status = 'active' AND created_at > '2026-01-01';

Composite Index Column Utilization:

Col 1: country

✅ B-Tree Active

Col 2: status

✅ B-Tree Active

Col 3: created_at

✅ B-Tree Active

Execution Verdict:

✅ Perfect match: Filters match index columns in exact leftmost order (country -> status -> created_at).

PostgreSQL Cost-Based Optimizer (CBO)

EXPLAIN (ANALYZE, BUFFERS) Plan Inspector

Cost Estimation & Execution Latency

psql explain output100,000 Rows Table

Index Scan using idx_orders_user_id on orders (cost=0.42..8.45 rows=2 width=32)
  Index Cond: (user_id = 942)
  Buffers: shared hit=3 (B-Tree) read=1 (Heap)
Planning Time: 0.110 ms
Execution Time: 0.082 ms

Optimizer Decision Analysis:

B-Tree Index Scan + Table Heap Fetch

Traverses the 3-level B-Tree index in 0.05ms, then fetches the exact 2 matching rows from the heap data page.

CBO Cost Formula:

Cost = (pages * seq_page_cost) + (tuples * cpu_tuple_cost)

Query Engine Execution Strategies

SQL Physical Joins Engine (Nested Loop, Hash, Merge)

Join Operator Optimization

Query: SELECT * FROM users u JOIN orders o ON u.id = o.user_id;hash-join

Phase 1: Build Phase (work_mem)

Hash Table created on smaller table (users):

• Bucket #42: [id: 1 ➔ Alice]

• Bucket #88: [id: 2 ➔ Bob]

Phase 2: Probe Phase

Streaming orders table and probing hash table:

✅ Match: order #101 (user_id: 1) ➔ Alice

✅ Match: order #102 (user_id: 2) ➔ Bob

ACID Transaction Isolation

Concurrency Anomaly & Isolation Level Stepper

🛡️ Required: READ COMMITTED (or higher)

Concurrency Timeline: Dirty Read AnomalyTime: T1

Session A (Transaction #1)Client 1

BEGIN;

Session B (Transaction #2)Client 2

BEGIN;

Step Narration:

Both transactions start concurrent sessions.

Step 1 of 5

PostgreSQL / InnoDB Storage Internals

MVCC Tuple Versioning & AutoVacuum Simulator

Readers Never Block Writers • Writers Never Block Readers

Physical Disk Block Page Tuples:

Tuple Version #1name: "Alice"

t_xmin: 100 (Created)t_xmax: 0 (Alive)ACTIVE

MVCC Engine Log:

Click "Execute UPDATE (TX 101)" to see how PostgreSQL creates new tuple versions without in-place overwrites.

Advanced Analytical SQL

Window Functions & Frame Partitions Visualizer

OVER (PARTITION BY dept ORDER BY salary DESC)

Click any row to inspect its partition window frame:Active: Row #2

Name	Department	Salary	ROW_NUMBER()	RANK()	DENSE_RANK()
Alice	Engineering	$120,000	1	1	1
Bob	Engineering	$120,000	2	1 (Tie)	1 (Tie)
Charlie	Engineering	$95,000	3	3 (Gap)	2 (No Gap)
Diana	Marketing	$90,000	1	1	1
Ethan	Marketing	$85,000	2	2	2

In-Browser Relational Engine

Interactive SQL Query Scratchpad & Planner

Tables: users, orders • Real-Time Engine

SQL Query Input:Press “Execute Query” to run

Presets:

Query Result Set:(3 rows returned)

Execution Time: 0.042 ms

name	role	product	total
Alice	admin	MacBook Pro	2499
Bob	developer	Mechanical Keyboard	150
Charlie	developer	4K Monitor	450

20 In-Depth Lessons

Relational algebra, B+ Tree leaf pages, Leftmost Prefix Rule, Window functions, and WAL crash recovery.

EXPLAIN Plan Optimizer

Dissect Seq Scan vs Index Scan vs Index-Only Scan with real buffer cache metrics.

Senior Flashcards

Master tough database interview questions on Write Skew, MVCC dead tuples, and composite indexes.