Skip to content

Path 1 · lesson 6 of 8

Intermediate30 minPostgreSQL 18.7 · Apache Cassandra 5.0.9 (three nodes), all in Docker

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 partitioningCassandra
What is splitOne table into child tables, on one serverEvery table, across all nodes, always
How rows are placedBy range, list or hash of the key, into partitions you createBy a hash (token) of the partition key, onto the token ring
Why you do itPrune queries, retire old data cheaply, keep each piece smallScale storage and throughput beyond one machine
Across machines?Not built in; extensions such as Citus, or application-level shardingBuilt in: adding nodes moves token ranges
Same word, different jobs: Postgres partitions for manageability on one machine, Cassandra to spread across many.

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 MB
Four months, four partitions of similar size. Each is a separate table on disk, with its own copy of the index.

A 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))
The three other partitions do not appear in the plan at all. (The index used leads with 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)
Without the partition key, the query is run against all four partitions and the results appended. Cheap here with four, expensive with hundreds: a partitioned table rewards queries that name the key.

Retiring old data: DROP instead of DELETE

Database outputRemoving January: DELETE on an unpartitioned copy, then DETACH and DROP of the partition

Real database output
Time: 3533.760 ms (00:03.534)
Time: 24.293 ms
Time: 71.541 ms
 flat_table_size_after_delete
------------------------------
 355 MB
Deleting a million rows took 3.5 s, wrote WAL for every row and left the table at 355 MB: the space is only reused after VACUUM. Detaching and dropping the partition took under 0.1 s and returned its space to the operating system at once.

The rules partitioning imposes

Database outputA row with no partition to go to

Real database output
ERROR:  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).
Partitions must exist before data arrives. Teams create them ahead of time with a scheduled job (or the pg_partman extension), or add a DEFAULT partition to catch the rest.

Database outputA primary key without the partition key

Real database output
ERROR:  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.
Each partition enforces uniqueness only within itself, so a unique key must include 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 output
state node tokens owns
UN c2 16 31.6%
UN c1 16 32.7%
UN c3 16 35.7%
Tokens in the ring: 48
With replication factor 1 for this keyspace, each node owns about a third of the token space. Not exactly a third: ownership depends on where the tokens fall.

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 output
c1:
  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 3,000 small channels spread evenly, about 1,000 per node. The one busy channel is a single partition, so all 3,000 of its messages are on c3, which stores twice as much as either other node. Its largest partition is 88 kB against 42 bytes elsewhere. Every read and write for that channel goes to c3 too.

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 output
channel 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
Each day lands wherever its token falls, so over many days the channel's data spreads across all nodes. Over six days it is still uneven: buckets spread load statistically, not exactly. And each partition stays bounded in size, however long the channel lives.

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
One partition key value goes to one place; adding a bucket spreads it.
  • 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
Partition pruning in Postgres, with and without the partition key.
  1. Pruning happens at planning time for constants, and at execution time for parameters, so prepared statements benefit too.
  2. A query without the partition key is not wrong, but its cost grows with the number of partitions.
  3. 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 tablestats reports 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

schema/messages.cqlCQL
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

schema/messages.cqlCQL
-- 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 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 tablestats shows 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 used and a large Compacted partition maximum bytes on 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