Partitioning and sharding: pruning, retention, shard keys

Partitions are skipped only by conditions on the partition key; a shard is reached directly only by a query that carries the shard key.

Before you read: predict

ExamHall's proctor_events table is partitioned by range on occurred_at, one partition per month from April to October 2026. October's partition was created ahead of time and holds no rows yet, and ANALYZE has run.

How many partitions does the plan for SELECT count(*) FROM proctor_events WHERE occurred_at >= '2026-09-14' scan?

The 30-second version

A partitioned table stores each row in the partition whose bounds hold its partition key. The planner skips a partition only by comparing conditions on that key with its bounds; other filters and indexes skip none. Dropping a partition removes old rows without row-by-row deletes. Sharding spreads parts across servers; queries lacking the shard key visit every shard.

Partitions are skipped only by conditions on the partition key; a shard is reached directly only by a query that carries the shard key.

How it works

Step 1 of 14

Declarative partitioning. PARTITION BY RANGE, LIST or HASH on a key makes a parent table. Each row lives in exactly one child partition.

In the kitchen

one bay per vintage, all listed as "the cellar" → the parent table and its partitions (step 1)

Analogy: A wine merchant's cellar

Every bottle goes in the bay for its vintage, and each bay's card gives the vintage it holds.

In the cellar	In the system
one bay per vintage, all listed as "the cellar"	the parent table and its partitions (step 1)
a bottle whose vintage has no bay yet cannot be put away	routing, and the missing partition (step 2)
"the 2019s" means one bay; "2019 and later" includes next year's empty bay	pruning by bounds, not by contents (step 3)
"every bottle from one grower" means every bay, even with a grower list in each	no key, no pruning (step 4)
an old vintage sold at auction as one lot, bay and all, not bottle by bottle	`DETACH` and `DROP` (step 7)
cellars in several towns, each keeping some growers' wines	shards and the shard key (steps 9–10)
the town whose cellar holds the grower everyone orders	a hot shard (step 12)

In real terms: before partitioning or sharding a table, list its hot queries and check that each one carries the key.

Breaks down when: you move data. Carrying a bay's bottles to another town is one trip; moving a shard means copying its rows while writes keep arriving (step 14).

Contrasting case: a second cellar holding a copy of every bay. It adds places to look but takes in no new bottles; that is replication, not sharding (see db-replication-read-replicas).

Examples, run for real

4.1 ExamHall: proctor events by month

EXH-1290: nightly retention ran DELETE FROM proctor_events WHERE occurred_at < <cutoff> on one large table. MR !1490 partitions it by month on occurred_at, creates next month's partition ahead, and retires the oldest month with DETACH PARTITION and DROP TABLE. The seeded copy holds 20,000 events a month.

4.1 proctor_events: PARTITION BY RANGE (occurred_at), 6 monthly partitions, April..September 2026, 20000 events each
4.1 insert dated 1 October, before its partition exists: ERROR 23514 no partition of relation "proctor_events" found for row
4.1 after CREATE TABLE proctor_events_2026_10 PARTITION OF ...: the row lands in proctor_events_2026_10
4.1 dashboard week (occurred_at from 14 to 21 September): plan scans 1 of 7 partitions: proctor_events_2026_09
4.1 retention by DELETE ... WHERE occurred_at < '2026-05-01': 20000 rows deleted one by one · April partition after: live 0, dead row versions 20000 (left for VACUUM), file 1253376 bytes
4.1 retention by DETACH PARTITION + DROP TABLE on a fresh copy: rows deleted one by one 0 · partitions left 5 · April's table gone: true

An event with no partition to go to is refused (step 2). A week's range on occurred_at reads one partition of seven (step 3). The DELETE empties April but keeps its file and 20,000 dead versions; DROP removes the table (step 7).

4.2 Ticketing: four shards by event

4.2 MODEL ticketing: 4 shards, shard = hash(event_id) mod 4 · 200 events · 40000 orders
4.2 "orders for event 42" (carries the shard key): 1 shard
4.2 "orders of customer 17" (no shard key): sent to 4 shards; its 5 orders actually sit on 3
4.2 "move a seat from event 42 to event 1" in one transaction: rows on 2 shards (shard 1 and shard 3)
4.2 add a fifth shard: hash mod N moves 150 of 200 events (75%) · consistent hashing (64 points per shard) moves 35 (17.5%)

The router cannot know where customer 17's orders are, so it asks all four shards (step 10). The seat move spans two servers and needs a distributed commit (step 13). Growing to five shards, mod N moves three events in four; the ring moves about a sixth (step 14).

Picture it

on the left, a tall blue double-door cabinet stands open; its shelves are divided into sections holding green drawers, binders, folders and a grey file box. A grey-haired woman in a green suit pulls folders from an open drawer, and a man in a blue suit holds a stack of papers and gestures towards the cabinet. Behind them stand two separate pale green archive buildings with green roofs: the larger has an open bay of shelves with boxes and binders, the narrower a green door with binders pinned to it. On the floor to the right are a stack of books and two file boxes. — An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

payout_events is partitioned by range on at, one partition per month from April to September 2026. On 15 September the retention job must leave no event from before 17 June, keep every event from 17 June on, and make as few individual row deletes as it can.

Which job does that?

Question 2 of 5

