Enable Slow Query Logging on AWS RDS PostgreSQL

The two tools for finding slow queries on RDS PostgreSQL are log_min_duration_statement (logs individual slow query executions to CloudWatch Logs) and pg_stat_statements (aggregates statistics across all executions in memory). They are complementary, not interchangeable. This guide covers the logging approach — when to use it, how to configure it safely on a live RDS instance, and how to extract actionable data from CloudWatch Logs.

The key parameters

Step 1 — Create or modify a parameter group

You cannot modify the default parameter group on RDS. Create a custom parameter group if you haven't already. In the RDS console: Parameter groups → Create parameter group → DB parameter group → Engine: postgres16 (match your engine version exactly).

Once created, search for log_min_duration_statement and set it to 1000. This is a dynamic parameter on RDS — it applies immediately without requiring an instance reboot. Attach the parameter group to your instance if it isn't already attached. Attaching a parameter group for the first time requires a reboot.

Step 2 — Enable CloudWatch Logs export

RDS does not ship logs to CloudWatch by default. Enable it in the RDS console: DB instance → Modify → Log exports → PostgreSQL log → Enable. This enables shipping of the PostgreSQL log to the CloudWatch log group /aws/rds/instance/<instance-id>/postgresql.

Alternatively via AWS CLI:

aws rds modify-db-instance \
  --db-instance-identifier your-instance-id \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql"]}' \
  --apply-immediately

Step 3 — Verify logging is active

After enabling, confirm that slow queries are actually being captured. Run a query you know is slow (or force a seq scan for testing), then check the log in the RDS console under Logs & events, or query CloudWatch Logs Insights:

# CloudWatch Logs Insights query — paste into the console
fields @timestamp, @message
| filter @message like /duration:/
| sort @timestamp desc
| limit 50

A captured slow query entry looks like this in the log:

2026-05-28 14:22:31 UTC:12345(app):appuser@mydb:LOG:
  duration: 4312.847 ms
  statement: SELECT u.*, o.total FROM users u
             JOIN orders o ON o.user_id = u.id
             WHERE o.created_at > '2026-01-01'

Step 4 — Extract slow queries at scale with Logs Insights

Once you have days of slow query logs, use CloudWatch Logs Insights to find patterns. This query extracts the slowest 20 queries by duration over a 24-hour window:

fields @timestamp, @message
| filter @message like /duration:/
| parse @message "duration: * ms  statement: *" as duration_ms, stmt
| sort duration_ms desc
| limit 20

And to count frequency of slow queries by query shape (useful for finding high-frequency slow queries that pg_stat_statements would show as high total_exec_time):

fields @message
| filter @message like /duration:/
| parse @message "duration: * ms  statement: *" as duration_ms, stmt
| stats count(*) as occurrences, avg(duration_ms) as avg_ms, max(duration_ms) as max_ms
    by stmt
| sort occurrences desc
| limit 25

Step 5 — Enable auto_explain for execution plans (optional)

If you need the actual execution plan alongside the slow query text (not just the query string), enable auto_explain. This writes the EXPLAIN output directly into the log for any query exceeding the threshold. It requires loading the library — add it to shared_preload_libraries in your parameter group:

-- In RDS parameter group, set:
-- shared_preload_libraries = auto_explain   (requires reboot)
-- auto_explain.log_min_duration = 5000      (5 seconds)
-- auto_explain.log_analyze = off            (never on for production — runs EXPLAIN ANALYZE)
-- auto_explain.log_buffers = on             (buffer hit/miss data)
-- auto_explain.log_nested_statements = on   (capture subqueries)

Choosing the right threshold

The correct value for log_min_duration_statement depends on your workload's latency targets:

• 1000ms — good default for most production instances. Catches queries that are definitely causing user-visible latency without log spam.
• 500ms — appropriate when investigating a workload where SLA requires <200ms response time. Still low log volume on a well-tuned instance.
• 100ms — only for a specific investigation window. Revert after 24–48h. Monitor CloudWatch Logs storage costs.
• 0ms — never on production. Use pg_stat_statements instead, which captures all queries with zero log overhead.

After the investigation, set log_min_duration_statement = -1 to disable logging, or leave it at 1000ms as a permanent safety net that captures only genuinely problematic queries. We recommend leaving it at 1000ms permanently — the log volume is minimal on a well-tuned instance and it provides invaluable signal during incidents.