Read Write Splitting: Mermaid Flowchart Diagram

About Source

Read-write splitting is a database routing pattern that directs write operations to the primary node and read operations to one or more replica nodes, allowing read traffic to scale independently of write capacity.

This diagram shows the routing logic implemented by a proxy layer — tools like ProxySQL, PgBouncer, or an application-level ORM plugin. The proxy inspects each incoming query. If it is a write (INSERT, UPDATE, DELETE, DDL), the proxy forwards it to the primary. If it is a read (SELECT), the proxy distributes it across available replicas using a load-balancing algorithm such as round-robin or least-connections.

The primary handles all writes and then replicates changes to replicas via the mechanism shown in Primary Replica Sync. Replicas serve read queries using slightly stale data — the degree of staleness depends on replication lag, which is a function of write volume and network latency.

The practical impact is significant: in most OLTP workloads reads outnumber writes 10:1 or higher. By moving reads to replicas you can scale read throughput by simply adding more replica nodes without touching the primary. This is far cheaper than vertical scaling and avoids the complexity of Database Sharding.

There are two important consistency caveats. First, a client that writes a record and immediately reads it may hit a replica that hasn't yet applied the update — a read-your-writes violation. Applications must either route post-write reads to the primary for a brief window or use sticky sessions. Second, transactions must execute entirely on the primary, since replicas are read-only. The Connection Pooling diagram shows how poolers manage the underlying connections to both primary and replica pools.

Frequently asked questions

Read-write splitting is a database routing pattern that directs all write operations (INSERT, UPDATE, DELETE, DDL) to the primary node and all read operations (SELECT) to one or more replica nodes. This allows read throughput to scale horizontally by adding replicas without increasing load on the primary.

A proxy layer (ProxySQL, PgBouncer, or an ORM plugin) inspects each incoming query and determines whether it is a read or write. Writes are forwarded to the primary. Reads are distributed across replicas using a load-balancing algorithm such as round-robin or least-connections. The proxy maintains separate connection pools for primary and replica endpoints.

Implement read-write splitting when your read traffic significantly outpaces your write traffic — typically in OLTP workloads where reads outnumber writes 5:1 or more. It is a cost-effective alternative to sharding when write throughput is not the bottleneck. It is most valuable when you already have replicas set up for high availability and want to make use of their read capacity.

The main consistency risk is read-your-writes violations: a user writes data and then reads from a replica that has not yet applied the update, making the write appear to be lost. Mitigate by routing reads to the primary for a short window after a write, using session-level primary pinning, or tracking replica LSN and only routing reads to replicas that have caught up to the required point.

mermaid

flowchart LR
    App[Application] --> Proxy[DB Proxy / Router]
    Proxy --> QueryType{Query Type?}
    QueryType -->|Write\nINSERT UPDATE DELETE| Primary[(Primary DB)]
    QueryType -->|Read\nSELECT| LoadBalance{Load Balancer}
    LoadBalance --> Replica1[(Replica 1)]
    LoadBalance --> Replica2[(Replica 2)]
    LoadBalance --> Replica3[(Replica 3)]
    Primary --> ReplicationLog[Replication Log]
    ReplicationLog --> Replica1
    ReplicationLog --> Replica2
    ReplicationLog --> Replica3
    Primary --> WriteResponse[Write Response]
    Replica1 --> ReadResponse[Read Response]
    Replica2 --> ReadResponse
    Replica3 --> ReadResponse
    WriteResponse --> App
    ReadResponse --> App