When read/write splitting routes SELECT statements to read-only nodes, those nodes may lag behind the primary node due to asynchronous replication. PolarDB for PostgreSQL offers two consistency levels — eventual consistency and session consistency (also referred to as transaction-level read consistency) — to control how your application handles this replication gap.
How it works
PolarDB runs in a cluster architecture. Each cluster contains a primary node and one or more read-only nodes. Connect to the cluster using two endpoint types:
Cluster endpoint (recommended): routes traffic across all nodes and enables read/write splitting automatically.
Primary endpoint: routes all traffic to the primary node only.
The built-in proxy layer handles read/write splitting. It parses each SQL statement and routes write operations (INSERT, UPDATE, DELETE, CREATE) to the primary node and read operations (SELECT) to read-only nodes.
Data replication from the primary node to read-only nodes uses asynchronous physical replication based on write-ahead logging (WAL). Under normal load, replication completes within a few milliseconds. Under light load, the synchronization latency can be reduced to less than five seconds. Under heavy load — such as DDL operations on large tables or bulk inserts — synchronization latency can increase significantly.
Because replication is asynchronous, a SELECT routed to a read-only node may return data that hasn't caught up with the latest writes on the primary node. The two consistency levels determine how PolarDB handles this gap.
Consistency levels
| Eventual consistency | Session consistency | |
|---|---|---|
| Read behavior | SELECT is routed to any available read-only node immediately, without waiting for replication. | SELECT is forwarded to a read-only node that has already completed replication for the current session's writes. |
| Stale reads | Possible — the node may not have the latest data. | Not possible within a session — reads always reflect writes from the same session. |
| Performance | Slightly higher throughput; no wait time. | Minimal overhead in most workloads. |
| Best for | Analytics, reporting, and read-heavy workloads where a short replication lag is acceptable. | Transactional workloads where reads must reflect recent writes from the same session. |
Eventual consistency
With eventual consistency, the proxy routes each SELECT to any available read-only node without checking whether that node has the latest data. The node returns results immediately.
This means a read following a write in the same session may return stale data:
INSERT INTO t1(id, price) VALUES(111, 96);
UPDATE t1 SET price = 100 WHERE id=111;
SELECT price FROM t1;
-- May return 96 or NULL if the read-only node hasn't replicated the write yetUse eventual consistency for workloads where a short replication lag is acceptable: scheduled jobs, analytics queries, aggregate reports, or read-only access by users who don't write data.
Session consistency
With session consistency, the proxy ensures that after a write in a session, subsequent reads in the same session are routed only to read-only nodes that have already applied those writes. The SELECT is forwarded to a qualifying node and returns up-to-date results.
The same example behaves predictably:
INSERT INTO t1(id, price) VALUES(111, 96);
UPDATE t1 SET price = 100 WHERE id=111;
SELECT price FROM t1;
-- Returns 100, guaranteedSession consistency adds minimal overhead to most workloads and covers the majority of transactional use cases. Use it as the default for applications where users read their own recent writes.
How session consistency works
The proxy tracks the redo log progress on each node using a log sequence number (LSN). When a write is committed on the primary node, the proxy records the LSN of that write as the session LSN.
When the next read arrives in the same session, the proxy compares the session LSN against the LSN of each read-only node. It forwards the read to a node whose LSN is greater than or equal to the session LSN, guaranteeing that the node has applied the write. While the selected node returns results to the client, other nodes continue replicating in the background.
This mechanism delivers session consistency, read/write splitting, and load balancing simultaneously.
Best practices
Use session consistency for most workloads. It has minimal impact on cluster performance and handles the common case where an application reads data it just wrote.
For queries that must read the absolute latest data — regardless of session boundaries — redirect them to the primary node using the FORCE_MASTER hint:
/*FORCE_MASTER*/ select * from user;Reserve FORCE_MASTER for specific queries rather than using it broadly. Routing all reads to the primary node eliminates the benefit of read/write splitting and increases load on the primary.