Monitor Queries with pg_stat_statements

You've tuned work_mem, added indexes, and rewritten queries until your fingers ache — but what if the slow query you're chasing isn't the one you think it is? Without visibility into what your database actually runs, you're guessing, and guessing wastes hours. pg_stat_statements ends the guesswork: it's the built-in PostgreSQL extension that records every query execution, its frequency, timing, and resource consumption, giving you a precise performance report straight from the source. In this lesson, you'll learn how to enable and use pg_stat_statements to monitor queries, spot bottlenecks, and confidently decide where to optimize next, without expensive external tools.

The problem this lesson solves

Every database has a handful of queries that consume 90% of its resources — but they're not always the ones in your most recent code review. Maybe it's a runaway JOIN from a background job, a missing index on a hot lookup table, or a query that was fine at 10,000 rows and now crawls at 10 million.

The core pain: Without monitoring, you can't see which queries are slow, how often they run, or how much CPU, I/O, and memory they use. You end up optimizing the wrong things while the real culprits hide in plain sight.

pg_stat_statements solves this by tracking every SQL statement executed by the server (within your configured limits) and aggregating key statistics: total execution time, average time, calls, rows returned, block reads, and temporary file usage. It's like a flight recorder for your SQL — you can rewind and ask: "What was the slowest query last night?"

This lesson matters because performance monitoring is the prerequisite for performance tuning. You wouldn't fix a broken engine without a diagnostic scanner; pg_stat_statements is your diagnostic scanner for PostgreSQL.

Core concept / mental model

Think of pg_stat_statements as a performance ledger for your database. Instead of reading individual log lines, you query a built-in view that summarizes every query's behavior.

• Query normalization: PostgreSQL replaces literal values with placeholders like $1 so that SELECT * FROM users WHERE id = 42 and SELECT * FROM users WHERE id = 99 are counted as the same query. This gives you a fair aggregate view.
• Rolling stats: The extension keeps cumulative counters from the moment it's loaded (or since stats reset). You can reset them to start a fresh monitoring window.
• Key metrics: calls (execution count), total_exec_time, mean_exec_time, max_exec_time, rows (rows returned), shared_blks_read (disk blocks), shared_blks_hit (cache hits), and temp_files / temp_bytes (spill to disk).

Mental model: Each row in pg_stat_statements is a unique normalized query. The columns tell you how many times it ran, how long it took, and how much disk/cache it touched. Sort by total_exec_time to find your biggest time sinks.

The stats persistence

pg_stat_statements stores statistics in shared memory and writes them to a regular table (pg_stat_statements view reads that memory). This means statistics are not persisted across restarts unless you enable the pg_stat_statements.max GUC and save them periodically — but for most monitoring, that's fine; you can reset and start fresh.

How it works step by step

Enabling pg_stat_statements involves a few configuration steps, then you query the extension. Here's the logical flow:

1. Add the extension to the shared preload libraries so it starts with the server.
2. Create the extension in the database where you want to query the view.
3. Configure limits (optional): set pg_stat_statements.max (number of unique queries to track) and pg_stat_statements.track (whether to track top-level or nested statements).
4. Restart PostgreSQL (required for shared_preload_libraries changes).
5. Run your workload — existing queries will begin to be recorded.
6. Query the view to analyze performance.
7. Reset stats when you want a clean slate for a new test.

Key configuration parameters

When track is top, only top-level statements are tracked — good for most cases. If you want to see inside functions or procedures, use all (with a performance cost).

Hands-on walkthrough

Step 1: Enable the extension

Edit your postgresql.conf (or use ALTER SYSTEM):

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

Then restart the server:

sudo systemctl restart postgresql

Step 2: Create the extension in your database

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 3: Generate some workload

-- Run a few sample queries
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 43;
SELECT * FROM orders WHERE created_at > NOW() - interval '1 day';

Step 4: Query the monitoring view

SELECT
  queryid,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND(max_exec_time::numeric, 2) AS max_ms,
  rows,
  shared_blks_hit,
  shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Expected output: a table showing repeated queries with high total time, helping you spot hot spots.

Step 5: Reset stats for a clean test

SELECT pg_stat_statements_reset();

Pro tip: Reset stats before deploying a new feature or running a benchmark to get a clean measurement window.

Compare options / when to choose what

pg_stat_statements is powerful, but it's not the only tool. Here's a comparison:

When to choose what

• Choose pg_stat_statements when you want to monitor queries over time, find top time-consuming queries, and track performance before/after changes.
• Choose EXPLAIN ANALYZE when you've identified a problematic query and need to understand its execution plan in detail.
• Choose auto_explain if you need to log every query that exceeds a threshold, e.g., auto_explain.log_min_duration = '1s'.
• Choose third-party tools when you need long-term storage, alerting, and visual dashboards — but even then, pg_stat_statements is the foundation.

Troubleshooting & edge cases

"Extension pg_stat_statements must be loaded via shared_preload_libraries"

This error occurs if you try to CREATE EXTENSION before setting shared_preload_libraries. Solution: add to config, restart, then create.

Stats are empty (all zeros) after restart

If you didn't set pg_stat_statements.save = on, statistics are zeroed on restart. Also, if the extension isn't in shared_preload_libraries, no data is collected.

Only one query is tracked

If pg_stat_statements.track = top, nested queries inside functions won't appear. Change to all if you need them — but expect a slight overhead.

High memory usage

Setting pg_stat_statements.max too high (e.g., 100,000) consumes shared memory. Monitor memory and adjust to a realistic value (5,000–20,000 is typical).

Query text is truncated

The default track_activity_query_size limits stored query text (usually 1024 bytes). Increase it in config if you need longer statements: track_activity_query_size = 8192.

The view returns rows even after reset

Because pg_stat_statements is in shared memory, resetting clears counters immediately, but new queries start to accumulate again. That’s normal.

What you learned & what's next

You now know how to monitor queries with pg_stat_statements: enabling the extension, configuring it, and using the view to identify performance bottlenecks. You can answer questions like "Which queries are consuming the most time?" and "How many times does this heavy query run?" You also understand how to reset stats for targeted testing and when to complement this tool with EXPLAIN ANALYZE or auto_explain.

Next step: Now that you can spot slow queries, the natural next step is to optimize them. In the upcoming lesson, you'll learn how to interpret EXPLAIN output and use that knowledge to add indexes and restructure queries for maximum performance. Monitoring is only half the battle — fixing is the next.

Keep this lesson’s example queries handy; you’ll reuse them as a baseline when you measure the impact of your optimization work.