Database Scaling: Replicas, Sharding and CQRS Decisions

What is the right order for scaling a database?

Cheap to expensive: query and index discipline first, then caching, then read replicas, then partitioning — and only when these are measurably exhausted, sharding. The order is not arbitrary; each step buys less operational complexity than the next. Teams that skip the order mistake a query problem for an infrastructure problem and pay for the most expensive solution earliest.

The strategy map: gains and costs

Read replicas: learning to live with lag

Read replicas are the first big win in read-heavy systems; their one real cost is replication lag: a replica can trail the primary by hundreds of milliseconds to seconds. Routing rules are therefore written in business semantics: flows that need read-your-writes (I placed an order → my orders list) read from the primary or a session-pinned replica; freshness-tolerant reads such as catalog and reporting go to replicas. On the Spring side the split is built with read-only transaction markers or an explicit routing layer; no replica takes traffic before its lag metric is on a dashboard.

Partitioning: making the huge table manageable

Tables reaching hundreds of millions of rows (orders, events, logs) are natural partitioning candidates. Split by time or tenant key, the table means smaller indexes, maintenance running per partition, and old data pruned in seconds by dropping partitions. The critical rule: queries must carry the partition key — a keyless query scans every partition and hands the gain back. When the retention policy (including KVKK/GDPR periods) is designed together with the partition scheme, 'deletion' stops being a nightly job and becomes an instant operation.

Sharding: powerful when needed, expensive when premature

Sharding splits data by key across independent databases and is the real answer to write scale; its cost settles permanently into the architecture: cross-shard queries are joined in the application layer, there is no cross-shard transaction guarantee (saga/outbox step in), and changing the shard count (resharding) is a planned migration. Two design decisions determine the pain: key choice — a key that balances traffic and keeps most business queries on one shard (usually customer/tenant) — and the routing layer — central, versionable management of the key-to-shard mapping. A virtual-shard setup from day one (many logical partitions on few physical servers) reduces resharding from data migration to a mapping change.

CQRS: scaling the read, not the dogma

CQRS's value under high traffic is pragmatism, not doctrine: the write model stays normalized and guards business rules; the read side is fed with precomputed views shaped for the screens (denormalized tables, search indexes, cached projections). Feeding happens asynchronously via change events — which means an eventual-consistency window, and the user experience is designed together with that window. The warning is equally clear: applying CQRS to every CRUD screen is complexity waste; the pattern earns its cost where read shapes genuinely diverge from the write model (dashboards, listings, search).

Business impact: data-layer decisions are the costliest to reverse

Application code can be rewritten; a wrong shard key is migrated with live data. The data-layer strategy must therefore advance with growth projections and measurement: a written ladder of which metric crossing which threshold triggers which step. Its commercial value is predictability: capacity growth becomes a plan instead of a crisis, the infrastructure budget stays proportional to growth, and in technical due diligence the "what is your scaling plan?" question has a documented answer.

Frequently asked questions

How is replication lag kept away from users?

By pinning critical post-write reads to the primary, session-level 'at least this fresh' rules, and health checks that pull a replica out of traffic when lag grows.

Are partitioning and sharding the same?

No: partitioning splits a table inside one database; sharding distributes data across separate databases. The first is for maintenance and table size, the second for write scale and total capacity.

Does moving to NoSQL remove the need for sharding?

Distributed NoSQL engines manage partitioning natively; but key design, hot partitions and cross-partition query cost come with you. The problem is solved by access-pattern design, not engine choice.

Why are connection pools critical to scaling?

The database's concurrent transaction capacity is finite; uncontrolled application pool growth turns that capacity into contention. Pool sizes are budgeted in aggregate against DB capacity; an intermediate pooling layer is used when needed.

Data-layer scaling checklist

1. No step up before slow-query/index discipline is exhausted

2. Read routing rules written in business semantics; lag on a dashboard

3. Partition scheme and retention policy designed together for huge tables

4. Shard key candidate and virtual-shard plan documented

5. Saga/outbox readiness for cross-shard operations

6. CQRS only where read shapes diverge; consistency window aligned with UX

7. The scaling ladder (metric → threshold → step) written and reviewed

SSH Yazılım builds your data-layer scaling strategy on measurement — from replica setup to shard design. Let us write your growth ladder together.