Read-Your-Writes at the Proxy: LSN Session Consistency for PolarDB

Most database proxies that do read/write splitting get one thing wrong: after a client writes a row and immediately reads it back from a replica, the read can return stale data. This article explains how we fixed that for PolarDB inside Polar Proxy — a ProxySQL-derived proxy — by tracking the write-ahead log position (LSN) and making replicas wait until they have replayed that far before answering a read. The proxy therefore guarantees that once a write has committed, a later read in the same session will see it, even when that read is served by a replica. And it does this without adding an extra round trip on the hot path.

Part I — Background

Read/write splitting, and why anyone bothers

A PostgreSQL cluster usually has one primary (the writer) and one or more replicas (readers). The primary accepts all writes; replicas receive the primary’s changes and serve reads. A proxy that performs read/write splitting sends writes to the primary and eligible reads to a replica. Reads that require the primary, such as SELECT ... FOR UPDATE, stay there. The goal is simple: get read load off the primary.

Replicas lag

Replicas do not apply changes instantly. The primary appends every change to its write-ahead log (WAL), and replicas replay that log asynchronously. A replica is therefore almost always a little behind the primary. How far behind depends on load, network, and hardware; it is hardly exactly zero.

The read-your-writes problem

That lag creates a correctness bug. Picture an application that does this:

INSERT INTO orders (id, status) VALUES (42, 'pending');
SELECT status FROM orders WHERE id = 42;

The INSERT goes to the primary. The SELECT — if the proxy routes it to a replica — may run before that replica has replayed the INSERT. The client reads status and gets nothing, or the old value. From the application’s point of view, its own write “disappeared”. This is the read-your-writes (RYW) failure. If you expect a client should always be able to read what it just wrote, this failure looks buggy.

A one-minute LSN primer

To fix RYW precisely, it requires a way to say “this exact point in the change stream.” PostgreSQL has one built in. Every record written to the WAL carries a 64-bit position called the LSN (Log Sequence Number). LSNs grow monotonically and never decrease. “The replica has replayed past LSN X” is a precise, it means every change at or before X is visible on that replica.

How the industry tries to solve it — and where each approach falls short

There are some workarounds in the industry. Each one trades correctness for performance or latency, which is why none of them is satisfying.

1. Replication-lag gating. The proxy measures each replica’s lag and refuses to send reads to a replica that is “too far behind” (a threshold in seconds or bytes). This is what stock ProxySQL’s max_replication_lag does. The problem: a threshold is a heuristic. A replica that is under the threshold can still be a few milliseconds behind a specific write; and a replica that is over the threshold is rejected entirely, even for reads that have nothing to do with recent writes. It reduces staleness, it does not eliminate it.

2. Sleep to a safe point. Some applications (and some middleware) deliberately delay reads after a write — sleeping a fixed interval, or literally running SELECT pg_sleep(1) before querying the replica — on the assumption that “a replica catches up within a second.” The problem is obvious: the delay is arbitrary. Under a replication spike, the replica may still be behind after the sleep, so the client still read stale data; and when the replica was already caught up, user paid the sleep for nothing. It adds latency to every post-write read without actually guaranteeing anything.

3. Stick to the primary after a write. Once a session has written, route all of its subsequent reads to the primary for a cooldown period. This is correct but coarse: it gives up exactly the read offload, and a long-lived session gets pinned to the primary permanently.

4. Synchronous replication. Configure replicas to acknowledge every WAL record before the write commits. This makes writes slower (every commit now waits on a replica round trip) and solves durability more than read-your-writes — a read replica still serves reads from its own replay point.

5. Sequence-number tracking. The principled approach: give every change a monotonic sequence number (MySQL’s GTID, OceanBase’s GTS, and so on), record the last sequence a client observed after a write, and only read replicas that have applied past it. This is causal consistency, and it is exactly right. PostgreSQL’s natural sequence number is the LSN — which is why we built on it.

Part II — What upstream ProxySQL lacks

Polar Proxy is derived from ProxySQL, an open-source proxy. ProxySQL’s PostgreSQL support can do read/write splitting, but it has none of the machinery needed for RYW.

