Database sharding: when it is genuinely necessary and what it takes away

01Exhaust vertical scaling first, honestly

A single modern Postgres or MySQL instance on decent hardware will handle tens of thousands of writes per second and several terabytes of data before it becomes genuinely awkward. Most teams that declare themselves at that limit are actually limited by a missing index, an N+1 query pattern, or a write path that opens transactions around network calls.

Read load is almost never a reason to shard, because read replicas solve it more cheaply and without changing your programming model. The honest triggers are sustained write throughput above what one primary can absorb, a working set that no longer fits in memory on the largest instance you can buy, or a dataset whose sheer size makes maintenance operations - vacuum, index rebuilds, backups, restores - take longer than your recovery objectives allow.

• Profile the top ten queries by total time before concluding the instance is saturated.
• Move analytics and reporting off the primary; they distort every capacity conclusion.
• Check whether restore time, not query time, is what actually forces the decision.
• Consider partitioning within one instance - it solves the maintenance problem without distributing it.

02What the shard key decides for you

The shard key determines which rows live together, and rows that live together can be queried, joined and transacted over cheaply. Everything else becomes a scatter-gather across the fleet or an application-level join. This is a data modelling decision disguised as an infrastructure decision, and it constrains your product roadmap for years.

The property that matters most is that your highest-volume queries can be answered from a single shard. If your dominant access pattern is 'everything for one tenant' or 'everything for one user', that entity is your key. If your dominant access pattern is genuinely global - leaderboards, cross-tenant search, time-ordered feeds across all users - then no key makes those queries cheap, and you need a separate system for them rather than a cleverer shard key.

03Hash versus range, and why hash usually wins

Range sharding assigns contiguous key spans to shards, which keeps range scans local and makes rebalancing conceptually simple. Its failure mode is that real data is rarely uniform: sequential IDs and timestamps put every new write on the last shard, so you have bought distribution and got a queue.

Hash sharding distributes the key space evenly and eliminates the hotspot, at the cost of destroying locality - a range scan now touches every shard. Use consistent hashing with virtual nodes rather than a plain modulo of the shard count. Plain modulo means adding a shard remaps almost every key, which turns capacity expansion into a full data migration; consistent hashing moves roughly one shard's worth of data instead.

• Never use `hash(key) % shard_count` - it makes growth a rewrite.
• Allocate virtual nodes generously up front; splitting them later is cheap.
• Store the shard mapping in a lookup service, not in application constants.
• Route through a directory layer so you can move a tenant without a deploy.

04What you lose on day one

Cross-shard joins stop existing. Queries that touched two entities now require two round trips and application-side assembly, and the result is no longer a consistent snapshot. Foreign keys across shards cannot be enforced by the database, so referential integrity becomes your code's problem and will be violated the first time a delete path is incomplete.

Transactions are the harder loss. A single ACID transaction spanning two shards requires two-phase commit, which introduces a coordinator that can fail while participants hold locks. Most teams correctly refuse to pay that price and instead redesign the operation as a saga with compensating actions - which is a real design, not a workaround, but it means every multi-entity operation becomes eventually consistent and needs an explicit failure story.

05Operational costs nobody quotes in the design doc

Schema migrations become fleet-wide operations. A change that took one lock now takes thirty-two, applied in sequence with partial-failure states in between, and your migration tooling must be idempotent and resumable per shard. Backups multiply, and restoring to a consistent point in time across shards is a genuinely hard problem because the shards' write-ahead logs are independent.

Monitoring changes shape too. Aggregate metrics hide the shard that is dying: mean query latency across the fleet looks healthy while one shard sits at 4 seconds. Every dashboard needs a per-shard breakdown, and every alert needs to fire on the worst shard rather than the average. Teams routinely discover this during the first hot-shard incident rather than before it.

06Resharding is the part you must design first

Every shard layout is eventually wrong: a tenant grows, traffic shifts, a shard fills. If you have not designed the resharding path before you shard, you will be designing it under pressure while a shard is at 95% disk. The mechanism worth building is a per-shard read-only mode, a copy-and-verify pipeline, and a routing layer that can be flipped per key range.

Logical replication makes this tractable: replicate the range you are moving to the new shard, let it catch up, briefly block writes for that range only, verify row counts and checksums, flip the routing entry, then delete the source rows after a retention window. The blocking window is seconds and affects one tenant rather than the fleet. Rehearse it on a low-traffic tenant before you need it on your largest one.

• Build per-key-range read-only mode before you need it.
• Verify with checksums, not row counts alone - silent truncation is real.
• Keep the source data for a retention window so rollback is possible.
• Rehearse a move quarterly; an untested procedure is not a procedure.

07The alternatives that usually suffice

Table partitioning within a single instance solves the maintenance and vacuum problems - the ones that actually force many teams' hands - without distributing anything. Functional separation, where you move a few high-write tables onto their own instance, buys substantial headroom for a fraction of the complexity, and it is reversible in a way sharding is not.

If your driver is one enormous tenant, extract that tenant onto dedicated infrastructure rather than sharding everyone. You get the isolation benefit immediately, the other 99% of customers keep a simple single-database model, and the routing layer you build for that one extraction is the same routing layer you would need later if you do eventually shard properly.