Enabling pg_stat_statements

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for every SQL statement run against the server. Query Doctor relies on it to know what queries your application is actually running — without it, there’s nothing to analyze.

Already enabled?

Before going through the setup, check if it’s already there:

SELECT count(*) FROM pg_stat_statements;

If you get a number back, you’re all set. If you see relation "pg_stat_statements" does not exist, keep reading.

Self-managed PostgreSQL

1. Edit postgresql.conf

Add pg_stat_statements to the shared preload libraries and turn on tracking:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Setting track = all makes sure statements inside functions and nested calls get tracked too, not just top-level queries.

2. Restart PostgreSQL

This setting requires a full server restart (a reload won’t do it):

# systemd
sudo systemctl restart postgresql

# pg_ctl
pg_ctl restart -D /var/lib/postgresql/data

3. Create the extension

Connect to your database and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Using a database migration

If your project uses an ORM or migration tool, putting CREATE EXTENSION in a migration is a good way to make sure the extension is always there — it becomes part of your schema history and runs automatically in every environment.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

This works well for the CREATE EXTENSION step, but keep in mind that shared_preload_libraries still needs to be configured at the server level (via postgresql.conf, Docker flags, or your provider’s settings). The migration handles the SQL side; the infrastructure handles the server config.

Note

CREATE EXTENSION requires superuser privileges (or the pg_execute_server_program role on Postgres 14+). If your migration user doesn’t have these, you’ll need to create the extension separately with a privileged account.

Docker

The official Postgres Docker image lets you pass -c flags directly to the server process, so you don’t need to touch any config files. There are two parts to this:

1. Server flags — tell Postgres to load the extension on startup
2. CREATE EXTENSION — register it in your database so you can actually query it

docker run

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:17 \
  -c shared_preload_libraries=pg_stat_statements \
  -c pg_stat_statements.track=all

Then create the extension:

docker exec -it postgres psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

docker-compose.yml

services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    command:
      [
        "-c",
        "shared_preload_libraries=pg_stat_statements",
        "-c",
        "pg_stat_statements.track=all",
      ]
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

After docker compose up, create the extension:

docker compose exec postgres psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Dockerfile

If you want the extension created automatically on first boot (no manual step), you can add an init script to a custom image:

FROM postgres:17

CMD ["-c", "shared_preload_libraries=pg_stat_statements", "-c", "pg_stat_statements.track=all"]

RUN echo "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" > /docker-entrypoint-initdb.d/pg_stat_statements.sql

The official Postgres image runs any .sql files in /docker-entrypoint-initdb.d/ the first time it initializes the data directory.

Caution

Init scripts only run on a fresh data directory. If you’ve already started the container before, either remove the volume (docker compose down -v) or create the extension manually.

Managed providers

Amazon RDS / Aurora

The extension ships with RDS but isn’t turned on by default. You’ll need to flip it on in your parameter group:

1. In the RDS console, find your DB parameter group
2. Set shared_preload_libraries to pg_stat_statements
3. Reboot the instance
4. Connect and run CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Supabase

Already enabled on all Supabase projects — nothing to do here.

Neon

Available out of the box. Just create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Useful queries

Once you’ve got the extension running, here are a couple of handy queries.

Top queries by total execution time

SELECT
  queryid,
  calls,
  mean_exec_time::numeric(10,2) AS avg_ms,
  total_exec_time::numeric(10,2) AS total_ms,
  left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Reset statistics

If things have gotten noisy and you want a clean slate:

SELECT pg_stat_statements_reset();

Configuration reference

Parameter	Default	Description
pg_stat_statements.max	5000	Maximum number of statements tracked. Increase if your application runs many distinct queries.
pg_stat_statements.track	top	Which statements to track. Set to all to include statements inside functions.
pg_stat_statements.track_utility	on	Whether to track utility commands (e.g. CREATE TABLE, VACUUM).
pg_stat_statements.track_planning	off	Whether to track planning statistics (adds overhead).

Further reading

• PostgreSQL docs: pg_stat_statements