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

Two teams both shard their Postgres by customer_id. One uses hash sharding; six months later they have no hot shards. The other uses range sharding with the same key; they have a permanent hot tail on the shard holding the highest-ID customers because new signups all go there. Same column, different strategy, opposite outcome.

The four properties of a good shard key

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.

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.

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.