Read-Write Split Pattern

Route reads vs writes.

Overview

The read-write split pattern routes write queries to the primary database and read queries to one of N replicas. The architecture is simple; the implementation is where it gets tricky. Per-query classification (is this a read or a write?) must be correct, read-after-write must fall back to the primary for the affected user, replica lag must inform routing, and the connection pool must understand the difference between read and write connections.

• Route reads vs writes. Per-query routing decision; writes go to primary, reads go to replica unless consistency demands otherwise.
• Per-query classification. Per-query the read-or-write classification; SELECT is not always a read (SELECT FOR UPDATE writes), and the router must know the difference.
• Read-after-write fallback. Per-write the user’s subsequent reads route to primary briefly; preserves the "user sees their own write" guarantee.
• Per-replica routing plus connection-pool awareness. Per-query the right replica based on lag; the connection pool maintains separate pools for read and write connections to avoid mixing.

The approach

The practical approach is to put the routing at the application layer (proxies like ProxySQL work but add a hop), classify queries explicitly rather than parsing SQL on the fly, implement read-after-write fallback for any user-affecting write, monitor per-route correctness so misrouting surfaces in metrics, and document the per-database routing strategy committed to the repo.

• Per-query routing. Application-layer routing decisions; the explicit choice beats SQL-parsing heuristics that misclassify SELECT FOR UPDATE.
• Read-after-write fallback. Per-write user’s subsequent reads route to primary; the fallback window matches the typical replication lag.
• Monitor routing correctness. Per-query routing audited via metrics; misrouting surfaces in observability before it surfaces in user reports.
• Per-replica routing plus documented strategy. Per-query the right replica based on lag and load; per-database routing strategy committed to the repo.

Why this compounds

Read-write split discipline compounds across services. Each correctly-routed query offloads the primary; each correctly-monitored route catches misclassification; the team builds a vocabulary for routing decisions that pays off on every new feature. The opposite, where routing is implicit and unverified, accumulates correctness bugs that surface as flaky user-reported issues months later.

• Read scale. Right read offload supports throughput; the primary serves writes and the replica tier serves the bulk of reads.
• Correctness. Right routing matches consistency; users see their own writes; the read replicas serve the rest.
• Operational fit. Right routing matches workload; the database tier shape matches the read-to-write ratio of the application.
• Institutional knowledge. Each routing decision teaches consistency patterns; the team builds intuition for when read-after-write actually matters.

Read-write split discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces routing patterns, and supports the team’s read-routing discipline.