How to enable pg_stat_statements in PostgreSQL

Enabling pg_stat_statements adds query-level visibility that helps pinpoint slow statements, high-frequency calls, and unexpected load in PostgreSQL without relying on application-side logging.

The feature works by preloading a server-side library at startup and tracking normalized statement fingerprints in shared memory. The pg_stat_statements extension exposes the collected counters through the pg_stat_statements view, enabling sorting and filtering by execution count, timing, and I/O.

Because the library must be listed in shared_preload_libraries, enabling it requires a full server restart and active sessions will drop. The collector consumes shared memory (controlled by pg_stat_statements.max) and introduces overhead on very busy systems, so capacity and access control should be reviewed before enabling in production; managed PostgreSQL services often require a parameter change and instance reboot instead of editing postgresql.conf.

Steps to enable pg_stat_statements in PostgreSQL:

Show the active postgresql.conf location from the running server.

$ sudo -u postgres psql -X -At -c "SHOW config_file;"
/etc/postgresql/16/main/postgresql.conf

Open the reported postgresql.conf file in an editor with elevated privileges.
```
$ sudoedit /etc/postgresql/16/main/postgresql.conf
```
Add pg_stat_statements to shared_preload_libraries in the configuration file.
```
shared_preload_libraries = 'pg_stat_statements'
```
Preserve existing preload entries by appending pg_stat_statements to the existing comma-separated list inside the same quoted value.
Configure optional pg_stat_statements settings when higher capacity or different tracking is required.
```
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
```
pg_stat_statements.track controls which statements are recorded (top is the common default), and raising pg_stat_statements.max increases shared memory usage.
Restart the PostgreSQL service to load the preloaded library.
```
$ sudo systemctl restart postgresql
```
A server restart disconnects existing sessions and can abort in-flight transactions.

When postgresql is not the unit name, identify the correct service with systemctl list-units 'postgresql*'.

Related: How to manage the PostgreSQL service with systemctl on Linux

Confirm pg_stat_statements appears in shared_preload_libraries after the restart.

$ sudo -u postgres psql -X -At -c "SHOW shared_preload_libraries;"
pg_stat_statements

Create the pg_stat_statements extension in the target database.
```
$ sudo -u postgres psql -X -d postgres -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
CREATE EXTENSION
```
Run CREATE EXTENSION in each database that needs to query the pg_stat_statements view.

An error about a missing extension control file typically indicates the PostgreSQL contrib/extension files are not installed for the server version.

Execute a simple statement to generate initial statistics.

$ sudo -u postgres psql -X -d postgres -c "SELECT now();"
              now              
-------------------------------
 2025-12-29 08:52:19.185812+00
(1 row)

Query the pg_stat_statements view to confirm statements are being tracked.

$ sudo -u postgres psql -X -d postgres -c "SELECT calls, query FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;"
 calls |                                                                                                                                              query                                                                                                                                              
-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     1 | SELECT pg_stat_statements_reset()
     1 | SELECT count(*) FROM orders
     1 | SELECT now()
     1 | EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE created_at > now() - interval $1 ORDER BY created_at DESC LIMIT $2
     1 | SELECT queryid, calls, round(total_exec_time::numeric,$1) AS total_ms, round(mean_exec_time::numeric,$2) AS mean_ms, rows, left(query,$3) AS query FROM pg_stat_statements WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database()) ORDER BY total_exec_time DESC LIMIT $4
(5 rows)

The recorded query text is normalized for grouping, so literals are commonly replaced with placeholders.

Reset collected statistics when a clean baseline is required.

$ sudo -u postgres psql -X -d postgres -c "SELECT pg_stat_statements_reset();"
 pg_stat_statements_reset
--------------------------

(1 row)

Resetting statistics clears counters for all tracked statements in the cluster.