Skip to content

Do read replicas fix slow reads?

Last updated

View as Markdown

Quick answer: Not if the query is slow because of its plan. A replica runs the same PostgreSQL over the same data, so a missing index is missing on both. It does help when the primary is contended, which is a different problem with the same symptom. Measure which one you have before you buy a server.

serversPostgreSQL 18.6 in containers dbg-rep-p (primary) and dbg-rep-s (streaming standby)
imagepostgres:18.6, defaults, each limited to --cpus 2 --memory 4g
identical settingsshared_buffers 128MB, work_mem 4MB, max_parallel_workers_per_gather 2, effective_cache_size 4GB, random_page_cost 4, max_wal_size 1GB on both
replicationphysical streaming, replication slot s1, asynchronous unless a section says otherwise
tableevents, 2,000,000 rows, 531 MB total, 62,500 heap pages
date2026-09-20

Both containers share one host, so they share its page cache pressure and its disk. This setup should minimize any difference between the two servers. One of the results below shows a difference anyway.

Not on an idle primary. The table, with no useful index on uid:

CREATE TABLE events (
id bigserial PRIMARY KEY,
uid int NOT NULL,
payload text,
created timestamptz NOT NULL DEFAULT now()
);
SELECT count(*) FROM events WHERE uid = 42;

Run alternately on each server so neither gets a run of warm cache the other did not, eight pairs, PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20:

fastestslowest
primary162.884 ms234.333 ms
standby194.708 ms260.967 ms

The ranges overlap, so this test does not show the standby to be slower either. On PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, both chose the same plan, a Parallel Seq Scan with two workers over the same shared hit=15979 read=46521, filtering 666,664 rows per process to find 7.

That part of the claim holds. The replica is not a faster machine, and moving the query does not improve the plan.

Change one thing: make the primary busy. Six concurrent sequential scans on the primary, nine active backends, then the same query on both servers, five pairs, PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20:

fastestslowest
primary810.751 ms901.118 ms
standby190.009 ms258.369 ms

The standby’s numbers did not move. The primary’s did, by roughly a factor of four, and the standby was then faster for this query.

The usual advice misses this distinction. A replica does not repair a bad plan, a missing index, a bad cardinality estimate or an expensive operation. It can remove queueing for CPU, I/O and locks. If latency comes from contention, moving the read may help. If latency comes from the plan, the plan moves too.

The inverse is also real, and this bench did not produce it, because its primary generated no WAL while the comparison ran: the six competing scans are reads. Under a heavy write load the standby is replaying that load while answering, and Hot Standby in the PostgreSQL 18 documentation, read 2026-09-20, names the contention:

The easiest conflict to understand is performance: if a huge data load is taking place on the primary then this will generate a similar stream of WAL records on the standby, so standby queries may contend for system resources, such as I/O.

So the result above is the favourable case for the standby, not the general one. Measure it under your own write load before routing anything.

CREATE INDEX events_uid ON events (uid);

On PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, that took 1098.066 ms to build on the primary and the standby had it moments later at 15 MB. The after column below is five interleaved pairs on the same servers and day; the before column is the eight-pair idle run from the first table on this page, not a fresh measurement:

beforeafter
primary162.884 to 234.333 ms1.131 to 1.561 ms
standby194.708 to 260.967 ms1.103 to 2.050 ms

Those after figures are what the client saw, round trip included. The plan’s own Execution Time on the primary was 0.054 ms, on an Index Only Scan reading 5 buffers, PostgreSQL 18.6 in container dbg-rep-p, 2026-09-20. Either number makes the same point against either number in the before column, and both are far below what a second server did for the idle case.

Do not read “one index” as a free answer. An index is write work on every INSERT, UPDATE and DELETE that touches the column, more WAL to generate and to replay, more to vacuum, more cache to displace, and 15 MB here on PostgreSQL 18.6 in container dbg-rep-p, 2026-09-20. On a busy primary an ordinary CREATE INDEX holds a lock that blocks writes for its duration; CREATE INDEX CONCURRENTLY avoids that at the cost of two table passes, a longer build and the possibility of leaving an invalid index behind. None of that was exercised on this bench, which was idle and small.

The index appears on the standby because it is a physical standby replaying the primary’s WAL, not because indexes propagate generally. You cannot create one there while it is in recovery. On dbg-rep-s, 2026-09-20:

