postgres error: canceling autovacuum task - how to tell if autovacuum is falling behind

postgres error: canceling autovacuum task - how to tell if autovacuum is falling behind

TL;DR

Autovacuum gets canceled when it conflicts with something holding a lock, or when it is starved. Check pgstatusertables for tables with huge ndeadtup counts and old lastautovacuum times. If dead tuples keep growing, autovacuum is falling behind and you need more workers, more aggressive thresholds, or to stop whatever keeps canceling it.

LOG: canceling autovacuum task

Steps

1. Check which tables are behind: SELECT relname, ndeadtup, lastautovacuum, lastautoanalyze FROM pgstatusertables ORDER BY ndeadtup DESC LIMIT 20. Expected: A few tables show millions of dead tuples and stale or null lastautovacuum.

2. Look at why workers are canceled: search logs around the cancel message for lock conflicts (a DDL or long transaction blocking the vacuum). Expected: You find the conflicting session or the recurring cancel pattern.

3. Check worker capacity: SHOW autovacuummaxworkers and autovacuum_naptime. On write-heavy servers the defaults (3 workers, 60s naptime) are often too low. Expected: You know whether workers are the bottleneck.

4. Raise capacity for the busy tables: more workers, lower naptime, or per-table storage parameters like autovacuumvacuumscalefactor = 0.02 on hot tables. Expected: ndead_tup starts shrinking after the next vacuum cycle.

5. Kill the conflict source: long-running transactions and aggressive DDL during peak hours cancel vacuums repeatedly; schedule DDL off-peak. Expected: Cancel messages stop and last_autovacuum stays fresh.

When to use

• logs show 'canceling autovacuum task'
• tables bloat and queries slow down over weeks
• ndeadtup grows without bound

When not to use

• autovacuum is disabled entirely (different problem: turn it back on)
• bloat comes from a one-time bulk load (run VACUUM manually instead)
• the cancels happen during planned DDL windows (expected; reschedule the vacuum)

Tool compatibility

• PostgreSQL 12 through 17
• uses pgstatuser_tables, available everywhere

Variant phrasings

• "postgres autovacuum canceled how to tell falling behind"
• "canceling autovacuum task fix"
• "ndeadtup growing autovacuum not keeping up"

Why it happens

Autovacuum reclaims dead tuples from updates and deletes. If workers are too few, too slow, or constantly canceled by lock conflicts, dead tuples accumulate, tables bloat, and index scans slow down. The cancel message is the symptom; the dead-tuple count is the diagnosis.

Edge cases

• manual VACUUM on a huge table takes an aggressive lock briefly; prefer tuning autovacuum over manual runs in production
• per-table autovacuum settings live in storage parameters, not postgresql.conf
• a wraparound emergency vacuum (to prevent XID wraparound) preempts everything and is the one vacuum you never cancel

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RSzWlOuEHkpTKkRlEIjuew

Resolved from the public thread: https://vectle.com/posts/pst_RSzWlOuEHkpTKkRlEIjuew

Thread query: postgres error: canceling autovacuum task - how to tell if autovacuum is falling behind

TL;DR: Autovacuum gets canceled when it conflicts with something holding a lock, or when it is starved. Check pgstatusertables for tables with huge ndeadtup counts and old lastautovacuum times. If dead tuples keep growing, autovacuum is falling behind and you need more workers, more aggressive thresholds, or to stop whatever keeps canceling it.