How to fix Skipping vacuum of "table" --- lock not available in PostgreSQL

Enable log_autovacuum_min_duration if not already set. Monitor PostgreSQL logs for which tables are frequently skipped. Use:

SELECT schemaname, relname, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables 
WHERE last_autovacuum IS NULL OR last_autovacuum < now() - interval '1 hour';

While the message appears, query the system views to identify what holds locks:

SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE pid IN (
  SELECT pid FROM pg_locks 
  WHERE relation = 'your_table'::regclass AND NOT granted
);

This shows connections that are holding or waiting for locks.

Identify and close idle transactions holding locks. In your application:
- Use connection pooling (PgBouncer, pgpool) to prevent connection leaks
- Set idle_in_transaction_session_timeout (PostgreSQL 13+): ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
- Close cursor-holding connections that are no longer needed

For already-stuck connections, carefully use:

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid != pg_backend_pid() AND state = 'idle in transaction';

If VACUUM TRUNCATE is causing lock conflicts, disable it:

ALTER TABLE your_table SET (vacuum_truncate = off);

This prevents VACUUM from trying to return disk space to the OS, which requires an exclusive lock. Space is still reclaimed for reuse within PostgreSQL, just not returned to the OS.

If you run manual VACUUM commands, use the SKIP_LOCKED option:

VACUUM (SKIP_LOCKED) your_table;

This tells VACUUM to skip tables that have conflicting locks instead of waiting. Works for both VACUUM and ANALYZE.

Set appropriate timeouts to prevent vacuum from waiting forever:

ALTER SYSTEM SET lock_timeout = '10s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

These limits prevent both user queries and maintenance tasks from blocking indefinitely.