Data Partitioning

What actually happens

Try it: see partition skew appear

Pick what you hash on, then replay a traffic mix with an optional celebrity spike. Shard bars show placement load, not storage bytes.

Partition strategies

Partitioning divides one logical dataset into smaller ownership or management units. It may happen inside one database machine, where partition pruning reduces scan work, or across machines, where it becomes sharding. Range partitioning keeps adjacent values together for scans; hash partitioning spreads distinct keys more evenly; a directory maps keys or tenants to explicitly chosen owners. The best choice depends on how queries locate their data.

Pick the partition key to match your hottest queries. Anything else pays scatter-gather cost or needs an alternate index.

Hot partitions and mitigation

Salting adds a secondary bucket to a hot logical key, distributing writes across several partitions. For a tenant producing 5,000 writes per second with a 2,000-per-partition safe capacity, three perfectly balanced buckets are a mathematical minimum, not comfortable reserve. Reads may now query all buckets and merge results. Select the salt count from measured skew and failure headroom, and preserve a way to determine the bucket for point lookups.

Skew comes from viral keys: a celebrity profile, a flash-sale SKU, a leaderboard artifact, not only from a naive partition key. Combine adaptive splitting with application tricks like write sharding and read aggregation.

Partition sizing and growth math

Partition design is capacity planning: estimate bytes, writes, reads, and the cost to move data when a shard grows too large or too hot.

Rebalancing workflow

Migration is a change in ownership while traffic continues. Copy the data, capture concurrent changes, verify the destination, and switch routing at a controlled point. The source must stop acting as an independent writer after handoff. Keep redirects or routing refreshes for stale clients, and track lag, copy bandwidth, checksums, and user latency. A failed migration should leave a clear authoritative owner, not two partially updated copies.

Moving a shard is not only copying bytes. You must keep writes correct, rebuild indexes, validate results, and flip the routing map safely.

Choosing partition keys under pressure

State the access pattern first, then the key. Interviewers are checking whether your choice reduces fan-out without creating a new hotspot.

Secondary indexes: local vs global

A local index organizes data already inside one partition, so a query still needs the partition key to route efficiently. A global index can answer a different access pattern across the dataset, but maintaining it creates additional writes and possibly a separate consistency boundary. If an order is partitioned by customer but looked up by seller, decide whether the seller index can lag or must update atomically with the order.

Local indexes stay collocated with the base partition: cheap writes, awkward fan-out if you omit the partition key on reads. Global indexes reorder data under new partition keys: faster alternate lookups, async propagation costs.

Cross-partition work

Prefer designs where related mutations touch one partition. Distributed transactions exist where money demands them. They coordinate replicas across shards at latency cost.

Time-based and geo partitioning

Time partitions make retention cheap because an expired interval can be removed as a unit, and time filters can skip irrelevant partitions. The newest interval can become a write hotspot, so combine time with a tenant or hash bucket when needed. Geographic partitioning improves locality but requires an ownership rule for users who travel or records shared across regions. A query must include enough placement information to avoid scanning every region.

Time partitions prune scans and allow dropping whole intervals. Geo partitions pin rows near users or inside compliance boundaries.

CREATE TABLE events (
  id BIGINT,
  event_time TIMESTAMPTZ,
  data TEXT
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

SELECT * FROM events WHERE event_time > '2026-03-10';  -- prunes old partitions

DROP TABLE events_2025_01;  -- bulk retire

-- Illustrative geo constraint sketch:
ALTER TABLE users PARTITION BY LIST (region) (
  PARTITION eu VALUES IN ('eu-west', 'eu-central'),
  PARTITION us VALUES IN ('us-east', 'us-west')
);
-- Tie partitions to regional replica sets via zone configs

Fixed shards vs dynamic splits

Over-provision logical shards up front for painless moves, or let the store split heavy partitions automatically: each approach trades operational simplicity against prediction risk.

Patterns at scale

Large social graphs often hash user ids into thousands of logical shards backed by pools of databases; ride platforms partition by city-day so localized queries stay narrow.

Degraded modes and rebalance drills

Partitioning spreads load until a hot key, split storm, or bad map update concentrates it again. Predefine how the product behaves while you rebalance.