Splitting the Read-Only Transaction: Replica Reads Inside an Open Transaction

Read/write splitting is easy for single, autocommit statements. It gets hard the moment a client opens a transaction — a sequence of statements that must behave as one unit. Most proxies give up and send every statement inside a transaction to the primary, even the reads. This article is about a middle path we built into Polar Proxy: keep the transaction on the primary, but let its read statements borrow a replica connection — importing the transaction’s own visibility and waiting for the replica to catch up — so long-running transactions can still offload their read load.

Part I — Background

What a transaction pins down

In PostgreSQL, a transaction is a sequence of SQL statements that either all take effect or none do. Internally each transaction is identified by a transaction ID (an XID), and it runs against one backend process on one server. While the transaction is open, that backend holds the transaction’s snapshot — the view of the database the transaction sees — plus any locks it has taken, plus any session-local settings.

This is why you cannot casually move a transaction to another server. If the proxy routed the first statement to one backend and the next statement to another, the second backend would not have the first statement’s uncommitted changes, its locks, or its snapshot. The transaction would fall apart. So a proxy that cares about correctness has, historically, had only one choice: route every statement of a transaction to the primary, read-only statement included.

Why isolation level matters

The isolation level decides how much a transaction sees of other concurrent transactions. The four standard levels, from weakest to strongest, are read uncommitted, read committed, repeatable read, and serializable.

The crucial distinction is the snapshot. Under repeatable read and serializable, the transaction takes a snapshot at its first statement and keeps it for the whole transaction — so a read later in the transaction must see the same snapshot as the first read. That makes mid-transaction offload effectively impossible: the borrowed replica would need to reproduce the exact snapshot. Under read committed, however, each statement takes a fresh snapshot. A read is only required to see everything committed before that statement — which is precisely what our LSN-based wait can enforce. This is the window that makes safe splitting possible.

A read inside any open transaction

A replica can serve a read inside an open transaction if it can reproduce two things: the transaction’s own visibility (its own writes, if any) and everything else that committed before the statement. Polar Proxy reproduces both, so it does not have to stop at the read-only prefix.

The transaction’s own writes are reproduced by importing its XID list onto the replica. Everything else is reproduced by an LSN wait. The first write is therefore not a hard boundary: a read before it needs only the LSN wait, while a read after it needs both the XID import and the wait. Part III walks through both cases.

What a pooled connection carries

Two facts about pooled connections set up everything that follows.

The first is identity. A PostgreSQL connection pool never hands just any free connection to just any client: it reuses a connection only when its backend user and database match the client’s. This is not something Polar Proxy invented — upstream ProxySQL has always done it — and the reason is authorization. PostgreSQL decides which tables a session may read, and which roles apply to it, from the identity it authenticated with. Give a connection authenticated as one user to a session running as another, and the read silently runs under different permissions. So “select a replica connection” always means “select a replica connection under the same user and database.”

The second fact is lifecycle. For an autocommit statement the answer is obvious: borrow a connection, run the statement, return it. But a split read runs inside an open transaction. How long should the borrowed replica be held — just for that one statement, or for the whole transaction? Upstream offers no precedent here, because upstream ProxySQL never splits a transaction at all. It is a question the PolarDB team had to answer from scratch, and Part III shows what they decided.

Part II — How other products handle it, and what upstream ProxySQL does

Before describing our approach, it is worth seeing what everyone else does, because the contrast makes the design choice clear.

Pgpool-II, the classic PostgreSQL proxy, does statement-level load balancing for autocommit queries, but for transactions it routes the whole transaction to one backend. Its documentation is explicit that load balancing is not guaranteed inside a transaction — the transaction simply does not get split.

PgBouncer is a connection pooler, not a query router: it forwards statements without parsing them for routing, so it has no read/write splitting to begin with.

Vitess, the MySQL-focused sharding layer, does route reads to replicas, but a read-write transaction is pinned to the primary; only autocommit (or explicit read-only transaction) reads are offloaded. Its “read-after-write” guarantee for autocommit is implemented by tracking GTIDs — MySQL’s global transaction identifiers — the same idea we use, applied to MySQL rather than PostgreSQL.

