pg_stat_monitor in PostgreSQL 18: Advanced Query Performance Monitoring on Amazon RDS

Why pg_stat_monitor Over pg_stat_statements?

Capability	pg_stat_statements	pg_stat_monitor
Time-based bucketing	No	Yes - 5 fixed time intervals on RDS
Query plan capture	No	Yes
Client IP tracking	No	Yes (masked to 127.0.0.1 on RDS)
Histogram output	No	Yes
Actual query parameters	No (normalized only)	Configurable
Top/nested query linking	Limited	Full parent-child tracking
Table access per statement	No	Yes (relations column)
SQL comments extraction	No	Yes
Error level and SQL code	No	Yes
CPU time tracking	No	Yes (user + system)
Application name grouping	Limited	Built-in key dimension

The key insight: pg_stat_statements provides ever-increasing cumulative counters. You have to snapshot and diff them yourself to understand time-bounded behavior. pg_stat_monitor handles that aggregation natively through its bucket system.

---

Availability on Amazon RDS

As of 2026, Amazon RDS for PostgreSQL supports pg_stat_monitor version 2.3 exclusively in RDSPostgreSQL 18. You can enable it directly through parameter groups without needing custom builds or manual installations.

RDS-Specific Behavior

RDS-Specific Behavior

A few things behave differently on RDS compared to self-managed PostgreSQL:

• Client IP is masked: RDS sets pgsm_hide_client_ip = on (controlled by rds_superuser context), so client_ip shows 127.0.0.1/32 rather than the actual client IP.
• Password masking: pgsm_mask_passwords = on by default — connection strings in queries won't expose credentials.
• Utility tracking off by default: RDS sets pgsm_track_utility = off, meaning DDL statements (CREATE, ALTER, etc.) are not tracked unless you change this.
• Normalized queries on by default: RDS defaults to pgsm_normalized_query = on, showing $1, $2 placeholders instead of literal values.
• Fewer buckets: RDS defaults to 5 buckets (vs. 10 in upstream), giving you 5 minutes of history at 60-second bucket time.

---

Setting Up pg_stat_monitor on RDS PostgreSQL 18

Step 1: Add to shared_preload_libraries

On RDS, you modify shared_preload_libraries via a custom DB parameter group (you cannot modify the default parameter group).

1. Open the Amazon RDS Console and navigate to Parameter Groups.

2. Create a new parameter group (or edit your existing custom one) for the postgres18 family.

3. Search for shared_preload_libraries and add pg_stat_monitor to the list.

   If you already have pg_stat_statements loaded, the value should look like:

   pg_stat_statements,pg_stat_monitor
   

4. Apply the parameter group to your DB instance.

5. Reboot the instance — shared_preload_libraries is a static parameter that requires a restart.

Step 2: Create the Extension

Connect to your database using psql or any PostgreSQL client:

-- Connect to your RDS instance
psql -h <<endpoint>> -U postgres -d postgres

-- Create the extension
CREATE EXTENSION pg_stat_monitor;

Verify the extension is installed:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_stat_monitor';

Expected output:

     extname      | extversion
------------------+------------
 pg_stat_monitor  | 2.3.1

Step 3: Verify It's Collecting Data

Run a few queries, then check:

SELECT count(*) FROM pg_stat_monitor;

---

Understanding Buckets

Unlike pg_stat_statements which accumulates stats indefinitely until reset, pg_stat_monitor organizes data into time-based buckets.

Think of buckets as rotating time windows:

Bucket 1: [00:00 - 01:00]  <- oldest data, will be recycled first
Bucket 2: [01:00 - 02:00]
Bucket 3: [02:00 - 03:00]
Bucket 4: [03:00 - 04:00]
Bucket 5: [04:00 - 05:00]  <- current active bucket

Default configuration on RDS:

• 5 buckets, each lasting 60 seconds
• Total history: 5 minutes of query data

(Upstream default is 10 buckets / 10 minutes. On self-managed PostgreSQL you can configure this; on RDS these are fixed.)

Querying Bucket Information

SELECT
    bucket,
    bucket_start_time,
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_monitor
ORDER BY bucket_start_time DESC, total_exec_time DESC
LIMIT 20;

---

Configuration Parameters

View all pg_stat_monitor settings:

SELECT name, setting, unit, context, short_desc
FROM pg_settings
WHERE name LIKE 'pg_stat_monitor.%'
ORDER BY name;

Key Parameters

Key Parameters (RDS Defaults vs Upstream Defaults)

