Read Replicas & Read/Write Splitting Mechanisms

In typical web applications, query workloads are heavily asymmetrical—often displaying an 80:20 or 95:5 read-to-write ratio. A single primary database can quickly become saturated if forced to handle both write mutations and heavy analytical or listing queries.

Read/Write splitting decouples query execution across specialized nodes:

• Write Operations (INSERT, UPDATE, DELETE, ALTER, SELECT ... FOR UPDATE): Routed strictly to the single read-write Primary Database.
• Read Operations (SELECT): Distributed across a horizontally scalable pool of Read Replicas via load balancing algorithms such as Round-Robin, Weighted Response Time, or Least Connections.

There are two primary architectural patterns to implement splitting:

1. Application-Level Routing (Dual DataSources): The application maintains two separate connection pools (e.g., primaryDataSource and replicaDataSource). Middleware or ORMs (like Spring's AbstractRoutingDataSource or TypeORM replication configuration) inspect the transaction context (@Transactional(readOnly = true)) to pick the corresponding pool.
2. Database Proxy Routing: The application connects to a single proxy port (e.g., ProxySQL for MySQL, Pgpool-II or PgBouncer for PostgreSQL). The proxy parses the raw SQL AST at wire speed and routes the statement transparently without application changes.