Mastering Database Read‑Write Splitting: Techniques, Use‑Cases, and Top Tools

1. Read‑Write Splitting Overview

Read‑write splitting separates database read operations from write operations to alleviate performance bottlenecks. Originating from the rapid growth of the Internet, it has become a standard optimization technique.

Improved performance : Distributes load across multiple nodes, allowing more resources to handle traffic.

Higher stability : Isolates heavy read queries from write workloads, preventing read‑induced CPU spikes from affecting critical writes.

Better resource utilization : Leverages read‑only replicas that would otherwise be idle.

Increased availability : Adds nodes and load‑balancing to avoid single‑point failures.

Higher access efficiency : Reduces lock contention by handling reads on separate servers.

Greater optimization space : Enables specialized tuning for read‑only resources.

2. Technical Implementation

The most common architecture follows a master‑slave (primary‑replica) model with an intermediate data‑access proxy that decides whether a request should be routed to the primary (write) or a replica (read).

Using a proxy layer requires no changes to application code, but introduces an extra middleware that can become a performance bottleneck and adds maintenance overhead. An alternative is to embed the proxy logic in the application via an SDK, which reduces latency but ties the solution to specific programming languages.

Key Techniques for Determining Read vs. Write

Port‑based routing : Different network ports map to primary or replica servers. Simple but offers little abstraction for developers.

SQL pattern matching : Regular‑expression rules in the proxy classify statements as read or write without modifying the application.

SQL hints : Applications embed hints in queries; the proxy parses these hints to direct traffic.

Full syntax parsing : The proxy parses the SQL AST to accurately identify operation type, enabling automatic routing while handling complex cases such as stored procedures or SELECT FOR UPDATE.

Handling Transactions

Two common strategies exist: (1) send the entire transaction to the primary, ensuring consistency; (2) analyze the transaction sequence, sending only read‑only statements to replicas while keeping write‑related statements on the primary.

Mitigating Replication Lag

Force all traffic to the primary when lag exceeds a configurable threshold.

Round‑robin or retry reads on alternative replicas when the preferred replica is stale.

Introduce a caching layer that serves reads from cache and falls back to the database on miss.

Optimize the primary to reduce lag (e.g., smaller transactions, index tuning).

Flexible Load‑Balancing Strategies

When multiple replicas exist, load can be distributed by random selection, round‑robin, weighted distribution, or QoS‑aware algorithms that consider each replica’s capacity.

Ensuring Read Consistency

Session‑affinity (sticky sessions) can route all requests of a user session to the same replica, reducing visible data staleness.

Topology Awareness

Read‑write splitters must detect changes such as primary failover or added replicas, either through active probing or by integrating with high‑availability components that push topology updates.

3. Best‑Practice Recommendations

Read‑write splitting is one of several database scaling strategies. Depending on business needs, consider the following hierarchy of optimizations:

Business‑layer vertical split : Separate services by domain, requiring the highest investment but yielding the greatest performance gains.

Architecture‑layer cache/search : Add caching or search services to offload read traffic.

Access‑layer read‑write splitting : Quick, low‑cost improvement for read‑heavy workloads.

Access‑layer sharding (horizontal partitioning) : Distribute data across multiple databases; higher complexity and compatibility requirements.

Database‑layer vertical/horizontal scaling : Upgrade hardware or add more nodes; effective but limited by cost and downtime.

4. Typical Products

MySQL‑Proxy

Official MySQL middleware that parses queries and routes them based on configured rules.

# ./mysql-proxy --daemon --log-level=debug --user=mysql \
    --keepalive --log-file=/var/log/mysql-proxy.log \
    --plugins="proxy" \
    --proxy-backend-addresses="192.168.1.5:3306" \
    --proxy-read-only-backend-addresses="192.168.1.6:3306" \
    --proxy-lua-script="/root/soft/mysql-proxy/rw-splitting.lua" \
    --plugins=admin --admin-username="admin" --admin-password="admin" \
    --admin-lua-script="/root/soft/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

Apache ShardingSphere

Open‑source database middleware offering dynamic read‑write splitting, automatic topology detection, multi‑database support, driver and proxy modes, syntax‑based routing, hint‑based routing, circuit‑breaker, hot‑config reload, and various load‑balancing algorithms.

MyCAT

Open‑source middleware where read‑write policies are defined in configuration files. Example options include:

balance 0 – no splitting, all reads go to the current write host.

balance 1 – both read and standby write hosts participate in SELECT load‑balancing.

balance 2 – reads are randomly distributed between writeHost and readHost.

balance 3 – reads are randomly sent only to readHost.

Write‑mode (writeType) and switch‑mode (switchType) parameters further control routing behavior.

Alibaba Cloud RDS Proxy (PostgreSQL example)

Transparent proxy service that provides read‑write splitting, connection pooling, end‑to‑end encryption, and fail‑over protection. Routing can be driven by SQL hints, read‑only mode flags, or custom policies, and health checks automatically reroute traffic when a replica exceeds latency thresholds.

OceanBase

Built‑in read‑write splitting via OBProxy. Supports two consistency levels:

Strong consistency – reads are routed to the primary to obtain the latest data.

Weak consistency – reads are routed to a replica without guaranteeing freshness.

Weak consistency can be enabled by adding a SQL hint to the query.

KunlunBase

Distributed relational database that implements read‑write splitting in its remote query optimizer. Conditions for splitting include:

SQL type is SELECT.

No user‑defined functions unless the transaction is read‑only.

Autocommit mode is on (no explicit transaction) or the transaction is read‑only.

When multiple replicas exist, routing decisions consider replica weight (ro_weight), network latency (ping), and replication lag (latency).