Shard Key

The shard key determines which queries can be answered by touching one partition and which require a scatter-gather across all of them. It also determines where load concentrates, what a transaction can span, and what an operational task like "export this customer" costs.

Everything else about a sharded system — shard count, routing mechanism, rebalancing strategy, tooling — can be changed later with effort. Changing the shard key means rewriting the entire dataset while the product is live, which is why it is treated as a one-way door.

Why even distribution is the least important property

The instinct is to choose the key that spreads rows most evenly. That instinct is wrong, and reliably so. A perfectly distributed key that does not match the dominant access pattern turns every ordinary query into a fan-out across every shard, which is slower than the unsharded system it replaced and far harder to operate.

The properties that matter, in order:

1. The dominant queries are answerable within one partition.
2. It matches a natural isolation boundary — a tenant, a customer, a workspace — so permissions, exports, deletions and migrations are single-shard operations.
3. Partition size is bounded, or can be bounded by composing with a second dimension such as time.
4. Load is acceptably distributed, with a plan for the keys that will inevitably be hot.

Implementation patterns

• Composite keys. (channel_id, time_bucket) or (tenant_id, entity_id) — the first component gives locality, the second bounds partition growth.
• Lookup-based routing rather than modular hashing, so one oversized tenant can be relocated without touching anything else. This is what makes uneven distribution survivable.
• More logical shards than physical nodes — start with hundreds of logical shards over a handful of machines, so growth is a remapping rather than a resharding.
• Explicit hot-key handling for the keys that will exceed one shard, since no key choice prevents skew in the workload itself.

Industry example

A workspace product storing every document as a tree of small block rows faces exactly this choice. Sharding by block id distributes beautifully and makes every single page load a scatter-gather across every shard — the worst possible outcome for the most common operation.

Sharding by workspace is the right answer despite very uneven sizes, because every meaningful query is already workspace-scoped: page loads, search, permission checks, exports, activity feeds. Permissions are workspace-rooted, so authorisation never crosses shards. Per-tenant export, deletion and migration to dedicated infrastructure come for free. And blast radius becomes a set of customers rather than a random slice of every customer.

The uneven distribution — workspaces ranging from one person to tens of thousands — is handled with a routing table rather than a hash, so a single large workspace can be moved alone.

The same reasoning appears in chat platforms partitioning by channel (with a time bucket to bound growth), in commerce platforms partitioning by merchant, and in observability platforms partitioning by customer.

Failure scenarios

• Choosing for distribution over access pattern, producing a system where the common query fans out.
• Unbounded partitions. A key with no time or size dimension eventually produces partitions that cannot be compacted, backed up or scanned efficiently.
• Monotonic keys — sharding by timestamp or auto-increment id sends all current writes to one shard while the rest sit idle.
• A key that is not present in every query. If some queries lack the shard key, they fan out; if many do, the sharding has failed.
• Discovering cross-shard transactions late, after the model assumed they were possible.

Trade-offs

A key optimised for the dominant access pattern accepts fan-out for secondary patterns — which is the correct trade, handled by feeding a separate aggregated store asynchronously for cross-shard features like global search and platform analytics.

A key matching the isolation boundary accepts skew. A key optimised for even distribution accepts query fan-out. There is no key that avoids both, so the decision is which cost you would rather operate — and skew is an operational problem with known remedies, while a key that fights your queries is a problem you can only solve by starting again.

Interview question

"You are sharding a table of a hundred billion rows. Give me three candidate shard keys, tell me which queries become expensive under each, and tell me which choice you could still change in two years."