PostgreSQL Monitoring and Maintenance

Proper monitoring and maintenance are critical for ensuring PostgreSQL databases remain healthy, performant, and reliable. Regular health checks, vacuuming, backups, and monitoring prevent data loss, reduce downtime, and improve query performance.

1. Monitoring Database Performance

• pg_stat_activity: View currently active queries and sessions.

  SELECT pid, usename, datname, state, query, query_start 
              FROM pg_stat_activity;

• pg_stat_statements: Track query execution statistics to identify slow or frequently executed queries.

  -- Enable extension
              CREATE EXTENSION pg_stat_statements;
  
              -- Query top 10 slowest queries
              SELECT query, calls, total_exec_time, mean_exec_time
              FROM pg_stat_statements
              ORDER BY mean_exec_time DESC
              LIMIT 10;

• Monitor replication and standby servers using pg_stat_replication:

SELECT pid, usename, application_name, state, sync_state
            FROM pg_stat_replication;

2. Regular Maintenance Tasks

• VACUUM: Reclaim storage and prevent table bloat.

  VACUUM my_table;
              VACUUM FULL my_table;

• ANALYZE: Update table statistics to help the query planner.

  ANALYZE my_table;

• REINDEX: Rebuild indexes to improve query performance.

  REINDEX TABLE my_table;

• Use autovacuum for automatic maintenance:

-- postgresql.conf
            autovacuum = on
            autovacuum_max_workers = 3
            autovacuum_naptime = 1min

3. Monitoring Disk Usage

• Check database size:

  SELECT pg_database.datname, pg_size_pretty(pg_database_size(datname)) AS size
              FROM pg_database;

• Check table size:

  SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size
              FROM pg_catalog.pg_statio_user_tables
              ORDER BY pg_total_relation_size(relid) DESC;

• Check index size:

  SELECT relname AS index, pg_size_pretty(pg_relation_size(relid)) AS size
              FROM pg_stat_user_indexes
              ORDER BY pg_relation_size(relid) DESC;

4. Logs and Alerts

• Enable logging for slow queries:

  -- postgresql.conf
              log_min_duration_statement = 500
              log_checkpoints = on
              log_connections = on
              log_disconnections = on

• Analyze logs using pgBadger for insights into performance and errors.
• Set up monitoring alerts using Prometheus and Grafana or cloud monitoring tools.

5. Connection and Resource Monitoring

• Check active connections:

  SELECT count(*) AS total_connections FROM pg_stat_activity;

• Monitor locks:

  SELECT pid, relation::regclass, mode, granted
              FROM pg_locks
              WHERE NOT granted;

• Monitor cache hit ratio:

  SELECT sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS hit_ratio
              FROM pg_stat_database;

6. Backup Verification

• Regularly test backups to ensure they can be restored.
• Perform restore drills periodically to simulate disaster recovery scenarios.

7. Best Practices for Maintenance

• Enable autovacuum and tune its parameters for large tables.
• Monitor table and index bloat regularly.
• Track slow queries and optimize them with indexes or query refactoring.
• Use connection pooling for production workloads.
• Maintain regular backups and test restore procedures.

Conclusion

Effective PostgreSQL monitoring and maintenance prevent performance degradation, downtime, and data loss. By combining regular vacuuming, analyzing, indexing, connection monitoring, logging, and alerting, you can maintain a healthy and reliable database environment.