advanced~5h

System Design for the Persistence Layer — Read Replicas, Sharding & CQRS

A single database instance eventually hits a real ceiling no amount of query tuning can fix — read replicas, sharding, and CQRS are the three deliberate architectural strategies for scaling a persistence layer past that point, each with a genuinely different cost and consistency tradeoff.

Learning objectives

Choose between read replicas, sharding, and CQRS based on whether the actual bottleneck is reads, writes, or divergent access patterns.
Explain replication lag and identify which reads have a strict read-after-write consistency requirement.
Design a shard key that avoids a hot-shard problem for a given access pattern.

Every chapter so far assumed a single database instance could handle your application's full load — this chapter is where that assumption breaks.

📖 Story

Imagine a single librarian trying to serve an entire city — checking out books, restocking shelves, answering questions, all alone. At small scale, fine. At city scale, an obvious bottleneck, no matter how efficient that one librarian becomes.

Here's what that bottleneck looks like for a real e-commerce product catalog: 95% of traffic is READS (browsing products), 5% is writes (price updates, new listings). One overloaded primary database is trying to serve both.

// Reads and writes both hitting the SAME single database instance
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> { }

Here's the read-replica fix — routing read-heavy traffic to a separate instance:

@Configuration
public class RoutingDataSourceConfig {
    // Reads go to a REPLICA, continuously synced from the primary
    @Bean
    @Qualifier("readDataSource")
    public DataSource readReplicaDataSource() { ... }

    // Writes still go to the ONE primary
    @Bean
    @Qualifier("writeDataSource")
    public DataSource primaryDataSource() { ... }
}

Read replica — a secondary database instance continuously replicating a primary, serving read traffic separately. Sharding — splitting a database's data across multiple physical instances based on a shard key. CQRS — architecturally separating the write model from the read model, often using entirely different data stores.

Let's extend this chapter's product-catalog example across all three strategies.

Read replicas — the simplest fix, for THIS chapter's exact problem

Reads route to a replica; writes still go through the one primary. This scales READ throughput (add more replicas as read load grows) but does nothing for write throughput. The tradeoff: replication lag — a read routed to a replica immediately after a related write might not yet reflect it.

Sharding — when writes ALSO become the bottleneck

If this chapter's product catalog grows to millions of sellers each with their own products, sharding by seller_id splits data across multiple independent databases — each shard handles both reads AND writes for its own subset. The cost: a report spanning ALL sellers can no longer be one simple query — it needs to fan out across every shard.

CQRS — when read and write SHAPES genuinely diverge

If product search needs complex, denormalized, full-text-searchable data (best served by Elasticsearch) while the write side needs normalized, transactional integrity (best served by PostgreSQL), CQRS keeps these as two DELIBERATELY separate models, synced via events — often using the outbox/CDC pattern from Chapter 17.

Strategy	Scales reads	Scales writes	Fits this chapter's example when...
Read replicas	Yes	No	Reads are the bottleneck (this chapter's opening 95/5 split)
Sharding	Yes	Yes	Writes ALSO become the bottleneck
CQRS	Yes	Indirectly	Read and write access SHAPES genuinely diverge

Read replicas work via the database's own native replication mechanism (PostgreSQL's streaming replication) — the exact same mechanism from an earlier database course, just applied here as a scaling strategy. Sharding requires either application-level routing logic or a sharding-aware proxy deciding which shard's connection to use based on the shard key. CQRS's read-model synchronization is frequently implemented using exactly the outbox pattern and Debezium from Chapter 17.

This chapter's exact 95%-reads product catalog is the textbook read-replica scaling case.
A large multi-tenant SaaS platform with thousands of tenants commonly shards by tenant ID.
An e-commerce platform with a search/filter-heavy catalog commonly uses CQRS to keep normalized write-side data separate from an Elasticsearch-backed read model.

Start with read replicas as the simplest strategy when reads specifically are the bottleneck, exactly this chapter's opening scenario.
Reach for sharding only once write throughput genuinely can't be served by a single, well-replicated primary.
Be explicit with your team about the resulting consistency model (replication lag) — this is a real tradeoff, not a detail to leave implicit.

⚠️ Why this keeps happening

These three strategies solve architecturally different problems, but are often reached for reflexively without first identifying whether the actual bottleneck is reads, writes, or query shape.

Adopting sharding to solve a read-scaling problem that this chapter's simpler read-replica fix would have solved.
Routing a read to a replica immediately after a related write and being surprised it doesn't reflect it — this is replication lag working exactly as expected, not a bug.
Choosing a shard key without considering actual query patterns, leading to most queries fanning out across every shard anyway.

Read replicas genuinely scale read throughput horizontally with minimal added complexity — the single best first move for this chapter's read-heavy bottleneck. Sharding scales both, but requires careful shard-key selection to avoid a 'hot shard.'

Cross-shard or cross-service (CQRS) data access needs the same access-control consistency as a single-database system — a read-optimized store that doesn't enforce the same row-level authorization as the write-side is a real, easy-to-overlook risk.

Monitor replication lag (for read replicas and CQRS read models alike) as a first-class metric — the direct, measurable proxy for how stale a given read might be.

Document explicitly which specific queries/endpoints route to a replica versus the primary, and which have a strict read-after-write consistency requirement.

Set up a primary/read-replica configuration for this chapter's product catalog, routing reads to the replica, and observe replication lag under a burst of writes.
Design a shard-key strategy for a multi-tenant application, reasoning through which query patterns stay within one shard versus fanning out.
Identify one specific read in a hypothetical application with a strict read-after-write consistency requirement, and explain why it must go to the primary.

✓ Quick recap

Read replicas scale reads only, with low complexity — the natural first move for this chapter's 95%-reads bottleneck.
Sharding scales both reads and writes by splitting data across independent instances, at real query-complexity cost.
CQRS separates the write and read models entirely, letting each be optimized independently.

Want a visual for this concept?

Generate a diagram tailored to “System Design for the Persistence Layer — Read Replicas, Sharding & CQRS” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Debugging & Troubleshooting — LazyInitializationException & Common Production Failures →← Back to all Spring Data JPA & Hibernate Mastery chapters