Read Replicas Do Not Fix a Bad Query Plan

PagerDuty fired because our order history page stopped responding. I opened the primary's pg_stat_activity and saw the same statement four times:

SELECT * FROM orders WHERE customer_id = 88213 ORDER BY created_at DESC LIMIT 50;

No index on customer_id. Postgres was running a sequential scan over the whole table, filtering down to 50 rows. Disk read throughput on the primary was pinned, and every other query queued behind it.

The fix everyone agreed on in Slack was "add read replicas and move reporting traffic over." We had done exactly that. It made things worse.

The replicas were running the same bad plan

A read replica does not change the plan. It copies the data and the tables. The planner on the replica parses the same query, looks at the same statistics, and picks the same sequential scan. If the query reads the whole table through shared buffers on the primary, it reads the whole table through shared buffers on each replica.

The difference is that now more machines are doing it at once, against the same table, at the same time, because that is what "distributing the load" means when the load is unindexed. We had not spread the cost. We bought more copies of it. Replica I/O saturated while the primary still had headroom, and user-visible latency did not improve, because the reporting jobs sitting on replicas were still slow.

You can see this directly. EXPLAIN (ANALYZE, BUFFERS) on both hosts:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 88213 ORDER BY created_at DESC LIMIT 50;

If the Seq Scan on orders line shows the same rows removed by filter on both, and buffer reads are comparable on both, the replica is not fixing anything. It is a second bill for the same mistake.

The planner can pick a different, worse plan on a replica

Same query, same database, different plan. Replicas are not bit-for-bit clones of planner inputs:

• Statistics. autovacuum runs independently. A replica can have older pg_statistic for a table that churns hard, so the row estimate can be off by an order of magnitude, which flips a nested loop into a hash join over the whole table.
• Physical ordering. Heap order depends on insert and vacuum history. A replica that was promoted, restored from a base backup, or built with pg_basebackup while the primary was under a different write pattern will have rows laid out differently, so a correlated scan that was fast on the primary is not fast there.
• Missing indexes. This is the dangerous one. If you created an index on the primary but the replica is behind, or the index build was skipped in a partial migration, the replica's planner has no choice but to scan. I have watched a query go from milliseconds to seconds purely because CREATE INDEX CONCURRENTLY had not finished replaying yet.

So "the replica is slow and the primary is fast for the same query" is a real, reproducible state. Check pg_stat_user_indexes on both hosts before you assume the plans match.

Lag turns into wrong answers

Replication lag is not just a delay. It is a correctness bug the moment a user reads their own write.

Our flow was: user places an order, we redirect to the order detail page, the page reads from a replica. With replication lag under load, the user got a 404 for the order they had just created. Then they refreshed and it appeared. Support tickets followed.

Do not paper over this with a longer LIMIT or a retry loop. Route reads that follow a write to the primary: after any write, pin the connection to the primary. If you use a pooler, pin at the request level, not the connection level, or you will leak the pin to an unrelated request.

Fix the query first, then scale reads

Order matters. Here is what actually worked.

-- The index the query needed all along.
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

Note CONCURRENTLY. A plain CREATE INDEX takes an ACCESS EXCLUSIVE lock, and on a table that size the lock is itself an outage. Run it, then verify it replayed to every replica before you trust the plan there:

SELECT indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders';

If idx_scan is 0 on a replica after traffic has run, the replica is not using the index. Find out why before adding capacity.

After the index, the same query went from a scan to an index-only path, and the reporting jobs stopped pushing the primary to its I/O ceiling. Then the replicas helped, because there was finally something to distribute.

Add replicas for genuinely parallel read volume, analytical isolation, failover. Not to make one expensive query cheaper. And budget for what you cannot index away: max_standby_streaming_delay canceling long reads, hot_standby_feedback causing bloat on the primary, and the cold shared_buffers cache on each replica after every restart.

Measure before you scale out. Run EXPLAIN (ANALYZE, BUFFERS) on the primary, then on a replica, and compare shared read and rows removed by filter. If those match, you do not have a capacity problem. You have a missing index with a bigger infrastructure bill.

---

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky