PolarDB for PostgreSQL (Compatible with Oracle) provides two consistency levels — session consistency and eventual consistency — to address read/write splitting scenarios where replication delay can cause stale reads. Consistency here refers to the C in ACID (atomicity, consistency, isolation, durability).
How it works
PolarDB replicates data from the primary node to read-only nodes by asynchronously transferring write-ahead logs (WALs). PolarProxy handles read/write splitting: it routes write operations (INSERT, UPDATE, DELETE, CREATE) to the primary node and SELECT statements to read-only nodes.
Replication is asynchronous, so read-only nodes may briefly serve data that hasn't caught up with the primary. The delay is typically a few milliseconds but increases under heavy write loads — for example, during DDL operations that add columns to large tables or bulk data inserts. This delay is the root cause of data inconsistency.
PolarProxy tracks the redo log applied on each node and records each log sequence number (LSN). When the primary node processes a write, PolarProxy records that write's LSN as the session LSN. For subsequent reads in the same session, PolarProxy compares the session LSN against the LSN on each available node and routes the request to a node whose LSN is greater than or equal to the session LSN.
To keep replication efficient, PolarDB continues replicating data to other read-only nodes while the selected node returns results to the client. By the time the next read arrives, those nodes are already up to date. In most workloads, reads far outnumber writes, so this approach sustains session consistency alongside read/write splitting and load balancing.
Levels at a glance
| Level | Behavior | Use when |
|---|---|---|
| Session consistency | Reads within a session always reflect that session's own writes. PolarProxy waits for a read-only node whose LSN is current enough before routing the request. | Most application scenarios — balances consistency with minimal performance overhead. |
| Eventual consistency | Reads may return data from any point before the last committed write. Different SELECTs in the same session may return different results depending on replication delay. | Read-heavy workloads where offloading reads to read-only nodes matters more than read-after-write guarantees. |
Session consistency
Session consistency (also called causal consistency) ensures that data written before a read request in a session is always visible in the response. Given the following sequence:
INSERT INTO t1(id, price) VALUES(111, 96);
UPDATE t1 SET price = 100 WHERE id=111;
SELECT price FROM t1;The SELECT always returns 100 within the same session, regardless of replication delay on other nodes.
Session consistency puts more routing pressure on the cluster than eventual consistency, but the overhead is minimal. It covers the consistency requirements of most application scenarios and is the recommended default.
Eventual consistency
Under eventual consistency, each SELECT is routed to whichever read-only node is available, regardless of whether it has applied the latest WAL. Given the same sequence:
INSERT INTO t1(id, price) VALUES(111, 96);
UPDATE t1 SET price = 100 WHERE id=111;
SELECT price FROM t1;The SELECT may return 96 or 100 depending on how much replication delay exists at the time of the query. Repeated reads in the same session can return different values.
Use eventual consistency when reducing load on the primary node is the priority and your application can tolerate brief read-after-write discrepancies.
Best practices
Use session consistency for most scenarios. It meets the consistency requirements of the majority of applications with minimal impact on cluster performance.
For strict cross-session consistency, use hints to force specific queries to the primary node:
/*FORCE_MASTER*/ select * from user;Hints take the highest routing priority and override both consistency levels and transaction splitting. Evaluate the impact on your workload before using them. Hints cannot contain statements that change environment variables — for example, /*FORCE_SLAVE*/ set names utf8; causes errors.
What's next
To change the consistency level for your cluster, see Configure PolarProxy.