Postgres autovacuum, bloat, and dead tuples in operations

Query latency doubled. EXPLAIN showed Index Scan — the same plan as last month. Buffer cache hit ratio looked fine. The table had four million live rows and thirty-eight million dead tuples autovacuum had not reclaimed because a long-running analytics query blocked cleanup for eleven days. Postgres autovacuum, bloat, and dead tuple management are not DBA trivia — they are production operations for any team running OLTP workloads with updates, soft deletes, or high churn. Vacuum is how Postgres reclaims space, updates visibility maps, and prevents transaction ID wraparound. When it falls behind, performance degrades gradually until it degrades suddenly.

Postgres multi-version concurrency control (MVCC) keeps old row versions visible to in-flight transactions. A UPDATE creates a new row version and marks the old one dead. A DELETE marks rows dead without removing them immediately. Dead tuples occupy disk, fill buffers on sequential scans, and inflate indexes until vacuum reclaims them. Autovacuum is the background worker that should keep dead tuple fraction low without manual intervention — when tuned and unblocked.

How dead tuples accumulate and why autovacuum exists

Every mutating statement produces dead tuples:

Churn-heavy tables — sessions, notifications, job queues, audit staging — generate dead tuples faster than append-mostly tables. Without vacuum, table file size grows monotonically while live row count stays flat. Queries scan through dead tuples even when WHERE clauses filter them out — visibility checks cost CPU and I/O.

Autovacuum launches workers when tables exceed dead tuple thresholds:

autovacuum threshold = autovacuum_vacuum_threshold
                     + autovacuum_vacuum_scale_factor * reltuples

Default scale factor 0.2 means vacuum triggers after 20% of estimated live rows are dead — on a ten-million-row table, two million dead tuples before cleanup starts. High-churn OLTP tables often need lower scale factors.

Autovacuum is not optional maintenance. It is part of Postgres write path hygiene.

Vacuum also freezes transaction IDs on old rows, marking them safe against transaction ID wraparound — a failure mode that can force shutdown to protect data integrity when the cluster approaches two-billion-transaction limit.

Bloat: table heap, indexes, and visibility maps

Heap bloat — table files larger than live data requires. Measurable via extensions or estimation queries comparing pg_relation_size to expected live row footprint. Bloat causes more pages read per query, lower cache efficiency, and longer backup windows.

Index bloat — indexes retain pointers to dead heap tuples until vacuum reclaims both. Bloated indexes increase scan I/O; composite index patterns deliver less benefit when pages are sparse with dead entries.

Visibility map — when current, index-only scans skip heap fetches. Lagging vacuum reduces index-only scan opportunities.

Bloat does not fix itself without vacuum. INSERT alone does not reclaim dead tuple space in the middle of pages — only vacuum marks space reusable. Extreme bloat may require VACUUM FULL or pg_repack-style rewrite — both carry operational cost and lock implications discussed below.

Autovacuum tuning for production OLTP

Default autovacuum settings suit low-churn development databases. Production OLTP with updates needs per-table or global tuning:

-- Aggressive tuning for high-churn table
ALTER TABLE notification_events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

Key parameters:

Global changes via postgresql.conf or ALTER SYSTEM; per-table via ALTER TABLE ... SET for outliers without affecting entire cluster.

Analyze runs alongside vacuum — stale statistics cause bad plans that look like missing indexes. Tables with heavy churn need aggressive analyze scale factors too.

Monitor autovacuum activity:

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Alert when dead_pct exceeds threshold (commonly 10–20% sustained) or last_autovacuum is older than expected for churn rate.

What blocks vacuum and how to unblock it

Autovacuum cannot reclaim dead tuples still visible to any open transaction. Blockers:

Long-running transactions. Analytics query open for hours holds xmin horizon — all dead tuples since query start remain. Fix: idle_in_transaction_session_timeout, statement timeouts on OLTP roles, route long queries to replicas.

Replication slots. Logical replication slots prevent vacuum from removing tuples replicas have not consumed. Lagging slot → vacuum blocked → bloat grows on publisher. Monitor pg_replication_slots lag bytes and age.

Prepared transactions. Two-phase commit leftovers block cleanup. Audit pg_prepared_xacts.

Lock contention. Autovacuum waits on table locks held by DDL or long DML. Rare on OLTP but common during zero-downtime migrations if migrations hold strong locks.

Diagnostic query for blockers:

SELECT pid, state, query_start, xact_start, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND backend_xmin IS NOT NULL
ORDER BY xact_start
LIMIT 10;

The oldest xact_start among active transactions defines how far vacuum can progress. Operations runbooks should include killing runaway idle-in-transaction sessions and escalating replication slot lag before bloat becomes customer-visible latency.

Manual vacuum, VACUUM FULL, and pg_repack tradeoffs

Routine VACUUM — safe, concurrent, reclaims dead tuples for reuse within table file, does not return space to OS in most cases. Run manually when autovacuum lagged after incident recovery:

