Introduction

PostgreSQL VACUUM operation stuck when table has long-running transactions or locks. This guide provides step-by-step diagnosis and resolution.

Symptoms

Typical error output:

bash
ERROR: canceling statement due to lock timeout
VACUUM (FULL) operation blocked by: process 12345
waiting for ShareUpdateExclusiveLock on relation 16384

Common Causes

  1. 1.Long-running transaction holding locks on target table
  2. 2.Autovacuum workers already processing other tables
  3. 3.Table has many indexes requiring index vacuuming
  4. 4.Cost delay settings causing vacuum to run slowly

Step-by-Step Fix

Step 1: Check Current State

sql
SELECT * FROM pg_stat_activity WHERE state = 'active' AND query LIKE '%VACUUM%';
SELECT relation::regclass, mode, pid FROM pg_locks WHERE mode LIKE '%Exclusive%';
SELECT * FROM pg_stat_progress_vacuum;

Step 2: Identify Root Cause

sql
-- Check for blocking processes
SELECT * FROM pg_stat_activity WHERE state != 'idle';
SELECT * FROM information_schema.processlist WHERE time > 30;

Step 3: Apply Primary Fix

```sql -- Terminate blocking transaction SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < now() - interval '10 minutes';

-- Run vacuum with specific options VACUUM (VERBOSE, ANALYZE, PARALLEL 4) large_table; ```

Step 4: Apply Alternative Fix

```sql -- Alternative fix: Check configuration SELECT * FROM pg_settings WHERE name LIKE '%vacuum%';

-- Adjust parameters dynamically ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 10; SELECT pg_reload_conf();

-- Verify the fix SELECT * FROM pg_stat_user_tables WHERE relname = 'target_table'; ```

Step 5: Verify the Fix

sql
SELECT * FROM pg_stat_user_tables WHERE relname = 'large_table';
SELECT * FROM pg_stat_progress_vacuum WHERE relid = 'large_table'::regclass;
SELECT pg_size_pretty(pg_total_relation_size('large_table'));

Common Pitfalls

  • Running vacuum during peak hours without resource management
  • Forgetting to analyze after vacuum for statistics update
  • Not monitoring autovacuum progress on large tables
  • Setting cost delay too high for high-churn tables

Best Practices

  • Schedule maintenance windows for vacuum full operations
  • Monitor bloat ratio and autovacuum frequency
  • Tune autovacuum parameters per table based on churn rate
  • Use pg_stat_progress_vacuum to monitor vacuum progress
  • PostgreSQL Autovacuum Not Running
  • PostgreSQL Dead Tuple Accumulation
  • PostgreSQL Transaction ID Wraparound
  • PostgreSQL Table Size Excessive