PostgreSQL Autovacuum Tuning: Fix Table Bloat and XID Wraparound Risk

Autovacuum controls table bloat and prevents transaction ID wraparound only if it runs often enough and nothing blocks it: lower autovacuum_vacuum_scale_factor on large, frequently updated tables, raise autovacuum_vacuum_cost_limit so workers aren't throttled, give them enough autovacuum_work_mem, and remove the long-running transactions, prepared transactions and stale replication slots that hold back cleanup. Then monitor age(datfrozenxid) against autovacuum_freeze_max_age (200 million by default) so freezing never becomes an emergency. This guide walks through measuring the problem, fixing the blockers, and tuning global and per-table settings on PostgreSQL 18, with notes for earlier versions.

Who this is for and what you will have at the end

This is for DBAs and engineers running self-managed PostgreSQL whose tables grow faster than their data, whose queries slow down on heavily updated tables, or who have seen wraparound warnings in the log. Managed services expose most of the same parameters as server parameters, so the method applies there too.

At the end you will have:

• Queries that show which tables are bloated, behind on vacuum or close to the freeze limit.
• A procedure for clearing whatever is blocking vacuum from removing dead rows.
• Global and per-table autovacuum settings sized for large tables.
• A recovery procedure for the wraparound warning and error.

How autovacuum decides what to vacuum

PostgreSQL's MVCC keeps old row versions until no transaction can see them. VACUUM removes those dead rows so the space can be reused, and freezes old transaction IDs so they don't wrap around. Transaction IDs are 32-bit, so every table must be vacuumed at least once every two billion transactions.

The autovacuum launcher wakes every autovacuum_naptime per database and starts a worker for each table that crosses a threshold:

vacuum threshold        = Minimum(autovacuum_vacuum_max_threshold,
                                  autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples)
vacuum insert threshold = autovacuum_vacuum_insert_threshold
                          + autovacuum_vacuum_insert_scale_factor * reltuples * (1 - relallfrozen / relpages)
analyze threshold       = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

Independently, any table whose relfrozenxid is older than autovacuum_freeze_max_age is vacuumed to prevent wraparound, even if autovacuum is turned off.

Two consequences explain most problems. First, with a 0.2 scale factor a table with 500 million rows waits for 100 million dead rows; in PostgreSQL 18 the new autovacuum_vacuum_max_threshold caps that at 100 million, but that is still a lot of bloat. Second, the cost limit is divided among running workers, so adding workers without raising the limit makes each worker slower.

Prerequisites

• Superuser access for the configuration changes and the recovery steps. For pgstattuple, superuser or membership in pg_stat_scan_tables.
• The pgstattuple extension available; it is one of the additional modules supplied with PostgreSQL.
• Permission to change postgresql.conf and reload or restart the server.
• A baseline of disk usage and the slowest affected queries, so you can confirm the improvement.

Step 1: Find tables that are behind

Start with the cumulative statistics. Tables with many dead rows relative to live rows, and old or missing last_autovacuum times, are your candidates.

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze,
       autovacuum_count,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

PostgreSQL 18 also adds total_autovacuum_time (milliseconds, including cost-delay sleep) to this view, which shows where autovacuum spends its time.

n_dead_tup is an estimate. For an exact picture of a specific table, use pgstattuple. It reads the whole table with only a read lock, so run it off-peak on big tables, or use pgstattuple_approx, which skips all-visible pages:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
 
SELECT table_len, dead_tuple_percent, free_percent
FROM pgstattuple('public.orders');
 
SELECT table_len, scanned_percent, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('public.orders'::regclass);

A high dead_tuple_percent means vacuum is behind. A high free_percent with few dead rows means vacuum ran but the table is still physically large; that space is reused for new rows but not returned to the operating system.

Step 2: Check wraparound headroom

Use the queries from the PostgreSQL documentation:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
 
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

Ages around autovacuum_freeze_max_age (200 million by default) are normal: that is where forced vacuums begin. Ages that keep rising well beyond it mean anti-wraparound vacuums aren't completing, usually because something in Step 3 is blocking them or because they are throttled. The pg_stat_progress_vacuum view shows a running vacuum's phase and progress:

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total,
       index_vacuum_count, dead_tuple_bytes, max_dead_tuple_bytes
