Tune PostgreSQL Autovacuum and VACUUM Settings

PostgreSQL autovacuum tuning is what keeps a busy table from growing forever on a small VPS. Autovacuum reclaims the dead tuples left behind by every UPDATE and DELETE, but its stock settings assume a large server with spare I/O, so on a modest VPS it often runs too rarely and lets bloat build up. This guide shows you how to read the current state, change global and per-table settings, and confirm that dead tuples actually fall.

Before you start

You need:

• A Linux VPS running PostgreSQL 12 or newer (Ubuntu 22.04/24.04, Debian 12, AlmaLinux 8–9 and Rocky 8–9 all ship a suitable version). Newer releases add settings such as autovacuum_vacuum_insert_threshold and autovacuum_vacuum_max_threshold — check the release notes for your version before relying on them.
• Shell access with sudo, and a psql session as the postgres user or another superuser.
• A rough idea of your workload. A write-heavy orders table needs different treatment from a mostly read-only lookup table.

How autovacuum decides to run

Autovacuum triggers on a table when the number of dead tuples exceeds:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × number of rows

The defaults are 50 and 0.2. On a table with 10 million rows that means roughly 2,000,050 dead tuples before a vacuum starts — far too late for a table that is updated constantly. ANALYZE uses the same pattern with autovacuum_analyze_threshold (50) and autovacuum_analyze_scale_factor (0.1).

Two more settings shape how hard autovacuum works. autovacuum_vacuum_cost_delay (2 ms by default since PostgreSQL 12) is how long a worker pauses after hitting its cost limit, and autovacuum_vacuum_cost_limit (default -1, which inherits vacuum_cost_limit = 200) is the budget before that pause. autovacuum_naptime (1 min) controls how often the launcher checks for work, and autovacuum_max_workers (3) caps how many tables are processed at once.

Step 1: Check current settings and dead tuples

Confirm what your server is actually using:

SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_vacuum_cost_limit;

Then find the tables carrying the most dead tuples:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

To see the dead-tuple ratio rather than the raw count:

SELECT relname,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 15;

Anything above roughly 20% dead tuples on a table that is written to regularly is a sign that autovacuum is falling behind. For a more precise picture, install the pgstattuple extension and run SELECT * FROM pgstattuple('public.orders');.

Step 2: Change global PostgreSQL autovacuum settings

Use ALTER SYSTEM so the changes land in postgresql.auto.conf and survive upgrades:

ALTER SYSTEM SET autovacuum_max_workers = 5;
ALTER SYSTEM SET autovacuum_naptime = '15s';
ALTER SYSTEM SET autovacuum_vacuum_threshold = 100;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
ALTER SYSTEM SET maintenance_work_mem = '256MB';
SELECT pg_reload_conf();

Lowering the scale factor to 0.05 means a 10-million-row table is vacuumed after about 500,100 dead tuples instead of 2 million. Raising the cost limit lets each worker do more between naps, which matters on a VPS with fast NVMe storage.

Step 3: Set per-table overrides for hot tables

Global changes affect every table. For the two or three tables that churn the most, override the settings directly:

ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 200,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0
);

For append-only tables that are never updated, PostgreSQL 13 and later adds autovacuum_vacuum_insert_threshold (default 1000) and autovacuum_vacuum_insert_scale_factor, which trigger vacuuming based on inserts instead of dead tuples. That helps tables where the visibility map needs refreshing so index-only scans keep working.

To confirm an override is stored:

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

Step 4: Run a manual VACUUM and ANALYZE

If a table has already accumulated heavy bloat, let autovacuum catch up by hand:

sudo -u postgres vacuumdb --all --analyze --verbose

Or target one table from psql:

VACUUM (VERBOSE, ANALYZE) public.orders;

Check it works

Re-run the dead-tuple query from Step 1 after a few minutes. The last_autovacuum column should show a recent timestamp on the tables you tuned, and n_dead_tup should drop.

While a vacuum is running, watch its progress:

SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

To log autovacuum activity for later review, set log_autovacuum_min_duration to 250ms (or 0 to log every run) and check the PostgreSQL log after the next busy period.

Troubleshooting

• Autovacuum never touches a table. A long-running transaction, an idle-in-transaction session or an old replication slot is holding back the oldest xmin, so vacuum cannot remove anything. Check SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state <> 'idle'; and close stale sessions.
• The log shows "oldest xmin is far in the past". Same root cause: something is pinning the snapshot. Look for forgotten BEGIN; sessions, prepared transactions in pg_prepared_xacts, and inactive replication slots in pg_replication_slots.
• A large table keeps growing anyway. The scale factor is still too high for its row count. Add a per-table override as shown in Step 3, or lower the global autovacuum_vacuum_scale_factor further.
• Autovacuum is saturating disk I/O. Raise autovacuum_vacuum_cost_delay to 10ms or lower autovacuum_vacuum_cost_limit, then re-check query latency.
• VACUUM FULL fails with "No space left on device". It needs room for a full copy of the table plus its indexes. Free space first, or use pg_repack, which needs less headroom.

Next steps

• Install PostgreSQL and create your first database if you are setting up a new instance.
• Create PostgreSQL users, roles and databases to keep application access tidy.
• Back up and restore PostgreSQL with pg_dump and pg_restore before any table rewrite.
• psql cheat sheet: essential PostgreSQL commands for the queries you will run most often.

If autovacuum is competing with your workload for I/O, a Linux VPS with faster storage and more RAM gives it room to keep up.