Continuous Query Profiling with pg_stat_statements & EXPLAIN (ANALYZE, BUFFERS): Unmasking Shared Buffer Churn

The Deceptive Nature of Slow Query Logs

Most engineering teams configure PostgreSQL performance alerts around log_min_duration_statement = '500ms'. While this successfully flags runaway analytical queries or unindexed joins, it leaves database operators completely blind to a far more dangerous performance parasite: high-frequency micro-queries.

Consider a simple authorization query running in 1.8 milliseconds. In isolation, it never triggers a slow query alarm. However, if this query executes 8,000 times per second and reads 150 unindexed disk blocks per execution, it forces PostgreSQL to read over 1.2 million blocks per second. This massive churn relentlessly evicts cached index trees and active tables from shared_buffers, causing a systemic cascade of I/O latency spikes across unrelated business transactions.

1. Enabling Continuous Telemetry with pg_stat_statements

To identify queries causing the highest systemic I/O impact, enable the pg_stat_statements extension in postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on

2. The Master Buffer Churn Detection Query

Execute this diagnostic query to rank SQL statements by total shared buffer page reads, revealing the exact queries driving disk I/O pressure:

-- Identify the Top 5 Buffer Churning Queries
SELECT 
    queryid,
    substring(query, 1, 60) AS clean_query,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_time_ms,
    shared_blks_hit,
    shared_blks_read,
    ROUND((shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0) * 100), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 5;

3. Decoding EXPLAIN (ANALYZE, BUFFERS)

When optimizing an identified query, standard EXPLAIN ANALYZE shows timing estimates but conceals I/O footprints. Always execute queries with the BUFFERS parameter enabled:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT user_id, balance 
FROM user_ledgers 
WHERE tenant_id = 'c8b417e2' AND status = 'settled';

Review the execution output for these key telemetry indicators:

• Buffers: shared hit=84 read=1203: Indicates that 1,203 pages (9.6 MB) had to be fetched directly from disk or OS page cache because they were missing from PostgreSQL RAM.
• Buffers: temp read=512 written=512: A critical red flag indicating that work_mem is undersized, forcing the query planner to spill in-memory sorts or hash tables onto disk.

Pairing continuous buffer profiling with PostgreSQL Query Planner Tuning keeps production database clusters running smoothly at multi-gigabyte scale. Explore our specialized audit services at Architecture Review & Audits.