Parameter	RDS Default	Upstream Default	Context	Description
pgsm_max	256 MB	256 MB	postmaster	Total shared memory allocated
pgsm_max_buckets	5	10	postmaster	Number of time buckets
pgsm_bucket_time	60 s	60 s	postmaster	Duration of each bucket
pgsm_query_max_len	1024	2048	postmaster	Max query text length stored
pgsm_query_shared_buffer	20 MB	20 MB	postmaster	Shared memory for query tracking
pgsm_histogram_min	1 ms	1 ms	postmaster	Histogram lower bound
pgsm_histogram_max	100000 ms	10000 ms	postmaster	Histogram upper bound
pgsm_histogram_buckets	20	20	postmaster	Number of histogram bins
pgsm_enable_query_plan	off	off	user	Capture execution plans
pgsm_normalized_query	on	off	user	Store normalized (parameterized) queries
pgsm_track	top	top	user	Track top-level, all, or none
pgsm_track_planning	off	off	user	Include planning time stats
pgsm_track_utility	off	on	user	Track utility commands (CREATE, ALTER, etc.)
pgsm_track_application_names	on	on	user	Track application name per query
pgsm_enable_pgsm_query_id	on	on	user	Generate unique query hash
pgsm_extract_comments	off	off	user	Extract SQL comments
pgsm_hide_client_ip	on	Masks client IP to 127.0.0.1/32
pgsm_mask_passwords	on	Masks passwords in query text
pgsm_security_mode	permissive	Security enforcement mode

Key differences from upstream:

• RDS gives you 5 buckets (5 minutes of history) instead of upstream's 10 (10 minutes)
• Query text is capped at 1024 bytes instead of 2048
• Histogram range is wider: 1ms to 100000ms (vs 1ms to 10000ms upstream)
• Queries are normalized by default ($1, $2 placeholders)
• Utility tracking is off by default (no DDL captured unless you enable it)

Modifying Parameters on RDS

Static parameters (context: postmaster) are not exposed in the RDS parameter group. RDS manages these internally with fixed defaults:

pg_stat_monitor.pgsm_max = 256 MB (not modifiable in RDS) pg_stat_monitor.pgsm_max_buckets = 5 (not modifiable in RDS) pg_stat_monitor.pgsm_bucket_time = 60 s (not modifiable in RDS) pg_stat_monitor.pgsm_query_max_len = 1024 (not modifiable in RDS) pg_stat_monitor.pgsm_query_shared_buffer = 20 MB (not modifiable in RDS) pg_stat_monitor.pgsm_histogram_min = 1 ms (not modifiable in RDS) pg_stat_monitor.pgsm_histogram_max = 100000 ms (not modifiable in RDS) pg_stat_monitor.pgsm_histogram_buckets = 20 (not modifiable in RDS)

You can view these values using pg_settings but cannot change them.

Session-level parameters can be changed using SET within a session, but the change is not persistent — it reverts when the session ends:

-- Enable query plan capture for this session only
SET pg_stat_monitor.pgsm_enable_query_plan = on;

-- Track all statements including nested ones (this session only)
SET pg_stat_monitor.pgsm_track = 'all';

-- Enable planning time tracking (this session only)
SET pg_stat_monitor.pgsm_track_planning = on;

-- Show actual parameters instead of $1, $2 (this session only)
SET pg_stat_monitor.pgsm_normalized_query = off;

There is no way to persist these changes on RDS. ALTER SYSTEM is blocked, and the parameters do not appear in the parameter group.

Verified Behavior (tested on RDS PostgreSQL 18.3)

=== Testing SET for user-context parameters ===
  SET pg_stat_monitor.pgsm_enable_query_plan = on       -> OK (now: on)
  SET pg_stat_monitor.pgsm_track = all                  -> OK (now: all)
  SET pg_stat_monitor.pgsm_track_planning = on          -> OK (now: on)
  SET pg_stat_monitor.pgsm_normalized_query = off       -> OK (now: off)
  SET pg_stat_monitor.pgsm_track_utility = on           -> OK (now: on)
  SET pg_stat_monitor.pgsm_track_application_names = off -> OK (now: off)
  SET pg_stat_monitor.pgsm_enable_pgsm_query_id = off   -> OK (now: off)
  SET pg_stat_monitor.pgsm_extract_comments = on        -> OK (now: on)

=== Testing ALTER SYSTEM ===
  ALTER SYSTEM -> BLOCKED: "ALTER SYSTEM command is not supported"

=== After reconnecting ===
  pgsm_enable_query_plan: off  -> NOT persisted (reverted to default)

Summary: SET works for per-session changes. No parameter group support. No ALTER SYSTEM. Changes do not survive a disconnect.

---

Practical Usage Examples

1. Find the Slowest Queries in the Current Bucket

SELECT
    datname,
    substr(query, 1, 80) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows
