Tuning autovacuum from evidence · PostgreSQL

Autovacuum tuning advice is abundant and mostly untethered. This lesson tunes one table by measurement, shows what the change was worth, and states what the measurement does and does not establish.

Deciding whether a table needs anything

Most tables need nothing. Start by finding the ones that do.

SELECT 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,
       round(50 + 0.2 * n_live_tup)                                      AS default_threshold,
       autovacuum_count,
       last_autovacuum,
       last_autoanalyze
  FROM pg_stat_user_tables
 WHERE n_live_tup > 10000
 ORDER BY n_dead_tup DESC
 LIMIT 20;

The signals worth acting on:

n_dead_tup sits persistently just below default_threshold. This is the case measured below. It is the most common one and the least obvious, because nothing is failing.
last_autovacuum is old relative to the churn rate.
autovacuum_count is zero on a table with heavy n_tup_upd. Check reloptions for autovacuum_enabled=false, and check the log for canceling autovacuum task.
n_mod_since_analyze is large. A statistics problem, covered in lesson VIII-07.

The measurement

Two tables, 500,000 rows each, identical definitions. An identical workload was applied to both at the same time: 2,000 rows updated per round, one round roughly every two seconds, sustained for five minutes.

The only difference:

Configuration changeper-table settings applied to one of the two— Captured on 18.6. reloptions is where per-table autovacuum settings live.

$ psql -U postgres -c "ALTER TABLE churn_tuned SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, autovacuum_vacuum_cost_delay = 0, autovacuum_analyze_scale_factor = 0.02)" -c "SELECT relname, reloptions FROM pg_class WHERE relname LIKE 'churn_%' ORDER BY relname"

    relname    |                    reloptions
---------------+---------------------------------------------------
churn_default |
churn_tuned   | {autovacuum_vacuum_scale_factor=0.01,
                autovacuum_vacuum_threshold=1000,
                autovacuum_vacuum_cost_delay=0,
                autovacuum_analyze_scale_factor=0.02}
(2 rows)

Effective thresholds:

Table	Computation	Threshold
`churn_default`	`50 + 0.20 × 500000`	100,050 dead tuples
`churn_tuned`	`1000 + 0.01 × 500000`	6,000 dead tuples

Read-only / Safedead tuples sampled every 30 seconds under the identical workload— Captured on 18.6. The workload stopped shortly before the 210s sample.

$ psql -U postgres -At -c "SELECT n_dead_tup, autovacuum_count FROM pg_stat_user_tables WHERE relname LIKE 'churn_%'"   # sampled in a loop

elapsed    default_dead     tuned_dead   def_av   tun_av
30s               58000          50000        0        1
60s              114000         106000        0        1
90s               50000          48000        1        2
120s             106000         104000        1        2
150s              50000          48000        2        3
180s              68000          66000        2        3
210s              68000              0        2        4
240s              68000              0        2        4
270s              68000              0        2        4
300s              68000              0        2        4

Watch the last four rows. The workload ended, and the two tables settled in different places.

Read-only / Safefinal state, ninety seconds after the workload stopped— Captured on 18.6. pgstattuple reads the whole relation, so these figures are exact rather than estimated.

$ psql -U postgres -x -c "SELECT * FROM pgstattuple('churn_default')" -x -c "SELECT * FROM pgstattuple('churn_tuned')"

churn_default:
tuple_percent      | 52.35
dead_tuple_count   | 67206
dead_tuple_percent | 7.04
free_percent       | 37.16

churn_tuned:
tuple_percent      | 52.35
dead_tuple_count   | 0
dead_tuple_percent | 0
free_percent       | 44.56

Identical live data — tuple_percent is 52.35 on both. The default table is carrying 67,206 dead tuples that nothing is coming for, because 68,000 is below its threshold of 100,050.

The permanent backlog is the point, not the peak

The instinct is to read the sampled table as “the tuned one peaks lower”. That is true and it is the less interesting half.

The important part is the steady state. A table with a 20% scale factor does not oscillate between 0 and 20% dead. It oscillates between roughly 0 and 20%, and when the churn stops or slows it stops wherever it happens to be — which is, on average, somewhere below the threshold, permanently.

That residue is not a transient waiting to clear. It clears only when enough further churn pushes the table back over the threshold, which on a table whose write rate has dropped may be never.

So the honest description of the default configuration on a busy table is not “vacuum runs a bit less often”. It is “the table carries a standing dead-tuple population proportional to its size, indefinitely.”

Choosing values

The settings, and how to reason about each.

autovacuum_vacuum_scale_factor. The one that matters. Lower it on large, high-churn tables. 0.01 to 0.05 is a normal range; the value should follow from how much dead space you are willing to carry, which is a capacity decision rather than a performance one.

autovacuum_vacuum_threshold. Raise it alongside a lowered scale factor so that a table which is momentarily small does not get vacuumed constantly. The pair (1000, 0.01) means “1,000 dead tuples plus 1% of the table”.

