pg_stat_statements Not Capturing Queries

Scenario

The DBA team deploys a new query performance dashboard powered by pg_stat_statements. The dashboard queries pg_stat_statements and returns no rows. The extension exists but pg_stat_statements.track is set to none. Additionally, the extension was not loaded at startup — it was only CREATE EXTENSION-ed after the server was already running without shared_preload_libraries including it. The result: the view exists but captures nothing.

How to Identify

Conditions:

• SELECT COUNT(*) FROM pg_stat_statements; returns 0 even after hours of activity
• pg_stat_statements exists in pg_extension but was never in shared_preload_libraries
• pg_stat_statements.track = 'none' in pg_settings
• SHOW shared_preload_libraries does not include pg_stat_statements
• Server log shows no “pg_stat_statements” at startup

Analysis Steps

-- Check if extension is installed:
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_statements';
-- If no row: CREATE EXTENSION pg_stat_statements hasn't been run yet

-- Check if it's actually loaded (instrumentation active):
SELECT name, setting FROM pg_settings
WHERE name LIKE 'pg_stat_statements%'
ORDER BY name;
-- pg_stat_statements.track = 'none' means capturing nothing
-- If these settings don't appear: library not loaded at startup

-- Check shared_preload_libraries (requires restart to change):
SHOW shared_preload_libraries;
-- Must include 'pg_stat_statements' for the library to be active

-- Try to query the view — should work if extension is created:
SELECT count(*) FROM pg_stat_statements;
-- 0 rows even after activity = tracking is disabled or library not loaded

-- Check pg_stat_statements.track setting:
SELECT name, setting, source
FROM pg_settings
WHERE name = 'pg_stat_statements.track';
-- 'none' = capturing nothing; 'top' = top-level queries only; 'all' = all including nested

Pitfalls

• CREATE EXTENSION pg_stat_statements is not enough — the library must be in shared_preload_libraries and the server restarted before the extension can instrument queries.
• pg_stat_statements.track = 'none' silently disables all tracking even when the library is loaded.
• The default pg_stat_statements.max = 5000 (PG13) can fill up — older entries are evicted. Increase if you have many distinct query shapes.
• pg_stat_statements_reset() wipes all statistics — typically done on a schedule, not during investigation.
• Calling pg_stat_statements as a non-superuser without pg_read_all_stats shows only that role’s own queries.

Resolution Approach

Add pg_stat_statements to shared_preload_libraries, restart PostgreSQL, create the extension, and set pg_stat_statements.track = 'top'. Verify by running a query and checking pg_stat_statements for the new entry.