ERROR: 25006: cannot execute CREATE INDEX in a read-only transaction

Logical replication is a different arrangement with different rules: a subscriber’s schema and indexes are managed independently, so nothing here about index propagation carries over to it.

Not from table statistics. seq_tup_read / seq_scan is the average number of rows a sequential scan read before any filter, not evidence that a table was read end to end, and idx_scan is cumulative across every query rather than attributable to the slow one. A selective sequential scan reads few rows; an unselective predicate correctly chooses a sequential scan on a table that has a perfectly good index.

Find the query, then read its plan:

SELECT queryid, calls, mean_exec_time, rows, shared_blks_read, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT count(*) FROM events WHERE uid = 42;

Then ask what the elapsed time is made of. A plan whose own Execution Time accounts for nearly all of the wall clock is a plan problem, and a replica will not touch it. A gap between the two is queueing, locking or connection waiting, and that is the case where moving the read can help.

Your own writes are not there, even when lag is tiny

Section titled “Your own writes are not there, even when lag is tiny”

On a primary doing nothing but this test, PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, 300 trials of inserting a row, committing it, then immediately counting it on the standby:

reads that did not see their own write299 of 300
how far behind the standby was at that instant0 to 304 bytes, 217 on average
how long it then took to catch up1.757 to 6.547 ms, 2.268 average, 3.422 at the 95th percentile

The standby was about two milliseconds and two hundred bytes behind. It still missed almost every read. Small replication lag is not the same as read-after-write safety, because the window you lose is the round trip you were going to make anyway.

That 299 and the 300 of 300 in the synchronous table below are one result, not two. Re-run on PostgreSQL 18.6 in containers dbg-rep-p2 and dbg-rep-s2, 2026-09-20, six runs of 300 trials each against an asynchronous standby: 297, 299, 299, 299, 300 and 300 reads missed. The count moves by a trial or two between runs. Read either figure as “almost every read” rather than as a difference between the two benches.

If you reproduce this, drive all 300 trials from one persistent session on the primary. A read issued through a fresh shell and a fresh connection spends more time connecting than replay takes, so the standby has caught up before the read arrives and the bench reports 0 of 300 whatever the truth is.

Under write load the window stops being small. Sampling every 250 ms during a 4,000,000 row insert on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, the standby reached replay_lag of 5.053 seconds and 111 MB of unreplayed WAL.

SELECT application_name,
state,
write_lag,
flush_lag,
replay_lag,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind
FROM pg_stat_replication
ORDER BY application_name;

The comparison is against pg_current_wal_lsn(), the primary’s current write position, and not against sent_lsn. sent_lsn - replay_lsn excludes WAL the primary has generated but not yet sent, so it understates staleness when the sender is the bottleneck. Both were sampled side by side during that insert on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20: at one sample the true distance was 111 MB while sent_lsn - replay_lsn read 53 MB, and at another 96 MB against 38 MB. A dashboard built on the second number would have shown, on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s on 2026-09-20, 53 of the 111 MB, which is 48 percent, and 38 of the 96 MB, which is 40 percent. The part it missed is 58 MB in both samples, because that is WAL the primary had generated and not yet sent, and the sender was the bottleneck for the same amount at both moments.

write_lag and replay_lag differ for a reason, and both appear above: WAL that has arrived is not WAL that has been replayed, and only replay makes a row visible to a reader on the standby.

Synchronous replication, and what each level actually buys

Section titled “Synchronous replication, and what each level actually buys”

synchronous_commit = remote_apply is the only setting that gives read-after-write from a standby, and the level below it does not. Three configurations, 300 read-after-write trials and three runs of 200 commits each, PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20:

configurationsync_statereads that missed their own write200 commits, three runs
asynchronous, no synchronous_standby_namesasync300 of 300502.996, 551.568, 516.713 ms
synchronous_standby_names = 's1', synchronous_commit = onsync2 of 300841.568, 833.432, 870.691 ms
synchronous_standby_names = 's1', synchronous_commit = remote_applysync0 of 300865.083, 880.689, 850.659 ms

The middle row is the misleading one. on with a named synchronous standby waits for the standby to flush the WAL, which is durability, not visibility. It got the misses down from 300 to 2 and did not get them to 0, and it cost nearly all of what remote_apply costs. If you are paying for synchronous replication in order to read your own writes, remote_apply is the level that does it.

