Read/Write Separation and CQRS Pattern

Two patterns under one question

Read/write separation is tactical: same database, multiple replicas, route writes to primary, reads to replicas. Same schema, no domain change.

CQRS is architectural: separate write model (commands, normalized, transactional) from read model (queries, denormalized projections), often in different stores. Optimizes each side for its access pattern.

Read/write split — Spring implementation

AbstractRoutingDataSource picks based on TransactionSynchronizationManager.isCurrentTransactionReadOnly(). LazyConnectionDataSourceProxy is critical — without it, Spring acquires the connection before knowing readOnly, defeating routing.

Usage: @Transactional(readOnly=true) routes to replica; @Transactional routes to primary.

For multiple replicas: round-robin selector wrapping N replicas.

Read-after-write — the trap

User creates order on primary, immediately reads order list from replica that hasn't replicated yet, sees nothing. Solutions:

• ThreadLocal flag: write sets it, subsequent reads check it (single-instance only)
• Sticky session to primary: cookie/flag for N seconds after write (works across instances if session shared)
• Version tokens (LSN-based): write returns LSN; reads pass it; replica blocks until caught up. AWS Aurora has this built in
• Synchronous/semi-sync replication: wait for at least one replica before ack
• Always read 'my-data' endpoints from primary

Pragmatic: sticky-to-primary for 5-30s + primary for 'my-data' endpoints.

When simple split is enough

Read:write > 5:1, reads use same schema as writes, eventual consistency OK with mitigations, single relational DB is the right model. Solves 80% of read-scaling with 5% of the complexity.

Don't go to CQRS yet.

When you need CQRS

• Read shapes radically differ from write shapes (write = ledger entries, read = account balance)
• Multiple specialized read views (dashboard, search, mobile, analytics) from same data
• Read scale dwarfs writes; need different tech (Elasticsearch, ClickHouse)
• Paired with Event Sourcing
• Natural read/write asymmetry (cart writes simple, catalog reads complex)

Don't use CQRS for simple CRUD with simple reads, when strong consistency is required on every read, or when the team is unfamiliar.

CQRS architecture

• Command side: API for state changes. Validates, applies business rules, persists, emits events via outbox
• Query side: API for reads. Hits denormalized read stores optimized per query shape
• Projections: subscribe to events, build read stores. Each projection independent and rebuildable from event log

Why powerful:

• Each read model optimized for one query shape — no awkward joins
• New view = new projection; no schema change to write side
• Specialized stores per projection (ES for search, ClickHouse for analytics, Redis for hot)
• Write model stays clean and transactional

Cost: eventual consistency — projections lag writes by ms-to-seconds.

Building projections

Write side emits events via outbox (event to DB in same tx as state change; worker publishes to Kafka). Avoids dual-write race.

Projection consumer: @KafkaListener; idempotent upsert (ON CONFLICT DO UPDATE); track last processed offset per projection.

Rebuild from scratch: drop read store, reset offset to 0, replay all events. Fixes projection bugs without data loss — the superpower.

Multiple projections of same topic: each is its own consumer group, reads independently.

Failure modes

Read/write split:

• Replica lag → read-after-write issues; monitor and fall back to primary if > threshold
• Replica down → router excludes; capacity-test for all-replicas-down scenario

CQRS:

• Projection lag → alert; users see stale
• Projection bug → drop read store, replay events with fixed handler
• Event schema change → upcasters or versioned types; never edit history
• Hot key on read store → Redis cache in front
• Read store outage → fall back to write model with circuit breaker

Recommendation

Default to simple read/write separation — Spring AbstractRoutingDataSource + LazyConnectionDataSourceProxy + @Transactional(readOnly=true), with sticky-to-primary or LSN tokens for read-after-write consistency. Apply CQRS only to bounded contexts that genuinely benefit — order workflows, analytics dashboards, search-heavy products. Pair CQRS with Event Sourcing and the outbox pattern. Don't apply CQRS everywhere; the rebuild-from-events superpower is wasted on settings pages and basic CRUD.