Database Sharding

Sharding is splitting one logical database across multiple physical nodes, with each node holding a subset of the data. It's a flavour of horizontal partitioning — different rows live on different machines.

Not the same as:

• Replication: every node has the same data; goal is availability/read scale.
• Vertical partitioning: different columns on different stores (e.g. blob storage for images, Postgres for metadata).
• Caching: a fast layer in front of one database; doesn't change the database's footprint.

The goal of sharding is storage + write throughput beyond what one machine can offer. If your bottleneck is read load, replication is usually the easier answer.

Sharding is operationally expensive. Reach for it only when one of these is true:

• A single node can't store the working set (you're at 80% of disk, and the trajectory is up).
• Single-node write throughput is the bottleneck and you've already optimised the obvious (indexes, batch writes, vacuum).
• The DB is regularly CPU-bound on writes and there's no upgrade path.

Things that are not good reasons to shard: read load (use replicas), occasional slow queries (use indexes), "we might grow someday" (premature). Modern hardware lets a single Postgres or MySQL instance handle a frankly massive workload — most apps that think they need sharding need indexes.

The shard key is the column the system uses to decide which shard each row lives on. It's the single most important design decision; getting it wrong is painful to undo.

Good shard keys:

• High cardinality: many distinct values (so data spreads evenly).
• Tied to access patterns: most queries already filter by it (so queries hit one shard, not all).
• Append-friendly: doesn't cause hot spots on inserts.

Typical choices: user_id, tenant_id, account_id. The pattern: the natural "ownership" boundary of your data is usually the right shard key.

Bad shard keys: created_at (everyone hits the newest shard), country_code (a few countries hold most users), low-cardinality enums. These create hot spots — most shards bored, one shard on fire.

Three common ways to map a shard key to a shard:

• Hash-based (hash(user_id) % N): even distribution, no range queries. Adding/removing shards requires rehashing — painful unless you use consistent hashing.
• Range-based (user_id 1–10000 → shard A, 10001–20000 → shard B): supports range queries, easy to add shards at the end. Risk: hot spots if recent IDs are far more active.
• Directory-based: a lookup table maps key → shard. Maximum flexibility, can move individual users between shards. Downside: every query needs a directory lookup, and the directory is now a critical dependency.

Most greenfield systems use consistent hashing (jump hash, rendezvous hash) to get the even distribution of hash without the rehash pain on resize.

Clients ask the router; the router picks the shard.

Once your data is split, any operation that doesn't filter by the shard key has to fan out to every shard, gather results, and merge. This kills the main selling point of sharding (you no longer have one slow shard, you have N slow shards).

Problematic operations:

• SELECT count(*) FROM orders WHERE status = 'shipped'
• ORDER BY created_at LIMIT 10 across all users
• Joins where the two tables shard on different keys
• Distinct counts, aggregations, percentiles

Mitigations: maintain denormalised summary tables for cross-shard aggregations (often updated via change streams), push heavy analytics to a separate data warehouse, accept that some queries will be slow and design UX around it (e.g. show "~10,000" instead of an exact count).

Eventually you need to add or remove shards. The naive approach (hash % N) requires moving most of your data — every existing row's destination changes.

Production patterns to avoid this:

• Consistent hashing: only ~1/N of keys move when you add/remove a node.
• Virtual shards: split logically into many more shards than physical nodes (say 1024 virtual shards across 8 nodes). When you add nodes, move whole virtual shards. The number of moving rows is small and predictable.
• Live migration: dual-write to old and new shard, backfill historical data, switch reads, retire the old shard. Multi-week project. There are no shortcuts.

If your DB layer (Vitess, Citus, Cassandra) handles this for you, life is easier. If you rolled your own sharding, this work is on you.

Heuristics to save yourself pain:

• Tune first. A CREATE INDEX often gives 100x what sharding would. Vacuum your tables. Run EXPLAIN ANALYZE.
• Vertical scale. A modern AWS instance maxes out at 24 TB RAM / 192 vCPU / dozens of TB of NVMe. A single Postgres can serve tens of thousands of QPS. That's a lot of headroom.
• Replicate reads. If reads are 95% of your load, one primary + several read replicas usually solves the problem.
• Use a managed sharded DB. CockroachDB, Spanner, Yugabyte do the sharding internally so you keep a SQL interface.

Rule of thumb: if you're sharding before you've hit the limits of one properly-tuned node on modern hardware, you're shaving years off your engineering team's life for marginal benefit.