VACUUM, ANALYZE, and Monitoring
PostgreSQL uses MVCC and creates new row versions for updates and deletes. VACUUM helps reclaim space for reuse and prevents transaction ID wraparound; autovacuum performs routine maintenance. ANALYZE gathers statistics used by the query planner. VACUUM FULL rewrites a table and takes a stronger lock, so it is not a routine substitute for ordinary vacuum.
Monitor connections, slow queries, locks, replication lag, disk growth, cache behavior, and autovacuum activity. Use pg_stat_activity, pg_stat_statements when installed, logs, and execution plans to diagnose issues. Establish baselines before tuning; a single metric rarely explains the entire performance picture.
Inspect active sessions
SELECT pid, usename, state, wait_event_type, wait_event,
query_start, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;💡 The view provides information about server processes and their current activity. Access to query text and other details can depend on privileges and server configuration.
Key Takeaways
- Autovacuum handles routine maintenance in most installations.
- ANALYZE updates planner statistics.
- Monitor workload patterns and diagnose with evidence before tuning.
Knowledge Check
What is a primary purpose of ANALYZE?