pg_stat_statements: Your New AWR

Where Did AWR Go?

When a PostgreSQL database is slow, the Oracle reflex — open AWR, pull an ASH report — has nowhere to go, and that’s disorienting. The closest and most valuable replacement is one extension: pg_stat_statements. It records normalized statistics for every query the server runs — how often, how long, how many rows, how much I/O — so “what is actually costing me” becomes a single ORDER BY.

Enabling it (and why it needs a restart)

It hooks the executor, so it must be preloaded — you can’t just CREATE EXTENSION it on a live server:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
track_io_timing = on          # so you get real I/O time, not just counts
# → restart, then:
#   CREATE EXTENSION pg_stat_statements;

The queries that find your real problems

Total time is almost always the right lens first — a fast query run a million times hurts more than a slow one run twice:

-- biggest total time consumers (your true top-N)
SELECT queryid, calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- slowest on average (candidates for an index or rewrite)
SELECT query, calls, round(mean_exec_time, 2) AS mean_ms
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

-- heaviest on I/O
SELECT query, shared_blks_read, shared_blks_hit
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

Measuring a clean window

Counters are cumulative since the last reset. To measure a specific load test or incident, reset first, run the workload, then read:

SELECT pg_stat_statements_reset();

Where it stops, and what fills the gap

• No snapshots by default. It’s cumulative, not per-hour like AWR. Snapshot it on a schedule (or use a tool that does) if you want time-window comparisons.
• No ASH-style wait sampling. For “what were sessions waiting on” you add pg_wait_sampling or a sampler on pg_stat_activity.
• Pair it with auto_explain to capture the actual plans of slow statements, and read them with EXPLAIN (ANALYZE, BUFFERS).

It isn’t a pixel-perfect AWR clone — but as the one tool you install on day one, it answers the question that matters most, faster than AWR ever did.