The cost above was measured between two containers on one host, PostgreSQL 18.6, 2026-09-20. Over a real network it is worse, and it comes with an availability cost this bench did not measure: a synchronous standby that stops responding stalls commits on the primary until you remove it from synchronous_standby_names.

You cannot write there, and it is broader than INSERT

Section titled “You cannot write there, and it is broader than INSERT”

Every statement below was run on dbg-rep-s while in recovery, 2026-09-20, and every one was rejected with SQLSTATE 25006:

statementmessage
INSERT INTO events (uid) VALUES (1);cannot execute INSERT in a read-only transaction
UPDATE events SET uid=1 WHERE id=1;cannot execute UPDATE in a read-only transaction
CREATE TABLE t_sb (i int);cannot execute CREATE TABLE in a read-only transaction
CREATE TEMP TABLE tt AS SELECT 1;cannot execute CREATE TABLE AS in a read-only transaction
SELECT nextval('events_id_seq');cannot execute nextval() in a read-only transaction
SELECT id FROM events WHERE uid=42 FOR UPDATE;cannot execute SELECT FOR UPDATE in a read-only transaction
LISTEN chan;cannot execute LISTEN during recovery
NOTIFY chan;cannot execute NOTIFY during recovery

Temporary tables, sequences, row-locking selects and LISTEN/NOTIFY all fail, which catches people who assumed “read-only” meant “no INSERT”. Treat the exact wording as this version’s wording rather than as an interface guarantee; the SQLSTATE is the stable part.

The routing consequence is about transactions, not statements. Any unit of work where an earlier read informs a later write belongs entirely on the primary: read-modify-write, uniqueness checks, quota and inventory decisions, authorization. Sending its first read to an asynchronous standby means deciding from a state that is not the one you are about to write against.

The standby must replay WAL that removes row versions a running query is still reading. With max_standby_streaming_delay = 0 on dbg-rep-s, 2026-09-20, a 25 second read on a 400,000 row table while the primary ran two update-and-vacuum cycles produced:

ERROR: canceling statement due to conflict with recovery

and pg_stat_database_conflicts.confl_snapshot moved from 1 to 2. Repeating the identical test with hot_standby_feedback = on left the counter at 2. The counter moving in the first leg establishes the second leg’s absence: the instrument registers this conflict, so not registering it is a result rather than a blind spot.

Two corrections to how this is usually described. max_standby_streaming_delay is not a grace period each query is granted. It is measured from when the WAL arrived, so a query can get far less than the 30 second default. Hot Standby in the PostgreSQL 18 documentation, read 2026-09-20:

Thus, the grace period allowed to any one query on the standby is never more than the delay parameter, and could be considerably less if the standby has already fallen behind as a result of waiting for previous queries to complete, or as a result of being unable to keep up with a heavy update load.

And vacuum cleanup is the common conflict, not the only one: access-exclusive locks from DDL, dropped tablespaces and databases, and visibility-map changes all conflict too, and hot_standby_feedback addresses only the cleanup case.

Reading the cost of hot_standby_feedback, correctly

Section titled “Reading the cost of hot_standby_feedback, correctly”

With feedback on, the standby tells the primary its oldest snapshot and the primary stops cleaning up past it. That is bloat on the primary caused by a report on the standby, and it is a real trade.

Look for it on the replication slot, not in pg_stat_replication:

SELECT slot_name, active, xmin, catalog_xmin, restart_lsn
FROM pg_replication_slots;

Read the slot, not pg_stat_replication.backend_xmin, which reports nothing when a physical replication slot is in use. Measured on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, with a long read running on the standby and hot_standby_feedback = on: the slot’s xmin read 10165, matching the standby’s own pg_stat_activity.backend_xmin for that query exactly, while pg_stat_replication.backend_xmin was null. Turning feedback off with the same read still running took the slot’s xmin to null within one wal_receiver_status_interval. So the slot column tracks the setting and the other one reported nothing in either state. Monitoring pg_stat_replication.backend_xmin here would have told you feedback was off while it was holding vacuum back.

