Postgres’ built-in pg_stat_activity
-view shows the list of all open connections, including the current query from that connection in the query
-column. What if that is not enough? How can you see not only the current query, but a list of recent queries?
That is what the pg_stat_statements
extension is for.
Enabling the pg_stat_statements
-extension
Unlike pg_stat_activity
, which is active by default, pg_stat_statements
needs to be enabled on the postgres server. There are two steps:
- run the query
CREATE EXTENSION pg_stat_statements;
- enable the
pg_stat_statements
-extension either in thepostgresql.conf
or by starting the server withpostgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all
(Note: You can do step 2 first, but you will need to restart the server after step 1.)
If you are using docker-compose
, here is an easy way to enable the extension without needing to create custom postgres-image:
version: '3.6'
services:
postgres:
image: postgres:14-alpine
command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all
Once you have enabled the extension you can query the view:
SELECT * FROM pg_stat_statements;
This will not only show you the list of all queries, it will also give useful statistics about each query:
- the number of times the query was executed
- the minimum and maximum time the query needed when executed, as well as the total, mean, and standard deviation
- the total number of rows affected by the query
- and several IO related stats
See the documentation for details. – make sure to check your postgres version, as the column-names have changed recently.
If you liked this post, please, do share it:
Thanks, for reading (and sharing)! 🥳