LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction

1. First-Principles Mental Model & Relational Mechanics

Every SQL statement in LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction compiles down to a deterministic tree of relational algebra operators acting on physical pages in the buffer pool. At the logical layer, Turning random writes into sequential appends via an in-memory skip-list MemTable, immutable sorted SSTable runs, Bloom filters, and leveled/tiered compaction. Rather than mutating global variables imperatively, declarative SQL specifies the mathematical properties of the desired output relation while delegating physical access path selection to the cost-based query optimizer.

When the database engine processes lsm-trees-memtable-sstable-compaction within Storage Engine Internals: Pages, Buffer Pool, WAL & LSM-Trees, it enforces strict invariants over tuple domains, attribute visibility, and I/O page budgets. Understanding the boundary between logical relational semantics (what rows qualify) and physical execution (whether the engine performs a sequential heap scan, a B+Tree range seek, or an in-memory hash probe) is the defining skill of a Staff Database Engineer.

2. Database Table Schema, Execution Pipeline & Visual Architecture

Tracing how LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction flows through the parser, catalog binder, cost-based optimizer, and physical executor reveals exactly where latency and cardinality bugs originate:

• Stage 1 — Lexical Parsing & Catalog Binding (lsm-tree): The SQL parser builds an Abstract Syntax Tree (AST) and resolves every relation, column identifier, and data type against system catalog tables (such as pg_class, pg_attribute, or sqlite_master).
• Stage 2 — Logical Rewrite & Algebraic Simplification (sstable): The planner normalizes predicates, pushes selective filters as close to the storage scan as possible, and verifies whether Three-Valued NULL semantics permit subquery unnesting or join elimination.
• Stage 3 — Cost Estimation & Physical Operator Selection (compaction): Using histogram boundaries, Most Common Values (MCV), and Number of Distinct Values (NDV), the optimizer selects the minimum-cost physical plan and streams tuples through the execution pipeline.

3. Interactive Table-Level & Storage Engine Simulator

Use the interactive simulator below to step through LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction at the tuple and page level. Observe how input rows, intermediate filter/join states, NULL badges, and physical page reads evolve at each step:

4. Live WASM SQL Lab, Clause-by-Clause Visualizer & Automated Test Checker

Execute, edit, and test the production SQL query below directly in your browser using the embedded WebAssembly SQLite 3 engine. Click ▶ Run Query to execute, 🔍 Step Clauses to inspect the live intermediate table after FROM/JOIN, WHERE, GROUP BY, HAVING, WINDOW, SELECT, and ORDER BY, or ✓ Check SQL Tests to run automated relational assertions against the reference result:

-- Dataset: fintech_ledger | Double-Entry ACID Invariant & Ledger Conservation Audit
SELECT
  t.transfer_id,
  t.idempotency_key,
  t.status,
  COUNT(je.entry_id) AS journal_legs,
  SUM(CASE WHEN je.direction = 'debit' THEN je.amount_cents ELSE 0 END) AS total_debit_cents,
  SUM(CASE WHEN je.direction = 'credit' THEN je.amount_cents ELSE 0 END) AS total_credit_cents,
  SUM(CASE WHEN je.direction = 'credit' THEN je.amount_cents ELSE -je.amount_cents END) AS net_balance_cents
FROM transfers AS t
INNER JOIN journal_entries AS je
  ON je.transfer_id = t.transfer_id
GROUP BY t.transfer_id, t.idempotency_key, t.status
ORDER BY t.transfer_id ASC;

5. Cross-Engine Dialect & Cost Comparison Matrix

While the SQL ISO standard defines the logical behavior of LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction, production storage engines differ in how they execute, lock, and index the underlying tuples:

6. Live Verification, Catalog Introspection & Execution Plan Lab (SQL)

To verify how a production database engine executes LSM-Trees (RocksDB / Cassandra): MemTable, Immutable SSTables, Bloom Filters & Compaction across edge-case rows, run or modify the second live SQL verification query below. Inspect how GROUP BY buckets, window partitions, and Three-Valued NULL guards behave in real time:

-- Dataset: fintech_ledger | Double-Entry Conservation & Account Audit (lsm-trees-memtable-sstable-compaction)
SELECT
  a.account_id,
  a.owner_name,
  a.account_type,
  COUNT(je.entry_id) AS journal_postings,
  SUM(CASE WHEN je.direction = 'credit' THEN je.amount_cents ELSE 0 END) AS credit_cents,
  SUM(CASE WHEN je.direction = 'debit' THEN je.amount_cents ELSE 0 END) AS debit_cents,
  SUM(CASE WHEN je.direction = 'credit' THEN je.amount_cents ELSE -je.amount_cents END) AS computed_balance_cents,
  a.posted_balance_cents
FROM accounts AS a
INNER JOIN journal_entries AS je
  ON je.account_id = a.account_id
GROUP BY a.account_id, a.owner_name, a.account_type, a.posted_balance_cents
ORDER BY a.account_id ASC;

7. Production Failure Modes, Anti-Patterns & Staff Tuning Checklist

• Verify Execution Plans Early: Never guess what the planner will do—run EXPLAIN QUERY PLAN (SQLite) or EXPLAIN (ANALYZE, BUFFERS) (PostgreSQL) and confirm that estimated row counts match actual row counts within $2\times$.
• Guard Against Three-Valued NULL Traps: Audit every NOT IN, outer-join WHERE filter, and aggregate AVG/COUNT(col) expression for nullable columns; prefer NOT EXISTS and explicit COALESCE defaults.
• Keep Predicates SARGable: Never wrap an indexed column in a function, arithmetic expression, or implicit type cast on the left-hand side of a comparison operator.
• Bound Memory & Lock Scope: Ensure sorting, hashing, and batch mutations operate within bounded memory budgets (work_mem) and hold row/table locks for the shortest possible duration.