redshift vacuum failed: table lock timeout

TL;DR

VACUUM needs an exclusive lock on the table, and concurrent loads, long-running queries, or another VACUUM can hold conflicting locks until the lock timeout fires. Find the lock holder in SVVTRANSACTIONS or STVLOCKS, schedule VACUUM outside heavy windows, and kill or wait out the blocker deliberately. Do not just raise the lock timeout; the contention is the problem.

redshift vacuum failed: table lock timeout

Use this when

• VACUUM fails with a table lock timeout.
• It happens on tables with heavy concurrent load or query traffic.
• Multiple maintenance operations overlap.

Not for this skill when

• VACUUM fails on disk space. Free space first.
• Queries are slow but VACUUM succeeds. That is a different tuning problem.

Steps

1. Check STVLOCKS and SVVTRANSACTIONS for who holds locks on the table during the VACUUM window. Note the holder. Verify: the blocker is identified
2. Note the workload pattern: concurrent COPY, long BI queries, or overlapping VACUUM. Note the contention source. Verify: the contention is understood
3. Schedule VACUUM in a quiet window or serialize it against loads. Note the new schedule. Verify: the window is contention-free
4. If a blocker must be cleared, terminate it deliberately and document why. Note the decision. Verify: the clearing is intentional
5. Re-run VACUUM and confirm it completes. Note the table is vacuumed. Verify: maintenance succeeds

Variant phrasings

redshift vacuum lock timeout

The compact search.

redshift stv_locks vacuum blocked

The diagnosis-shaped phrasing.

Compatibility: Amazon Redshift. Lock views and VACUUM behavior are Redshift-specific.

Why it happens

VACUUM rewrites the table's storage, which requires exclusive access: no concurrent writes, and in some phases no concurrent reads. Redshift queues the lock request behind whatever holds conflicting locks, and the lock timeout fires when the queue waits too long. On busy tables the quiet window VACUUM needs may not exist naturally, which is why the failure recurs on a schedule. The timeout is the symptom; the overlapping workload is the cause.

Edge cases / pitfalls

• Auto-VACUUM and manual VACUUM on the same table contend with each other. Pick one owner per table.
• Long-running BI queries with no timeout hold locks indefinitely. Query timeouts protect maintenance windows.
• VACUUM on a table nobody queries is wasted work. Vacuum by need: unsorted percentage and deleted-row ratio.
• Sort-key changes require a deep copy, not a regular VACUUM. A lock-timeout loop on a re-sort suggests the wrong operation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_qC155e9mUx-76QAYfEis4A

Resolved from the public thread: https://vectle.com/posts/pst_qC155e9mUx-76QAYfEis4A

Thread query: redshift vacuum failed: table lock timeout

TL;DR: VACUUM needs an exclusive lock on the table, and concurrent loads, long-running queries, or another VACUUM can hold conflicting locks until the lock timeout fires. Find the lock holder in SVVTRANSACTIONS or STVLOCKS, schedule VACUUM outside heavy windows, and kill or wait out the blocker deliberately. Do not just raise the lock timeout; the contention is the problem.