PostgreSQL Query Optimization with pg_stat_statements: Track Down Slow Queries on Production

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Problem Statement: Sudden CPU Spikes and I/O Bottlenecks on Production

It usually starts with a PagerDuty alert at 2:00 AM: database CPU spikes to 98%, disk read IOPS hit their provisioned ceiling at 3,000 IOPS, and API p99 latency degrades from 45ms to 4.2 seconds. Application logs suddenly drown in 504 gateway timeouts. Yet those logs rarely tell you which SQL statement triggered the incident.

Reaching for traditional query logging during an active outage often makes things worse. Setting log_min_duration_statement = 250ms on an instance handling 1,500 transactions per second can generate 15GB of raw text logs per hour. That disk thrashing starves the database of the very I/O bandwidth it needs to recover, and it still leaves two critical questions unanswered:

  • Which query consumed the most aggregate CPU time over our six-hour peak window?
  • Is latency driven by a few expensive batch operations, or by a lightweight 2ms query fired 500,000 times inside an ORM N+1 loop?

Guessing indexes under pressure is equally dangerous. Every redundant index slows down INSERTs and UPDATEs while leaving the true culprit untouched. Beyond system-level PostgreSQL performance monitoring, you need low-overhead execution telemetry captured straight from shared memory.

Core Concepts: How pg_stat_statements Tracks Query Execution

PostgreSQL provides this visibility out of the box through pg_stat_statements. Unlike standard text logging that intercepts queries and dumps them to disk, this extension hooks directly into the query executor inside shared memory, adding less than 1-2% CPU overhead in typical production workloads.

1. Query Normalization and Parameter Masking

Dynamic applications constantly swap out query arguments. Recording every unique string would quickly blow out your memory cache. Take these two lookups:

SELECT email, status FROM users WHERE id = 1042;
SELECT email, status FROM users WHERE id = 9811;

pg_stat_statements hashes the query parse tree into a 64-bit integer called queryid. It replaces literal constants with bind parameter placeholders ($1, $2), grouping millions of individual invocations under a single fingerprint: SELECT email, status FROM users WHERE id = $1.

2. Critical Telemetry Metrics

For each normalized statement, the extension exposes vital runtime counters:

  • calls: Total execution count.
  • total_exec_time & mean_exec_time: Aggregate and average runtimes in milliseconds. Comparing these metrics separates high-frequency micro-queries from infrequent, resource-heavy batch jobs.
  • shared_blks_hit & shared_blks_read: Buffer cache efficiency. A hit means PostgreSQL found the 8KB page in RAM (shared_buffers). A read means it had to query the OS page cache or pull data off NVMe/EBS storage.
  • shared_blks_dirtied & shared_blks_written: Write volume generated by mutations.
  • blk_read_time & blk_write_time: Milliseconds spent waiting on disk I/O (requires track_io_timing = on).

Hands-On Practice: Setup, Detection, and Remediation

Step 1: Enabling pg_stat_statements in PostgreSQL

Because the module reserves a fixed hash table in shared memory at boot, you must preload it. Add these lines to postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# Configure tracker capacity and scope
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off

# Enable microsecond-level I/O timing metrics
track_io_timing = on

Setting track_utility = off excludes DDL statements like VACUUM or CREATE INDEX, keeping your stats focused purely on application queries. Apply the changes with a service restart:

sudo systemctl restart postgresql

Once restarted, enable the view in your application database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 2: Pinpointing the Top 5 Queries by Total Runtime

A query running in 12ms looks harmless in isolation. But if workers call it 800 times a second, it will quietly choke your server. Run this query to locate cumulative CPU hogs:

SELECT 
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage_overall,
    substr(query, 1, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Pay close attention to percentage_overall. If one query accounts for 35% of total runtime across your entire cluster, optimizing that single statement cuts your database load by a third immediately.

Step 3: Detecting I/O Bottlenecks and Cache Misses

Fetching data from RAM takes nanoseconds; fetching it from storage takes milliseconds. When shared_blks_read dwarfs shared_blks_hit, PostgreSQL is starving for cache hits:

SELECT 
    queryid,
    calls,
    shared_blks_read,
    shared_blks_hit,
    round((100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0))::numeric, 2) AS cache_hit_ratio,
    round(blk_read_time::numeric, 2) AS io_read_wait_ms,
    substr(query, 1, 120) AS query_preview
FROM pg_stat_statements
WHERE (shared_blks_read + shared_blks_hit) > 5000
ORDER BY shared_blks_read DESC
LIMIT 5;

Target a cache hit ratio above 99% for transactional OLTP workloads. Anything under 95% indicates an unindexed scan forcing full table sweeps, or a working set that has outgrown your current shared_buffers pool.

Step 4: Real-World Case Study: Fixing a Costly Sequential Scan

On an e-commerce platform processing 12,000 orders an hour, the I/O inspection query flagged this lookup running with heavy disk wait times:

SELECT order_id, amount, status FROM orders WHERE customer_id = $1 AND status = $2;

Running EXPLAIN (ANALYZE, BUFFERS) with production-like arguments showed the raw execution plan:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_id, amount, status 
FROM orders 
WHERE customer_id = 91842 AND status = 'COMPLETED';

The query planner bypassed existing single-column indexes:

Seq Scan on orders  (cost=0.00..84520.10 rows=4 width=28) (actual time=42.105..48.310 rows=3 loops=1)
  Filter: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
  Rows Removed by Filter: 1850200
  Buffers: shared read=38410

PostgreSQL loaded 38,410 buffer pages—about 300MB of data—off disk just to find 3 matching records out of 1.85 million. To fix this without locking write operations on a live table, we created a composite index concurrently:

CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status);

A second plan inspection verified the payoff right away:

Bitmap Heap Scan on orders  (cost=4.30..18.40 rows=4 width=28) (actual time=0.045..0.052 rows=3 loops=1)
  Recheck Cond: ((customer_id = 91842) AND (status = 'COMPLETED'::text))
  Buffers: shared hit=4
  ->  Bitmap Index Scan on idx_orders_customer_status  (cost=0.00..4.30 rows=4 width=0) (actual time=0.021..0.021 rows=3 loops=1)
        Buffers: shared hit=2

The database read zero blocks from disk, serving the query entirely from 6 buffer hits in RAM. Execution latency plunged from 48.3ms to 0.05ms—a 960x speedup.

Step 5: Resetting Baseline Statistics

Old metrics blur the impact of newly deployed indexes and configuration tweaks. Clear stale stats to measure the performance of subsequent deployments cleanly:

-- Reset all counters across the database cluster
SELECT pg_stat_statements_reset();

-- Or clear only the fixed query ID (PostgreSQL 12+)
SELECT pg_stat_statements_reset(userid, dbid, 3421890241);

Conclusion

Stop tuning databases by intuition. Combining pg_stat_statements with track_io_timing gives you hard telemetry on memory hits, execution counts, and disk stalls at almost zero performance cost. Check your top queries by total execution time first, eliminate the worst sequential scans with composite indexes, and let empirical metrics guide your tuning.

Share: