Codelab: Partition a Dataset

You've spent this whole section on partitioning and sharding as ideas — the 2×2 map, the shard key, the hot shard, federation. Time to make it real. In this codelab you'll take one ordinary orders table on a single Postgres and partition it two different ways — by range (month) and by hash (customer) — and watch, by hand, exactly what each choice buys and costs: rows routing themselves, a query pruning to a single partition, a whole month retired in seven milliseconds, a hot partition forming in real time, and the hash rebuild that spreads writes dead even but can no longer prune a date range. Every command and every line of output below is from a real run — type them and you'll see the same thing.

One framing before we start: partitioning splits a table into pieces on one machine (that's what Postgres does natively, and what we'll do here); sharding is the same split spread across machines (Partitioning vs Sharding drew that map). Everything you feel in this codelab — the routing, the pruning, the hot partition, the key choice — is the local, runnable version of the exact decisions a sharded system makes at scale. Learn it here where you can watch it.

All you need is Docker. Spin up a throwaway Postgres and open a shell:

# one throwaway Postgres — nothing here touches your machine
docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:16
docker exec -it pg psql -U postgres

Create the table with a partition rule instead of plain columns, then create one child partition per month. The bounds are lower-inclusive, upper-exclusive — January is ['2024-01-01', '2024-02-01').

CREATE TABLE orders (
    id        bigint,
    created   date not null,
    customer  int  not null,
    amount    numeric
) PARTITION BY RANGE (created);          -- ← the split rule

CREATE TABLE orders_2024_01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Here's the thing to notice: the parent orders table stores nothing. It's a routing layer. Run \d orders and it says Partition key: RANGE (created) · Number of partitions: 3. Now throw 300,000 orders at it — with random dates across the quarter — and ask where each one landed. That tableoid::regclass trick prints which physical partition each row actually lives in:

INSERT INTO orders (id, created, customer, amount)
SELECT g,
       DATE '2024-01-01' + (random()*89)::int,   -- a day somewhere in the quarter
       1 + (random()*99999)::int,
       (random()*400)::numeric(8,2)
FROM generate_series(1, 300000) g;

SELECT tableoid::regclass AS partition, count(*) FROM orders GROUP BY 1 ORDER BY 1;

   partition    | count
----------------+--------
 orders_2024_01 | 102557
 orders_2024_02 |  97831
 orders_2024_03 |  99612

You never told a single row which partition to go to. You wrote INSERT INTO orders and Postgres routed each row by its date — January's rows to the January partition, and so on (that's tuple routing). One logical table, three physical files, automatic. That's the whole mechanism; everything else is what you get for it.

Here's the payoff that makes partitioning worth the trouble. Ask for March's orders and read the plan with EXPLAIN:

EXPLAIN (COSTS OFF) SELECT count(*) FROM orders WHERE created >= '2024-03-01';

 Aggregate
   ->  Seq Scan on orders_2024_03 orders
         Filter: (created >= '2024-03-01'::date)

Look at what it scanned: orders_2024_03. Just March. January and February aren't in the plan at all — Postgres proved they can't contain a matching row and skipped them entirely. That's partition pruning, and on a table split into a hundred months it's the difference between reading one month and reading a hundred.

Don't take my word that pruning did it — turn it off and watch the same query fall back to reading everything:

SET enable_partition_pruning = off;
EXPLAIN (COSTS OFF) SELECT count(*) FROM orders WHERE created >= '2024-03-01';

 Finalize Aggregate
   ->  Gather  (Workers Planned: 2)
         ->  Parallel Append
               ->  Parallel Seq Scan on orders_2024_01 orders_1
               ->  Parallel Seq Scan on orders_2024_03 orders_3
               ->  Parallel Seq Scan on orders_2024_02 orders_2

With enable_partition_pruning = off, the exact same query now scans all three partitions. Pruning is the engine; the partition layout is just what lets it work. (Turn it back on: SET enable_partition_pruning = on;.) The catch to remember: pruning only fires when your WHERE filters on the partition key. A query that doesn't mention created touches every partition — sometimes slower than one big table. Partition for the queries you actually run.

Now the second thing range partitioning buys you, and it's the reason every time-series and log system on earth uses it. Say January is past your retention window and needs to go. On a normal table that's DELETE FROM orders WHERE created < '2024-02-01' — millions of rows, a long transaction, a WAL flood, and a VACUUM cleanup afterward. On a partitioned table, January is a table:

\timing on
DROP TABLE orders_2024_01;          -- retire January

DROP TABLE
Time: 7.617 ms

Seven milliseconds. You didn't delete 102,557 rows — you dropped a whole partition, which is a metadata change: unlink the file, forget it exists. No row-by-row delete, no VACUUM storm. (If you want to archive rather than destroy, ALTER TABLE orders DETACH PARTITION orders_2024_01; pops it out as a standalone table you can copy elsewhere first.) Retiring old data by the truckload is range partitioning's superpower — and it's why when you partition by time, aging out data stops being a scary batch job and becomes a one-liner.

Range partitioning has a dark side, and you can make it appear in one insert. Real traffic isn't spread evenly across the quarter — real orders arrive now, all with today's date. Add an April partition and simulate a day of live traffic: 50,000 new orders, all dated early April.

CREATE TABLE orders_2024_04 PARTITION OF orders FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- a day of live traffic: 50,000 new orders, all dated early April
INSERT INTO orders (id, created, customer, amount)
SELECT g, DATE '2024-04-01' + (random()*2)::int, 1+(random()*99999)::int, (random()*400)::numeric(8,2)
FROM generate_series(300001, 350000) g;

