Below every SQL API, below every transaction manager, sits a storage engine. B+ trees for read-heavy OLTP (PostgreSQL, MySQL), LSM trees for write-heavy workloads (RocksDB, Cassandra, TiKV), and columnar formats for analytics (Parquet, ClickHouse, DuckDB) — three fundamentally different structures with three fundamentally different performance profiles. Understanding which fits which workload — and why — is the specific competence that turns "our database is slow" into "our storage engine mismatches our workload; here\'s the specific fix."
Every database — SQL, NoSQL, analytics, time-series, graph — has a storage engine underneath its query API. The storage engine is the specific data structure that organizes bytes on disk (or in memory), handles reads and writes, manages durability, and provides indexes. PostgreSQL uses B+ trees. MySQL InnoDB uses B+ trees. SQL Server uses B+ trees. Cassandra uses LSM trees. RocksDB uses LSM trees. TiKV, YugabyteDB, and CockroachDB use RocksDB underneath. ClickHouse and DuckDB use columnar formats. Snowflake uses columnar over object storage. Each storage engine has a specific write amplification (how many bytes actually get written per byte of user data), read amplification (how many bytes must be read to serve a user read), and space amplification (how much disk space is used per byte of user data). These three amplification factors — plus the compaction/checkpoint model — determine the storage engine\'s workload fit: a B+ tree is efficient for random reads but suffers under heavy random writes; an LSM tree is efficient for high write throughput but suffers under range scans with many overwrites; a columnar format is efficient for analytical scans but wasteful for OLTP row lookups. Choosing the wrong engine for a workload produces production issues that no amount of query tuning can fix.
The specific engineering task M.53 addresses is understanding these three storage engines precisely enough to (a) diagnose which one your existing database uses, (b) predict how it will behave under your workload, and (c) know when to choose a different one. This isn\'t rare knowledge for senior engineers — it\'s standard competence. When a PostgreSQL database hits a write throughput ceiling, the specific mechanism is B+ tree page contention plus buffer pool eviction plus WAL fsync latency. When a Cassandra cluster shows read latency spikes, the specific mechanism is LSM compaction stalling reads plus tombstone accumulation plus SSTable-count explosion. When a ClickHouse query is 100× faster than the equivalent PostgreSQL query for a specific analytical pattern, the specific mechanism is columnar layout plus vectorized execution plus SIMD-friendly compression. Each observation has a specific storage-engine mechanism as its explanation, and knowing these mechanisms is what turns "the database is slow" into "here\'s the specific fix and here\'s why it works".
Each earlier attempt fails specifically. Framework defaults miss workload-specific leverage. Query-layer-only tuning hits ceilings the storage engine imposes. Rolling your own underestimates the specialized engineering already done. The Expert pattern: analyze workload characteristics, match to the storage engine designed for that profile, use different engines for different workloads, understand the specific tradeoffs each engine makes. §02 walks through B+ trees vs LSM trees mechanically. §03 covers columnar plus the universal WAL mechanism. §04 lets you explore all three across three workload types.
The historical arc of storage engines is specifically about accumulating engineering wisdom for specific workloads. 1972: Bayer & McCreight publish B-tree paper. The fundamental self-balancing tree structure that dominates database indexes for the next fifty years. Later evolved into B+ trees (all data in leaves, internal nodes contain only keys) which are the specific variant used by all modern OLTP databases. Simple, elegant, well-understood, extensively optimized. 1990s: B+ trees dominate OLTP. PostgreSQL, MySQL InnoDB, SQL Server, Oracle — all use B+ trees as their primary index structure. Buffer pool management, write-ahead logging, and MVCC are all built on top. Assumption: reads dominate writes, random access is common, data fits (mostly) in memory. Works well for a large class of workloads. 1996: O\'Neil, Cheng, Gawlick, and O\'Neil publish "The Log-Structured Merge-Tree" (LSM tree paper). Recognizing that B+ trees are inefficient for write-heavy workloads (in-place updates require random writes, page splits cause write amplification), the LSM tree proposes append-only writes to sorted runs, merged in background. Specific insight: convert random writes into sequential writes at the cost of read amplification, then compact in the background to keep read amplification bounded. 2004: Google publishes Bigtable paper. Uses LSM at massive scale (Google\'s specific workload: web crawl, indexing, analytics on trillions of rows). Basis for HBase (Facebook rewrite), Cassandra (Facebook then Apache), and subsequent NoSQL systems. LSM proves out in production at unprecedented scale. 2006: Cassandra released. LSM-based distributed database, borrows from Bigtable + Dynamo. Popularizes LSM in open source. Write throughput becomes a defining feature — Cassandra can sustain millions of writes per second per cluster while B+ tree databases plateau at tens of thousands. 2011: LevelDB open-sourced by Google. Extracted LSM engine from Chrome. Small, embeddable, high-quality reference implementation. Immediately adopted for embedded storage in many systems. 2013: Facebook releases RocksDB. Fork of LevelDB, optimized for server workloads. Adds column families, transactions, checkpointing, tunable compaction. Becomes the reference LSM engine — used by TiKV, YugabyteDB, CockroachDB, ArangoDB, MongoDB\'s WiredTiger competitor, and countless embedded uses. RocksDB is the specific storage engine underneath more modern distributed databases than any other single project. 2013: Apache Parquet released. Columnar file format for Hadoop ecosystem. Inspired by Google\'s Dremel paper (2010). Enables analytical scans over massive datasets on cheap storage. Basis for modern data lakes. 2016: Apache Arrow. In-memory columnar format. Enables zero-copy sharing between systems and vectorized execution across languages. Basis for DuckDB, Polars, modern analytical engines. 2020s: Storage engine as a first-class architectural component. Distributed SQL databases explicitly declare their storage engines (CockroachDB uses Pebble — Go rewrite of RocksDB — since 2020). Analytical databases explicitly use columnar (ClickHouse, DuckDB, Snowflake). Specialized workloads use specialized engines (TimescaleDB\'s hypertables for time-series, DuckDB\'s vectorized-in-process for analytics). The specific competence: understand which engine your database uses, why, and when to choose differently. The arc explains why "storage engines are one thing" turned into "storage engines are a menu of specific structures with specific workload fits, and choosing the right one is Expert-tier data engineering."
B+ trees and LSM trees are the two dominant storage engine structures for transactional workloads, and they represent fundamentally opposed answers to the same question: how do you keep data sorted on disk while still handling writes efficiently? B+ trees answer with in-place updates in sorted pages — the tree stays balanced and sorted through all operations, at the cost of expensive writes (random I/O, page splits, write amplification through the tree). LSM trees answer with append-only sorted runs plus background compaction — writes go to memory then flush sequentially to disk, sorted runs merge periodically to bound read amplification. Understanding the specific mechanics of each — where each has its performance ceiling, where each amplifies costs — is the specific competence that turns "we chose PostgreSQL" or "we chose Cassandra" from a defaults-driven decision into a workload-driven decision.
Traverse tree (root → internal → leaf, ~3-4 page reads). Load leaf page (random I/O, ~4-16KB read). Insert key. If page full, split into two pages, potentially propagating splits up. Log to WAL for durability. Eventually flush dirty page to disk. Cost: 1 leaf read + 1 leaf write + possible splits + WAL append. Typical write amplification: 2-5×.
Append to WAL (sequential write, ~1KB). Insert into MemTable (in-memory sorted structure, O(logN) memory op). Client ACK — done. In background: when MemTable is full, flush to disk as a new L0 SSTable (sequential write). Compaction later merges SSTables. Cost per write: 1 WAL append + memory op. Extremely fast writes.
Traverse tree (root → internal → leaf). Return the key from the leaf. Cost: logN page reads. If pages are in buffer pool (memory), essentially free. If pages must come from disk, ~logN random I/Os. Read amplification: near 1× (only the requested key is read from the leaf page, plus tree traversal). Fast for point lookups and range scans.
Check MemTable. Check L0 SSTables (unsorted, must check all — bloom filters help). Check L1, L2, ... SSTables (sorted by key, binary search or index lookup within each). Cost: 1 MemTable check + N L0 checks + logN L1+ checks. Read amplification: 5-20× depending on levels. Bloom filters reduce false positives dramatically.
Write amp: 2-5× (leaf update + tree splits + WAL). Read amp: ~1× (single leaf page read for point lookup). Space amp: 1.05-1.3× (fill factor of ~70% typical). Optimal for read-heavy workloads with moderate write rates. Ceiling: random I/O throughput of underlying storage; concurrent writes to same page cause lock contention.
Write amp: 10-30× (compaction rewrites data multiple times). Read amp: 5-20× (check multiple SSTables). Space amp: 1.1-2× (obsolete versions before compaction). Optimal for write-heavy workloads. Ceiling: compaction throughput; when compaction can\'t keep up with writes, "write stalls" pause the database entirely until compaction catches up.
The B+ tree write amplification (v) is worth understanding precisely because it\'s the specific mechanism that limits B+ tree databases under write-heavy workloads. Consider inserting a new key into a leaf page that\'s already 70% full. First, the page must be read into the buffer pool (if not already there — random I/O from disk). Then the key is inserted and the page is dirty. If the page fits, we\'re done — the dirty page is later flushed (another I/O). If the page is full, it splits: allocate a new page, redistribute keys, update the parent internal node to reference the new page, potentially cascading splits up to the root. Each split is a page allocation plus multiple page writes. Under sustained write load, splits become common and the write amplification climbs. Typical measured B+ tree write amplification: 2-5× under moderate load, 5-10× under heavy write load with high key entropy (random keys). Concurrent writes to the same page require exclusive locks, producing contention that further limits throughput. The specific ceiling: a well-tuned PostgreSQL B+ tree on modern NVMe SSD sustains ~50,000-100,000 random writes per second per node; beyond this, throughput degrades due to WAL fsync latency, buffer pool contention, and vacuum/autovacuum overhead. For workloads exceeding this, LSM engines like RocksDB are 10-100× faster.
The LSM tree compaction (vi) is the specific and defining feature of LSM engines — the background process that keeps read amplification bounded by merging SSTables. Two dominant compaction strategies. Leveled compaction (RocksDB default, HBase): SSTables organized into levels L0, L1, L2, ..., LN with each level ~10× larger than the previous. When L1 exceeds its size limit, one SSTable from L1 is merged with all overlapping SSTables in L2, producing new L2 SSTables. Aggressive read optimization — each level has non-overlapping SSTables (except L0), so lookups check at most one SSTable per level. Cost: high write amplification (each key can be rewritten O(logN) times as it moves through levels). Best for read-heavy LSM workloads. Tiered/size-tiered compaction (Cassandra default): SSTables grouped by size; when N SSTables of similar size accumulate, they\'re merged into one larger SSTable. Less write amplification (each key rewritten fewer times) but higher read amplification (more SSTables overlap per lookup). Best for write-heavy LSM workloads. The specific tuning discipline: choose compaction strategy based on read/write ratio, tune level count and size ratios, monitor compaction throughput vs write rate, alert on compaction lag. Compaction that can\'t keep up produces "write stalls" — RocksDB will slow client writes to prevent SSTable count from exploding. Real production LSM incidents almost always involve compaction: either misconfigured (wrong strategy), under-resourced (insufficient background thread count), or over-triggered (too-small SSTables create compaction storms).
The read amplification of LSM (iv) deserves specific attention because it\'s counterintuitive — LSM engines can serve reads reasonably fast despite checking multiple SSTables. The specific techniques that make this work: Bloom filters. Each SSTable has a bloom filter — a probabilistic data structure that answers "does this SSTable definitely NOT contain key X?" with certainty (no false negatives) but "might contain key X" with configurable false-positive rate (typically 1%). For a point lookup, a well-tuned bloom filter means checking only 1-2 SSTables on average despite having 5-20 levels. Block cache. Recently-accessed SSTable blocks are cached in memory. Hot data effectively lives in memory; cold data pays disk I/O. Similar to B+ tree buffer pool but for SSTable blocks. Compaction-driven consolidation. Compaction merges overlapping SSTables, reducing the effective read amplification for hot key ranges. Frequently-updated ranges get compacted more, so lookups on them check fewer SSTables. Level-aware indexing. Each SSTable has a sparse index (typically 1 entry per ~64KB block) allowing binary search within the SSTable. Combined with bloom filter negative-check, a "hit" typically requires one binary search + one block read. Real-world LSM read latency: ~50-200μs for uncached hot data on modern NVMe, competitive with B+ trees for random access. Range scans are slower than B+ trees because you must merge streams from multiple SSTables, but tolerable for most workloads. Understanding these techniques is what turns "LSM has high read amp" into "LSM read amp is bounded and mitigated through specific engineering; here\'s what to monitor and tune".
Columnar storage is the third dominant storage engine structure, optimized for a specific workload class distinct from OLTP: analytical scans that aggregate over many rows but only touch a few columns. Where B+ trees and LSM trees store rows contiguously (row-oriented layout), columnar formats store each column contiguously (column-oriented layout). This structural difference produces order-of-magnitude performance differences on analytical queries — a query like SELECT AVG(amount) FROM sales WHERE year = 2024 reads only 2 of 50 columns in a columnar format vs all 50 columns in a row-oriented format. Combined with column-specific compression (dictionary encoding, RLE, delta encoding) and vectorized execution (SIMD operations on column batches), columnar engines like ClickHouse, DuckDB, Snowflake, and Parquet-on-Spark achieve 10-100× faster analytical query performance than row-oriented databases on the same hardware. Meanwhile, WAL (Write-Ahead Log) is the universal durability mechanism across all storage engines — the specific append-only log that ensures crash recovery is possible for every write.
Data organized column-by-column instead of row-by-row. Reading one column doesn\'t require reading others. For queries touching few columns of many rows (analytical scans), reads only the needed columns from disk — often 10-100× less I/O than row-oriented. Enables SIMD-friendly column-wise compression and vectorized execution over batches of column values.
Each column stored with type-specific compression. Dictionary encoding: for low-cardinality columns (country, status), replace values with dictionary indices — often 10-50× compression. Run-length encoding (RLE): for repeated values, store (value, count) pairs. Delta encoding: for sorted/near-sorted columns, store differences from previous value. Combined, columnar achieves 5-30× smaller storage vs row-oriented.
Query operators process batches of column values (typically 1024-8192 at a time) using SIMD CPU instructions. Instead of processing one row at a time (row-oriented), process a vector of column values. Modern CPU SIMD (SSE, AVX-512) applies operations to 4-16 values in parallel per cycle. Vectorization + columnar layout produces 10-100× analytical query speedup on same hardware.
Row assembly for OLTP lookups is expensive — reading one row means fetching one value from each column\'s storage. Writes are slow (must update multiple column files). Point lookups are slow. Small transactions with mixed operations are inefficient. Columnar is a specific tool for a specific workload — analytical scans — and misapplying it to OLTP produces catastrophic performance.
Universal durability mechanism across all storage engines. Sequential append-only log persisted before any change to the actual storage. Client write → WAL append → fsync → ACK. On crash: replay WAL from last checkpoint. Fast because sequential I/O + no locking. Every write in every storage engine (B+ tree, LSM, columnar) goes through WAL first.
WAL optimization: instead of fsync per write (100 writes → 100 fsyncs), batch multiple writes into single fsync (100 writes → 1 fsync). Each fsync ~100μs on NVMe; batching produces 10-100× throughput improvement. Trade-off: individual write latency slightly higher (waits for batch). Standard in PostgreSQL, MySQL, RocksDB, and every serious storage engine.
The columnar layout advantage (i) is worth understanding precisely because it drives virtually all modern analytical databases. Consider a sales table with 50 columns and 100 million rows, and the query SELECT AVG(amount) FROM sales WHERE year = 2024. In a row-oriented engine, executing this query requires reading each row (all 50 columns) from disk, extracting the year and amount columns, filtering by year, and averaging. Total I/O: 100M rows × 50 columns × ~20 bytes/column ≈ 100GB read. Even with indexes, the leaf pages contain full rows and must all be scanned. In a columnar engine, only two columns are read: year (100M × 4 bytes = 400MB) and amount (100M × 8 bytes = 800MB) — total 1.2GB, ~80× less I/O. Additionally, both columns are compressed — year (integer, likely low cardinality — 5-10 distinct values) compresses to ~10MB via dictionary encoding; amount compresses to ~200MB. Actual disk I/O: ~210MB — nearly 500× less than row-oriented. Add vectorized execution using SIMD instructions and the query runs 100× faster than PostgreSQL on the same hardware. This 100-1000× speedup for analytical queries is why every modern data warehouse (Snowflake, BigQuery, Redshift, ClickHouse, DuckDB) uses columnar storage. It\'s not a marginal optimization; it\'s a fundamental structural advantage for the analytical workload class.
The WAL mechanism (v) is the specific engineering trick that makes every serious storage engine crash-safe without sacrificing write throughput. The problem WAL solves: to ensure a write is durable, it must be on disk before the client is told "committed." But writing to the actual storage structure (updating a B+ tree page, updating a MemTable + eventually flushing) involves random I/O and complex data structures that are slow to persist synchronously. WAL\'s solution: write a sequential append-only log entry first (fast — sequential I/O + fsync), then update the actual storage lazily later. Client sees: write → WAL append → fsync → ACK. The actual storage update happens in background. On crash, recovery replays WAL from the last checkpoint, reconstructing any writes that weren\'t yet applied to the storage. This gives durability (write survives crash) at the cost of one sequential I/O + fsync per write. Modern NVMe fsync: ~100μs. So per-write durability cost: ~100μs. But group commit batches multiple concurrent writes into a single fsync — 100 concurrent writes = 1 fsync at ~100μs instead of 100 fsyncs at ~10ms total. This is why PostgreSQL, MySQL, and RocksDB can all sustain tens of thousands of writes per second per node while maintaining crash safety. WAL + group commit is the specific engineering pattern; understanding it precisely is what distinguishes "we use PostgreSQL" from "we understand what \'synchronous_commit=on\' actually costs and when to relax it for throughput."
The vectorized execution (iii) is the specific execution model that combines with columnar layout to produce the analytical speedup. Traditional row-at-a-time execution (Volcano model, used by PostgreSQL and most OLTP engines) processes one row through the query operators one at a time — next_row = filter.next(); result = aggregate.process(next_row); return next_row. Each row incurs function call overhead, branch prediction misses, and cache thrashing. Modern vectorized execution (used by ClickHouse, DuckDB, Snowflake) processes batches of column values through operators — batch = filter.next_batch(); aggregate.process_batch(batch); return batch. A batch is typically 1024-8192 column values. This produces specific efficiencies: (a) function call overhead amortized over 1000+ values; (b) tight loops enable auto-vectorization by the compiler using SIMD (AVX2/AVX-512 for x86, NEON for ARM); (c) cache-friendly access patterns — sequential column values fit in L1/L2 cache; (d) branch prediction becomes trivial because branches are batch-wide, not per-value. Combined result: modern vectorized columnar engines like DuckDB and ClickHouse achieve close to memory bandwidth throughput for aggregations — often 1-10 billion values processed per second per core, compared to ~10 million rows per second for a well-tuned PostgreSQL query. This is why analytical workloads should use analytical engines, not OLTP engines — the structural difference is a two-order-of-magnitude speedup that no query tuning can overcome.
Below: each of three storage engines (B+ tree · LSM tree · Columnar) evaluated against three workload profiles (Write-heavy OLTP · Read-heavy OLTP · Analytical scans). Watch how each engine fits or fails each workload — the sharp diagonals show exactly which engine suits which workload, and the off-diagonals show the specific performance costs. This is the matrix Expert engineers implicitly consult when choosing databases.
The failure modes of storage engines are the specific mechanisms by which "we chose PostgreSQL" turns into "PostgreSQL can\'t sustain our write rate" or "our Cassandra cluster is unusable due to compaction storms" or "our analytical query takes 30 minutes instead of 300 milliseconds." Each of these anti-patterns is a real pattern with a specific mitigation that Expert engineers deploy by default. Recognizing them at architecture time avoids the migration under time pressure.
LSM engines have inherently variable read latency due to compaction and multi-SSTable lookups. For workloads where p99 read latency matters more than write throughput, LSM is the wrong choice. The specific mechanism: reads check MemTable + multiple SSTables. Bloom filters help but don\'t eliminate the cost. During compaction, read latency spikes as SSTables are being rewritten. In steady state, well-tuned LSM p50 is ~1ms; but p99 during compaction can be 100-500ms. For a session store where consistent low latency matters more than 100K writes/sec, a B+ tree engine (PostgreSQL) or in-memory store (Redis) is the correct choice. The fix: (a) migrate to PostgreSQL/MySQL for read-latency-critical OLTP; (b) use Redis for hot session data with periodic snapshots to persistent storage; (c) if staying on LSM, tune compaction aggressively (leveled compaction, smaller L0, more compaction threads) — helps but doesn\'t eliminate the p99 tail; (d) accept the p99 latency if write throughput requirements dominate. The general principle: LSM optimizes write throughput at the cost of read latency variance; use it only when write throughput is the constraint.
B+ trees have specific write throughput ceilings driven by page contention, write amplification, and vacuum overhead. Beyond these ceilings, no tuning helps — the structural bottleneck is the tree itself. Random writes cause page splits, which propagate up the tree and require exclusive locks. Concurrent writes to the same page block. Vacuum reclaims dead tuples but adds background load. Well-tuned PostgreSQL on modern NVMe sustains ~50-100K random writes/sec per node; beyond this, structural limits dominate. The fix: (a) for time-series or append-only workloads, use LSM engines (RocksDB, TimescaleDB\'s hypertables) — 5-20× higher write throughput; (b) partition data across multiple B+ trees (sharding) — throughput scales linearly with shards; (c) use write-optimized index types (BRIN for time-series columns, hash for point lookups); (d) batch writes to reduce lock contention; (e) migrate write-heavy tables to a different engine while keeping OLTP on B+ tree. The general principle: B+ trees have hard write throughput ceilings around 100K/sec/node; for higher throughput per node, choose LSM.
Columnar formats are specifically optimized for analytical scans (few columns, many rows) and are catastrophically inefficient for OLTP (many columns, single row). Row assembly requires random access to each column\'s storage — the opposite of what columnar layouts optimize for. Point lookups can be 100-1000× slower than a B+ tree. Writes are equally bad (must update multiple column files, disrupt compression). The fix: (a) use OLTP engines (PostgreSQL, MySQL) for transactional workloads with single-row reads/writes; (b) use columnar engines (ClickHouse, DuckDB, Snowflake) only for analytical workloads with aggregation over many rows; (c) use ETL/CDC to replicate OLTP data to columnar analytics store — best of both; (d) modern databases like DuckDB support both by allowing OLTP-friendly single-row operations in-memory with columnar disk layout, but this is a special case. The general principle: match engine class to workload class — OLTP goes to row-oriented, analytics goes to columnar; mixing produces catastrophic performance.
LSM engines require ongoing compaction tuning; when write rate exceeds compaction throughput, SSTable counts grow and reads slow catastrophically. Specific mechanism: writes create L0 SSTables faster than compaction can merge them. RocksDB has configurable stall thresholds (e.g., stop writes if L0 has >30 SSTables) — beyond these, client writes are throttled or paused entirely. Root cause is usually: (a) too few compaction threads; (b) compaction strategy mismatch (using tiered when leveled is needed, or vice versa); (c) I/O throughput ceiling (SSD saturated by compaction reads/writes); (d) memory pressure limiting MemTable size. The fix: (a) increase compaction thread count (RocksDB max_background_compactions parameter); (b) switch to leveled compaction if reads are latency-sensitive; (c) increase L0 SSTable size to reduce compaction frequency; (d) provision more I/O bandwidth (bigger NVMe, more disks); (e) monitor compaction lag as a first-class metric — alert when SSTable count exceeds thresholds. The general principle: LSM compaction is a background process that must keep pace with writes; monitor and tune compaction as a first-class operational concern.
B+ tree databases depend heavily on the buffer pool (cache of hot pages in memory) — when working set exceeds buffer pool, every query becomes disk-bound and latency degrades by 10-100×. Specific mechanism: PostgreSQL\'s shared_buffers holds cached B+ tree pages; if a query needs a page not in cache, it triggers disk I/O (~100μs on NVMe vs ~10ns memory access — 10000× slower). Buffer pool hit ratio dropping from 99% to 40% means 60% of page accesses now hit disk instead of memory — catastrophic latency degradation. The fix: (a) size buffer pool to fit working set — typically 25-50% of total system memory (PostgreSQL shared_buffers) or 50-75% (MySQL InnoDB buffer_pool_size); (b) monitor buffer pool hit ratio as first-class metric — alert when it drops below 95%; (c) if working set exceeds memory, consider vertical scaling (more RAM) or partitioning (shard data across nodes so each node\'s working set fits); (d) use SSD/NVMe for storage to reduce the disk-hit cost; (e) for very large working sets, consider LSM engines (which handle disk-bound workloads better due to sequential I/O). The general principle: B+ tree databases assume the working set fits in memory; when it doesn\'t, provision more memory or partition to keep working set per-node in memory.
The composite pattern across all five is that storage engine performance is dominated by structural fit to workload, not by hardware or configuration alone. A B+ tree on premium NVMe with abundant RAM will still hit its write throughput ceiling on write-heavy workloads. An LSM with unlimited compaction threads will still have p99 read latency spikes. A columnar engine with fastest CPU will still be slow for OLTP row lookups. The specific engineering discipline: analyze workload characteristics (read/write ratio, access patterns, latency requirements, data size, growth rate) at architecture time; choose the storage engine designed for that profile; monitor for workload drift over time; be willing to migrate engines when workloads change fundamentally. Modern polyglot persistence — PostgreSQL for OLTP + ClickHouse for analytics + Redis for hot state + object store + Parquet for archival — reflects this discipline in practice.
The terms that show up in every database architecture review, every "why is our database slow?" investigation, every storage engine benchmark comparison.
Test the engines. Click an answer; explanation drops in instantly.
Perfect. B+ tree sorted pages, LSM MemTable + SSTables + compaction, columnar layout + vectorized execution, WAL + group commit — the specific structures determining every database\'s throughput and latency. Next up: M.54, hardware-aware system design.
The composite understanding that turns "we use database X" into "we understand the specific storage engine mechanisms determining our database\'s performance profile."
Every database sits on a storage engine — B+ tree, LSM, or columnar. Each has specific write amplification, read amplification, space amplification, and compaction characteristics. These determine throughput and latency BEFORE any query optimizer runs. Choosing the wrong engine for a workload produces performance issues that no query tuning can fix.
B+ trees pay for writes (in-place updates + page splits) to keep reads cheap (logN, ~1× read amp). LSM trees pay for reads (multiple SSTables + bloom filter mitigation) to keep writes cheap (sequential MemTable + WAL). Columnar pays for OLTP (row assembly) to keep analytics fast (100× less I/O + vectorized). The workload\'s read/write ratio and access pattern picks the winner.
Real production data architectures use different engines for different workloads. PostgreSQL for OLTP + ClickHouse for analytics + Redis for hot state + Parquet on S3 for archival. Each optimized for its specific workload class. Understanding which engine fits which workload — and using them together — is the specific Expert-tier competence for modern data engineering.