PostgreSQL Monitoring with pg_stat_statements

Query Monitoring with pg_stat_statements

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for every SQL statement. It's the single most valuable monitoring tool for identifying slow queries, high-frequency queries, and optimization opportunities.

Installation and Setup

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

-- After restart, enable the extension
CREATE EXTENSION pg_stat_statements;

Top Queries by Total Time

SELECT query, calls, total_exec_time::numeric(12,2) as total_ms,
       mean_exec_time::numeric(12,2) as avg_ms,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Building Grafana Dashboards

Connect Grafana to your PostgreSQL instance and create panels for:

• Top 10 queries by total execution time (identifies biggest optimization targets)
• Queries with highest cache miss ratio (need more shared_buffers or indexes)
• Query call frequency over time (detect traffic pattern changes)
• Mean execution time trends (detect performance regressions)

Eazy SaaS Tip: Reset statistics weekly with SELECT pg_stat_statements_reset() to keep the data fresh and relevant. Focus optimization on the top 5 queries by total_exec_time — they usually account for 80% of database load.