Do read replicas fix slow reads?
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.
What was measured, and where
Section titled “What was measured, and where”| servers | PostgreSQL 18.6 in containers dbg-rep-p (primary) and dbg-rep-s (streaming standby) |
| image | postgres:18.6, defaults, each limited to --cpus 2 --memory 4g |
| identical settings | shared_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 |
| replication | physical streaming, replication slot s1, asynchronous unless a section says otherwise |
| table | events, 2,000,000 rows, 531 MB total, 62,500 heap pages |
| date | 2026-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.
Does the replica run the query faster?
Section titled “Does the replica run the query faster?”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:
| fastest | slowest | |
|---|---|---|
| primary | 162.884 ms | 234.333 ms |
| standby | 194.708 ms | 260.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.
Where the replica does win
Section titled “Where the replica does win”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:
| fastest | slowest | |
|---|---|---|
| primary | 810.751 ms | 901.118 ms |
| standby | 190.009 ms | 258.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.
What actually fixed the query
Section titled “What actually fixed the query”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:
| before | after | |
|---|---|---|
| primary | 162.884 to 234.333 ms | 1.131 to 1.561 ms |
| standby | 194.708 to 260.967 ms | 1.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 transactionLogical 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.
How do I find out which one I have?
Section titled “How do I find out which one I have?”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_timeFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 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.
What it costs you, measured
Section titled “What it costs you, measured”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 write | 299 of 300 |
| how far behind the standby was at that instant | 0 to 304 bytes, 217 on average |
| how long it then took to catch up | 1.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.
Watch the right numbers
Section titled “Watch the right numbers”SELECT application_name, state, write_lag, flush_lag, replay_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behindFROM pg_stat_replicationORDER 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:
| configuration | sync_state | reads that missed their own write | 200 commits, three runs |
|---|---|---|---|
asynchronous, no synchronous_standby_names | async | 300 of 300 | 502.996, 551.568, 516.713 ms |
synchronous_standby_names = 's1', synchronous_commit = on | sync | 2 of 300 | 841.568, 833.432, 870.691 ms |
synchronous_standby_names = 's1', synchronous_commit = remote_apply | sync | 0 of 300 | 865.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:
| statement | message |
|---|---|
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.
Queries get cancelled by replication
Section titled “Queries get cancelled by replication”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 recoveryand 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_lsnFROM 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.
The routing contract
Section titled “The routing contract”| read type | where it goes | what you must accept |
|---|---|---|
| read-after-write, authorization, inventory, quota, read-modify-write | primary | keep the whole transaction there |
| stale-tolerant feeds, dashboards, exports | asynchronous replica | a freshness bound you measure and alert on |
| must be current, on a replica | synchronous standby with remote_apply | higher commit latency and a primary that stalls if the standby does |
| long-running analytics | a replica dedicated to it | a deliberate conflict, lag and bloat policy |
How do I decide?
Section titled “How do I decide?”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.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”“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.
How do I stop it coming back?
Section titled “How do I stop it coming back?”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.