The problem
Read traffic outgrows a single primary long before write traffic does, but the replicas that can absorb it are always some distance behind, so routing a read to one silently weakens the consistency the application was written against.
What makes it hard
- Streaming replication is asynchronous by default, so a replica is eventually consistent with the primary and the gap is unbounded rather than merely small.
- Read-after-write is not a property of the database, it is a property of the request path, and only the application knows which reads need it.
- A proxy that routes by inspecting SQL cannot know whether a SELECT is part of a read-modify-write, because the intent is in the application, not the statement.
- Long-running replica queries and prompt WAL replay are in direct conflict; resolving it either cancels queries or bloats the primary.
- Standbys reject a set of statements outright, so a mis-routed connection fails in ways that look nothing like a routing problem.
Components
How it fails
| Failure | How you notice | What bounds it |
|---|---|---|
| Read-after-write violation - a user sees state from before their own committed write. | Support reports of "my change did not save" that resolve on refresh; a canary that writes a row, immediately reads it from the replica pool, and records the miss rate. | Route reads that follow a write in the same logical operation to the primary, or make the read wait for the replica to reach the LSN the write returned. |
| Reads keep being served by a node that has just been promoted, or writes are sent to a node that has just been demoted. | Read-only transaction errors clustered immediately after a failover, and a write-path latency step change with no traffic change. | Derive both the write target and the read pool from live role checks against the Patroni REST API, and drop sessions on a role change rather than reusing them. |
| Unbounded lag from a single stuck replica while the pool average looks healthy. | Per-replica replay lag, not an average. One replica pinned while the others track means a stalled WAL receiver or a replay conflict, and it is serving stale reads the whole time. | Eject a replica from the read pool on a lag threshold rather than on liveness, and alert on the maximum across replicas. |
| Replica query cancellations under WAL replay conflict. | Errors about canceling statements due to conflict with recovery, counted in pg_stat_database_conflicts on the standby. | Choose deliberately between raising max_standby_streaming_delay (staler reads, fewer cancellations) and enabling hot_standby_feedback (fewer cancellations, more bloat on the primary). |
| A statement that cannot run on a standby is routed to one. | Errors naming a read-only transaction, or failures of nextval, LISTEN/NOTIFY, SELECT FOR UPDATE and advisory locks from a subset of connections. | Route by connection intent declared at acquisition, not by parsing the statement. Anything holding a lock or a sequence goes to the primary by definition. |
| Replication slot retention filling the primary's disk because a read replica was left offline. | pg_replication_slots showing a growing restart_lsn distance, and pg_wal growth uncorrelated with write volume. | Set max_slot_wal_keep_size so the primary sacrifices the replica rather than itself, and alert on inactive slots. |
When not to use this
- The primary is not read-bound. Splitting adds a consistency problem and no capacity, and connection pooling or an index usually recovers more headroom than a replica does.
- The workload is dominated by read-modify-write transactions. Almost every read must go to the primary anyway, so the split buys a routing layer and no traffic.
- The application cannot express intent - no way to say "this read must be current". Without that signal the only safe policies are "everything to the primary" or "accept stale reads everywhere".
- Reporting and analytics, where the requirement is isolation rather than consistency. Give those a dedicated replica with a long delay tolerance and keep it out of the serving pool entirely.
PostgreSQL’s own documentation states the constraint in one sentence: data on a standby is eventually consistent with the primary, and the same query run nearly simultaneously against both may return different results. Once the commit record for a transaction has been replayed on the standby, that transaction becomes visible to new snapshots there, and not before.
That is the whole problem. Everything below is about the interval between “the primary said commit succeeded” and “the standby has replayed it”, who is exposed to that interval, and what it costs to close it. See replication lag for the measurement itself and eventual consistency for the model.
Reads are not one thing
The mistake that makes read/write splitting hard is treating “reads” as a category. They are at least three, with different requirements, and a routing design that does not distinguish them will either be unsafe or will route almost everything to the primary and achieve nothing.
| Class | Example | Tolerable staleness | Correct destination |
|---|---|---|---|
| Read-after-write | Rendering the page immediately after saving a form | None | Primary, or a replica proven to have caught up |
| Read-modify-write | SELECT then UPDATE in one transaction, uniqueness checks, balance checks |
None, and it needs row locks | Primary, always. A standby cannot take the locks |
| Ambient read | Search results, listings, dashboards, most GET requests | Seconds | Replica pool |
| Analytical read | Reports, exports, backfills | Minutes to hours | A dedicated replica, out of the serving pool |
The third row is where the capacity is. In a typical request-serving application it is the large majority of queries, and it is the only class that can be moved without an argument. The design question is therefore not “how do I route reads” but “how does a request declare that it belongs to one of the first two classes”.
Where to split
There are three places to make the decision, and they differ mainly in who owns the mistake.
In the application. Two connection pools, chosen at the point where work is dispatched. The application declares intent because the application is the only thing that has it: a handler knows it has just written, a background job knows it is idempotent, a search endpoint knows it can tolerate a second of staleness. This is the only option that can be correct without heuristics, and the only one where a missing declaration is a visible code change, not an emergent behaviour.
In a pooler. PgCat and Pgpool-II can
route per query and balance the read pool, which means the application connects to one endpoint and
the split happens below it. This is the pragmatic answer when you do not control the client, and
Pgpool-II additionally understands a lag threshold, so a replica that has fallen too far behind stops
receiving reads. The cost is that the router is now inferring intent from statement text. A SELECT
inside an explicit read-write transaction has to go to the primary; a SELECT that will be followed
by an UPDATE in the next statement is indistinguishable from one that will not.
In a proxy, by port. HAProxy with two listeners (one backed by whichever node Patroni reports as leader, one balanced across nodes reporting as healthy replicas) is coarse, obvious and hard to get subtly wrong. It cannot route per query at all, so the application still has to choose a port, which pushes the decision back into the application where it belongs. This is the combination worth reaching for first.
PgBouncer is not in this list on purpose. It pools connections and has no notion of primary or replica; it belongs behind the split, one instance per destination, solving the connection-count problem, not the routing one.
Making read-after-write safe
There are four mechanisms, and they trade cost against how much of the application has to know.
Sticky-to-primary window
After a write, route that session’s reads to the primary for a fixed period. Simple, requires no database features, and is the technique most systems actually ship.
The trade is that the window is a guess. Too short and it fails exactly when the cluster is under load, which is when lag is worst and when you can least afford the primary to take the reads. Too long and the split stops relieving anything. Worse, the window is per-session, so it does nothing for a write in one session that must be visible to a read in another. That is the common shape of “the worker picked up the job before the row existed”.
The LSN token
This is the mechanism that is actually correct, and it is not complicated. On the write path, capture the primary’s current WAL position:
-- On the primary, immediately after COMMIT.
SELECT pg_current_wal_insert_lsn();Carry that value with the unit of work: in the session, the job payload, or a response header the client echoes back. On the read path, before using a replica, compare against what it has replayed:
-- On the candidate replica.
SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS caught_up;If it is behind, either pick another replica or fall back to the primary. pg_wal_lsn_diff gives you
the gap in bytes if you want to record how close you came instead of just a boolean.
This turns an unbounded correctness risk into a bounded latency risk, which is a much better thing to own. The cost is real: the write path has to return the position, the read path has to carry it, and every intermediate hop (a queue, a cache, an HTTP client) has to preserve it. That plumbing is the actual work, and it is why teams reach for the sticky window instead.
synchronous_commit = remote_apply
PostgreSQL can make the commit itself wait until the standby has applied the change, not merely received it. At that point a read on that standby after a successful commit is guaranteed to see it, with no token and no application change.
This is the most expensive option and should be scoped, not set globally. Every commit now pays a round trip plus the replay, so write latency becomes a function of the slowest synchronous standby, and if no synchronous standby is available the commit blocks. It is set per transaction, so the sane pattern is to leave the cluster default alone and raise the level only for the small number of writes whose reads must be immediately visible elsewhere.
Do not split that read
Always available, frequently correct, and routinely overlooked. If a read is on the critical path of a mutation, sending it to the primary costs one query’s worth of capacity and removes a class of bug. Reserve the machinery for the reads that are actually numerous.
| Mechanism | Correct? | Cost | Who must change |
|---|---|---|---|
| Sticky window | Probabilistic | None | Routing layer only |
| LSN token | Yes | Occasional fallback to primary; plumbing through every hop | Application and every intermediary |
remote_apply |
Yes | Write latency and write availability | Database configuration, per transaction |
| Route to primary | Yes | Primary capacity | Nothing |
The replica’s own problems
A read pool is not just a copy of the primary that lags. Two behaviours have to be configured deliberately, and both are trades rather than fixes.
Replay conflicts cancel queries. A standby must apply WAL it has already received, so when an
incoming record conflicts with a running query - a vacuum cleanup removing rows that query can still
see, an exclusive lock from DDL, a dropped tablespace - one of them has to lose, and it is the query.
max_standby_streaming_delay governs how long replay will wait first; the default is 30 seconds, and
-1 means wait indefinitely. Raising it protects long reads and lets the replica fall further
behind, which is the same as making your reads staler. This is the clearest illustration that
“replica for reads” and “replica for failover” are different jobs with opposite tuning: a standby
kept for availability wants a delay near zero, a standby serving reports can tolerate hours.
hot_standby_feedback moves the cost to the primary. Off by default. Turning it on makes the
standby tell the primary about its running queries so that vacuum does not remove rows those queries
still need, which removes cleanup conflicts as a source of cancellations. In exchange, dead rows
survive longer on the primary and the table bloats. The documentation makes the fair point that the
situation is no worse than running those queries on the primary directly, but it is worse than not
running them at all, and a replica pool with long-running queries and feedback enabled can hold back
vacuum indefinitely. Cap the exposure with a statement timeout on the read pool. Do not trust the
queries to be short.
The related trap is retention. If you use a replication slot per replica so the primary keeps WAL
the replica still needs, then a replica that is down or partitioned makes the primary retain WAL
forever. Set max_slot_wal_keep_size so the primary discards a hopeless replica instead of filling
its own disk; the replica can be rebuilt, and a full pg_wal on the primary is an outage.
Routing has to follow failover
The last piece is the one that gets discovered during an incident. When the cluster fails over, the node that was serving reads becomes the primary and the node that was primary either disappears or comes back as a replica. Any routing layer that resolved “primary” once and cached it will now be sending writes at a read-only node, and any read pool built from a static host list will include the new primary. That is usually fine for correctness and bad for the write path’s latency.
So the read pool and the write target both have to be derived from the same live role information the high-availability topology already publishes, and sessions have to be dropped rather than reused across a role change. A demoted node with live sessions on it does not reject them; it fails their next write with a read-only transaction error, which the application will report as a bug in itself.
Two things belong on the dashboard permanently: the maximum replay lag across the read pool, and the fraction of reads that fell back to the primary because a replica was not caught up. The first tells you whether the lag budget holds. The second tells you whether the split is still doing anything. A fallback rate creeping towards one means you have paid for the routing layer and are back to a single node serving everything.