autovacuum_vacuum_cost_delay = 0. Removes throttling for this table’s autovacuums. Justified when the table genuinely must be kept clean and you have measured that the storage can absorb it. It is not a free win: the worker will read and dirty pages as fast as it can, and that competes with your workload.

autovacuum_analyze_scale_factor. Separate trigger, separate decision. A table whose distribution changes fast needs frequent analyze even if it has few dead tuples — an append-only table with a timestamp column that queries filter on is the classic case.

autovacuum_vacuum_insert_scale_factor. For append-only tables, to control how often they are vacuumed for visibility map and freezing purposes.

fillfactor. Not an autovacuum setting, but part of the same loop, as lesson VII-02 measured. Consider it together with these.

autovacuum_enabled = false is not a tuning setting

ALTER TABLE big_table SET (autovacuum_enabled = false);

It is occasionally correct — during a bulk load you intend to vacuum manually afterwards, or on a table you are about to drop.

It is a disaster as a permanent state. The table stops reusing space, stops maintaining its visibility map, and stops freezing. The last of those is the one that ends in an outage, because transaction age keeps climbing on a table nothing is allowed to touch.

It is also nearly invisible. It appears only in reloptions, and a table with it set looks in every summary view exactly like a table autovacuum has not got round to yet. Include this in your monitoring:

SELECT c.oid::regclass AS relation, c.reloptions
  FROM pg_class c
 WHERE c.relkind IN ('r','m','p')
   AND c.reloptions::text LIKE '%autovacuum_enabled%';

One saving grace: anti-wraparound vacuum ignores it. A table with autovacuum disabled will still be force-vacuumed when it crosses autovacuum_freeze_max_age. That is a backstop against catastrophe, not a reason to leave it off — the forced vacuum arrives unscheduled, on a table that has never been vacuumed, and takes as long as that implies.

Verifying the change

A tuning change that is not verified is a guess with extra steps.

-- 1. Confirm the setting is actually in force
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

-- 2. Reset the counters so the comparison is clean
SELECT pg_stat_reset_single_table_counters('orders'::regclass);

-- 3. After a representative period, compare against the prediction
SELECT relname, n_dead_tup, autovacuum_count, last_autovacuum,
       round(1000 + 0.01 * n_live_tup) AS new_threshold
  FROM pg_stat_user_tables WHERE relname = 'orders';

-- 4. And measure the thing you were actually trying to fix
SELECT * FROM pgstattuple('orders');

Step 4 matters because n_dead_tup is an estimate maintained by the statistics collector, while pgstattuple reads the relation. If a tuning change is worth making it is worth confirming with the exact number at least once.

Why a table can be vacuumed constantly and never get clean

There is a failure state that looks like successful tuning and is not: autovacuum_count climbing steadily, last_autovacuum always recent, and n_dead_tup never falling.

Three causes, distinguishable.

The horizon is held. Vacuum runs, finds everything “dead but not yet removable”, and removes nothing. last_autovacuum updates because it completed. Diagnose with the four-source horizon query from lesson VII-03, or by reading VACUUM (VERBOSE) output directly.

Churn exceeds the achievable rate. The table produces dead tuples faster than 39 MB/s of throttled vacuum can remove them. Diagnose by comparing n_tup_upd growth per minute against what one vacuum pass achieved in the autovacuum log. The fix is throughput, or less churn.

Every pass is cancelled before finishing. canceling autovacuum task in the log, and autovacuum_count may not even increment. This is failure mode four in the next lesson.

The distinguishing question is what a single pass reports. Turn on log_autovacuum_min_duration and read one:

“0 removed, N are dead but not yet removable” — the horizon.
“N removed” but N is small relative to the churn — throughput.
No completion line at all, or an error — cancellation.

Tuning thresholds fixes none of these three. Lowering the scale factor on a table whose horizon is held simply means vacuum runs more often and removes nothing more often.

What to take from this

Most tables need no per-table settings. Find the ones that do from pg_stat_user_tables.
The default scale factor leaves a standing dead-tuple population proportional to table size: 67,206 tuples, 7.04% of the table, in the measurement here.
(threshold, scale_factor) is a pair. Lower the factor, raise the threshold.
autovacuum_enabled = false is not tuning. Monitor for it.
Verify with pgstattuple at least once; n_dead_tup is an estimate.
If a table is vacuumed constantly and never gets clean, thresholds are not the problem.

Cross-course references

Ansible for Production Sysadmins — Part XVII (Templates) covers generating per-table storage parameters from an inventory rather than applying them by hand, and Part XXXVI (Drift) covers detecting a setting somebody changed in an incident and never reverted.
Observability for Production Sysadmins — Part LIX (Database observability) covers the before-and-after series that is the only evidence a tuning change worked.