A migration creates price_history (id bigint PRIMARY KEY, sku text, cents int, valid_from date) PARTITION BY RANGE (valid_from) and fails with SQLSTATE 0A000.

What is the bug?

Question 3 of 5

A team chat service will shard its messages over 8 servers. Its hot query is SELECT … FROM messages WHERE workspace_id = $1 AND channel_id = $2 ORDER BY created_at DESC LIMIT 50, and it must read one shard. No shard may take more than a quarter of each day's new messages; workspace 3, one large customer, sends 4 in 10 of them.

Which shard key meets both?

Question 4 of 5

A plays table is partitioned by range on played_at, one partition per month for a year. A report runs as a prepared statement with a generic plan: ... WHERE played_at >= $1 AND played_at < $2. Run for one day in May, its EXPLAIN EXECUTE output includes the line Subplans Removed: 11.

How many partitions did the executor read?

Question 5 of 5

Orders are sharded over 8 servers by hash(event_id) mod 8. In a stadium on-sale hour, 6 orders in 10 are for event 777, and its shard falls behind. The goal is that no shard takes more than a quarter of the hour's writes.

Which change meets it?

Go deeper

Pro tips

Create next month's partition ahead of time. A scheduled job that adds the coming month's partition keeps inserts from failing at midnight on the 1st. When: range partitions by time (step 2). Verify: the 1 October insert fails with SQLSTATE 23514 until proctor_events_2026_10 exists, then lands in it.
Check pruning with plain EXPLAIN before you ship a query. Count the partitions the plan lists; for a prepared statement, also read Subplans Removed. When: any new query on a partitioned table (steps 3–6). Verify: every partition count in this lesson is read from plan output.
Detach with CONCURRENTLY on a busy table. Plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent; DETACH PARTITION … CONCURRENTLY needs only SHARE UPDATE EXCLUSIVE, and cannot run inside a transaction block. Status: DOC (PostgreSQL 17, 5.12.2.2); not run here.

Source

PostgreSQL 17, 5.12 Table Partitioning: https://www.postgresql.org/docs/17/ddl-partitioning.html · fetched 2026-09-28. §5.12.1: hash partitioning puts in each partition "the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder" (steps 1, 5); "Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE" (step 7); the benefits "will normally be worthwhile only when a table would otherwise be very large". §5.12.2: "Inserting data into the parent table that does not map to one of the existing partitions will cause an error" (step 2). §5.12.2.3: a unique or primary key constraint's "columns must include all of the partition key columns" (step 8). §5.12.4: the planner "will examine the definition of each partition and prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause"; pruning "is driven only by the constraints defined implicitly by the partition keys, not by the presence of indexes" (steps 3–4); it "can be performed not only during the planning of a given query, but also during its execution", with the count shown as "Subplans Removed" (step 6). §5.12.6: "Too many partitions can mean longer query planning times and higher memory consumption". The docs do not state that a range on a hash key prunes nothing; step 5's second half is observed (t2), not quoted. Valid as of 2026-09-28.
PostgreSQL 17, PREPARE TRANSACTION: https://www.postgresql.org/docs/17/sql-prepare-transaction.html · fetched 2026-09-28. It "prepares the current transaction for two-phase commit"; its purpose "is to allow an external transaction manager to perform atomic global transactions across multiple databases" (step 13). Valid as of 2026-09-28.
Citus 13.0 docs, Choosing Distribution Column: https://docs.citusdata.com/en/stable/sharding/data_modeling.html · fetched 2026-09-28. "Choose a column with high cardinality"; "If you distribute a table on a column skewed to certain common values, then data in the table will tend to accumulate in certain shards"; "Partition distributed tables by a common tenant_id column"; "rows with the same distribution column value are always on the same machine, even across different tables"; "Do not choose a timestamp as the distribution column" (steps 11–12; model rule 1). Valid as of 2026-09-28.
MongoDB manual, Choose a Shard Key: https://www.mongodb.com/docs/manual/core/sharding-choose-a-shard-key/ · fetched 2026-09-28. "If the shard key value is always increasing, all new inserts are routed to the chunk with maxKey as the upper bound … The shard containing that chunk becomes the bottleneck for write operations"; "When the queries do not contain the shard key, the queries are broadcast to all shards for evaluation" (steps 10–11; model rules 2–3). Valid as of 2026-09-28.
MongoDB manual, mongos (query routing): https://www.mongodb.com/docs/manual/core/sharded-cluster-query-router/ · fetched 2026-09-28. "For queries that don't include the shard key, mongos must query all shards, wait for their responses and then return the result to the application" (step 10). Valid as of 2026-09-28.
Apache Cassandra docs, Dynamo: Consistent Hashing using a Token Ring: https://cassandra.apache.org/doc/latest/cassandra/architecture/dynamo.html · fetched 2026-09-28. In naive hashing "adding a single node might invalidate almost all of the mappings"; "consistent hashing only has to move a small fraction of the keys" (step 14; model rule 4). Valid as of 2026-09-28.
Background, not quoted: Kleppmann, Designing Data-Intensive Applications, ch. 6 (Partitioning); Karger et al., Consistent Hashing and Random Trees, STOC 1997 (curriculum row sources).

Checked against the tools and docs as of 2026-09-28.

Predict first