MVCC, VACUUM and Monitoring

Dead tuples, autovacuum, statistics

Because of MVCC, updates and deletes leave old row versions (dead tuples) that autovacuum later cleans up and makes reusable, while ANALYZE refreshes planner statistics. Long-running transactions block this cleanup, causing table bloat. Monitor with system views: pg_stat_user_tables (dead tuples, last autovacuum), pg_stat_activity (running and idle-in-transaction sessions), and the pg_stat_statements extension (slowest and most frequent queries). Tune autovacuum for very busy tables instead of disabling it.

Keep it healthy as it grows

Understand MVCC housekeeping, change schemas safely, and review with a checklist.

Useful monitoring queries (sketch)

Common queries for a running system; pg_stat_statements must be enabled. Not run here.

-- tables with the most dead rows
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;

-- sessions stuck idle inside a transaction
SELECT pid, state, now() - xact_start AS open_for, left(query, 60)
FROM pg_stat_activity WHERE state = 'idle in transaction';

-- most time-consuming queries
SELECT calls, round(total_exec_time) AS ms, left(query, 60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

Set idle-in-transaction timeouts

idle_in_transaction_session_timeout ends forgotten transactions that would otherwise block vacuum and hold locks.

त्वरित जाँच: What blocks autovacuum from cleaning up dead rows?

• Long-running or idle-in-transaction sessions
• Too many indexes only
• Small tables
• Using numeric columns