Partitioning — DB Notes

Splitting a large dataset into pieces — across nodes (sharding) or within one node — so size, write load, and query cost stay manageable.

Partitioning splits a large dataset into smaller pieces. The core design question is: given a key, which partition does it live on? Get this wrong and some partitions become overloaded “hot” partitions while others sit nearly idle.

Fig. 4 — Key range vs. hash partitioning

Sequential, timestamp-prefixed keys always fall into whichever range is 'newest' — every write lands on the same partition, creating a hotspot no matter how many partitions exist.

When you actually need it

Most applications never should partition — a single well-indexed Postgres/MySQL instance handles far more than people assume (billions of rows, tens of thousands of writes/sec). Reach for it when you hit a real wall:

The table won’t fit. A metrics table grows 500GB/month. No single disk or buffer pool keeps up; old data has to age out cleanly.

Write throughput exceeds one node. A ride-sharing app’s location_updates take 200k writes/sec. One leader can’t absorb that, and you can’t just add read replicas because the bottleneck is writes.

Data residency. EU user data must physically stay in the EU. Partition by region so each shard lives in the right jurisdiction.

Blast radius. One noisy tenant shouldn’t be able to degrade everyone. Separate shards contain the damage.

Key range partitioning

Each partition owns a contiguous range of keys — like the volumes of a paper encyclopedia. Great for range queries (WHERE created_at BETWEEN ... touches one or two partitions), and it makes dropping old data free (DROP the January partition).

But it’s exactly what creates hotspots: if keys arrive in increasing order (timestamps, auto-increment IDs), every new write goes to the one partition owning the newest range while the rest are read-only. Time-series systems live with this by keeping the hot partition on the fastest hardware and rotating.

Hash partitioning

Run the key through a hash function first, then assign by hash value. This scatters keys evenly and kills the sequential-write hotspot — the trade-off is that range queries no longer map to a contiguous set of partitions, so created_at BETWEEN becomes a scatter/gather across every partition.

def partition_for_key(key: str, partition_count: int) -> int:
    return hash(key) % partition_count

Choosing the partition key: worked examples

System	Good partition key	Why
Team chat (Slack-like)	`workspace_id`	Every query is already scoped to one workspace; a workspace’s data stays co-located, so channel lists and searches hit one shard.
Multi-tenant SaaS	`tenant_id`	Natural isolation boundary; a tenant’s reports never fan out. Watch for whale tenants (below).
Time-series / metrics	time range (`day` or `hour`)	Old partitions drop instantly; queries are almost always “recent window”. Accept the write hotspot on the current partition.
Social feed	`user_id` (hashed)	“My posts”, “my timeline”, “my followers” all key on one user; hashing spreads load evenly.
E-commerce orders	`customer_id` (hashed)	“My order history” is the dominant query. Cross-customer analytics goes to a warehouse, not this store.

Queries that fight the partition scheme

Cross-partition search. “Find every message containing ‘invoice’ across all my channels” when partitioned by channel means querying every channel’s shard and merging results — slow and fan-out-heavy. A separate search index (partitioned differently, or not at all) usually owns this.

Secondary-index queries. You partitioned orders by customer_id, but the fulfilment team queries by warehouse_id. That query either scatters across every shard or needs a separate global index partitioned by warehouse_id and kept in sync — real added complexity.

Joins across partitions. Joining two tables partitioned on different keys means shipping data between nodes. Systems that shard well tend to co-locate related data under the same key (all of a tenant’s tables sharded by tenant_id).