Chapter 28: Partitioning (Sharding) — Splitting Data Across Nodes
When data is too large for one machine, split it across multiple nodes. Each node holds a subset (partition/shard).
Partitioning Strategies
Range Partitioning
Partition by user_id range:
Node 1: user_id 1-1000000
Node 2: user_id 1000001-2000000
Node 3: user_id 2000001-3000000
Pro: range queries are efficient (scan one partition)
Con: hot spots (if new users are sequential, Node 3 gets all writes)
Hash Partitioning
// partition = hash(key) % num_partitions
// Distributes evenly, but range queries must hit ALL partitionsPostgreSQL Native Partitioning
-- Range partition by date (common for time-series)
CREATE TABLE events (
id BIGSERIAL, created_at TIMESTAMPTZ, data JSONB
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Queries automatically route to correct partition
SELECT * FROM events WHERE created_at >= '2024-01-15';
-- Only scans events_2024_01!Challenges
- Cross-partition queries: JOINs across shards are expensive
- Rebalancing: adding/removing nodes requires moving data
- Hot spots: uneven distribution overloads some nodes
- Transactions: ACID across partitions is very hard (2PC)
💡 When to Partition
Don't partition prematurely. A single PostgreSQL instance handles tables with billions of rows if properly indexed. Partition when: table exceeds available disk, or you need to drop old data efficiently (DROP PARTITION is instant vs DELETE which is slow).
Key Takeaways
- Partitioning splits data across nodes/files for scale
- Range: good for time-series. Hash: good for even distribution.
- PostgreSQL native partitioning: transparent to queries
- Main benefit for time-series: fast DROP PARTITION for old data
- Don't shard until you've exhausted single-node optimizations