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.

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

   $ sudo -u postgres psql -X -At -c "SHOW config_file;"
   /var/lib/postgresql/data/postgresql.conf

2. Open the reported postgresql.conf file in an editor with elevated privileges.

   $ sudoedit /etc/postgresql/16/main/postgresql.conf

3. 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.

4. 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.

5. 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 in Linux

6. 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

7. 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.

8. Execute a simple statement to generate initial statistics.

   $ sudo -u postgres psql -X -d postgres -c "SELECT now();"
                 now
   -------------------------------
    2025-12-24 22:30:57.543155+00
   (1 row)

9. 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 current_database()
        1 | SELECT now()
        1 | SELECT pg_stat_statements_reset()
        1 | SHOW shared_preload_libraries
   (4 rows)

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

10. 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.