Amazon Aurora PostgreSQL is the most instructive comparison. Aurora implements read-your-writes inside the database engine: a session parameter aurora_replica_read_consistency = 'session' makes a replica serve reads only after applying the commit LSN of the primary, tracked inside the kernel. It solves the same problem, but only because Aurora owns the storage engine and can thread the commit LSN through every backend. You cannot get that guarantee on stock PostgreSQL — which is exactly the gap a proxy-layer solution fills.

Upstream ProxySQL sits at the proxy layer, so it faces the same problem Aurora solved in-kernel — but without the kernel access. Its PostgreSQL support routes transactions to the primary wholesale. It has no transaction metadata, no notion of a “safe read”, and no way to temporarily borrow a replica while keeping a transaction open. Every read inside a transaction hits the primary, no matter how read-only that transaction is.

Part III — What the Awide dev team built

Our approach: keep the transaction open on the primary, and offload its read statements to a replica — before the first write and after it — under read committed. Both cases wait for the replica to reach the target LSN; the after-write case also imports the transaction’s XIDs so the replica sees its own writes.

See the transaction state

The first problem is the same as the last article: the proxy cannot see whether a transaction is splittable. Standard ReadyForQuery (RFQ) carries a single transaction-state byte — “idle”, “in transaction”, “in failed transaction” — but nothing about which transaction, its XIDs, or whether it has written yet.

We extended the bundled libpq again. A connection requests extra transaction metadata at startup with the _polar_send_xact parameter. The backend then appends a single extra marker byte to each RFQ: 'x' means the transaction is splittable (it has no unflushed WAL of its own — either it has not written yet, or everything it wrote is already flushed to shared storage), and 'w' means there is pending WAL (it has written but the WAL is not flushed yet, so a replica cannot be assumed to have reached it). If the transaction is not splittable, no extra byte is sent at all. A NUL-terminated XID string follows the marker whenever the transaction is splittable; it may be empty. New libpq calls — PQgetXactSplitXids, PQisXactSplittable, PQisXactWalPending — expose these three logical facts to the proxy, again at zero extra round trip.

“Splittable” is not the same as “read-only”. A transaction that has already written is still splittable once its WAL is flushed; the RFQ then carries a non-empty XID list. A transaction that has not written yet reports no XIDs at all. The XID list separates the two cases: an empty list is the read-only prefix (no import needed), a non-empty list means the proxy must import those XIDs so the replica can see the transaction’s own writes.

Enumerate the safe reads

A read is eligible for splitting only if all of these hold:

• the transaction is under read committed isolation — the proxy tracks each transaction’s effective level, and repeatable read and serializable transactions are never split;
• the transaction is splittable — it has no unflushed WAL of its own, so the RFQ reports x rather than WAL-pending (w); a transaction that has written stays eligible once its WAL is flushed, and one that has not written is trivially splittable. A w report is not an automatic exclusion: when the monitor has already observed a replica of the group replaying at or past the transaction’s reported LSN, the proxy admits the split anyway, because the confirmed replay position supersedes the pending-flush state;
• the statement is a top-level SELECT without a locking clause — no FOR UPDATE/FOR SHARE/FOR NO KEY UPDATE/FOR KEY SHARE;
• it is a single statement (no multi-statement string, no extended-protocol flow);
• for a read before the first write, the session has not run a SET inside the transaction — a transaction-local setting exists only on the primary connection, and until that state can be modeled the proxy keeps such reads on the primary.

If any check fails, the read stays on the primary. The default is safety; the offload is the exception.

Select a replica for one statement

When a read passes every check, the proxy selects a compatible replica connection without closing the primary connection. It sends a hidden setup statement, the consistency wait, and the original query, all in one shot:

SET polar_xact_split_xids = '1,1234,5678';           -- command id, then the transaction's XIDs
SET polar_consistency_mode = 'strict';               -- forced internally by the split wrapper
SET polar_proxy_wait_timeout_ms = 5000;              -- the wait deadline, in milliseconds
SET polar_xact_split_wait_lsn = '97500059720';       -- wait until replay reaches here (decimal LSN)
<the original SELECT>

