← Back to Curriculum

Monitoring and Maintenance

📚 Lesson 13 of 15 ⏱️ 55 min

Monitoring and Maintenance

55 min

Regular monitoring and maintenance are essential for keeping PostgreSQL databases running efficiently and reliably. Monitoring helps you identify performance issues, capacity problems, and potential failures before they impact users. Maintenance tasks like VACUUM, ANALYZE, and REINDEX keep the database optimized and prevent performance degradation over time. Understanding monitoring and maintenance enables you to proactively manage database health.

VACUUM is PostgreSQL's housekeeping process that removes dead tuples (rows that have been deleted or updated) and reclaims storage space. Dead tuples accumulate from UPDATE and DELETE operations, as PostgreSQL uses MVCC (Multi-Version Concurrency Control) which keeps old row versions until they're no longer needed. VACUUM also updates the visibility map and free space map, enabling the query planner to make better decisions. Regular VACUUM prevents table bloat and maintains performance.

VACUUM FULL is a more aggressive form of VACUUM that rewrites entire tables, reclaiming all free space and defragmenting tables. However, VACUUM FULL requires an exclusive lock and is much slower than regular VACUUM. It should be used sparingly, typically when tables have significant bloat. Autovacuum (automatic VACUUM) runs regularly in the background, but manual VACUUM may be needed for high-activity tables or after bulk operations.

ANALYZE updates table statistics that the query planner uses to create efficient query plans. Statistics include information about data distribution, column value frequencies, and table sizes. Without current statistics, the planner may choose inefficient query plans. ANALYZE is typically run automatically by autovacuum, but should be run manually after bulk data loads or significant data changes. Current statistics are essential for optimal query performance.

PostgreSQL provides system catalogs and views for monitoring database health. pg_stat_user_tables shows table-level statistics including row counts, dead tuples, and index usage. pg_stat_user_indexes shows index usage statistics. pg_stat_activity shows current database connections and queries. pg_stat_database provides database-level statistics. Understanding these views enables you to monitor performance, identify bottlenecks, and track database usage patterns.

Additional maintenance tasks include REINDEX for rebuilding corrupted or bloated indexes, checking for table bloat using pg_stat_user_tables, monitoring connection counts, and reviewing slow queries. Regular maintenance prevents performance degradation and ensures the database continues to perform well as data grows. Automated maintenance through autovacuum handles most tasks, but understanding manual maintenance enables you to address specific issues when they arise.

Key Concepts

  • Regular monitoring and maintenance keep databases running efficiently.
  • VACUUM removes dead tuples and reclaims storage space.
  • ANALYZE updates statistics for the query planner.
  • System catalogs provide monitoring data for database health.
  • Autovacuum automates most maintenance tasks.

Learning Objectives

Master

  • Understanding VACUUM and its role in database maintenance
  • Using ANALYZE to update query planner statistics
  • Monitoring database health using system catalogs
  • Performing manual maintenance when needed

Develop

  • Understanding database maintenance requirements
  • Designing monitoring and alerting strategies
  • Proactively managing database performance

Tips

  • Let autovacuum handle most VACUUM tasks automatically.
  • Run ANALYZE after bulk data loads to update statistics.
  • Monitor pg_stat_user_tables for dead tuples and bloat.
  • Use pg_stat_activity to identify long-running queries.

Common Pitfalls

  • Not running VACUUM, causing table bloat and performance degradation.
  • Ignoring autovacuum warnings, missing maintenance issues.
  • Not updating statistics, causing poor query plans.
  • Not monitoring database health, missing performance problems.

Summary

  • Regular maintenance prevents performance degradation.
  • VACUUM removes dead tuples and reclaims space.
  • ANALYZE updates statistics for optimal query planning.
  • Monitoring system catalogs helps identify issues early.

Exercise

Perform maintenance tasks and monitor database health.

-- Check table and index sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check for bloat
SELECT 
    schemaname,
    tablename,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    n_live_tup as live_tuples,
    n_dead_tup as dead_tuples
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

-- Run VACUUM
VACUUM ANALYZE;

-- Check for long-running queries
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state = 'active';

-- Update table statistics
ANALYZE;

Exercise Tips

  • Schedule regular VACUUM: use pg_cron extension for automated maintenance.
  • Monitor autovacuum: check pg_stat_progress_vacuum for vacuum progress.
  • Use VACUUM VERBOSE to see detailed information about vacuum operations.
  • Check index bloat: use pg_stat_user_indexes to identify unused indexes.

Code Editor

Output