FROM pg_stat_monitor
WHERE bucket_done = false  -- current active bucket
ORDER BY mean_exec_time DESC
LIMIT 10;

2. Track Query Performance Over Time (Across Buckets)

SELECT
    bucket_start_time,
    queryid,
    substr(query, 1, 60) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_monitor
WHERE query ILIKE '%customers%'
ORDER BY bucket_start_time;

This shows how a specific query's performance changed over the last 5 buckets (5 minutes on RDS) — something impossible with pg_stat_statements alone.

3. Identify Queries by Application Name

Since client IP is always masked on RDS, use application_name to identify query sources instead:

SELECT
    application_name,
    substr(query, 1, 60) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_monitor
WHERE application_name != ''
ORDER BY total_exec_time DESC
LIMIT 10;

4. View Query Execution Plans

First enable plan capture:

SET pg_stat_monitor.pgsm_enable_query_plan = on;

Run some queries, then inspect the plan:

SELECT
    substr(query, 1, 60) AS query_preview,
    query_plan,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_monitor
WHERE query_plan IS NOT NULL
    AND query_plan != ''
ORDER BY mean_exec_time DESC
LIMIT 5;

5. Identify Which Tables a Query Accesses

SELECT
    substr(query, 1, 60) AS query_preview,
    relations,
    calls,
    rows
FROM pg_stat_monitor
WHERE relations IS NOT NULL
    AND array_length(relations, 1) > 0
ORDER BY calls DESC
LIMIT 10;

6. Use the Histogram Feature

The histogram shows the distribution of query response times:

SELECT
    substr(query, 1, 50) AS query_preview,
    calls,
    resp_calls,
    round(min_exec_time::numeric, 2) AS min_ms,
    round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_monitor
WHERE calls > 10
ORDER BY calls DESC
LIMIT 10;

The resp_calls column contains a comma-separated list showing how many calls fell into each histogram bucket (ranging from 1ms to 100000ms on RDS, fixed). You can use the histogram() function for a visual representation:

SELECT * FROM histogram(queryid, datname) 
WHERE queryid = (
    SELECT queryid FROM pg_stat_monitor 
    ORDER BY total_exec_time DESC LIMIT 1
);

7. Monitor Errors and Warnings

SELECT
    elevel,
    sqlcode,
    message,
    substr(query, 1, 60) AS query_preview,
    calls
FROM pg_stat_monitor
WHERE elevel > 0
ORDER BY elevel DESC, calls DESC;

Error levels correspond to PostgreSQL log levels (e.g., WARNING, ERROR, FATAL).

8. Track WAL and I/O Statistics per Query

SELECT
    substr(query, 1, 60) AS query_preview,
    calls,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_dirtied,
    wal_records,
    wal_bytes
FROM pg_stat_monitor
WHERE shared_blks_read > 0 OR wal_records > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

9. CPU Time Analysis

SELECT
    substr(query, 1, 60) AS query_preview,
    calls,
    round(cpu_user_time::numeric, 4) AS cpu_user_s,
    round(cpu_sys_time::numeric, 4) AS cpu_sys_s,
    round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_monitor
WHERE cpu_user_time > 0
ORDER BY cpu_user_time DESC
LIMIT 10;

---

Resetting Statistics

To clear all collected statistics and start fresh:

SELECT pg_stat_monitor_reset();

---

Best Practices for Production Use

1. Bucket limitations on RDS: RDS fixes pgsm_max_buckets at 5 and pgsm_bucket_time at 60 seconds, giving you only 5 minutes of history. For longer retention, query pg_stat_monitor periodically and store results in a separate table or monitoring system.

2. Be cautious with query plan capture: Enabling pgsm_enable_query_plan adds overhead. Use it for debugging sessions, not as a permanent setting in high-throughput environments.

3. Disable application name tracking in high-connection environments: If you have hundreds of connections, set pgsm_track_application_names = off to reduce overhead.

4. Use with Performance Insights: Combine pg_stat_monitor's bucket-based insights with RDS Performance Insights for a complete picture.

5. Query text truncation: RDS caps query text at 1024 bytes. If you have long queries, the text may be truncated. This is not configurable on RDS.

6. Keep pg_stat_statements active: The two extensions can coexist. Keep pg_stat_statements for compatibility with existing tooling (Performance Insights depends on it) and use pg_stat_monitor for deeper analysis.

---

Limitations and Considerations

