Autovacuum Tuning for a Write-Heavy Postgres Table

The Postgres autovacuum defaults are tuned for a database of mostly quiet tables. Point them at one table that takes thousands of updates a minute and that table will bloat between vacuum runs, its indexes will fatten, and your query plans will slowly rot — while every other table in the cluster is perfectly happy.

This is one of the most common bits of self-inflicted technical debt I see in production Postgres: a single hot table — a ledger, an events feed, a session store, an idempotency-key table — running under the cluster-wide autovacuum defaults that were never meant for it. The fix is not to touch postgresql.conf and change the defaults for every table. It is to give the one table that needs it its own policy, through per-table storage parameters. Here is how, on PostgreSQL 17.

Why the default lets a hot table bloat

Autovacuum decides a table needs vacuuming when the number of dead tuples crosses a threshold defined as:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

The defaults are autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2. The scale factor is the problem. Twenty per cent is a sensible proportion for a small table, but it scales with row count. On a fifty-million-row table, 0.2 means autovacuum waits for roughly ten million dead tuples before it does anything. On a table taking constant updates — and remember every UPDATE in Postgres creates a dead tuple, because the old row version is left behind for MVCC — that is a lot of accumulated bloat sitting in your heap and indexes before a single vacuum fires.

Worse, once that much churn has built up, the vacuum run itself is large and slow, and it is throttled by the cost-based delay so it does not saturate your I/O. So you get the worst of both: long stretches of growing bloat, punctuated by a heavy vacuum that struggles to keep up. The table only ever trends fatter.

Confirm it first: read pg_stat_user_tables

Do not tune blind. Postgres already tells you which tables are carrying dead tuples and when they were last cleaned. Before changing anything, look at the evidence — the same instinct that should govern any operational metric you claim to manage.

-- Tables carrying the most dead tuples, with churn and last-vacuum time
SELECT
    relname                                            AS table_name,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric / nullif(n_live_tup, 0), 4) AS dead_ratio,
    n_mod_since_analyze,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

The columns that matter: n_dead_tup is the current dead-tuple count; dead_ratio tells you how bloated the table is relative to live rows; last_autovacuum tells you when the daemon last touched it; and autovacuum_count tells you how often it has, cumulatively. A hot table where last_autovacuum is hours old and n_dead_tup is in the millions is the exact pattern this post fixes. If last_autovacuum is null and autovacuum_count is zero on a busy table, autovacuum has never run on it — check that it is not disabled.

Give the table its own policy

Per-table storage parameters override the cluster defaults for one table only. For a high-churn table you want three changes: make the trigger fire far sooner, pin it to a fixed number of dead rows rather than a percentage, and let the vacuum work harder once it runs.

-- Per-table autovacuum policy for a write-heavy table (PostgreSQL 17)
ALTER TABLE ledger_entries SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- 1% instead of the 20% default
    autovacuum_vacuum_threshold    = 2000,   -- fixed floor of dead tuples
    autovacuum_vacuum_cost_limit   = 2000,   -- more work per round (default via vacuum_cost_limit is 200)
    autovacuum_vacuum_cost_delay   = 2       -- keep the default 2ms nap; raise only if I/O suffers
);

What each line does. Dropping autovacuum_vacuum_scale_factor to 0.01 means the same fifty-million-row table now vacuums at roughly 500,000 dead tuples instead of ten million — twenty times more often, each run twenty times smaller. The autovacuum_vacuum_threshold of 2000 sets a sensible floor so a smaller table does not vacuum on a handful of rows. Raising autovacuum_vacuum_cost_limit to 2000 lets each vacuum do ten times the default work before it pauses for autovacuum_vacuum_cost_delay — the default limit derives from vacuum_cost_limit, which is 200 — so the more frequent runs also finish quickly rather than dawdling under I/O throttling. Numbers are a starting point; measure and adjust for your row size and write rate.

These settings take effect immediately — no restart, no reload, no table rewrite. Confirm they are attached:

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

The one knob you cannot set per table

There is a trap here worth naming, because the instinct is to reach for it. autovacuum_max_workers — the number of vacuum processes that can run at once, default 3 — is not a storage parameter. You cannot set it on a table. It is cluster-wide, lives in postgresql.conf, and changing it requires a server restart:

# postgresql.conf — cluster-wide, needs a restart
autovacuum_max_workers = 5

Raise it only if you genuinely have many tables competing for the three default workers and vacuum is queueing. Adding workers does not make any single table vacuum faster — the total vacuum I/O budget is shared across all of them via the cost limit — so more workers on the same budget just means each works more slowly. For one hot table, the per-table policy above is the lever, not the worker count.

Make it a change you can defend

Two operational points. First, if the table is also insert-heavy — an append-only events or idempotency-key table where rows are written and rarely updated — the equivalent insert-driven parameters (autovacuum_vacuum_insert_scale_factor, default 0.2, and autovacuum_vacuum_insert_threshold, default 1000) govern the visibility-map vacuums that keep index-only scans working, and deserve the same treatment.

Second, put the ALTER TABLE in a checked-in migration, not a one-off psql session someone runs by hand and forgets. A per-table tuning decision that lives only in the running database is invisible the moment that person leaves — exactly the kind of undocumented state that belongs in version control, reviewed and reproducible. A tuned table is only lean until someone restores from a schema dump that never had the setting.

The cluster defaults are not wrong. They are just a policy for the average table, and your hot table is not average. Measure the dead tuples, give that one table the policy it needs, and check the setting into the repo — so it survives the next restore.