How to Fix Postgres Query Slow After Migration to AWS RDS / Aurora

A query that ran fast on your old PostgreSQL setup is noticeably slower after migrating to RDS or Aurora, even though the hardware specs look comparable or better on paper. This is a common and usually fixable migration gap — the database engine, network path, and configuration parameters can all differ meaningfully between a self-managed instance and a managed AWS database, even when the underlying PostgreSQL version is identical.

The Problem

A query that used to complete quickly now takes noticeably longer, without any obvious code or schema change:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Seq Scan on orders  (cost=0.00..48213.00 rows=1021 width=812) (actual time=812.291..1842.291 rows=20 loops=1)
Planning Time: 0.812 ms
Execution Time: 1843.019 ms

The same logical query, on the same schema, ran in milliseconds on the previous database before migration.

Why It Happens

A migration moves your data, but it doesn't automatically carry over everything that made the old environment fast — several distinct things commonly get lost or change in the process:

• Table statistics weren't refreshed after the migration — a fresh import or restore can leave PostgreSQL's query planner working from stale or default statistics about row counts and data distribution, leading it to choose a much worse execution plan than it would with accurate statistics.
• Missing or unrecreated indexes — a straightforward data dump/restore sometimes misses custom indexes if the migration process didn't fully replicate the original schema, or indexes were deliberately excluded to speed up the initial data load and never re-added afterward.
• Different default configuration parameters — RDS and Aurora use their own parameter groups, which may have different defaults for memory-related settings like work_mem, shared_buffers, or effective_cache_size than what your previous self-managed instance was tuned to.
• Instance sizing mismatch — the RDS/Aurora instance class may have less available memory or CPU than the previous setup, even if the marketing specs look similar, especially if storage-optimized instance types were chosen for cost reasons over compute/memory-optimized ones.
• Aurora's distributed storage architecture behaves differently from standard PostgreSQL's local disk I/O in some workload patterns, which can shift where bottlenecks show up compared to your previous setup.

The Fix

First, refresh statistics across the whole database — this is the single most common and easiest fix after any kind of migration or bulk data load:

ANALYZE;

Re-run your slow query and check whether the plan improved:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Verify every expected index actually exists post-migration by comparing against your original schema or migration scripts:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

If an index you expect is missing, recreate it explicitly:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Compare key configuration parameters between your old setup and the new RDS/Aurora parameter group:

SHOW work_mem;
SHOW shared_buffers;
SHOW effective_cache_size;

If these are significantly lower than what your previous instance used, adjust the RDS/Aurora parameter group to match, keeping in mind available instance memory:

# Via AWS CLI, modifying a custom parameter group
aws rds modify-db-parameter-group \
  --db-parameter-group-name my-postgres-params \
  --parameters "ParameterName=work_mem,ParameterValue=16384,ApplyMethod=immediate"

Check whether the instance class actually has comparable resources to what you had before — a t3.medium looks similar on paper to some previous setups but has meaningfully less sustained CPU and memory than a compute-optimized instance under real load:

aws rds describe-db-instances --db-instance-identifier mydb --query 'DBInstances[0].DBInstanceClass'

Still Not Working?

If statistics, indexes, and configuration all check out but performance is still noticeably worse, use RDS Performance Insights (available on RDS and Aurora) to identify exactly what the database is spending time on during the slow query, rather than continuing to guess based on EXPLAIN output alone:

aws pi get-resource-metrics \
  --service-type RDS \
  --identifier db-ABCDEFGHIJKLMNOP \
  --metric-queries '[{"Metric":"db.load.avg"}]' \
  --start-time 2026-08-07T10:00:00Z --end-time 2026-08-07T10:30:00Z

Performance Insights breaks down database load by wait event, which often reveals whether the bottleneck is I/O, lock contention, or CPU — each pointing at a very different fix than simply tuning query-level parameters. If the workload is genuinely I/O-heavy and Aurora's distributed storage is a poor architectural fit for your specific access pattern, comparing standard RDS PostgreSQL against Aurora PostgreSQL for your particular query mix is worth doing directly, since they don't always perform identically for the same workload despite sharing the same query engine.