Debugging postgres: Showing recent queries

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:

  1. run the query CREATE EXTENSION pg_stat_statements;
  2. enable the pg_stat_statements-extension either in the postgresql.conf or 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'
      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.

See also