PostgreSQL 9.6中Auto Vacuum/Vacuum无法释放死行的排查求助

Hey Varun, sorry to hear you're hitting this frustrating issue with dead rows not being freed by VACUUM or auto-vacuum in PostgreSQL 9.6. Let’s break down the key troubleshooting steps and parameter adjustments you can try to get to the bottom of this:

Start by verifying the actual number of dead rows and when the table was last vacuumed. Run these queries:

-- Check dead row count and vacuum timestamps
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
FROM pg_stat_user_tables 
WHERE relname = 'your_table_name';

-- Run a verbose manual vacuum to get detailed output
VACUUM VERBOSE your_table_name;

Look for messages in the verbose output that explain why dead rows aren't being removed—PostgreSQL often gives clues here, like "skipped frozen page" or "waiting for transaction".

PostgreSQL’s MVCC model means dead rows can’t be cleaned up if any active transaction still has a snapshot that can see them. Identify long-running idle or active transactions:

-- Find idle-in-transaction sessions that might be holding old snapshots
SELECT pid, now() - xact_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
ORDER BY duration DESC;

-- Check how old the table's frozen transaction ID is (critical for vacuum)
SELECT age(datfrozenxid) 
FROM pg_database 
WHERE datname = 'your_database_name';

If the age(datfrozenxid) value is close to vacuum_freeze_table_age (default 1.5 billion in 9.6), vacuum might be prioritizing freezing old tuples over cleaning dead rows.

Your table might have custom settings that override global auto-vacuum behavior. Check for these:

SELECT relname, reloptions 
FROM pg_class 
WHERE relname = 'your_table_name';

Look for options like autovacuum_enabled=false (which would disable auto-vacuum entirely for the table) or overly high vacuum_threshold/vacuum_scale_factor values that prevent auto-vacuum from triggering. You can adjust these with:

-- Example: Lower threshold and scale factor to trigger auto-vacuum sooner
ALTER TABLE your_table_name 
SET (autovacuum_vacuum_threshold = 500, autovacuum_vacuum_scale_factor = 0.01);

Vacuum can get stuck waiting for locks on the table. Check if there are conflicting locks:

-- Check locks on your table
SELECT pid, locktype, mode, relation::regclass 
FROM pg_locks 
WHERE relation = 'your_table_name'::regclass;

-- Check if a vacuum process is stuck waiting
SELECT pid, query, state, wait_event_type, wait_event 
FROM pg_stat_activity 
WHERE query LIKE '%VACUUM%';

If you see a vacuum process in waiting state, you might need to terminate the blocking session (with caution!) using SELECT pg_terminate_backend(blocking_pid);.

If table-level settings aren’t the issue, adjust these global parameters in postgresql.conf (remember to reload the config after changes with SELECT pg_reload_conf();):

• autovacuum_max_workers: Increase this if you have multiple tables needing vacuum and system resources allow (default is 3 in 9.6).
• vacuum_cost_limit: Raise this value (default 200) to let vacuum run faster without being throttled by cost-based delays.
• vacuum_freeze_min_age and vacuum_freeze_table_age: If your table’s frozen XID is getting too old, lower these values to prioritize freezing tuples earlier, which can free up dead rows afterward.

• Partitioned Tables: If your table is manually partitioned (PostgreSQL 9.6 doesn’t have native declarative partitioning), make sure you’re vacuuming all child tables too—auto-vacuum should handle them, but double-check.
• Trigger/External Dependencies: If your table has triggers or foreign keys pointing to other tables, long transactions on those related tables might block vacuum on your target table.
• Stale Statistics: Run ANALYZE your_table_name; to update table statistics, as outdated stats can make pg_stat_user_tables show incorrect dead row counts.

If you’ve gone through all these steps and still see dead rows lingering, share the output of VACUUM VERBOSE and the results of the transaction/lock queries—those details will help narrow things down further.

内容的提问来源于stack exchange，提问作者varun kamal