System Design · Core

Database Scaling

Grow the data tier without hand-waving: vertical vs horizontal, read replicas, sharding, indexes, and when SQL vs NoSQL actually fits.

1. Start from the bottleneck

Before proposing shards, ask: is the pain read QPS, write QPS, storage size, or query shape (joins, analytics)? Each answer points to a different tool.

Read QPS high → replicas + cache Write QPS high → stronger primary, then shard / CQRS Disk full → archive, partition, shard Complex queries → keep SQL longer; add warehouse for analytics

2. Vertical scaling

Bigger instance: more CPU, RAM (buffer pool), and faster disks (NVMe). Cheap in engineering time. Ceiling is real (largest SKU, single AZ blast radius). Always do this first for a young product.

3. Read replicas

App writes ----------> Primary App reads --> Replica1 / Replica2 / Replica3 (async replication lag: ms to seconds)

Scale read traffic almost linearly with replica count (until primary ship bandwidth saturates).
Failover: promote a replica if primary dies (automation + fencing matter).
Stale reads: after write, read-your-writes via primary or sticky session.

4. Horizontal scaling — sharding

Split rows across multiple primaries. Each shard owns a key range or hash bucket. Application or a proxy routes queries.

Method	How	Pros	Cons
Range	user_id 0–1M → shard A	Range scans easy	Hotspots on recent IDs
Hash	hash(user_id) % N	Even spread	Reshard pain; no range locality
Directory	Lookup table key→shard	Flexible moves	Lookup latency + HA for directory

Shard key selection

High cardinality, present on almost every query.
Avoid keys that create celebrity hotspots (one viral creator_id).
Co-locate data that is always accessed together (orders + order_lines by order_id).

Hard parts

Cross-shard joins → denormalize or app-side join.
Global unique constraints → allocate IDs centrally or use UUIDs.
Resharding → consistent hashing, dual writes, or online migration tools.

5. Indexes (do not skip this)

An index is a side structure (usually B-tree) that turns a full scan into a seek. Wrong indexes waste write I/O and RAM; missing indexes kill p99.

SELECT * FROM orders WHERE user_id=? AND created_at > ? → composite index (user_id, created_at) Write cost: each INSERT/UPDATE maintains every index on the table

Index columns used in WHERE / JOIN / ORDER BY.
Leftmost prefix rule for composite indexes.
Covering indexes avoid heap lookups for hot read paths.

6. Partitioning vs sharding

Partitioning often means splitting one logical table inside one database (by date) for maintenance and pruning. Sharding means separate database instances. Interviews mix the words — clarify which you mean.

7. SQL vs NoSQL — when

Choose SQL when	Choose NoSQL when
Multi-row transactions, rich joins, mature tooling	Simple key access, huge write scale, flexible documents
Strong consistency for money / inventory	Tunable consistency, multi-region AP (with care)
Ad-hoc admin queries matter	Access patterns are known and fixed

Modern answer: many systems are polyglot — Postgres for source of truth, Redis for cache, object store for blobs, warehouse for analytics. Do not pick NoSQL only because it “scales”; SQL with replicas and careful sharding scales very far.

8. CQRS and read models

Commands (writes) → Write DB (normalized) | async project v Queries (reads) → Read DB / search / cache (denormalized)

Useful when read shapes differ wildly from write schema (feeds, search). Accept eventual consistency on the read side.

9. Interview sequence

Single primary + indexes + connection pooling.
Replicas for reads; cache for hottest keys.
Archive cold data; partition large tables by time.
Shard only with a clear key and migration plan.

Quick revision

Name the bottleneck: read, write, size, or query shape.
Vertical first; then replicas for reads.
Shard with range/hash/directory; shard key is the design.
Indexes are part of scaling — measure EXPLAIN plans.
SQL for transactions/joins; NoSQL for simple high-scale access patterns.
CQRS when read models diverge from write schema.
Cross-shard TX and joins are the tax you pay for sharding.