FROM pg_stat_progress_vacuum;

A high index_vacuum_count means the worker filled its memory with dead row identifiers and had to scan every index several times; raise autovacuum_work_mem.

Step 3: Remove what blocks cleanup

VACUUM can only remove rows that are dead to every transaction. Anything holding an old snapshot or transaction ID pins the cleanup horizon for the whole cluster. Check the three documented culprits.

Long-running transactions and idle-in-transaction sessions:

SELECT pid, usename, state, xact_start,
       age(backend_xid) AS xid_age, age(backend_xmin) AS xmin_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC NULLS LAST
LIMIT 10;

End the offenders with pg_cancel_backend(pid) or pg_terminate_backend(pid) and fix the application that leaves transactions open.

Forgotten prepared transactions:

SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

Commit or roll them back with COMMIT PREPARED 'gid' or ROLLBACK PREPARED 'gid' after confirming with the application owner.

Stale replication slots:

SELECT slot_name, slot_type, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;

An inactive slot with a large age holds back cleanup (logical slots hold catalog_xmin, which affects system catalogs). If the consumer is gone, drop it with pg_drop_replication_slot('slot_name'); a replica that still uses it would then need rebuilding. The logical replication guide covers slot monitoring and max_slot_wal_keep_size.

Step 4: Tune global autovacuum settings

Change these in postgresql.conf. The values below are an example starting point for a server with large, busy tables, not universal recommendations; adjust them using the measurements from Steps 1 and 2.

# postgresql.conf
autovacuum_max_workers = 6               # reloadable up to autovacuum_worker_slots in PG18
autovacuum_vacuum_cost_limit = 2000      # shared by all workers; default -1 means 200
autovacuum_vacuum_cost_delay = 2ms       # default
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_work_mem = 1GB                # per worker; total can reach max_workers x this
log_autovacuum_min_duration = 1min       # see what autovacuum is doing
track_cost_delay_timing = on             # PG18: report time spent sleeping in cost delay

Notes:

• Cost limit before workers. The limit is distributed proportionally among running workers, so raising autovacuum_max_workers alone spreads the same I/O budget thinner. Raise the cost limit first, watch disk latency, then add workers if many tables need vacuuming at once.
• Memory. autovacuum_work_mem is allocated per worker, so the total can reach autovacuum_max_workers times this value. Size it against available RAM.
• Restart versus reload. autovacuum_freeze_max_age, autovacuum_multixact_freeze_max_age and autovacuum_worker_slots need a restart. In PostgreSQL 18, autovacuum_max_workers can be changed with a reload up to autovacuum_worker_slots; on earlier versions it needs a restart.
• Leave autovacuum_freeze_max_age at its default unless you have measured a reason. Raising it delays forced vacuums and lets more unfrozen data accumulate.
• Eager freezing. PostgreSQL 18 lets normal vacuums freeze some all-visible pages ahead of time, controlled by vacuum_max_eager_freeze_failure_rate (default 0.03). This spreads freezing work out so aggressive vacuums have less to do.

Apply and confirm:

SELECT pg_reload_conf();
SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name LIKE 'autovacuum%' OR name IN ('log_autovacuum_min_duration', 'track_cost_delay_timing');

Step 5: Tune individual tables

Per-table storage parameters override the global values and are the right tool for the handful of large or hot tables that dominate the problem.

-- Large, heavily updated table: vacuum after about 1 percent changes, run faster
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 3000
);
 
-- Append-mostly table: make sure inserts trigger vacuum so pages get frozen
ALTER TABLE public.events SET (
  autovacuum_vacuum_insert_scale_factor = 0.01,
  autovacuum_vacuum_insert_threshold = 100000
);
 
-- Review what is set
SELECT oid::regclass AS table_name, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

Most of these parameters have toast. variants for the table's TOAST data; if you don't set one, the TOAST table inherits the table's value. A per-table autovacuum_freeze_max_age can only be set lower than the global value; higher values are ignored. These parameters can't be set on a partitioned table itself, only on its leaf partitions, and autovacuum doesn't analyze partitioned parents, so schedule ANALYZE on the parent if your queries need its statistics.

Step 6: Reclaim space that is already bloated

