FULL AI ANALYSIS

Logical backups hang waiting for locks when VACUUM FULL is running on large PostgreSQL tables

Generated Oct 3, 2026, 12:35:03 AM

Analysis summary

VACUUM FULL takes an exclusive lock on each table, which prevents other transactions (including pg_dump which needs consistent snapshots and may access the same tables) from proceeding. Logical backups like pg_dump require long-lived snapshots which conflict with VACUUM FULL. Using pg_basebackup (physical backup) avoids these logical lock conflicts because it operates at a different level, but many organizations depend on logical dumps for portability or text-based schema backups. The underlying issue is performing exclusive operations concurrently with logical snapshot backups.

Possible causes

VACUUM FULL demands exclusive locks, conflicting with long-lived logical backup transactions.

98%

Logical backup method chosen (pg_dumpall) requires a consistent snapshot that can't be created or progressed while exclusive locks are held.

92%

Large bloat forcing use of VACUUM FULL instead of online maintenance tools causing long maintenance windows and blocking.

88%

Backup and maintenance windows overlap due to scheduling constraints; autovacuum settings delayed causing manual VACUUM FULL runs.

80%

Suggested fixes

Diagnostic step 1

Prefer physical backups for nightly backups if restore requirements permit: use pg_basebackup or filesystem-level snapshotting of a consistent data directory while WAL archiving is enabled. Test restores on a non-production host before switching.

Diagnostic step 2

Avoid VACUUM FULL on production during backup windows. Use alternatives such as VACUUM (non-FULL) combined with REINDEX CONCURRENTLY and partitioning large tables to reduce bloat without exclusive locks.

Diagnostic step 3

Consider pg_repack to reclaim bloat online without requiring exclusive locks; test on staging first as it requires appropriate extensions and disk capacity for copies.

Diagnostic step 4

If logical backups are mandatory, schedule them on a standby replica to avoid locking the primary. Configure a replica and run pg_dump there; ensure the replica is caught up or accept recovery_min_apply_delay tradeoffs.

Diagnostic step 5

Document and automate a maintenance plan: small frequent maintenance (vacuum/analyze) to prevent massive bloat, set autovacuum tuning for large tables, and schedule any necessary VACUUM FULL / exclusive operations during a planned maintenance window with stakeholder notification. Escalate to DBAs if storage constraints or regulatory backup format requirements prevent moving to physical backups or replicas.

Contact • Help • API Docs • Careers • Terms • Privacy • Cookies • Ads