read typewhere it goeswhat you must accept
read-after-write, authorization, inventory, quota, read-modify-writeprimarykeep the whole transaction there
stale-tolerant feeds, dashboards, exportsasynchronous replicaa freshness bound you measure and alert on
must be current, on a replicasynchronous standby with remote_applyhigher commit latency and a primary that stalls if the standby does
long-running analyticsa replica dedicated to ita deliberate conflict, lag and bloat policy

If one query is slow and its plan accounts for the time, fix the query. On PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20, the index above took it from 162.884 to 234.333 ms down to 1.131 to 1.561 ms on the primary, and from 194.708 to 260.967 ms down to 1.103 to 2.050 ms on the standby. That needed no replica, no routing and no second server to operate.

If the query is slow only when the primary is busy, a replica is a real answer, and the measurement to take is the one above: the same query on both, under load, interleaved.

If many queries are slow because there are many of them, a replica is the right tool and the work is the routing, not the server.

If the slow thing is a report competing with the application, a replica is right for isolation, and saying so is what tells you how much lag you can accept.

If you need to read your own writes, route them to the primary, or use remote_apply and pay for it, or measure the staleness you are accepting.

“Scale reads with a read replica” is the standard architectural answer to the standard architectural question, and the shape of “reads” plus “slow” matches it whatever the cause. The replica gets built, the query stays slow, and the diagnosis moves on to caching.

The second pattern is the confident staleness claim. Generated advice tends to land on one of two absolutes: replication lag is milliseconds so it is fine, or replicas are always stale so never read from them. The measurement above is both at once, on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20: two milliseconds behind, and 299 reads in 300 missing their own write.

Measure the query before adding hardware. EXPLAIN (ANALYZE, BUFFERS, SETTINGS) answers the question a replica cannot, and the gap between plan time and wall clock is what says whether contention is your problem.

Alert on replay lag against pg_current_wal_lsn(), not on whether the replica is connected, and not on sent_lsn - replay_lsn.

Decide hot_standby_feedback explicitly, and watch the slot’s xmin for what it is costing you.

Route by transaction, not by statement.

Do not treat the standby as a backup or a finished HA plan. Asynchronous replication can lose committed transactions on a primary failure, a standby whose required WAL has been recycled needs rebuilding, and promotion still needs endpoint rerouting and fencing against split brain.

  • Will a read replica make my slow query faster?

    It depends on why the query is slow, and the honest answer has two halves. Measured 2026-09-20 on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, an unindexed count on 2,000,000 rows ran 162.884 to 234.333 ms on an idle primary and 194.708 to 260.967 ms on the standby, so the replica was no faster. With six competing scans on the primary the same query ran 810.751 to 901.118 ms there and 190.009 to 258.369 ms on the standby. A replica does not fix a bad plan; it can remove contention. The index fixed the plan and took the primary to 1.131 to 1.561 ms and the standby to 1.103 to 2.050 ms, PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s, 2026-09-20.

  • What is a read replica actually for?

    Capacity, contention relief and isolation. It answers more concurrent queries, it removes work from a saturated primary, and it keeps reporting traffic away from the transactional workload. None of those is the same as making an unchanged query algorithmically faster.

  • How far behind is a replica?

    Less than you think, and it still loses your writes. Measured 2026-09-20 on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s on a primary doing nothing but this test, the standby trailed by 0 to 304 bytes and caught up in 1.757 to 6.547 ms, yet 299 of 300 reads issued straight after a commit did not see their own write. Under a 4,000,000 row insert the same standby reached 5.053 seconds and 111 MB behind.

  • Can synchronous replication give me read-after-write?

    Only remote_apply can, and remote flush is not enough. Measured 2026-09-20 on PostgreSQL 18.6 in containers dbg-rep-p and dbg-rep-s across 300 trials each, asynchronous replication missed 300 of 300, synchronous_commit = on with a named synchronous standby still missed 2 of 300, and remote_apply missed 0 of 300. The price was 200 commits in 502.996 ms asynchronous against 865.083 ms under remote_apply.

  • Why did my query on the replica get cancelled?

    Recovery conflict. Measured 2026-09-20 on PostgreSQL 18.6 in container dbg-rep-s with max_standby_streaming_delay = 0, a 25 second read during primary churn was cancelled and pg_stat_database_conflicts.confl_snapshot moved from 1 to 2. With hot_standby_feedback = on the identical test left the counter at 2. Vacuum cleanup is the common cause and not the only one.