• Static parameters are not configurable on RDS: Unlike self-managed PostgreSQL, you cannot change pgsm_max, pgsm_max_buckets, pgsm_bucket_time, or pgsm_query_max_len on RDS. They are fixed at RDS-chosen defaults.
• Only 5 minutes of history: With 5 buckets at 60 seconds each, old data is lost quickly. Plan for external persistence if you need longer retention.
• Query text capped at 1024 bytes: Long queries get truncated (upstream allows 2048).
• Client IP is always masked: RDS forces pgsm_hide_client_ip = on, so you cannot see actual client IPs.
• Memory allocation is static: The 256 MB shared memory is allocated at startup and cannot be changed without a full restart.
• Bucket rotation loses old data: Once all 5 buckets have been filled, the oldest bucket is overwritten.
• Some overhead: Like any statistics extension, there is a small performance cost. The overhead is generally minimal but measurable under extreme workloads.

---

Conclusion

pg_stat_monitor fills a significant observability gap in PostgreSQL. Its time-based bucketing gives you the ability to ask "how did this query perform 5 minutes ago vs. now?" — something that required external tooling before. Combined with query plan capture, client IP tracking, and histogram support, it provides a complete query performance monitoring solution.

With PostgreSQL 18 support on Amazon RDS, enabling it is straightforward: add it to your parameter group, reboot, create the extension, and start querying.

---

Live Test Results (RDS PostgreSQL 18.3)

The following output was captured from a live Amazon RDS for PostgreSQL 18.3 instance with pg_stat_monitor 2.3 enabled:

Environment

PostgreSQL 18.3 on aarch64-unknown-linux-gnu (Graviton)
shared_preload_libraries: rdsutils,pg_tle,pg_stat_statements,pg_stat_monitor,rds_casts
Extension version: 2.3

Configuration (RDS defaults)

pgsm_bucket_time              = 60 s 
pgsm_enable_overflow          = on 
pgsm_enable_pgsm_query_id    = on (user)
pgsm_enable_query_plan        = off (user)
pgsm_extract_comments         = off (user)
pgsm_hide_client_ip           = on 
pgsm_histogram_buckets        = 20 
pgsm_histogram_max            = 100000 ms 
pgsm_histogram_min            = 1 ms 
pgsm_mask_passwords           = on 
pgsm_max                      = 256 MB 
pgsm_max_buckets              = 5 
pgsm_normalized_query         = on (user)
pgsm_query_max_len            = 1024 
pgsm_query_shared_buffer      = 20 MB 
pgsm_track                    = top (user)
pgsm_track_application_names  = on (user)
pgsm_track_planning           = off (user)
pgsm_track_utility            = off (user)

Query Performance Results

After inserting 10,000 rows and running various query patterns:

Query                                                    Calls    Rows   Total ms    Mean ms  Type
------------------------------------------------------- ------ ------- ---------- ---------- --------
INSERT INTO pgsm_test (name, value) SELECT ...               1   10000     29.272     29.272  INSERT
SELECT substr(name, ...) GROUP BY prefix                     1       9      3.067      3.067  SELECT
SELECT * FROM pgsm_test WHERE value > $1 ORDER BY ...        1      10      0.967      0.967  SELECT
SELECT count(*) FROM pgsm_test                               1       1      0.581      0.581  SELECT
UPDATE pgsm_test SET value = value * $1 WHERE ...            1     100      0.528      0.528  UPDATE
DELETE FROM pgsm_test WHERE id > $1                          1     100      0.526      0.526  DELETE
SELECT a.name, b.value FROM pgsm_test a JOIN ...             1      10      0.050      0.050  SELECT

Query Plans Captured

Query: SELECT substr(name, $1, $2) AS prefix, count(*), avg(value), max(value) ...
Plan:
  HashAggregate
    Group Key: substr(name, 1, 6)
    ->  Seq Scan on pgsm_test

Query: SELECT count(*) FROM pgsm_test
Plan:
  Aggregate
    ->  Seq Scan on pgsm_test

Tables Accessed (relations column)

SELECT substr(...) GROUP BY prefix    -> {public.pgsm_test}
INSERT INTO pgsm_test ...             -> {public.pgsm_test,(null).(null)}
UPDATE pgsm_test SET ...              -> {public.pgsm_test}
SELECT count(*) FROM pgsm_test        -> {public.pgsm_test}

Response Time Histogram

The resp_calls array shows distribution across 22 time buckets (1ms to 100000ms):

INSERT (29ms total)  -> {0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}  -- 1 call in bucket 7
GROUP BY (3ms total) -> {0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}  -- 1 call in bucket 3
SELECT count (0.5ms) -> {1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}  -- 1 call in bucket 1

---

References

• Percona pg_stat_monitor Documentation
• pg_stat_monitor GitHub Repository
• Amazon RDS PostgreSQL Extensions
• Amazon RDS for PostgreSQL 18 Announcement