Autovacuum had been starting on the same table every night for a year and had never once finished

Reported symptoms

The events table has grown from 400 GB to 1.4 TB over fourteen months. Its row count has grown by 30 percent.

Sequential scans take four times as long as a year ago for the same number of rows.

pg_stat_user_tables shows autovacuum_count in the hundreds for this table and last_autovacuum within the last day. n_dead_tup is 1.1 billion and has never fallen.

Other tables in the same database are vacuumed normally.

autovacuum_max_workers was raised to 10 and the cost delay lowered to zero for this table, with no effect.

The database team has been told several times that autovacuum is running. The monitoring agrees.

Evidence provided

$ grep -B1 'canceling autovacuum task' /var/log/postgresql/postgresql-18-main.log | tail -4

2026-08-28 02:14:03.771 UTC [4471] LOG:  sending cancel to blocking autovacuum PID 4302
2026-08-28 02:14:03.771 UTC [4471] DETAIL:  Process 4471 waits for AccessExclusiveLock on relation 24601 of database 16385.
2026-08-28 02:14:03.771 UTC [4471] STATEMENT:  ALTER TABLE events DETACH PARTITION events_2026_07;
2026-08-28 02:14:03.774 UTC [4302] ERROR:  canceling autovacuum task

That pair appears once per night, at 02:14, for fourteen months.

The nightly partition-maintenance job runs at 02:14 and issues DETACH PARTITION and ATTACH PARTITION on the parent.

A manual VACUUM VERBOSE during a maintenance window completed in 3 hours 51 minutes and removed 1.1 billion tuples.

autovacuum_naptime is 60 seconds, so the worker starts again within a minute — and is cancelled again the following night.

Work the evidence before reading on

1. autovacuum_count is in the hundreds and last_autovacuum is recent. What exactly do those two columns record?
2. The vacuum takes 3h51m and is cancelled at 02:14. When does the next one start, and where does it start from?
3. What lock does a vacuum worker hold, and what conflicts with it?
4. Why did raising autovacuum_max_workers change nothing?

Root cause

The DDL wins, by design

A cancelled vacuum keeps nothing

Every metric said it was fine

autovacuum_count in the hundreds. last_autovacuum recent. Both are true, and both record that a worker started.

Neither records whether it finished, and PostgreSQL exposes no column that does. The only place the truth appears is the log.

Raising autovacuum_max_workers and zeroing the cost delay were reasonable answers to “vacuum is too slow” and irrelevant to “vacuum is being killed”. They let the worker cover more of the table in its twenty minutes, and then be cancelled at the same moment.

Resolution

Confirm the cancellations. They are the diagnosis and they exist only in the log:

grep -c 'canceling autovacuum task' /var/log/postgresql/postgresql-18-main.log
grep -B2 'canceling autovacuum task' /var/log/postgresql/postgresql-18-main.log | tail -20

Correlate the timestamps with your scheduled jobs.

Give the table one uninterrupted pass in a window long enough for the whole thing, and measure rather than guess:

VACUUM (VERBOSE, ANALYZE) events;

Watch it from a second session — this is how you tell a vacuum that is working slowly from one that is not working:

SELECT pid, phase, heap_blks_total, heap_blks_scanned,
       round(100.0 * heap_blks_scanned / nullif(heap_blks_total,0), 1) AS pct,
       dead_tuple_bytes, max_dead_tuple_bytes
FROM pg_stat_progress_vacuum;

Then remove the collision. Two honest options:

• Give the vacuum a window the DDL does not enter, as a documented constraint rather than an accident of cron ordering.

• Make the DDL wait a bounded time instead of cancelling. Set lock_timeout so the job fails fast and retries:

  SET lock_timeout = '5s';
  ALTER TABLE events DETACH PARTITION events_2026_07 CONCURRENTLY;

  DETACH PARTITION CONCURRENTLY avoids the long ACCESS EXCLUSIVE hold on the parent altogether and is the better instrument where it applies.

Give the table its own settings, so a normal night’s work fits in a normal night:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000,
  autovacuum_vacuum_cost_delay   = 0
);

Vacuuming more often makes each run smaller, which is what makes it survivable.

Verification

canceling autovacuum task stops appearing for this table. Grep daily for a week; the count must be zero, not lower.

A vacuum completes, confirmed from the completion line rather than from last_autovacuum:

automatic vacuum of table "prod.public.events": index scans: 1
tuples: 1103882914 removed, 2841002 remain, 0 are dead but not yet removable

n_dead_tup falls and stays low:

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'events';

Table size stops growing while row count is flat. It will not fall without a rewrite; flat is the success condition.

Run the partition-maintenance job while a vacuum is in progress, deliberately, and confirm it no longer cancels the worker. Cheap to do in a window, and it is the only test of the fix.

Prevention

Alert on canceling autovacuum task. It is the only signal that separates a vacuum that finished from one that started, there is no catalog column for it, and every dashboard metric here read as healthy for fourteen months.

Do not trust last_autovacuum or autovacuum_count. They record starts.

Set log_autovacuum_min_duration so completions are recorded and can be compared against starts:

ALTER SYSTEM SET log_autovacuum_min_duration = '1s';
SELECT pg_reload_conf();

Alert on n_dead_tup that never falls, per table. A count that only rises is a vacuum that is not completing.

Alert on table size growing while row count is flat. The plainest statement of bloat available, and it needs no extension.

Set lock_timeout in every scheduled DDL job.

Use DETACH PARTITION CONCURRENTLY where it applies.

Give large, high-churn tables their own autovacuum settings. A table that needs four hours of vacuuming needs to be vacuumed before it needs four hours.

Measure full-vacuum time on your largest tables once a year. If it exceeds the quiet window, that is a capacity fact to plan around rather than discover.