How to Enable pg_stat_statements on AWS RDS PostgreSQL

pg_stat_statements is the single most useful diagnostic tool available for PostgreSQL performance work. It aggregates execution statistics for every distinct query type seen since the extension was last reset — total calls, total execution time, rows returned, shared buffer hits and misses, and more. Without it, identifying your slowest queries requires expensive query-log analysis. With it, the answer is a single SQL query.

On AWS RDS PostgreSQL 16, 17, and 18, enabling pg_stat_statements requires a parameter group change followed by a reboot — it cannot be enabled at session level alone because it must be loaded at server start.

Step 1 — Add pg_stat_statements to shared_preload_libraries

In the AWS Console, navigate to RDS → Parameter Groups and find the custom parameter group attached to your instance (create one if you're still using the default group, which cannot be edited).

Set shared_preload_libraries to include pg_stat_statements. If you already have other libraries loaded (e.g. pg_hint_plan, auto_explain), append it as a comma-separated value:

shared_preload_libraries = pg_stat_statements,auto_explain

Step 2 — Create the extension

After the reboot, connect as the master user (or any superuser) and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

This only needs to be done once per database. Confirm it loaded correctly:

SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Step 3 — Configure sampling parameters

Two parameters control what gets tracked. Set these in the parameter group (no reboot required):

Querying pg_stat_statements — the key queries

Find your top 20 slowest queries by total execution time:

SELECT
  left(query, 80)                                           AS query_snippet,
  calls,
  round(total_exec_time::numeric, 2)                         AS total_ms,
  round((total_exec_time / calls)::numeric, 2)              AS avg_ms,
  round(stddev_exec_time::numeric, 2)                        AS stddev_ms,
  rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

Find queries with high I/O (buffer misses = disk reads on RDS):

SELECT
  left(query, 80)                                           AS query_snippet,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(shared_blks_read::numeric / nullif(shared_blks_hit + shared_blks_read, 0) * 100, 1) AS miss_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 20;

Resetting statistics

Call pg_stat_statements_reset() to clear accumulated statistics — useful after a schema or application deployment to start a clean measurement window:

SELECT pg_stat_statements_reset();
-- Or reset a single query by its queryid:
SELECT pg_stat_statements_reset(0, 0, <queryid>); -- PG14+

Common problems on RDS

pg_stat_statements not visible after enabling: The most common cause is that the parameter group change was saved but the instance was not rebooted. Check SHOW shared_preload_libraries — if pg_stat_statements is not in the output, the reboot has not happened or the parameter group is not attached to this instance.

Statistics disappearing after minor version upgrade: RDS minor version upgrades on PostgreSQL 16 and 17 may reset the shared memory area used by pg_stat_statements. This is expected — schedule your baseline measurement windows to avoid crossing a maintenance event.

max rows exhausted: If pg_stat_statements is logging a dealloc count greater than zero, the statement store is being evicted. Increase pg_stat_statements.max to 10000–20000 for high-cardinality workloads.