Finding and Fixing Slow Queries in Aurora PostgreSQL Finding and Fixing Slow Queries in Aurora PostgreSQL

A cache helps with repeated reads, but the database query itself can be slow, and no cache saves you on the first hit or a cache miss. This article is about finding slow queries in Aurora PostgreSQL and fixing them, and it starts where someone new to AWS actually should: the web console. We’ll use Performance Insights and CloudWatch to spot the offender, then drop into pg_stat_statements and EXPLAIN to diagnose and fix it, taking one query from 17 ms to 0.1 ms with a single index. I’ll use the same 100,000-row products table from the Aurora setup and cache-aside articles.

Reach for AWS’s tools before psql

As a developer, your instinct when a query is slow is to open psql, run EXPLAIN ANALYZE, and start reading query plans. Hold that thought. On Aurora, AWS gives you tools that answer the first two questions faster: which query is slow, and why it’s slow (CPU, disk, or lock contention). You still end up in psql eventually, but you’ll get there knowing exactly what to look at instead of guessing.

There are three places you’ll spend your time, all reachable from the AWS web console. Let’s get oriented before touching anything.

1. The RDS console, where your database lives

Aurora isn’t a separate service in the AWS console; it’s part of RDS (Relational Database Service), Amazon’s managed-database offering. To get there, sign in and type “RDS” into the search bar at the top of the console, or go to console.aws.amazon.com/rds.

Before you do anything else, check the Region in the top-right corner of the console. AWS scopes almost everything to a region, and you’ll only see your resources if you’re in the right one. For this series that’s US East (N. Virginia), us-east-1. If it says something else, switch it there.

Now click Databases in the left sidebar. You’ll see your cluster, database-1. A quick vocabulary note, because AWS’s terms trip up newcomers:

• A cluster is Aurora’s storage plus one or more compute nodes.
• Each compute node is a DB instance. Yours is a single db.t3.medium, the writer instance, which handles both reads and writes since there are no read replicas.
• db.t3.medium is a burstable instance class: it runs at a baseline CPU level and dips into a credit balance for short spikes. That detail matters later, so keep it in mind.

Click into database-1 and you’ll land on the cluster’s detail page, with tabs across the top including Monitoring, Connectivity & security, and Configuration.

2. Performance Insights, which queries are loading the database

Performance Insights is the tool that actually finds your slow queries. It samples what the database is doing once a second and rolls that up into a single picture of database load. It’s already turned on for your cluster, so there’s nothing to configure.

Open it from Performance Insights in the left sidebar of the RDS console (or via the Monitoring dropdown on the instance page). At the top there’s an instance selector.

The main chart is Database load, and its y-axis is Average active sessions (AAS), roughly, the average number of queries actively running or waiting at any given moment. The dashed horizontal line labeled Max vCPU marks how many virtual CPUs the instance has (2, for your db.t3.medium). The rule of thumb: when the load bars sit above the Max vCPU line for a sustained period, the database is asking for more CPU than it has, and things queue up.

The colors in the chart come from a Slice by control. By default it slices by wait state, what queries are stuck waiting on. For Aurora PostgreSQL these are standard Postgres wait events, with names like CPU, IO:DataFileRead (reading pages from storage), Lock:transactionid (waiting on a row lock), and Client:ClientRead (waiting on the application). A tall stack of IO:DataFileRead points at a missing index or an unselective scan; a lot of Lock:* points at contention, not slow SQL.

Below the chart is a set of tabs, Top SQL, Top waits, Top hosts, Top users, Top databases. Top SQL is the one you’ll live in: it ranks statements by how much load they contribute, and you can add columns such as calls per second, rows per second, and average latency per call. That ranking is your shortlist of queries to investigate. (Under the hood, Performance Insights reads query text and stats from the pg_stat_statements extension, which we’ll meet shortly.) You get the last 7 days of history for free.

3. CloudWatch, the health of everything around the query

CloudWatch is AWS’s general-purpose metrics and monitoring service. Where Performance Insights tells you which query is expensive, CloudWatch tells you whether the instance itself is healthy, CPU, memory, connections, I/O. The two answer different questions, and you’ll cross-check them.

The quickest way in is the Monitoring tab on the instance’s detail page in the RDS console, which embeds the relevant CloudWatch graphs. You can also open the full CloudWatch console (search “CloudWatch”) for custom time ranges and alarms. The metrics you’ll actually use for slow-query work:

• CPUUtilization — percentage of CPU in use. Pair this with the Max vCPU signal from Performance Insights.
• DatabaseConnections — open connections; a runaway count often accompanies pile-ups.
• FreeableMemory — available RAM. When this drops low, Postgres reads more from storage instead of cache, and queries slow down.
• ReadLatency / WriteLatency — average time per storage I/O operation.
• CPUCreditBalance — because your db.t3.medium is a burstable instance. If this balance drains to zero, the instance is throttled and everything gets slow regardless of query quality. Always rule this out first on a T-class instance before blaming a query.

How they fit together

The workflow for the rest of this article is straightforward:

1. Performance Insights → Top SQL to find the query worth fixing and see what it’s waiting on.
2. CloudWatch to confirm the instance itself isn’t the problem (out of CPU credits, out of memory, connection storm).
3. psql with EXPLAIN (ANALYZE, BUFFERS) to read the plan for that specific query and fix it.

Start at the top of that list, not the bottom. The console tells you where to point psql, which saves you from optimizing a query that was never the real cause.

