Menu

Your primary key is a storage decision
📰

Your primary key is a storage decision

DEV Community: postgres·Ajitkumar Singh·14 days ago
#tHpowr1J
#devtopostgresql#devto#dev#tree#compaction
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.