VACUUM (VERBOSE, ANALYZE) notification_events;

VACUUM FULL — rewrites entire table, exclusive lock, returns space to OS. Downtime risk on large tables. Reserve for maintenance windows or tables small enough to lock briefly. Not a routine bloat response.

pg_repack / pg_squeeze — online rewrite with minimal locking. Operational overhead of extension install and scheduling. Justified when heap bloat exceeds 50% and VACUUM cannot shrink file because pages are sparse but not empty enough for reuse.

Prevention beats cure: tuned autovacuum, no long transactions on primary, replication slot monitoring. Manual rewrite is expensive recovery, not steady state.

Wraparound vacuum and the emergency tier

Postgres transaction IDs are 32-bit and wrap. Rows older than autovacuum_freeze_max_age transactions must be frozen. Autovacuum launches anti-wraparound vacuum aggressively when tables approach age limit — visible in logs as autovacuum: vacuum on tables with high relfrozenxid age.

If wraparound vacuum cannot keep pace — blocked by long transactions, I/O throttling, or insufficient workers — the cluster enters warning and eventually transaction ID wraparound protection shutdown to prevent data corruption. This is a full-stop emergency, not gradual degradation.

Monitor:

SELECT datname, age(datfrozenxid) AS db_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Alert at 50% and 75% of autovacuum_freeze_max_age. Wraparound incidents trace to ignored autovacuum lag signals weeks earlier.

Operations runbook: daily signals and incident response

Integrate vacuum health into standard database monitoring:

Daily dashboard

• Top tables by n_dead_tup and dead percentage
• Time since last_autovacuum on top-churn tables
• Oldest active transaction age
• Replication slot lag (if logical replication enabled)
• Database and table frozenxid age

Incident response: check dead tuple ratio and blocking transactions first; run manual VACUUM ANALYZE if autovacuum stalled; schedule pg_repack only when bloat exceeds 2× live data footprint. Preventive: per-table autovacuum on hot tables at schema time, idle_in_transaction_session_timeout on app roles, analytics on replicas not primary.

How should teams manage Postgres vacuum in production?

Operational decisions that prevent bloat from becoming latency incidents.

When should autovacuum settings be per-table vs global?

Per-table for outliers — notification queues, session stores, job status tables with update-heavy patterns. Global defaults for moderate-churn schema. Document per-table settings in migration comments so future engineers know why they exist.

Is dead tuple percentage or absolute count more useful for alerting?

Both. High percentage on small tables matters less than moderate percentage on billion-row tables. Alert on absolute n_dead_tup above threshold AND percentage above threshold for tables above size cutoff.

Can autovacuum be too aggressive?

Yes. Very low cost_delay on I/O-saturated instances competes with OLTP queries. Tune during observed I/O headroom — increase delay if vacuum causes latency spikes, decrease if dead tuples accumulate. Measure, do not guess.

When is bloat bad enough to require rewrite?

When table size exceeds 1.5–2× estimated live data size AND vacuum does not reduce file size AND query I/O correlates with table bloat metrics. Below that threshold, tuned autovacuum usually suffices.

A common argument runs the other way

The opposing view holds that modern SSD storage is cheap — let tables grow, add disk when needed, and skip vacuum tuning complexity.

Disk cost is the smallest line item. Buffer cache pollution is not cheap — bloated tables evict useful pages, raising read I/O across the workload. Index bloat multiplies write amplification on every update. Backup and restore windows grow with file size regardless of live row count. Wraparound protection shutdown is not solved by provisioning more terabytes. Vacuum tuning is operational hygiene that preserves predictable latency — storage scaling is a compensating control that arrives after performance already degraded.

Key takeaways

• Updates and deletes create dead tuples; autovacuum reclaims them and freezes transaction IDs.
• Default autovacuum scale factor (20%) is too lazy for high-churn OLTP — tune per-table.
• Long transactions and lagging replication slots block vacuum — monitor oldest xact and slot lag.
• Bloat degrades heap and index efficiency; index-only scans suffer when visibility maps lag.
• VACUUM FULL locks exclusively — prevention via autovacuum beats emergency rewrite.
• Wraparound age monitoring prevents the only failure mode that shuts down Postgres entirely.
• Dead tuple metrics belong on the same dashboard as query latency and replication lag.

Conclusion

Autovacuum is invisible when it works and conspicuous when it does not — usually as unexplained latency growth on tables whose query plans never changed. Postgres operations maturity includes dead tuple monitoring, per-table autovacuum tuning for churn hotspots, and runbook steps for blocked vacuum before bloat requires rewrite surgery.

The first audit: top ten tables by dead tuple count, oldest active transaction on primary, replication slot lag if applicable, frozenxid age per database. Non-zero findings in multiple columns indicate vacuum debt accumulating faster than autovacuum can repay — tune before wraparound warnings turn into outage pages.