PostgreSQL autovacuum tuning starts with one fact: the defaults suit a small, quiet database. A table that takes thousands of updates a second outgrows them, and it bloats until queries slow down. In the worst case, Postgres stops taking writes to protect itself from transaction ID wraparound. The fix comes in three parts: a bigger vacuum budget, tighter thresholds on hot tables, and removing whatever stops vacuum from cleaning.
What breaks at peak
Dead rows pile up. An UPDATE or DELETE leaves the old row version in place for transactions that might still see it. Vacuum removes those dead rows later and marks the space reusable. Until it does, every scan reads past them, indexes keep pointing at them, and the table grows.
Autovacuum starts late on big tables. A table gets vacuumed once its dead rows pass this line:
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × live rows
50 + 0.2 × live rows
On a 100-million-row table, that’s 20 million dead rows before the first pass starts. PostgreSQL 18 adds autovacuum_vacuum_max_threshold, which caps the line at 100 million rows by default. That cap is still far too high for a hot table.
Autovacuum runs slow. Vacuum pauses for autovacuum_vacuum_cost_delay, 2 ms by default, each time it spends vacuum_cost_limit credits, 200 by default. That limit is shared across every running worker. Reading a page from disk costs 2 credits on PostgreSQL 14 and later, and dirtying one costs 20. All workers together can then dirty about 5,000 pages a second, around 40 MB. On a write-heavy database, dead rows arrive faster than that.
Something holds back the xmin horizon. Vacuum can only remove a row once no open transaction might still need it. One old snapshot anywhere stops cleanup on every table in the database. The usual causes:
- A session left
idle in transactionby a bug or a crashed worker - A long report,
pg_dumpor migration - A replication slot whose consumer is gone
- A replica with
hot_standby_feedback = onrunning long queries - A forgotten prepared transaction from
PREPARE TRANSACTION
Autovacuum then runs, finishes and removes nothing. With VACUUM VERBOSE you see it in the line dead but not yet removable.
Wraparound. Transaction IDs are 32 bits wide, so Postgres must freeze old rows before the counter wraps. Autovacuum forces a freezing pass on any table older than autovacuum_freeze_max_age, 200 million transactions by default. That pass doesn’t yield to lock requests, so an ALTER TABLE queues behind it, and every query queues behind the ALTER TABLE. If freezing still can’t keep up, Postgres warns in the log and to every client:
WARNING: database "app" must be vacuumed within 9876543 transactions
Then it refuses new transaction IDs. PostgreSQL 17 and later say:
ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "app"
HINT: Execute a database-wide VACUUM in that database.
PostgreSQL 16 and older print database is not accepting commands to avoid wraparound data loss in database "app". Their hint says to vacuum in single-user mode. Reads keep working. Every insert, update and delete fails until vacuum finishes.
The autovacuum tuning config
Changed lines only. Apply them with ALTER SYSTEM, so the rollback is one RESET per setting:
ALTER SYSTEM SET autovacuum_max_workers = 5; -- restart before PostgreSQL 18
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_work_mem = '1GB';
ALTER SYSTEM SET log_autovacuum_min_duration = '10s';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();
Each line, and how to size it:
autovacuum_max_workerssets how many tables get vacuumed at once. Too few, and one huge table holds a worker for hours while small hot tables wait. On PostgreSQL 18 it reloads, up toautovacuum_worker_slots. Older versions need a restart.autovacuum_vacuum_cost_limitis the shared I/O budget. Workers split it, so more workers without a bigger limit only makes each one slower. I raise it in steps and watch disk latency at each one. Stop when vacuum starts to compete with your queries for I/O.autovacuum_vacuum_cost_delaystays at 2 ms. On PostgreSQL 11 and older it defaults to 20 ms, which is the first thing to change there.autovacuum_naptimesets how often the launcher checks each database. A shorter nap helps when you have many databases.- The two scale factors lower the trigger line on every table. Hot tables get their own values below.
autovacuum_work_memholds dead row IDs during a pass. Too little, and vacuum scans every index more than once. Each worker can take the full amount, so budgetautovacuum_max_workers × autovacuum_work_memof RAM. Before PostgreSQL 17, vacuum uses at most 1 GB of it.log_autovacuum_min_durationlogs every run longer than 10 seconds, with pages removed and time spent.idle_in_transaction_session_timeoutends sessions that sit in an open transaction, the most common horizon blocker. PostgreSQL 17 addstransaction_timeoutfor active ones too.
Hot tables get their own thresholds. Find the tables with the most dead rows and updates:
SELECT relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd,
last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Then trigger vacuum on a fixed row count instead of a share of the table:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 100000,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_limit = 4000,
fillfactor = 90
);
Size the threshold from the update rate. Pick how long a table may collect dead rows, a few minutes for a hot table, and multiply by its updates per second. Read the rate from n_tup_upd deltas between two checks. A per-table cost limit takes that table out of the shared budget, so its worker doesn’t slow the others.
fillfactor = 90 leaves free space on each new page. An update that changes no indexed column can then stay on the same page. That HOT update needs no index change and is cheap to clean. Compare n_tup_hot_upd with n_tup_upd to see your share. The setting only applies to pages written after the change.
Clear the horizon. Before you tune anything, find out whether something blocks cleanup. I run these three queries first, because no setting helps while one of them returns an old row:
-- sessions holding an old snapshot
SELECT pid, usename, application_name, state, xact_start, age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;
-- replication slots holding rows or WAL
SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;
-- forgotten prepared transactions
SELECT gid, prepared, owner, age(transaction) AS xid_age
FROM pg_prepared_xacts;
End the session with pg_terminate_backend(pid), drop the dead slot with pg_drop_replication_slot, or finish the prepared transaction with ROLLBACK PREPARED 'gid'. On replicas with hot_standby_feedback = on, the replica’s oldest query shows up as backend_xmin in pg_stat_replication on the primary.
Shrink what already bloated. Vacuum makes space reusable. It doesn’t return it to the operating system, except for empty pages at the end of the table. Measure first with the pgstattuple extension:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple_approx('orders');
VACUUM FULL rewrites the table but locks it against reads and writes for the whole run. The pg_repack extension rebuilds it online and holds an exclusive lock only for a moment at the start and end. For index bloat, REINDEX INDEX CONCURRENTLY rebuilds without blocking writes.
Out of wraparound. List the oldest tables, clear the horizon blockers above, then vacuum the oldest table first:
SELECT c.oid::regclass AS table_name, age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 10;
VACUUM (FREEZE, VERBOSE) orders;
A blocker left in place makes every vacuum here a no-op, so the order matters. From PostgreSQL 14, once a table passes vacuum_failsafe_age, vacuum drops its cost delay and skips index cleanup to freeze faster.
Managed Postgres. On Amazon RDS and Aurora, change the global settings in a DB parameter group. Azure Database for PostgreSQL calls them server parameters, Cloud SQL calls them database flags, and OCI Database with PostgreSQL uses configurations. Some of them, autovacuum_max_workers among them, need a reboot. ALTER SYSTEM is blocked on all of these, so the rollback goes through the same console. Per-table ALTER TABLE ... SET works everywhere, since the table owner runs it. Aurora’s storage layer differs from stock Postgres, but its row versioning doesn’t, so dead rows and wraparound work the same. Most providers support pg_repack and pgstattuple, but check the extension list for your engine version first.
Test it on your system
Use a staging copy with production’s table sizes, indexes and settings. Bloat depends on the data, so an empty pgbench schema hides most of it.
Run a write load at your peak rate for at least an hour. The built-in simple-update script updates pgbench_accounts and inserts into pgbench_history:
pgbench -i -s 500 -h pg-staging.internal -U app app
pgbench -h pg-staging.internal -U app -b simple-update \
-c 32 -j 8 -R 3000 -T 3600 -P 60 app
Set -R to your own peak write rate. For your real hot tables, write their UPDATE statements into a script and pass it with -f, as the pgbench recipe shows.
Every few minutes, record dead rows, table size and vacuum progress:
SELECT relname, n_dead_tup, autovacuum_count,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname IN ('pgbench_accounts', 'orders');
SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;
Watch the Postgres log for the automatic vacuum of table lines, and disk latency on the host.
Then repeat the run with a blocker in place. Open a psql session, run SET idle_in_transaction_session_timeout = 0; BEGIN; SELECT txid_current(); and leave it open for the whole run. The SET stops your new timeout from ending the test session. Dead rows should climb with no cleanup, which proves the horizon queries above find it.
Set your pass criteria before the clean run:
n_dead_tupon each hot table rises and falls in a saw-tooth with no upward trend across the hour.- Table size levels off after the first few vacuum passes.
- p99 latency at the end of the run matches the start, within the budget you set.
- Disk latency during vacuum passes stays inside what your queries can tolerate.
Keep it healthy
Scrape Postgres with postgres_exporter, with the database_wraparound collector turned on. The postgres_exporter recipe covers the setup and the alerts for idle transactions and slots.
groups:
- name: postgres-vacuum
rules:
- alert: PostgresDeadTuplesHigh
expr: |
pg_stat_user_tables_n_dead_tup
/ (pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup) > 0.2
and pg_stat_user_tables_n_dead_tup > 100000
for: 30m
labels:
severity: warning
annotations:
summary: "{{ $labels.relname }} in {{ $labels.datname }} is over 20% dead rows"
description: "Autovacuum isn't keeping up. Check for horizon blockers in pg_stat_activity and pg_replication_slots, then lower this table's autovacuum_vacuum_threshold."
- alert: PostgresTableNotVacuumed
expr: |
(time() - pg_stat_user_tables_last_autovacuum) > 172800
and pg_stat_user_tables_n_dead_tup > 100000
for: 1h
labels:
severity: warning
annotations:
summary: "{{ $labels.relname }} hasn't been autovacuumed in two days"
description: "Check log_autovacuum output for this table and pg_stat_progress_vacuum. A worker stuck on a larger table or a cost limit too low are the usual causes."
- alert: PostgresAutovacuumWorkersBusy
expr: |
sum by (instance) (pg_stat_activity_count{backend_type="autovacuum worker"})
>= on (instance) max by (instance) (pg_settings_autovacuum_max_workers)
for: 1h
labels:
severity: warning
annotations:
summary: "Every autovacuum worker on {{ $labels.instance }} has been busy for an hour"
description: "Tables are queuing for vacuum. Raise autovacuum_vacuum_cost_limit first, then autovacuum_max_workers, and give the largest tables their own cost limit."
- alert: PostgresWraparoundWarning
expr: max by (instance, datname) (pg_database_wraparound_age_datfrozenxid) > 500000000
for: 30m
labels:
severity: warning
annotations:
summary: "{{ $labels.datname }} is past 500 million transactions since its last freeze"
description: "Freezing is falling behind. List the oldest tables by age(relfrozenxid), clear horizon blockers, then run VACUUM (FREEZE) on the oldest table off-peak."
- alert: PostgresWraparoundCritical
expr: max by (instance, datname) (pg_database_wraparound_age_datfrozenxid) > 1500000000
for: 5m
labels:
severity: critical
annotations:
summary: "{{ $labels.datname }} is close to the wraparound write stop"
description: "Writes stop if this keeps rising. Clear blockers now and run VACUUM (FREEZE, VERBOSE) on the oldest tables. Don't restart Postgres, which cancels the vacuum."
Alert on these log lines too, since each one means you’re past the point where tuning is enough:
must be vacuumed within
is not accepting commands
Roll back
Save the current values before the change:
\copy (SELECT name, setting, unit, source FROM pg_settings WHERE name LIKE '%vacuum%' OR name = 'idle_in_transaction_session_timeout') TO 'vacuum-settings-before.csv' CSV HEADER
\copy (SELECT oid::regclass, reloptions FROM pg_class WHERE reloptions IS NOT NULL) TO 'table-options-before.csv' CSV HEADER
To undo the global settings, reset each one and reload:
ALTER SYSTEM RESET autovacuum_vacuum_cost_limit;
ALTER SYSTEM RESET autovacuum_max_workers;
SELECT pg_reload_conf();
To undo a table’s settings, run ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold, autovacuum_vacuum_cost_limit, fillfactor);.
If vacuum I/O is slowing queries at peak, lower autovacuum_vacuum_cost_limit now. It takes effect on reload, and running workers pick it up. Don’t roll back the horizon fixes or idle_in_transaction_session_timeout unless they break a real job. Those protect you from wraparound. Any other revert can wait for a quiet hour.
More recipes
- Patroni PostgreSQL High Availability and Failover
Set up Patroni automatic failover with health-checked routing and test it under load, so a dead primary costs seconds of errors and no lost writes.
- PostgreSQL Load Testing with pgbench
Use pgbench for PostgreSQL load testing at fixed, rising rates with your own queries, so you find the database's breaking point before your users do.
- Monitor PostgreSQL with postgres_exporter
Run postgres_exporter with the right collectors and alert on PostgreSQL replication lag, connections and stuck transactions in Prometheus before users notice.
- PostgreSQL Connection Pooling with PgBouncer
PgBouncer in transaction mode lets hundreds of app connections share a small pool of Postgres connections, so scaling out pods stops hitting max_connections.