Store five million rows of 120 bytes each. That is 572 MiB of data. How much does the database write to disk to store it? Anywhere from about twice that to about seventy times that, depending on two choices most teams make for other reasons.
The figures below come from the course's lab, a model with 8 KiB pages and five compaction levels rather than a benchmark of one particular database. The model is simple enough to reason about, which is the point.
| Layout | Bytes written | Amplification | Where the writes go |
|---|---|---|---|
| B-tree, sequential keys | 1,147 MiB | 2.0× | the write-ahead log, plus packed pages of 68 rows each |
| LSM tree, size-tiered compaction | 4,005 MiB | 7.0× | the log, the first flush, and five tier merges |
| LSM tree, leveled compaction | 24,033 MiB | 42.0× | the log, the flush, and about ten rewrites at each of four levels |
| B-tree, random keys | 39,635 MiB | 69.3× | the log, plus 39,062 MiB of dirty pages |
Why key order matters to a B-tree
With sequential keys, each insert lands on the last leaf page. The page fills up with 68 rows and is written once, full. With random keys, such as UUIDv4, consecutive inserts land on unrelated pages all over the tree, so storing one 120-byte row can mean writing a whole 8 KiB page. Same rows, same disk: about 35 times more writing, purely from the order in which keys arrive.
Why compaction matters to an LSM tree
An LSM tree writes sequentially and merges files in the background. How it merges sets the cost. Leveled compaction rewrites each byte roughly ten times at every level below the first, and in return a read touches about one file per level. Size-tiered compaction rewrites each byte about once per tier, and a read may check several same-size files per tier. Bloom filters let reads skip most of those files either way.
Common Mistake
Choosing UUIDv4 primary keys for a naming reason (globally unique, no coordination needed) without noticing it is also a storage decision. On a B-tree-backed table with heavy inserts, it can be the most expensive storage decision in the schema.
The fix comes with its own trade
Time-sortable identifiers such as UUIDv7, ULID and Snowflake IDs exist because random IDs destroy write locality. They restore it, and in doing so bring back the write hotspot: every insert now targets the newest page, or the newest partition once the table is sharded. That is a different problem, with different fixes. Which one you would rather have depends on the workload, and it is better chosen on purpose than inherited from a default.
