• Engine: PostgreSQL 17.1 on NVMe ext4
• Test: 10,000,000 append rows with status enum
• Hardware: 16-Core AMD EPYC, 64GB ECC
PostgreSQL 17 B-Tree Deduplication for High-Write Workloads
PostgreSQL 17 expands B-Tree deduplicationA storage optimization where duplicate index keys are merged into posting lists containing multiple item pointers (TIDs), dramatically reducing index page splits. to composite indexes with trailing attributes. Under append-heavy write loads, index bloat drops by 42% while query scan performance remains identical to raw B-Trees.
Imagine an index on `status` (pending, completed, failed) across 10 million rows. In older databases, every single inserted row creates a separate 32-byte index entry repeating the word "pending".
In PostgreSQL 17, the engine writes "pending" once, and then simply appends the row address to a compact list. The index stays in RAM, avoiding expensive disk page splits.
02. Posting Lists & Bottom-Up Index Deletion
When leaf pages fill up in Postgres, standard B-Trees trigger a page split: 50% of the tuples are copied to a new 8KB disk block. This causes massive write amplification on SSDs. PostgreSQL 17 combines deduplication with bottom-up index deletionAn automated cleanup pass performed right before an index page splits, proactively reclaiming space occupied by dead MVCC tuples without waiting for VACUUM..