First, the normal response carries no LSN. The proxy uses libpq, PostgreSQL’s C client library, for backend connections. libpq already parses a small status message called ReadyForQuery (RFQ), which the backend sends when a query cycle finishes. But the standard RFQ carries only a transaction-state byte, not a WAL position. Learning the writer’s position would therefore require another mechanism — for example, an extra SELECT pg_current_wal_lsn() after each committed write. We wanted to avoid that extra round trip on the query path.

Second, there is no decision machinery. Even if the proxy could see the LSN, upstream routing has no notion of “this read must wait for position X.” A read is either “send to a replica” or “send to the primary,” with nothing in between.

Third, the additions must stay PolarDB-specific. Ordinary PostgreSQL servers do not support these protocol extensions or wait settings. We therefore needed explicit PolarDB configuration and build controls so the new behavior applies only where it is supported.

Part III — What the Polar Proxy team built, and what it achieves

The fix has three layers: see the LSN, decide what to do, and make the replica wait.

See the LSN, for free

We patched the bundled libpq so that the backend can append its current LSN to the ReadyForQuery message. A connection requests this at startup with the _polar_send_lsn parameter, and three new libpq calls expose it to the proxy: PQsetPolarSendLSN turns LSN parsing on, and PQgetLSN / PQhasLSN read the value back.

The key point is no extra round trip. The backend was already going to send an RFQ when the query cycle finished; we only asked it to attach one more number to that message. The proxy learns the writer’s reported WAL position or the replica’s replay position as a byproduct of normal query execution.

Decide, top-down

The implementation has four stages: three for routing the request and one for processing the response:

1. collect — gather the routing inputs: the hostgroup topology (a hostgroup is ProxySQL’s term for a numbered group of backend servers), query-rule decisions, the session’s remembered LSNs, transaction state, and protocol shape.
2. plan — evaluate the routing checks in a fixed order. For the ordinary reads covered here, the main outcomes are PASSTHROUGH (route as normal), FORCE_PRIMARY (send to the writer), or REPLICA_WITH_WAIT (choose a replica and enforce the required LSN). The plan can also reject a request when its consistency requirements cannot be met.
3. execute — apply the decision: set the target hostgroup and stage any required wait.
4. process_result — read the RFQ LSN and advance the session’s remembered write or observed position as appropriate.

Two design rules make this safe. Within a writer generation, the session’s target LSN is monotonic: later observations cannot lower the bar. Writer changes are tracked separately so positions from different writer histories are not mixed. And when a required position cannot be enforced, the proxy follows the configured failure policy: use the primary or fail the request. It is possible to explicitly allow weaker behavior with a warning, but that choice no longer provides the same consistency guarantee.

Make the replica wait

For an eligible simple-protocol read whose selected replica still needs a wait, the proxy prepends three SET statements to the read, all in one query message:

SET polar_consistency_mode = 'strict';         -- or 'best_effort'
SET polar_proxy_wait_timeout_ms = 500;         -- how long to wait
SET polar_xact_split_wait_lsn = '97500059720'; -- the wait condition: LSN 16/B374D848, in decimal
<the original SELECT>

The last SET records the wait target. When the replica acquires the read snapshot, it waits until replay reaches that LSN or the wait times out. On success, it then runs the read. The proxy discards the three successful SET completion results and forwards the SELECT result, while preserving relevant warnings and errors.

The two consistency modes decide what happens on a timeout: strict raises an error; best_effort serves possibly-stale rows with a warning. Either way the client knows, and — critically — a best-effort timeout is reported to the client rather than hidden.

The result

The effect is a precise read-your-writes guarantee for protected reads. After a committed write, the proxy remembers its LSN. The next replica-eligible read carries that LSN in the wait wrapper, and the replica answers only after it has replayed that far. The client always sees its own writes — no arbitrary sleeps, no threshold guessing, no pinning to the primary. And because the LSN rides on messages the backend was already sending, the guarantee costs no extra round trip on the query path.

When the proxy cannot enforce the wait — a backend without LSN support, a replica it cannot reach — it falls back to the primary and preserves correctness. Consistency is never silently sacrificed for throughput.

In the next article we will look at the harder case: a transaction — a sequence of statements that must behave as one unit — and how we safely offload a read inside it without breaking its semantics.