TopolearnWhen one table or one machine is no longer enough, the partition key decides whether scaling works.
Partitioning splits one logical table into smaller physical pieces on the same database server. Common schemes:
- Range: by date, e.g. one partition per month. Ideal for time series: queries for last week touch one partition, and dropping old data is an instant
DROPof a partition instead of a hugeDELETE. - List: by a discrete value such as region.
- Hash: by a hash of a key, to spread rows evenly.
The planner skips partitions that can't match the WHERE clause (partition pruning), so queries must filter on the partition key to benefit.
Sharding splits data across multiple servers, each holding a subset of rows. It scales writes and storage beyond one machine, but adds real costs: cross-shard queries and joins are expensive, transactions across shards need coordination, and moving data when adding shards (rebalancing) is operationally hard.
Choosing the shard key is the critical decision:
- High cardinality and even distribution, to avoid hot shards (sharding by country puts most rows on one shard).
- Matches the access pattern, so most queries hit one shard: shard a multi-tenant SaaS by
tenant_idso each customer's data lives together. - Stable: a row's shard key shouldn't change.
Consistent hashing or a directory of key ranges lets you add shards without moving everything. Exhaust simpler options first — indexes, caching, read replicas, a bigger machine — because sharding is hard to undo.
Example
CREATE TABLE measurement (
sensor_id int NOT NULL,
taken_at timestamptz NOT NULL,
value double precision
) PARTITION BY RANGE (taken_at);
CREATE TABLE measurement_2026_04 PARTITION OF measurement
FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE measurement_2026_05 PARTITION OF measurement
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
EXPLAIN SELECT avg(value) FROM measurement
WHERE taken_at >= '2026-05-10' AND taken_at < '2026-05-11';
-- only measurement_2026_05 is scanned (partition pruning)
DROP TABLE measurement_2026_04; -- retention: instant, no bulk DELETERemember
- Partitioning splits a table within one server; sharding across servers
- Queries must filter on the partition key to get pruning
- A good shard key is high-cardinality, evenly spread and matches access patterns
Watch out for
- Sharding before trying indexes, caching, replicas and vertical scaling.