SQLSTATE 57014 — query_canceled: Autovacuum Task Canceled by Lock Conflict

! Symptoms Free

Conditions:

• Log shows ERROR: canceling autovacuum task on specific tables
• pg_stat_user_tables.n_dead_tup keeps growing for the affected table
• pg_stat_user_tables.last_autovacuum timestamp is stale (hours/days ago)
• Long-running queries (pg_stat_activity with old xact_start) on the same table
• age(relfrozenxid) approaching autovacuum_freeze_max_age (200M transactions)

1 Environment & reproduce Free

Difficulty: Intermediate  |  PostgreSQL versions: 12, 13, 14, 15, 16, 17

? Root cause Free

• canceling autovacuum task is not a crash — autovacuum politely yields to user queries. But if it is cancelled repeatedly, dead tuple accumulation risks transaction ID wraparound (which DOES force a full-table emergency VACUUM).
• idle_in_transaction_session_timeout is the best prevention — it kills sessions that have been idle-in-transaction for too long, freeing vacuum to proceed.
• Manually running VACUUM tablename competes with user queries just like autovacuum. For wraparound risk: VACUUM FREEZE tablename takes priority.
• vacuum_cost_delay controls autovacuum's I/O throttling — reducing it makes autovacuum faster but more intrusive.
• autovacuum_vacuum_cost_delay = -1 means autovacuum uses vacuum_cost_delay setting.

Terminate long-running idle-in-transaction sessions. Set idle_in_transaction_session_timeout. For urgent wraparound risk: run VACUUM FREEZE manually. Tune autovacuum aggressiveness for the affected table.

2 Diagnose Free

-- Find tables with high dead tuple counts and stale vacuum:
SELECT schemaname, 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,
    last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 20;

-- Find tables close to wraparound (CRITICAL if age > 1.5 billion):
SELECT relname,
    age(relfrozenxid) AS xid_age,
    current_setting('autovacuum_freeze_max_age')::int AS freeze_max
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC LIMIT 10;

-- Find long-running queries blocking vacuum:
SELECT pid, usename, state, now() - xact_start AS txn_age, left(query,80)
FROM pg_stat_activity
WHERE xact_start < now() - INTERVAL '5 minutes'
ORDER BY xact_start;

🔒 Diagnose deeper Pro

3 Recovery & verify Free

Terminate long-running idle-in-transaction sessions. Set idle_in_transaction_session_timeout. For urgent wraparound risk: run VACUUM FREEZE manually. Tune autovacuum aggressiveness for the affected table.