Vacuum, Maintenance and Monitoring

Why Vacuum Matters

Updates and deletes leave dead row versions behind. PostgreSQL does not overwrite rows in place; it marks them dead. VACUUM reclaims that space, and autovacuum does it for you in the background.

Vacuum and Analyze

VACUUM (VERBOSE, ANALYZE) logs;

ANALYZE refreshes planner statistics in the same pass, which keeps query plans accurate.

Watching Activity

SELECT pid, state, wait_event, query
FROM pg_stat_activity
WHERE state NOT IN ('idle', 'idle in transaction');

Autovacuum Tuning

Raise the autovacuum thresholds for very large tables and watch for long-running transactions, which can stall vacuum and bloat indexes.

Key Points

• Dead rows make tables and indexes grow.
• VACUUM ANALYZE reclaims space and fixes statistics.
• pg_stat_activity reveals live queries and locks.
• Autovacuum needs monitoring on large tables.