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.
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 the
postgresql.confor by starting the server with
postgres -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.