Autovacuum ran hundreds of times and removed nothing, on tables the nightly report has never touched

Reported symptoms

Disk usage on the primary grows by 40 GB every night between 22:00 and 04:00, and only partially recovers during the day.

Query latency on the busiest OLTP tables degrades through the night and improves each morning.

Autovacuum is running constantly — hundreds of runs across the night. The runs report tuples: 0 removed and a large number dead but not yet removable.

The affected tables include several the analytics workload has never read.

An engineer raised autovacuum_max_workers from 3 to 8 and lowered the scale factors. This increased the number of runs and changed nothing else.

The problem stops the moment the nightly reporting job finishes.

Evidence provided

$ grep 'automatic vacuum' /var/log/postgresql/postgresql-18-main.log | tail -1 -A3

2026-08-28 02:41:07.203 UTC [8812] LOG:  automatic vacuum of table "prod.public.orders": index scans: 0
tuples: 0 removed, 4821992 remain, 3910442 are dead but not yet removable
removable cutoff: 41880231, which was 14002119 XIDs old when operation ended

Compare that against a healthy vacuum on the same cluster:

$ grep 'automatic vacuum' /var/log/postgresql/postgresql-18-main.log | tail -1 -A3

2026-08-28 00:56:50.630 UTC [10589] LOG:  automatic vacuum of table "lab10.public.churn": index scans: 1
tuples: 15000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 838, which was 0 XIDs old when operation ended

pg_stat_activity shows one session in state active, xact_start 22:04, backend_xmin unchanged since. It is the reporting job, connected as analytics, running 60 to 90 queries inside one explicit BEGIN and committing at the end. By 04:00 its age(backend_xmin) reaches 14 million.

It reads from a reporting schema only, and takes no locks on the OLTP tables.

Work the evidence before reading on

1. 0 removed and 3910442 not yet removable. What is the difference between those two numbers?
2. The report never touches orders. Why is orders affected?
3. What did raising autovacuum_max_workers change?
4. Is this the same incident as an abandoned idle in transaction session?

Root cause

A snapshot is cluster-scoped

The tuning change made it worse

More workers and lower scale factors made autovacuum run more often. Each run scanned more pages, wrote more WAL, consumed more I/O, and removed nothing — because the constraint was never the rate of vacuuming.

The 40 GB, and why mornings only partly recover

The 40 GB is dead row versions plus the index entries pointing at them. At 04:00 the snapshot is released and the next pass reclaims them — but the files do not shrink. What recovers is reusable space inside them, not disk.

This is not an abandoned session

Resolution

Identify the horizon holder, and check whether it is working before considering ending it:

SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       age(backend_xmin)  AS xmin_age,
       left(query, 80)    AS current_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

If the report can be sacrificed, cancel rather than terminate — it is the gentler signal and the connection survives:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

Confirm the horizon moved before assuming anything:

SELECT max(age(backend_xmin)) FROM pg_stat_activity;
VACUUM (VERBOSE) orders;   -- expect a non-zero "removed" this time

The durable fix is at the job. Split the report so each query runs in its own transaction. Six hours of work in sixty transactions holds a snapshot for the length of one query, not the length of the job.

If the report genuinely needs one consistent snapshot across all sixty queries — worth challenging, because most reports do not — move it off the primary, and choose hot_standby_feedback deliberately:

-- on the standby
SHOW hot_standby_feedback;        -- on: the standby holds the PRIMARY's horizon
SHOW max_standby_streaming_delay; -- off: the standby cancels the query instead

With feedback on you have moved the workload without moving the problem.

Do not raise autovacuum_max_workers or lower the scale factors.

Verification

Autovacuum reports a non-zero removed, and not yet removable falls to zero. That trailing zero is the whole verification.

The oldest snapshot stays small through the night:

SELECT max(age(backend_xmin)) AS oldest_snapshot FROM pg_stat_activity;

n_dead_tup on the OLTP tables stops climbing during the reporting window, and the nightly 40 GB excursion stops.

Existing bloat does not disappear on its own — the files are already large and vacuum makes space reusable rather than returning it. Measure with pgstattuple and decide separately whether a rewrite is warranted.

Run the report and watch age(backend_xmin) for its session. Under the fix it rises and resets repeatedly instead of climbing monotonically for six hours. That is the direct test that the job’s shape changed.

Prevention

Alert on the oldest snapshot, not on session duration. A long-lived connection is fine; a long-lived snapshot is what stops vacuum.

Alert on the phrase “not yet removable” in autovacuum logs.

Set transaction_timeout per role. PostgreSQL 17 added it, and it bounds the whole transaction rather than a single statement or an idle period — exactly the control this incident needed:

ALTER ROLE analytics SET transaction_timeout = '15min';

Set it on roles that should never hold a long transaction, and deliberately not on the ones that must.

Write long reports as many short transactions. A report requiring a single snapshot across six hours is asserting a consistency requirement almost no report actually has. Make somebody state it out loud before accepting it.

Run analytics on a standby, with hot_standby_feedback chosen on purpose.

Do not treat this as an autovacuum tuning problem. The lever is the snapshot.