PostgreSQL 17 VACUUM Overhaul
Most developers know VACUUM reclaims dead tuples. Few know how it tracks them — until PostgreSQL 17, a hardcoded 1GB ceiling silently crippled autovacuum on busy tables.
PostgreSQL 17 replaces the flat array with an adaptive radix tree (ART) based TIDStore, cutting memory use up to 20x and collapsing multiple index vacuum phases into one.
The Old Way: Flat Array, 1GB Cap
Pre-17 used a simple ItemPointerData array. Each dead tuple costs ~6 bytes. Even with maintenance_work_mem = 4GB, vacuum silently stopped at 1GB, then vacuumed indexes, released the array, and resumed — repeating for every 1GB chunk.
This staccato behavior appears in pg_stat_progress_vacuum as index_vacuum_count incrementing repeatedly.
The New Way: TIDStore + ART
PostgreSQL 17 introduces TIDStore built on adaptive radix trees (Leis et al., 2013). The tree dynamically chooses node fanout (4, 16, 48, 256) based on key density.
- TIDStore: typed wrapper around ART storing ItemPointerData keys
- DSA allocation: memory from maintenance_work_mem without 1GB cap
- Sorted iteration: TIDs emit in (block, offset) order
Memory Comparison
| Scenario | Old Array | ART TIDStore |
|---|---|---|
| 10M dead tuples | ~60 MB | ~3-5 MB |
| 100M dead tuples | ~600 MB (capped) | ~35-50 MB |
| 1B dead tuples | Impossible | ~350-500 MB |
Real-World Impact
pganalyze benchmark (100M rows, 3.4 GB, maintenance_work_mem = 64 MB):
- PG16: 9 index vacuum phases, 773s
- PG17: 1 index vacuum phase, 619s (20% faster)
Progress columns renamed: max_dead_tuples -> max_dead_tuple_bytes, num_dead_tuples -> num_dead_item_ids, new dead_tuple_bytes.
Why It Matters
- Autovacuum completes faster = less I/O, WAL, buffer churn
- Large tables vacuum in one pass
- Predictable memory use
- Monitoring reflects reality
Tuning for 17+
- maintenance_work_mem: 1-4 GB now works
- autovacuum_work_mem: increase for busy workers
- vacuum_buffer_usage_limit: default now 2 MB
Bigger Picture
ART now backs SLRU caches, ReorderBuffer eviction. PostgreSQL systematically replaces flat arrays with ART.
References: PostgreSQL 17 Release Notes, pganalyze blog, ART paper, Press Kit
Top comments (0)