SELECT tableoid::regclass AS partition, count(*) AS written
FROM orders WHERE id > 300000 GROUP BY 1;

   partition    | written
----------------+---------
 orders_2024_04 |   50000     ← every single one

Every single one of the 50,000 landed in orders_2024_04. Of course they did — they all have April dates, and range-by-date sends April to the April partition. Which means all your current write traffic hammers one partition while the older ones sit idle: the same row lock, the same index, the same disk pages, all contended. This is the hot partition from Resharding & Hot Shards — not a diagram this time, but a thing you just made happen. Time-ordered data plus range-by-time always funnels the present into one partition. Hold that thought; it's exactly what the next strategy fixes.

Let's fix the hotspot by changing the rule. Build a second table partitioned by a hash of the customer instead of the date — four partitions, each holding the rows whose customer hashes to its slot (MODULUS 4, REMAINDER 0..3) — and pour the same rows in:

CREATE TABLE orders_h (LIKE orders) PARTITION BY HASH (customer);   -- ← hash the customer, not the date
CREATE TABLE orders_h0 PARTITION OF orders_h FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_h1 PARTITION OF orders_h FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_h2 PARTITION OF orders_h FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_h3 PARTITION OF orders_h FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO orders_h SELECT * FROM orders;   -- the same rows, hashed by customer
SELECT tableoid::regclass AS partition, count(*) FROM orders_h GROUP BY 1 ORDER BY 1;

 partition | count
-----------+-------
 orders_h0 | 61821
 orders_h1 | 61994
 orders_h2 | 61959
 orders_h3 | 61669     ← dead even, and no hot partition

Dead even — about 61,900 rows in each of the four, regardless of dates. Hashing scatters customers uniformly, so there's no "current" partition to overload; writes spread across all four no matter when they arrive. The hot partition is gone. (This is Consistent Hashing's even spread, in a CREATE TABLE.)

But you don't get that for free — run the exact March query from Step 2 on the hash table and watch the bill come due:

-- the SAME 'orders in March' range query, now on the hash table:
EXPLAIN (COSTS OFF) SELECT count(*) FROM orders_h WHERE created >= '2024-03-01';

 Parallel Append
   ->  Parallel Seq Scan on orders_h1     ┐
   ->  Parallel Seq Scan on orders_h2     │  ALL FOUR partitions —
   ->  Parallel Seq Scan on orders_h0     │  hash scattered the dates,
   ->  Parallel Seq Scan on orders_h3     ┘  so nothing can be pruned

-- but a point lookup on the KEY prunes perfectly:
EXPLAIN (COSTS OFF) SELECT count(*) FROM orders_h WHERE customer = 42;

 Aggregate
   ->  Seq Scan on orders_h2 orders_h     ← exactly one partition

The range query now scans all four partitions — hash scattered the dates across every partition, so Postgres can't rule any of them out. Hash partitioning can't prune a range at all. It only prunes on an equality on the partition key: WHERE customer = 42 lands on exactly one partition (orders_h2), because that customer hashes to exactly one slot. So hash is brilliant for "look up one customer" and useless for "everything in March." Same data, opposite strengths — that's the whole lesson, and you just watched both halves.

Before we tally the trade, play with it until the pattern is muscle memory. Pick a strategy, insert rows — try time-ordered on the range table and watch a partition glow red — then run the range and point queries and watch which partitions light up as scanned versus stay dark as pruned. Flip to hash and watch the writes spread even while a range query lights up every partition. Then drop the oldest month and watch it vanish in O(1). Everything you just ran, in one panel.

You've now felt both bets, so the decision writes itself — and it's the same decision as Choosing a Shard Key, just runnable:

There is no universal winner — your query pattern picks the strategy. Do you slice data by time and age it out? Range, and you'll manage the hot partition (partition-manager tools like pg_partman roll new months for you). Do you look rows up one key at a time and dread a write hotspot? Hash, and you'll give up range pruning. And if you want both — the O(1) archival of range and the even spread of hash — you can sub-partition: split by range monthly, then split each month by hash. More moving parts, best of both. But start simple: for most datasets, range by date is the right first move, exactly because retiring old data is the pain that shows up first.

Tear it down — the whole lab was disposable:

docker rm -f pg

In fifteen minutes, on real Postgres, you proved the entire shape of partitioning by hand:

• A partitioned table routes rows for you — one INSERT INTO orders, and each row filed itself by the partition key (300k rows → the right monthly partitions, automatically).
• Pruning is the payoff — a range query on a range table scanned 1 partition, not all; turn pruning off and it read everything. Pruning only fires on the partition key.
• Range partitioning retires data in O(1) — DROP a month in 7.6 ms, no bulk DELETE, no VACUUM storm.
• …but range hot-spots on time-ordered writes — 50,000 new orders all hit one partition. That's Resharding & Hot Shards, live.
• Hash partitioning spreads writes dead even (≈61,900 each) and prunes a point lookup to one partition — but a range query scans every partition, and the modulus is frozen.
• The split rule is the whole decision (Choosing a Shard Key, runnable): range for time-and-retention, hash for even-spread-and-point-lookups.

That closes out §5's toolkit — split across boxes (shard), split within a box (partition), make each write cheaper (batch). Next — the §5 Checkpoint: you'll shard a social app end to end, choosing the key and the strategy and justifying both. You've now got every tool you need.