Tuning stops bloat from growing, but plain VACUUM only makes dead space reusable; it returns space to the operating system only when empty pages sit at the end of the table and it can briefly take an exclusive lock. To shrink a table that is already far larger than its data, use VACUUM FULL, CLUSTER, or a table-rewriting ALTER TABLE. All of them take an ACCESS EXCLUSIVE lock for the duration and need temporary disk space about the size of the table, so schedule them in a maintenance window.

VACUUM (VERBOSE, ANALYZE) public.orders;     -- normal cleanup, does not block reads and writes
VACUUM FULL public.orders;                   -- rewrite, blocks all access to the table

VACUUM can't run inside a transaction block. In PostgreSQL 18, VACUUM parent_table also processes inheritance children by default; use VACUUM ONLY parent_table for the parent alone.

Verification

After a day or two of normal workload, confirm the changes worked:

• n_dead_tup on the tuned tables stays a small fraction of n_live_tup, and last_autovacuum is recent.
• pgstattuple shows a low dead_tuple_percent on the worst tables from Step 1.
• age(datfrozenxid) for each database stays near or below autovacuum_freeze_max_age and drops after anti-wraparound vacuums finish.
• The server log shows autovacuum runs (from log_autovacuum_min_duration) finishing, with reasonable durations and no repeated skips due to lock conflicts.
• Disk latency during vacuum is acceptable; if not, lower the cost limit slightly.

Troubleshooting

WARNING: database must be vacuumed within N transactions

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

This appears when a database is within 40 million transactions of the limit. Run a database-wide VACUUM as a superuser (otherwise system catalogs are skipped), and check Step 3 for the blocker that stopped autovacuum from getting there first.

ERROR: database is not accepting commands that assign new transaction IDs

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

At 3 million transactions from the limit, only read-only transactions can start. The current documentation says not to stop the server or use single-user mode. Instead:

1. Commit or roll back old prepared transactions from pg_prepared_xacts.
2. End long-running transactions found in pg_stat_activity.
3. Drop old replication slots with a large age(xmin) or age(catalog_xmin).
4. Run VACUUM in the affected database. Don't use VACUUM FULL, which needs a transaction ID, or VACUUM FREEZE, which does more work than necessary.
5. Once writes work again, fix the autovacuum configuration.

Dead rows are not removed even though autovacuum runs

Autovacuum runs and finishes, but n_dead_tup stays high. Something is holding back the cleanup horizon: a long transaction, a prepared transaction or a replication slot. Work through Step 3.

Autovacuum runs for hours on one table

The worker is throttled or making several index passes. Raise autovacuum_vacuum_cost_limit for that table, raise autovacuum_work_mem if index_vacuum_count is above 1, and check delay_time in pg_stat_progress_vacuum (with track_cost_delay_timing on) to see how much of the time is spent sleeping.

Vacuum is skipped on a table

With log_autovacuum_min_duration set to anything other than -1, the log records autovacuum actions skipped because of a conflicting lock. Frequent DDL or long LOCK TABLE operations on that table are the usual cause.

Checklist

• Bloat and dead row queries run; worst tables identified.
• age(datfrozenxid) and per-table XID and multixact ages checked and alerted on.
• Long transactions, prepared transactions and stale slots cleared, with monitoring in place.
• autovacuum_vacuum_cost_limit raised before adding workers; autovacuum_work_mem sized to RAM.
• log_autovacuum_min_duration set so autovacuum activity is visible.
• Per-table scale factors set for large and append-mostly tables.
• Existing bloat removed in a maintenance window where needed.
• Wraparound warning and error procedure documented in the runbook.

References

• Routine vacuuming (PostgreSQL 18)
• Vacuuming server settings (PostgreSQL 18)
• Resource consumption settings (PostgreSQL 18)
• Error reporting and logging settings (PostgreSQL 18)
• VACUUM (PostgreSQL 18)
• CREATE TABLE storage parameters (PostgreSQL 18)
• Progress reporting (PostgreSQL 18)
• Cumulative statistics system (PostgreSQL 18)
• pgstattuple (PostgreSQL 18)
• pg_class (PostgreSQL 18)
• pg_prepared_xacts (PostgreSQL 18)
• pg_replication_slots (PostgreSQL 18)
• PostgreSQL 18 release notes