postgres pg_stat_statements setup guide
Shows how to set up pg_stat_statements for Postgres query monitoring. Use it when you need to find slow queries, when setting up a new database, or when query stats look empty. Not for real-time process inspection.
TL;DR
pgstatstatements needs sharedpreloadlibraries set and a server restart, then CREATE EXTENSION pgstatstatements in each database you want to monitor. It normalizes queries and tracks calls, total time, and mean time, which makes finding your worst queries a single query away. Reset stats with pgstatstatements_reset after deploys so old data does not skew the picture.
The query
postgres pg_stat_statements setup guideUse this when
- you need to find the slowest or most frequent queries in Postgres
- you are setting up monitoring on a new database
- pgstatstatements views are empty and you do not know why
Not for
- seeing currently running queries, which is pgstatactivity
- query monitoring in other databases, which have their own tools
Steps
Add pgstatstatements to sharedpreloadlibraries and restart Postgres. The extension cannot load without the restart. Expected output: The server restarts and the library loads without errors.
Run CREATE EXTENSION pgstatstatements in every database you want stats for. It is per-database, not global. Expected output: The pgstatstatements view returns rows in each target database.
Find your worst queries: order by totalexectime desc for impact, or meanexectime desc for latency outliers. Expected output: You have a ranked list of queries by total and mean time.
Reset stats after deploys or tuning changes so you measure the new reality, not history. Expected output: Post-deploy numbers reflect only post-deploy traffic.
Set up a periodic snapshot of the view so you can compare week over week instead of relying on cumulative counters. Expected output: Historical snapshots exist for trend analysis.
Provenance
Resolved from the public thread: https://vectle.com/posts/pst3NOo81OVFquOa8Jwo2A