Polar Proxy enables read-only statement offloading within open PostgreSQL transactions
Polar Proxy allows read statements to be routed to replicas even during an active transaction by synchronizing XIDs and using LSN-based waits.
Summary
Traditional PostgreSQL proxies typically route entire transactions to the primary server to ensure data consistency and session integrity. This approach prevents read-only statements within a transaction from utilizing replicas, which limits the ability to scale read workloads. Polar Proxy introduces a middle-ground solution that keeps the transaction pinned to the primary while 'borrowing' replica connections for specific read operations.
To maintain correctness, the proxy ensures that offloaded reads can see the transaction's own uncommitted changes and all previously committed data. This is achieved by importing the transaction's XID list onto the replica and implementing a wait mechanism based on the Log Sequence Number (LSN). This capability is specifically designed for the 'read committed' isolation level, where each statement can safely take a fresh snapshot.
Technical implementation of transaction splitting
- Isolation Level Dependency: The mechanism relies on the 'read committed' isolation level, which allows each statement to take a fresh snapshot, unlike 'repeatable read' or 'serializable' levels that require a static snapshot for the entire transaction.
- Visibility Synchronization: To ensure a read sees its own previous writes within the same transaction, Polar Proxy imports the transaction's XID (Transaction ID) list onto the target replica.
- Consistency via LSN: The proxy uses Log Sequence Number (LSN) waits to ensure the replica has caught up to the primary's state before executing the offloaded read, preventing stale data reads.
- Identity and Authorization: The proxy maintains security by ensuring borrowed replica connections match the original client's authenticated user and database identity, preventing permission mismatches.
Why it matters
This development provides a way to scale PostgreSQL read workloads without requiring proprietary storage engines like Amazon Aurora. It offers database administrators a method to offload heavy read operations from the primary node even when application logic requires complex, multi-statement transactions.
Original reference: postgr.es →