Shard-key selection: hash, range, list, and directory strategies

The four properties of a good shard key

If you get the shard key wrong, no amount of hardware or tuning can save you — the damage is baked into the schema for years. What does “right” look like?

A shard key must satisfy all four of these simultaneously:

1. Selectivity: 95%+ of your queries filter by it. Queries without the shard key must fan out to every shard — expensive and slow.
2. Uniform distribution: each shard receives roughly equal data and traffic. Uneven keys produce hot shards.
3. Stable: the value never changes for a given row. Changing a shard key would require moving the row to a different shard — a problem you cannot solve at update time.
4. Available at routing time: every API request that touches this data must carry the shard key. If requests arrive without it, you cannot route them without a cross-shard scan.

For multi-tenant B2B SaaS, tenant_id almost always satisfies all four. For B2C, user_id. For geo-partitioned systems, region. Designs that do not have a column meeting all four properties are red flags — split the data model until one emerges. Together these four properties form a filter: when you see a candidate column pass all four, you have found your key; if any one fails, keep looking.

The four strategies

Hash sharding is the default for most OLTP systems because it distributes evenly when keys are uniformly distributed. The problem is that real-world tenants are power-law distributed: one customer is 100–1000× the median. Hash still routes that customer to a single shard — a hot shard.

Range sharding is natural for time-series data (shard by month) but creates a hot tail by default: all new writes go to the latest shard while older shards are idle. Production time-series systems typically combine range (by time bucket) with hash (within the bucket) to spread write load.

Directory-based sharding is the most flexible: a lookup table maps tenant_id → connection_string. Moving a tenant means updating one row. This is the foundation of Citus’s tenant isolation primitive and of app-level routing in multi-tenant SaaS. The cost is that the lookup table becomes critical-tier-1 infrastructure — it must be highly available, low-latency, and cached aggressively.

Consistent hashing is a hash variant used mostly in caches (Memcached, Redis Cluster) where adding or removing a shard moves only K/N keys instead of most of them. Some Postgres sharding setups use directory-based consistent hashing for online shard rebalancing.

Each strategy trades one failure mode for another. Hash buys even distribution but loses range friendliness; range buys time-series convenience but creates a hot tail; directory buys per-tenant flexibility but makes the lookup table tier-1 infrastructure. When you see a time-series write pattern with an auto-increment key, that is the “hot tail” warning sign — reach for a composite approach.

The shard-key contract in practice

The key must be in the primary key of every sharded table. If orders is sharded by tenant_id, then (tenant_id, order_id) is the primary key — not just order_id. This guarantees uniqueness across shards and forces every query path to carry tenant_id.