Where the data actually lives

Sooner or later the box marked “database” has to open. Inside, nearly every store you will ever meet is one of two machines — and knowing which one you are holding lets you predict its behaviour instead of discovering it in production.

The B-tree, mechanically

Postgres, MySQL/InnoDB, and most of the relational world keep rows in fixed-size pages (commonly 8–16 KB) arranged as a shallow, wide tree — three or four levels reach billions of keys, so any lookup is a handful of page reads, mostly cached. Updates happen in place: find the page, modify it — but first write the change to the write-ahead log and fsync (Foundations’ third lie, industrialised): sequential log writes make random page writes safe to defer, and crash recovery replays the log. The personality that follows: steady, predictable reads; writes that pay a read (find the page) plus eventual page I/O; and fifty years of operational tooling.

The LSM tree, mechanically

RocksDB, Cassandra, and the engines under most write-heavy systems refuse to update in place. Writes append to an in-memory memtable (plus a WAL for durability); full memtables flush to disk as sorted immutable files (SSTables); background compaction merges files so reads stay sane. Reads check memtable, then files newest-first — with a Bloom filter per file (a tiny probabilistic “definitely not here / maybe here” structure) skipping most of them. The costs live on a triangle you can now name: write amplification (compaction rewrites each datum several times), read amplification (multiple places to look), and space amplification (obsolete versions await compaction) — tiered compaction favours writes, leveled favours reads and space; tuning LSMs is choosing a point on that triangle.

The decision rule survives contact with vendors: read-heavy, update-in-place, transactional → B-tree family. Ingest-heavy, append-mostly, huge working sets → LSM family. Say it from the workload’s read/write mix and you will out-predict the benchmark pages.

Indexes — buying reads with writes

A secondary index is one more sorted structure, keyed by the column you search, pointing at the rows. That one sentence carries the whole economics: reads stop scanning (O(log n) instead of O(n)); every write now maintains one more structure. The refinements that pay rent weekly: a composite index on (a, b) serves a and a,b but not b alone — column order is a design decision; a covering index that contains every selected column answers the query without touching the table at all; low-selectivity columns (status with three values) rarely deserve an index — the planner will rightly ignore it. And the professional habit that makes the database a machine instead of a mood: read the query plan. It tells you what the engine actually did — scan vs index, join order, row estimates — and a wildly wrong row estimate usually means stale statistics, not a broken optimiser.

Choosing a store without theology

Note what the table is really saying: “SQL vs NoSQL” was never the question. The question is the access pattern — and the row/column split (OLTP vs OLAP) matters more than any brand name on it.