Path 1 · lesson 6 of 8
Partitioning and sharding
How data is split: Postgres partitions a table by range, Cassandra hashes every partition key onto a ring of nodes, and a bad key puts all the load on one machine.
What you will learn
- Choose a partition key from the queries and the data's lifecycle
- Read a Postgres plan to confirm partition pruning
- Retire old data by dropping partitions instead of deleting rows
- Explain how Cassandra maps a partition key to a node, and spot a hot partition
- Split a hot partition with a bucket in the key
Before you start
The concept
Partitioning splits one logical table into pieces by the value of a partition key. When the pieces live on different machines, it is usually called sharding. Either way, the partition key decides which piece a row goes to, so it decides which piece a query has to look at, and which machine does the work.
| Postgres declarative partitioning | Cassandra | |
|---|---|---|
| What is split | One table into child tables, on one server | Every table, across all nodes, always |
| How rows are placed | By range, list or hash of the key, into partitions you create | By a hash (token) of the partition key, onto the token ring |
| Why you do it | Prune queries, retire old data cheaply, keep each piece small | Scale storage and throughput beyond one machine |
| Across machines? | Not built in; extensions such as Citus, or application-level sharding | Built in: adding nodes moves token ranges |
Why it exists
Tables that grow forever eventually make every maintenance task slow: indexes are rebuilt for hours, deleting old data takes the database down, backups take all night. Partitioning by time turns "delete last year" into dropping a table.
Across machines, the partition key is the most consequential and least reversible decision in a distributed database. A key that spreads load evenly lets a cluster grow by adding nodes; a key where one value dominates (one big customer, one busy channel, one celebrity) concentrates load on one node and its replicas, and no amount of hardware elsewhere helps. Changing it later means rewriting all the data.
How it works inside
Postgres: one table, partitioned by month
The lab creates an events table partitioned by range on created_at, one partition per month, and loads 4 million events from 1,000 tenants. Each partition is an ordinary table with its own indexes.
Database outputThe partitions
Real database output partition | bounds | size
----------------+--------------------------------------------------------------------------+-------
events_2026_01 | FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00') | 92 MB
events_2026_02 | FOR VALUES FROM ('2026-02-01 00:00:00+00') TO ('2026-03-01 00:00:00+00') | 83 MB
events_2026_03 | FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00') | 92 MB
events_2026_04 | FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-05-01 00:00:00+00') | 89 MBA query that filters on the partition key is planned against only the partitions that can hold matching rows. This is partition pruning:
Query planOne day in March: only the March partition is read
Real database output Aggregate (actual rows=1.00 loops=1)
Buffers: shared hit=1984 read=1338 written=1092
-> Index Only Scan using events_2026_03_tenant_id_created_at_idx on events_2026_03 events (actual rows=33333.00 loops=1)
Index Cond: ((created_at >= '2026-03-10 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-11 00:00:00+00'::timestamp with time zone))tenant_id, so this scan uses the Postgres 18 skip scan from lesson 2.)Query planOne tenant, no date: every partition is searched
Real database output -> Append (actual rows=4000.00 loops=1)
Buffers: shared hit=9 read=32 written=32
-> Index Only Scan using events_2026_01_tenant_id_created_at_idx on events_2026_01 events_1 (actual rows=1034.00 loops=1)
Index Cond: (tenant_id = 42)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=1 read=9 written=9
-> Index Only Scan using events_2026_02_tenant_id_created_at_idx on events_2026_02 events_2 (actual rows=933.00 loops=1)Retiring old data: DROP instead of DELETE
Database outputRemoving January: DELETE on an unpartitioned copy, then DETACH and DROP of the partition
Real database outputTime: 3533.760 ms (00:03.534) Time: 24.293 ms Time: 71.541 ms flat_table_size_after_delete ------------------------------ 355 MB
The rules partitioning imposes
Database outputA row with no partition to go to
Real database outputERROR: no partition of relation "events" found for row DETAIL: Partition key of the failing row contains (created_at) = (2026-06-15 00:00:00+00).
Database outputA primary key without the partition key
Real database outputERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "events" lacks column "created_at" which is part of the partition key.
id alone cannot be guaranteed unique across months.Cassandra: the token ring
Cassandra hashes every partition key with the Murmur3 partitioner to a 64-bit token. Each node owns several ranges of tokens, its virtual nodes: 16 per node by default in Cassandra 5.0 (num_tokens: 16). A row is stored on the node that owns its token's range (and, with replication, the next nodes on the ring).
Database outputThree nodes, 16 tokens each
Real database outputstate node tokens owns UN c2 16 31.6% UN c1 16 32.7% UN c3 16 35.7% Tokens in the ring: 48
The lab writes 3,000 messages to 3,000 different channels, one each, and then 3,000 messages to one busy channel, 424242. The table's partition key is channel_id.
Database outputWhat each node stores
Real database outputc1: Space used (live): 29993 Number of partitions (estimate): 982 Compacted partition maximum bytes: 42 c2: Space used (live): 28804 Number of partitions (estimate): 937 Compacted partition maximum bytes: 42 c3: Space used (live): 62165 Number of partitions (estimate): 1082 Compacted partition maximum bytes: 88148 Node that owns channel 424242: c3
The fix is to put a bucket in the partition key, such as the day: PRIMARY KEY ((channel_id, day), message_id). Each day of the channel is then a separate partition with its own token.
Database outputThe same channel, bucketed by day
Real database outputchannel 424242, day 1 -> c1 channel 424242, day 2 -> c3 channel 424242, day 3 -> c3 channel 424242, day 4 -> c3 channel 424242, day 5 -> c2 channel 424242, day 6 -> c3
Bucketing has a cost: a query for a channel's recent messages must now name the day, and a query spanning a week reads seven partitions. Choose the bucket size from the query: the smallest bucket that most reads can stay within.
Architecture
Drawing diagram…
Diagram. The partition key channel 424242 is hashed with Murmur3 to one token, which falls in a range owned by node c3, so every message of that channel is on c3. With the key (channel, day), each day hashes to its own token, spreading the channel across c1, c2 and c3.
flowchart TB
K["Partition key: channel_id = 424242"] --> H["Murmur3 hash"]
H --> T["Token"]
T --> RING{"Token ring: 48 ranges, 16 per node"}
RING -->|range owned by| C3["Node c3: every message of channel 424242"]
RING -.-> C1["Node c1"]
RING -.-> C2["Node c2"]
K2["Partition key: (424242, day)"] --> H2["One token per day"]
H2 --> C1
H2 --> C2
H2 --> C3- The hash makes placement uniform for many distinct keys, which is why 3,000 small channels split almost evenly.
- It does nothing for one key with a lot of data: hashing one value always gives the same token.
- Virtual nodes make ownership even across nodes and let a new node take small ranges from every existing node instead of half of one.
Step by step
Drawing diagram…
Sequence diagram. A query on one day in March is compared with each partition's bounds; only the March partition is scanned and the others are never opened. A query on tenant 42 without a date scans the March partition and every other partition, appending the results.
sequenceDiagram participant App as Application participant PG as Postgres planner participant M as events_2026_03 participant O as Other partitions App->>PG: count events WHERE created_at in 10 March PG->>PG: compare the range with each partition's bounds PG->>M: scan, the only partition that can match Note over O: pruned, never opened M-->>App: 33,333 App->>PG: count events WHERE tenant_id = 42 PG->>M: scan PG->>O: scan each of them too O-->>App: results appended, 4,000
- Pruning happens at planning time for constants, and at execution time for parameters, so prepared statements benefit too.
- A query without the partition key is not wrong, but its cost grows with the number of partitions.
- The same holds in Cassandra, more strictly: a query without the partition key must ask every node, which is why it is refused without ALLOW FILTERING (lesson 2).
In a real system
- Partition Postgres tables by time when old data is retired by age: logs, events, audit trails, metrics. Size partitions so that each holds what you drop at once (a day, a month).
- Create partitions ahead of time, with a scheduled job or pg_partman, and alert when the newest partition is close to filling.
- Pick the Cassandra partition key so that no single value grows without bound. Ask: what is the biggest customer, channel or device, and how much will it write in a year?
- Bucket by time for anything append-only (messages, readings, events):
(id, day)or(id, month), sized so most reads touch one or two buckets. - Watch the largest partition, not the average.
nodetool tablestatsreports the compacted partition maximum bytes per table on each node.
Wrong, then right
A chat service on Cassandra stores messages with the channel as the partition key. It works well in testing. In production, one public channel has millions of members.
Incorrect implementation
CREATE TABLE chat.messages_by_channel ( channel_id bigint, message_id timeuuid, author_id bigint, body text, PRIMARY KEY ((channel_id), message_id)) WITH CLUSTERING ORDER BY (message_id DESC);Why it is written this way
- Every message ever sent in a channel is one partition, on one replica set.
- The biggest channel's partition grows forever, and all its traffic lands on the same three nodes.
Why it fails
- The nodes holding the busiest channel run hot while the rest of the cluster is idle; the lab showed one channel doubling one node's storage.
- As the partition grows into gigabytes, reads of it slow down, compaction of it takes longer and repairs stream it whole. Adding nodes does not help: the partition cannot be split.
Corrected implementation
-- One partition per channel per day: bounded size, spread across the ring.CREATE TABLE chat.messages_by_channel_day ( channel_id bigint, day date, message_id timeuuid, author_id bigint, body text, PRIMARY KEY ((channel_id, day), message_id)) WITH CLUSTERING ORDER BY (message_id DESC);-- Recent messages: today's bucket first, then yesterday's if more are needed.SELECT * FROM chat.messages_by_channel_dayWHERE channel_id = ? AND day = ? LIMIT 50;Why it is written this way
- A day of the busiest channel is bounded, so no partition grows forever.
- Different days of the same channel hash to different tokens, so its load spreads over the cluster.
Trade-offs
- Reading history means walking back day by day, one query per bucket; the application has to do that loop.
- Quiet channels end up with many tiny partitions. For very uneven traffic, teams size buckets per channel (a busy channel gets hourly buckets) at the cost of more application logic.
- Changing the partition key of a live table means creating a new table and migrating the data; choose it before launch.
Configuration
Settings that shape partitioning. Defaults are those of PostgreSQL 18 and Cassandra 5.0.
enable_partition_pruning (Postgres)Default on
- What it does
- Whether the planner and executor skip partitions that cannot match.
- When to change it
- Leave it on; check plans to make sure queries actually name the partition key.
DEFAULT partition (Postgres)Default None
- What it does
- A partition that catches rows no other partition accepts.
- When to change it
- Useful as a safety net, but adding a new partition must then scan it for rows that belong in the new one. Prefer creating partitions ahead of time.
num_tokens (Cassandra)Default 16
- What it does
- How many token ranges each node owns on the ring.
- When to change it
- Set before a node first starts and keep it the same across the cluster; it cannot be changed on a node with data.
partitioner (Cassandra)Default Murmur3Partitioner
- What it does
- The hash function from partition key to token.
- When to change it
- Never change it on an existing cluster; data would be in the wrong place.
Hands-on exercise
Prune, drop, and find the hot node
Run the lab and see, on real Postgres 18 and a three-node Cassandra 5.0 cluster, partition pruning, a partition dropped in milliseconds against a slow DELETE, the errors partitioning imposes, and one busy partition key concentrating data on one node until it is bucketed by day.
Steps · 0 of 5 done
What you should see
The day query scans only events_2026_03; the tenant query appends results from all four partitions. DELETE takes seconds and leaves the table's size unchanged until VACUUM; DETACH and DROP take milliseconds. Inserting a row with no partition and adding a primary key without created_at both fail. In Cassandra the small channels split evenly across the three nodes, while all of channel 424242 is on one node, which stores about twice as much; bucketed by time, its partitions land on all three nodes. Your token ownership and placements will differ.
What can go wrong
Forgetting to create next month's partition
- Symptom
- At midnight on the first of the month, every insert fails with "no partition of relation found for row".
- Cause
- Range partitions are created by hand or by a job, and the job did not run.
- Fix
- Create partitions several periods ahead, alert on the newest partition's upper bound, or add a DEFAULT partition as a safety net.
Partitioning a table by a column queries do not use
- Symptom
- After partitioning, most queries are slower.
- Cause
- Queries filter on other columns, so each one is run against every partition.
- Fix
- Partition by the column most queries filter on, usually time, or do not partition. Check plans before and after.
An unbounded Cassandra partition
- Symptom
- One node has much higher load and latency than the others;
nodetool tablestatsshows a huge maximum partition size. - Cause
- One value of the partition key keeps growing: a big tenant, a busy channel, a device that never stops.
- Fix
- Add a time or hash bucket to the partition key and migrate to a new table.
Too many tiny buckets
- Symptom
- Reads of history are slow because they query hundreds of partitions.
- Cause
- Buckets were sized for the busiest key, so quiet keys have many near-empty partitions.
- Fix
- Size buckets so typical reads touch one or two, and let only the busiest keys use smaller buckets.
How to troubleshoot
Does a Postgres query prune partitions?
Check
EXPLAIN SELECT ... ;
- How to read it
- Only the partitions that can match should appear. An Append over every partition means the WHERE clause does not constrain the partition key in a way the planner can use.
Which partitions exist, and how big are they?
Check
SELECT c.relname, pg_get_expr(c.relpartbound, c.oid), pg_size_pretty(pg_relation_size(c.oid)) FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid WHERE i.inhparent = '<table>'::regclass;
- How to read it
- Check that future periods exist and that no partition is far larger than the rest.
One Cassandra node is busier than the others
Check
nodetool tablestats <ks>.<table> on each node, and nodetool getendpoints <ks> <table> <key> for suspect keys
- How to read it
- Uneven
Space usedand a largeCompacted partition maximum byteson one node point to a hot partition; getendpoints confirms which nodes hold a key.
Key takeaways
- The partition key decides where each row lives and which pieces a query must read.
- Postgres partitioning prunes queries that name the key and turns deleting old data into a fast DROP; unique keys must include the partition key.
- Cassandra hashes the partition key to a token on a ring of virtual nodes; many distinct keys spread evenly.
- One key with a lot of data stays in one partition on one replica set; no number of nodes spreads it.
- A time or hash bucket in the partition key bounds partition size and spreads a busy key, at the cost of queries that span buckets.
Practical check
Small scenarios, one decision each. Pick what you would do, then read why.
Scenario 1 of 3
A Postgres audit_log table gets 50 million rows a month and must keep 13 months. A nightly DELETE ... WHERE created_at < now() - interval '13 months' takes over an hour and causes replication lag.
What is the best change?
Scenario 2 of 3
An IoT platform on Cassandra stores readings with PRIMARY KEY ((device_id), ts). Most devices send a reading a minute. A few industrial devices send a hundred a second and have run for two years. Three nodes show high latency; the rest are fine.
What is the most likely cause?
Scenario 3 of 3
A team partitions a Postgres orders table by created_at month. Most queries are WHERE customer_id = ? (all of a customer's orders). After the change, those queries got slower.
Why did those queries get slower?
References
References: PostgreSQL 18 documentation: Table partitioning ↗ · PostgreSQL 18 documentation: Query planning settings (enable_partition_pruning) ↗ · Apache Cassandra 5.0 documentation: Dynamo (consistent hashing, tokens and virtual nodes) ↗ · Apache Cassandra 5.0 documentation: Refining a data model (partition size and bucketing) ↗ · Apache Cassandra 5.0 documentation: nodetool getendpoints ↗ · Apache Cassandra 5.0 default cassandra.yaml (num_tokens, partitioner) ↗ · Karger et al., Consistent Hashing and Random Trees (STOC 1997) ↗ · Report a problem