VACUUM running for 8 hours on your 500GB table while replication lag climbs and autovacuum is blocked by a long-running transaction — every PostgreSQL DBA has been here. PostgreSQL 17 shipped the most significant vacuum improvements in a decade. Here's what changed and how to tune it.
- PostgreSQL 17 introduces eager freezing that dramatically reduces full-table freezing vacuums
- Autovacuum now tracks vacuum-skipped tables and prioritizes them correctly
- The new vacuum delay tracking prevents autovacuum from starving under I/O pressure
- Monitor
pg_stat_user_tables.n_dead_tupand autovacuum lag as key production signals
Why VACUUM Matters in PostgreSQL
PostgreSQL uses Multi-Version Concurrency Control (MVCC) to provide consistent reads. Each UPDATE creates a new row version (dead tuple) while the old version remains until VACUUM reclaims it. Without regular vacuuming, dead tuples accumulate, bloating tables and indexes, and eventually triggering transaction ID wraparound — a database emergency that forces PostgreSQL offline.
Understanding Transaction ID Wraparound
-- Check how close each table is to wraparound
SELECT
schemaname,
relname,
n_dead_tup,
last_autovacuum,
age(relfrozenxid) AS xid_age,
2147483648 - age(relfrozenxid) AS xids_until_wraparound
FROM pg_stat_user_tables
JOIN pg_class ON relname = pg_stat_user_tables.relname
WHERE schemaname = 'public'
ORDER BY xid_age DESC
LIMIT 20;
-- Danger zone: xid_age approaching 200 million
-- Emergency: xid_age above 1.5 billion triggers forced vacuumPostgreSQL 17 Vacuum Improvements
Eager Freezing
Previously, PostgreSQL only froze tuple XIDs during periodic anti-wraparound vacuums (every 150M transactions). PostgreSQL 17 introduces eager freezing: when VACUUM visits a page that contains old-enough tuples, it freezes them immediately rather than waiting for the dedicated freeze vacuum. This spreads the freezing work across regular vacuums, reducing the intensity of periodic full-table freezes.
-- PostgreSQL 17: tune eager freezing aggressiveness
-- (age at which tuples become candidates for eager freezing)
ALTER TABLE events SET (vacuum_freeze_min_age = 5000000);
-- System-wide setting (postgresql.conf)
-- vacuum_freeze_min_age = 5000000 (default: 50000000)
-- vacuum_freeze_table_age = 100000000 (default: 150000000)
-- Lower these values to freeze more eagerly, reducing wraparound riskImproved Autovacuum Prioritization
-- PostgreSQL 17 tracks tables that needed vacuum but were skipped
-- Check autovacuum skip statistics
SELECT
schemaname,
relname,
n_dead_tup,
last_autovacuum,
last_autoanalyze,
autovacuum_count,
autoanalyze_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
-- Tables with high dead tuples and old last_autovacuum need attentionTuning Autovacuum for High-Write Tables
Per-Table Autovacuum Settings
-- Override autovacuum settings for a high-write table
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum when 1% rows are dead (default: 20%)
autovacuum_vacuum_threshold = 50, -- minimum dead rows before vacuum
autovacuum_analyze_scale_factor = 0.005, -- analyze when 0.5% rows change
autovacuum_vacuum_cost_delay = 2, -- ms (default: 20ms, lower = faster vacuum)
autovacuum_vacuum_insert_scale_factor = 0.05
);
-- For large tables (100M+ rows), use absolute thresholds:
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 100000 -- vacuum when 100K dead rows accumulate
);Global Autovacuum Configuration
# postgresql.conf -- production autovacuum settings
autovacuum_max_workers = 6 # default 3; increase for many tables
autovacuum_naptime = 15s # default 60s; check more frequently
autovacuum_vacuum_cost_delay = 2ms # default 20ms; reduce for faster vacuum
autovacuum_vacuum_cost_limit = 400 # default 200; allow more I/O per cycle
# Anti-wraparound settings
autovacuum_freeze_max_age = 400000000 # default 200M; give more headroom
vacuum_freeze_min_age = 5000000 # default 50M; freeze earlierLong-running transactions block VACUUM from reclaiming dead tuples. Use idle_in_transaction_session_timeout = '10min' to terminate sessions that hold transactions open without activity — these are the most common cause of table bloat emergencies.
Manual VACUUM for Emergencies
-- Emergency freeze vacuum (when xid_age is dangerously high)
VACUUM FREEZE VERBOSE ANALYZE orders;
-- Parallel vacuum (PostgreSQL 13+)
VACUUM (PARALLEL 4, VERBOSE, ANALYZE) events;
-- Monitor running VACUUM progress
SELECT
p.pid,
p.phase,
p.heap_blks_scanned,
p.heap_blks_total,
round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS pct_done
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid;- PostgreSQL 17's eager freezing spreads anti-wraparound work across regular vacuums, reducing emergency full-table freeze vacuums.
- For high-write tables, lower
autovacuum_vacuum_scale_factorto 0.01 and use absolute thresholds for tables over 100M rows. - Set
idle_in_transaction_session_timeoutto prevent long transactions from blocking autovacuum and causing table bloat. - Monitor
age(relfrozenxid)weekly — tables approaching 1.5 billion transactions need emergency attention.
Working with JusDB on PostgreSQL Maintenance
JusDB tunes autovacuum configurations for PostgreSQL deployments at scale, preventing table bloat and transaction ID wraparound before they become incidents. We monitor vacuum health continuously and intervene proactively on tables approaching dangerous dead tuple counts.
Explore JusDB PostgreSQL Services → | Talk to a DBA
Related reading: