📈Scaling databases

Bigger box first, then split carefully

Vertical scale and a sane schema buy time cheaply. Horizontal scale adds replicas, caches, partitions, and eventually shards, each step changes freshness, routing, and operational load. SQL semantics do not get simpler when rows move across nodes; applications inherit locality and idempotency work.

SQL

Read scaling: route SELECT to replica (hint comment)

/* @readonly */ SELECT now(), pg_is_in_recovery();

✓Illustrative result set

now	pg_is_in_recovery
2026-04-12 10:00:00+00	true

SQL

Write scaling: shard key in query (application adds)

SELECT * FROM orders_shard_2 WHERE customer_id = 441;

✓Illustrative result set

order_id
9001
9002

🧭Technique ladder

Complexity vs consistency cost.

Technique	Complexity
Read replicas	Low, watch replication lag and routing rules.
Caches (Redis, CDN)	Medium, invalidation, thundering herds, stampede control.
Sharding	High, cross-shard joins, resharding, distributed invariants.

Shard day one

Sharding before working set exceeds RAM or before hot paths are proven.
Ignoring global secondary index write amplification on distributed SQL.
Cross-shard transactions without sagas, idempotency, or clear failure modes.

Evidence then router

Measure CPU, IO, and connection limits; add replicas and caches before shard math.
Centralize routing keys in one library; plan backfills and resharding early.
Pair caches with TTLs and explicit invalidation, stale reads are a product feature or a bug.

Connection storms

Scale-out fails loudly when every pod opens its own firehose, proxies and poolers are part of the architecture, not an afterthought.

Lag

Replication lag and cache TTL dominate user-visible correctness more often than raw query milliseconds.

Writes

Idempotent writes and clear retry boundaries matter more at scale than clever SQL, assume partial failures once traffic crosses regions or shards.

TL;DR

Scaling SQL usually means vertical tuning first (indexes, queries, pools), then horizontal read replicas, caching, partitioning/sharding, and CQRS-style read models. Each step changes consistency and operational complexity, there is no single “scale flag.” Design shard keys and hot-row patterns up front; resharding later is expensive.