PostgreSQL Autovacuum Tuning for Bloat and Wraparound

Give autovacuum the budget and per-table thresholds your write rate needs, and clear what blocks it, so tables stop bloating and wraparound never stops writes.

Published

Applies to PostgreSQL 13 and later; autovacuum_vacuum_max_threshold and reloadable autovacuum_max_workers need PostgreSQL 18

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 transaction by a bug or a crashed worker
  • A long report, pg_dump or migration
  • A replication slot whose consumer is gone
  • A replica with hot_standby_feedback = on running 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_workers sets 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 to autovacuum_worker_slots. Older versions need a restart.
  • autovacuum_vacuum_cost_limit is 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_delay stays at 2 ms. On PostgreSQL 11 and older it defaults to 20 ms, which is the first thing to change there.
  • autovacuum_naptime sets 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_mem holds dead row IDs during a pass. Too little, and vacuum scans every index more than once. Each worker can take the full amount, so budget autovacuum_max_workers × autovacuum_work_mem of RAM. Before PostgreSQL 17, vacuum uses at most 1 GB of it.
  • log_autovacuum_min_duration logs every run longer than 10 seconds, with pages removed and time spent.
  • idle_in_transaction_session_timeout ends sessions that sit in an open transaction, the most common horizon blocker. PostgreSQL 17 adds transaction_timeout for 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_tup on 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

Further reading