Catch a query in Performance Insights (hands-on)

Orientation done, let’s actually catch one. Performance Insights samples once a second, so a single fast query won’t register, we need the database busy for a minute. From the EC2 box, run an un-indexed, full-scan query in a loop:

export RDSHOST="database-1.cluster-xxxxxxxx.us-east-1.rds.amazonaws.com"
END=$((SECONDS+120))
while [ $SECONDS -lt $END ]; do
  psql "host=$RDSHOST port=5432 dbname=postgres user=postgres sslmode=verify-full sslrootcert=./global-bundle.pem" \
    -c "SELECT count(*) FROM products WHERE name LIKE '%9%';" >/dev/null
done

While it runs, open Performance Insights, set the time range to the last 5 minutes, and make sure the load chart is sliced by Top SQL. The SELECT count(*) … LIKE '%9%' statement climbs to the top of the list, and because it’s a full table scan, its load is almost entirely CPU. That’s your suspect: click it, copy the SQL, and take it to EXPLAIN. This is the console-first loop in miniature, the dashboard hands you the query, and now you go diagnose it.

Rank queries precisely with pg_stat_statements

Performance Insights is the visual view; pg_stat_statements is the precise, in-database one. It records stats for every query, how often it ran, total time, mean time, rows returned. On Aurora it’s preloaded, so you just create the extension (connect with psql first):

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

After running a few queries against products, ask which ones cost the most on average:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       query
FROM pg_stat_statements
WHERE query ILIKE '%products%'
ORDER BY mean_exec_time DESC
LIMIT 5;

The WHERE query ILIKE '%products%' filters out all the system and catalog noise so you only see your app’s queries. In my run the repeatable offender stood out clearly:

 calls | total_ms | mean_ms | rows  |                         query
-------+----------+---------+-------+--------------------------------------------------------
     3 |     55.4 |   18.47 |    30 | SELECT id, name, price FROM products
       |          |         |       | WHERE category = $1 ORDER BY price DESC LIMIT $2

One-time statements like the initial INSERT will also show up with big numbers, ignore those; you care about queries that run often (high calls) or are slow every time (high mean_ms). This top-10 query is both a real pattern and 18 ms each time, so it’s the one to fix.

Read the plan with EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) actually runs the query and shows how Postgres executed it:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 10;

Before any index, the plan was:

 Limit  (actual time=16.995..16.997 rows=10)
   ->  Sort  (actual time=16.993..16.994 rows=10)
         Sort Key: price DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Seq Scan on products  (actual time=0.008..12.340 rows=20000)
               Filter: (category = 'electronics'::text)
               Rows Removed by Filter: 80000
 Execution Time: 17.031 ms

Read it bottom-up, and two lines jump out:

• Seq Scan on products … Rows Removed by Filter: 80000 — Postgres read all 100,000 rows and threw away the 80,000 that weren’t electronics.
• Sort … top-N heapsort — then it sorted the 20,000 survivors to find the 10 most expensive.

Reading the whole table and sorting it is why the query takes 17 ms.

Fix it with the right index

The query filters on category and sorts by price DESC. An index on exactly those columns, in that order, lets Postgres skip both the scan and the sort:

CREATE INDEX idx_products_category_price ON products (category, price DESC);

Run the same EXPLAIN again and the plan changes completely:

 Limit  (actual time=0.064..0.085 rows=10)
   ->  Index Scan using idx_products_category_price on products
         Index Cond: (category = 'electronics'::text)
 Execution Time: 0.110 ms

No Seq Scan. No Sort. Postgres walks the index straight to electronics, already ordered by price, and grabs the first 10. The result:

Seq scan (no index):     17.03 ms
Index scan (cold index):  3.19 ms   (first run, index pages read from disk)
Index scan (warm):        0.11 ms   (~155x faster than the seq scan)

That first index run (3 ms) still paid a one-time cost to read the new index pages from disk; the warm run at 0.11 ms is the true steady state. The index stores rows grouped by category and ordered by price, so “top 10 electronics by price” becomes a lookup instead of a scan-and-sort of 20,000 rows.

Index, cache, or both?

This series built a Valkey cache in front of Aurora, and now an index on Aurora itself. They solve different halves of the same problem:

• The index makes the query fast at the database, every time, including cache misses and writes-then-reads.
• The cache avoids hitting the database at all for repeated identical reads.

In production you usually want both: a well-indexed database so no single query is slow, and a cache so the database isn’t asked the same question a thousand times. Fix the query first (an index is cheap and permanent), then cache what’s still hot.

Wrapping up

Finding slow queries isn’t guesswork, and on AWS it doesn’t start in psql. Open Performance Insights, read Top SQL to see which query is loading the database and what it’s waiting on, glance at CloudWatch to rule out an unhealthy instance, then take the culprit into pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS). Watch for a Seq Scan throwing away most rows, add the matching index, and confirm the plan flips to an Index Scan. A 17 ms query became a 0.1 ms one with a single line of SQL. For the shell and Python around all this, see my Ubuntu Terminal cheatsheet and Python Basics & CLI cheatsheet.

References and Further Reading

• Amazon Web Services. Amazon RDS Performance Insights.
• Amazon Web Services. Monitoring Amazon Aurora with CloudWatch.
• PostgreSQL. pg_stat_statements.
• PostgreSQL. Using EXPLAIN.