The first field of polar_xact_split_xids is the command id, not an XID; the second field is the top transaction XID, and any remaining fields are child XIDs. The wrapper emits these four SET statements in this fixed order — the XID list, the consistency mode, the wait timeout, then the wait LSN — whenever the reader must still catch up. When a freshly chosen reader is already known from the monitor to have replayed past the target LSN, the wait statements are dropped and only the XID import precedes the query. When the session is reusing a reader it already pinned earlier in the same transaction, the wait is also omitted, but only if that reader’s cached writer scope, startup identity, and configuration generation still match the current session. The strict mode and the timeout are not copied from the client’s session — the split wrapper forces strict internally (so a split read never silently serves stale data) and appends its own timeout before the target LSN. A timeout value of 0 means the proxy wait loop waits indefinitely; statement-level timeouts and client cancellation can still interrupt it.

A read before the first write takes the normal reader wait path (REPLICA_WITH_WAIT) rather than the split wrapper. The transaction has no top XID yet, so there is nothing to import; the proxy just asks the replica to wait for the target LSN and then runs the original SELECT. The replica sees everything committed before the statement — exactly what read committed promises — and there is no transaction-local state to reproduce. Only after the first write does the path switch to REPLICA_TXN_SPLIT, which also imports the transaction’s XIDs.

“Compatible” has a precise meaning. The replica pool reuses a connection only when its identity matches the client’s — the same backend user and database, plus the startup settings the client negotiated. Upstream ProxySQL already matches user and database for its ordinary pool; the PolarDB team rebuilt that check for the reader pool as a four-part key (profile_generation, auth_hash covering user and database, startup_identity_hash, and startup_options_hash), so a connection can never leak across users or across a settings change.

The replica applies the read committed semantics to the borrowed statement: it sees everything committed before that statement, which the LSN wait guarantees. The proxy then consumes the hidden setup results and forwards only the SELECT result. The primary connection — still holding the transaction’s snapshot, locks, and settings — is untouched, and the transaction resumes on it for the next statement.

One engineering detail matters for latency: the replica connection must already exist, because the client’s request path must not block on a new connection handshake. To make that true, the proxy warms up replica connections in the background. And it opens those warmup connections outside the hostgroup-manager lock — it reserves capacity under the lock, performs the TCP/authentication handshake after releasing it, then re-acquires the lock only to validate and publish the finished connection. A handshake can take tens of milliseconds; holding the topology lock for that long would stall every other routing decision.

Hold the replica for the whole transaction

A split read borrows a replica, but it does not hand it back after the statement. The first eligible read in a transaction selects a replica connection and the session keeps it for the rest of that transaction; each later eligible read reuses the same replica instead of selecting a fresh one. The primary connection stays untouched throughout, still holding the snapshot, locks, and settings. Only when the transaction ends — commit or rollback — does the session return the replica to the pool.

This is the PolarDB team’s own decision, with no upstream precedent: upstream ProxySQL never splits a transaction, so it never had to say how long a borrowed replica lives. Holding it for the whole transaction buys two things. It avoids a fresh selection — and a possible pool miss — on every statement, and it lets a later read skip the LSN wait entirely once the held replica has caught up. The pin has one exception: if the routing target moves (the reader hostgroup changes) or the held replica can no longer serve (it drops, or stops being a valid replica), the session releases it and selects another.

Recover when the replica dies mid-split

A split read can fail in the middle: the selected replica can drop its connection, time out on the wait, or return a reusable SQL error. The proxy classifies the failure and applies a configurable policy — retry, forward the error, or terminate the session — with sane defaults (connection loss retries on another replica; timeout and a reusable SQL error are re-run on the primary, whose authoritative result is forwarded). A split read whose replica died gets one attempt on an alternate compatible replica before falling back to the primary. The moment any result rows have been sent to the client, the proxy stops retrying and preserves the original failure path, so the client never sees a half-finished then retried statement.

The result

The effect is read offload where it was previously impossible. A long-running read-only transaction — the kind that dominates reporting and batch workloads — can have its read statements served by replicas, while the transaction itself stays coherent on the primary. The split is bounded to a provably-safe window: read committed, WAL-flushed transactions, and top-level SELECTs without a locking clause. Anything outside that window stays on the primary, so correctness is never gambled.

Compared with the ecosystem: where Pgpool-II and PgBouncer give up on mid-transaction offload, where Vitess pins read-write transactions to the primary, and where Aurora solves the problem only by owning the kernel, Polar Proxy finds a safe middle ground at the proxy layer — split the provably-safe reads, keep everything else on the primary.

Next we will look at performance: how the reader connection pool is organized so that, under high concurrency, choosing and reusing a replica connection does not itself become the bottleneck.