Enabling pg_stat_statements

pg_stat_statements is a PostgreSQL extension that tracks execution statistics for SQL statements 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 n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

No rows, keep reading. A row tells you the schema the extension lives in — read from it to confirm the library is loaded too:

SELECT count(*) FROM ext.pg_stat_statements;

A number back means it’s working. pg_stat_statements must be loaded via shared_preload_libraries means the extension exists but the server isn’t preloading it, which the next section covers.

Pick a schema, not public

CREATE EXTENSION pg_stat_statements puts the extension in public unless you say otherwise, and that default breaks migration tools. The extension owns two views there, pg_stat_statements and pg_stat_statements_info, and it will not let anything else drop them. A tool that reconciles public against your declared schema sees them as strays, tries to drop them, and aborts partway through:

cannot drop view pg_stat_statements_info because extension pg_stat_statements requires it

That is SQLSTATE 2BP01. drizzle-kit push --force hits it; so does anything else doing the same reconciliation. The half-applied migration is what you notice — as unrelated tests failing on missing rows and 42P10, with nothing in the output naming the extension.

Put it in a schema your migration tool doesn’t manage:

CREATE SCHEMA IF NOT EXISTS ext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;

Any name works. ext is the one Query Doctor suggests when it notices the extension is missing. It resolves the schema from pg_extension on every read, so it finds the statistics wherever they are; your own queries against the view need the schema qualified or on the search_path.

If the extension is already in public, move it as a superuser:

CREATE SCHEMA IF NOT EXISTS ext;
ALTER EXTENSION pg_stat_statements SET SCHEMA ext;

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 SCHEMA IF NOT EXISTS ext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;

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 SCHEMA IF NOT EXISTS ext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;

The SCHEMA clause matters most here: a migration that creates the extension in public is a migration that later migrations cannot reconcile.

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 pg_stat_statements requires a superuser. Postgres lets a non-superuser install trusted extensions with only the CREATE privilege on the database, but pg_stat_statements is not trusted. If your migration user isn’t a superuser, create the extension separately with an account that is.

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 SCHEMA IF NOT EXISTS ext;" \
  -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;"

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 SCHEMA IF NOT EXISTS ext;" \
  -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;"

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 printf 'CREATE SCHEMA IF NOT EXISTS ext;\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;\n' \
  > /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. Enable it 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 SCHEMA IF NOT EXISTS ext; CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;

Supabase

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

Neon

Available out of the box. Create the extension:

CREATE SCHEMA IF NOT EXISTS ext;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements SCHEMA ext;

Useful queries

These name the views unqualified. If the extension isn’t in public and its schema isn’t on your search_path, qualify them — ext.pg_stat_statements.

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

To clear the accumulated statistics:

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