Comprehensive guide to PostgreSQL performance monitoring using pg_stat_statements, EXPLAIN ANALYZE, and advanced optimization techniques. Learn how to identify bottlenecks and optimize complex queries in production environments.
PostgreSQL's advanced features make it powerful, but without proper monitoring, performance issues can go unnoticed until they become critical. Effective monitoring can prevent 95% of database performance problems and reduce response times by up to 90%. This guide covers enterprise-grade monitoring techniques used by companies processing millions of transactions daily.
Track execution statistics for SQL statements. Essential for identifying slow queries.
-- Enable extension
CREATE EXTENSION pg_stat_statements;
-- View top slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;
Analyze query execution plans and actual performance metrics.
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE created_at > '2024-01-01'
AND status = 'pending';
Monitor active connections and running queries in real-time.
SELECT pid, usename, application_name,
state, query_start, query
FROM pg_stat_activity
WHERE state = 'active';
Database-level statistics including connections, transactions, and blocks.
SELECT datname, numbackends,
xact_commit, xact_rollback,
blks_read, blks_hit
FROM pg_stat_database;
Use EXPLAIN ANALYZE to identify expensive operations like sequential scans, nested loops, and sorts.
-- Identify expensive operations
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC;
-- Look for:
-- • Seq Scan (should be Index Scan)
-- • High cost numbers
-- • Excessive buffer reads
Performance Impact: Proper query plan analysis can reduce execution time by 85%
Monitor connection usage to prevent connection exhaustion and optimize pool size.
-- Monitor active connections
SELECT
state,
COUNT(*) as connection_count,
AVG(EXTRACT(EPOCH FROM (now() - query_start))) as
avg_duration
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY state;
-- Check for connection leaks
SELECT pid, usename, application_name,
state_change, query
FROM pg_stat_activity
WHERE state_change < now() - interval '1 hour';
Analyze buffer cache hit ratio to optimize memory usage and reduce disk I/O.
-- Calculate buffer cache hit ratio
SELECT
schemaname,
tablename,
heap_blks_read,
heap_blks_hit,
CASE
WHEN heap_blks_hit + heap_blks_read = 0 THEN
0
ELSE ROUND(
heap_blks_hit::numeric /
(heap_blks_hit + heap_blks_read) * 100, 2
)
END as hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;
Monitor slow queries, connection limits, and disk space with automated notifications.
Track performance trends over time to identify patterns and prevent issues.
Maintain table statistics and prevent bloat with regular maintenance operations.
Track table and row locks to identify concurrency bottlenecks.
Get expert PostgreSQL performance monitoring setup and optimization. Our comprehensive analysis identifies bottlenecks and provides automated monitoring solutions.
Get PostgreSQL Analysis