Three ways to find the queries that slow down an RDS for PostgreSQL database: the slow query log in CloudWatch, pg_stat_statements for totals, and Performance Insights for load by wait event, plus how to confirm a fix.

When an API on RDS gets slow, the database is the usual suspect, but CloudWatch's CPU and connection graphs only say that something is busy, not which statement. Finding the query takes one of three tools, and each answers a different question about where the time goes.
This guide sets up all three on RDS for PostgreSQL and shows how to tell whether a fix worked.
Starting with pg_stat_statements is usually the quickest route, since it ranks every query by the time it consumed and does not depend on a threshold you guessed.
The pg_stat_statements module normalizes queries by replacing constants with placeholders and keeps running totals per query. According to the RDS Performance Insights docs for PostgreSQL, version 11 and later load the library by default, so the extension only needs creating:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT left(query, 80) AS query,
calls,
round(total_exec_time::numeric / 1000, 1) AS total_s,
round(mean_exec_time::numeric, 1) AS mean_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
The top of this list often surprises people: a 3 ms lookup called four million times a day outweighs a 2 second report that runs twice. Sort by mean_exec_time to find the queries that make individual requests slow, and by total_exec_time to find the ones that make the whole database busy. Run SELECT pg_stat_statements_reset(); after a deploy to measure the new code on its own.
log_min_duration_statement logs every statement that runs longer than the threshold, with its duration and, for most drivers, the bound parameters. Set it in a custom parameter group, since default groups cannot be edited:
aws rds modify-db-parameter-group \
--db-parameter-group-name app-pg17 \
--parameters "ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate"
aws rds modify-db-instance \
--db-instance-identifier app-db \
--cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql"]}'
Logs then land in a CloudWatch log group named /aws/rds/instance/app-db/postgresql, where a Logs Insights query filtering on duration: finds the worst offenders. Keep the threshold high enough that the log stays readable; at 0 it logs every statement and the ingestion bill grows with traffic. Adding auto_explain to shared_preload_libraries with auto_explain.log_min_duration set also logs the plan for each slow statement, which saves reproducing it by hand.
Performance Insights samples active sessions every second and shows database load broken down by wait event and by SQL statement. It answers the question the other two cannot: whether the database is slow because of CPU, I/O, lock waits or something like LWLock:BufferMapping. A tall band of Lock:transactionid means sessions are waiting on each other, which no amount of indexing fixes. The Performance Insights overview lists the wait events and how the free retention period works.
Once a query is identified, run it through EXPLAIN (ANALYZE, BUFFERS) with realistic parameters, add the index or rewrite, and compare. Then reset pg_stat_statements and check the same query's mean_exec_time a day later, because a plan that looks good for one parameter value can still be slow for the large customers.
Best fit: pg_stat_statements for ranking, the slow query log for reproducing individual calls, Performance Insights for diagnosing waits.
Encore brings infrastructure from code to the database, and the RDS or Aurora server it provisions in your AWS account already has Performance Insights enabled with the free 7-day retention, according to the AWS infrastructure docs. On top of that, every query a service runs is part of a distributed trace, so the slow statement shows up attached to the endpoint that ran it, both in production and in the local development dashboard.
The usual fix is an index plus the query that needs it, which Encore keeps together in one service:
Run in your terminal to get started locally.
-- orders/migrations/3_orders_customer_created.up.sql
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
package orders
import (
"context"
"time"
"encore.dev/storage/sqldb"
)
var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"})
type Order struct {
ID int64 `json:"id"`
TotalCents int64 `json:"total_cents"`
CreatedAt time.Time `json:"created_at"`
}
type RecentResponse struct {
Orders []Order `json:"orders"`
}
//encore:api public method=GET path=/customers/:customerID/orders
func Recent(ctx context.Context, customerID int64) (*RecentResponse, error) {
rows, err := db.Query(ctx, `
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20
`, customerID)
if err != nil {
return nil, err
}
defer rows.Close()
resp := &RecentResponse{}
for rows.Next() {
var o Order
if err := rows.Scan(&o.ID, &o.TotalCents, &o.CreatedAt); err != nil {
return nil, err
}
resp.Orders = append(resp.Orders, o)
}
return resp, rows.Err()
}
encore run starts Postgres locally and applies the migration, so you can load test data with encore db shell orders --write, call the endpoint and compare the query span in the trace before and after the index. The databases docs cover migrations and the query API.
With the query shown next to the request in the trace, an AI coding agent asked to speed up an endpoint can see which statement to change, write the index as a migration and check the result locally before a reviewer looks at it.
Best fit: teams on AWS who want slow queries tied to the endpoint that runs them, with the database configured in their own account.
Turn on pg_stat_statements everywhere, since it costs almost nothing and ranks queries by total time. Add the slow query log with a 500 ms or 1 second threshold for the outliers, and open Performance Insights when the database is busy but no single query stands out. Verify every fix with EXPLAIN ANALYZE and a fresh pg_stat_statements window.
Set log_min_duration_statement in a custom DB parameter group to a threshold in milliseconds, for example 500, and enable the postgresql log export to CloudWatch Logs on the instance. The parameter is dynamic, so it applies without a reboot.
Yes. On RDS for PostgreSQL 11 and later the library is loaded by default, so running CREATE EXTENSION pg_stat_statements in the database is enough to start querying the view. Older versions need it added to shared_preload_libraries and a reboot.
The slow query log records individual executions above a threshold with their parameters, which helps reproduce one bad call. pg_stat_statements aggregates every normalized query, so it finds the fast query that runs a million times a day and costs more in total than any single slow one.
Open the trace for a slow request in the local development dashboard or the Platform Dashboard. Encore records each database query as part of the trace with its duration, so the query that dominates the request is visible without adding logging.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.