Partitioning Postgres: locality and cheap bulk removal

01 The shape of the pain

One project's cleanup shouldn't read every project's rows.

Picture a claims table: a couple hundred million rows, spread across thousands of projects. A plain table in Postgres is one file, called the heap, and rows land in it roughly in the order they were inserted. Yours arrive interleaved, so one project's rows end up scattered the length of the file. Most days it behaves. The trouble starts with the per-project maintenance you have to run.

You run a dedup anti-join for project 42, deleting rows that a newer claim supersedes. You autovacuum. Later, project 42 wraps up and you want its data gone. Every one of these jobs cares about a single project. Every one of them, on a plain heap table, drags across all of it: the anti-join and the vacuum walk rows and indexes that belong to thousands of other projects, and the archive is a giant DELETE that leaves dead tuples behind for vacuum to chase.

Figure 1 · interactiveClick the button: project 42's rows light up where they actually sit. There is no physical boundary around a project, so a vacuum or anti-join for it must walk the whole heap to collect those five cells.

The waste is structural. Your data has a natural slice, one project, but the storage has no idea that slice exists. Every logical operation on one slice pays for the whole table.

Picture itA shuffled deck. Every heart is somewhere in the pile, but to gather all the hearts you flip through the whole deck. The cards are fine; nothing keeps a suit together.

The two jobs that hurtPer-tenant locality (a dedup or vacuum that should touch one project, not all of them) and bulk removal (retiring a finished project, or dropping last quarter's logs). Hold both in mind. They want two different partition strategies.

02 Why the big table fights back

A big DELETE is the slowest way to remove data.

Deleting a million rows in Postgres does not free a million rows. It marks each one dead, a dead tuple, writes a WAL record (the write-ahead log, Postgres' crash-safety journal) for each, leaves every matching index entry pointing at a corpse, and hands the cleanup to vacuum. Until vacuum runs and the pages are reused, the table keeps the same size on disk. You paid full price to remove data and the file did not shrink.

Figure 2 · interactiveClick once to DELETE two rows: they go dead but the page keeps its size. Click again to VACUUM, and only now do the dead tuples clear and the file shrink. That gap is the cost of a big delete.

Indexes help you find project 42's rows, but they do nothing for the second problem: a vacuum or an anti-join scoped to one project still has to reason about a structure shared with everyone else. There is no physical boundary around a project, so there is nothing the engine can skip.

Picture itImagine one warehouse where every tenant's boxes are shuffled together on the same shelves. To clear out one tenant you walk every aisle checking labels, then leave gaps you have to compact later. What you want is a separate room per group, so clearing one is closing a door, not searching the building.

Removing rows is expensive. Removing a whole partition is nearly free, because it was already a separate thing.

03 What partitioning actually gives you

A partitioned table is many tables the planner can skip.

Declare a table PARTITION BY some key and Postgres stops storing rows in one heap. Each row goes to a child table, a partition, chosen by that key. The parent becomes a routing rule with no storage of its own. Two things fall out of that, and they are the whole point.

Figure 3 · interactiveClick 42, 7, or 99. The parent holds no rows; it reads the key and sends the row to one child. A query that names the key lets the planner walk this same arrow to one child and ignore the rest.

First, pruning: when your query restricts the partition key, the planner proves which partitions can possibly match and skips the rest before reading a single page. Second, whole-partition operations: a partition is a real table, so you can DETACH or DROP it as a catalog change, instantly, without touching the rows inside.

Those map exactly onto the two jobs from the start. Locality comes from choosing a key that puts one logical slice in one partition, so pruning localizes the work. Cheap removal comes from choosing a key where a slice you retire is a whole partition, so you drop it instead of deleting it. Different jobs, different keys.

Picture itThe parent table is a coat-check counter, not a closet. It holds no coats. It only knows the rule for which numbered room each ticket goes to, so it can point straight at one room and ignore the rest. Skipping partitions is reading the ticket, not the coats.

04 The three ways to split

RANGE, LIST, or HASH: the key's shape picks the type.

Postgres gives you three partitioning methods, and choosing between them is not a matter of taste. It follows from the shape of the column you split on, and from whether the slice you want to drop lines up with a single partition.

RANGE splits on contiguous, ordered ranges: a timestamp, an id band, a price bracket. A row lands in the partition whose range contains its value. This is the one for anything ordered with a retention story, because the slice you retire, last month, is a contiguous range you can drop whole.

LIST splits on an explicit set of discrete values: a region, a status, a tier, or one specific heavy tenant pinned to its own partition. A row routes by exact membership. Reach for it when the column is a small set of meaningful categories, or when you want to isolate one value so that dropping exactly that slice is a single DROP.

HASH splits on modulus and remainder over the hash of the key, with no order and no categories, just even spread. A row routes by its hash. This is the locality play: it co-locates one key's rows without you enumerating anything, at the price of not pruning ranges and not dropping a single logical value, because a bucket holds many.

Picture itRANGE is a ruler, ordered cuts along a line. LIST is a wall of labeled drawers, one named value each. HASH is a coat-check that spreads tickets evenly across rooms with no meaning to which room. Ordered data wants the ruler, categorical data wants the drawers, and shapeless data wants the coat-check.

Two differences decide most designs: what predicates let the planner prune, and whether a slice you retire is exactly one partition. Click a type below and read down its column.

Pick a type · the figure and table switch togetherrange

Figure 4 · interactiveClick a scheme. The table and DDL right below switch to it, so you watch the change as you make it. Same job, three routing rules; the trade-offs are what differ.

RANGE

LIST

HASH

Routes a row by

the range its value falls in

the set its value is listed in

hash(key) mod N

Planner prunes on

= < > BETWEEN

= IN

= only

Drop one logical slice

yes · a range, e.g. old month

yes · a value, e.g. one tenant

no · a bucket mixes values

DEFAULT partition

yes

n/a · buckets are exhaustive

Reach for it when

ordered data with retention (time-series, logs)

discrete categories, or isolating one value

even spread and locality, no natural grouping

Pruning columns are the predicate classes each method can prune on. HASH can only prune equality, which is why it buys locality but not range queries or single-value drops.

This resolves the hash caveatDropping a hash bucket takes many projects with it. If you truly need to archive one project with a single DROP, that is a job for LIST: pin the heavy project to its own partition and let the rest share a DEFAULT. Hash buys locality; LIST buys a droppable per-value slice. Real systems often nest the two, RANGE by month on top, HASH by tenant underneath.

05 Strategy A · locality

Hash by the owner key, and each tenant lands in one partition.

For per-tenant locality, hash-partition on the owner key. Every row for a given project_id hashes to the same bucket, so a project's entire history lives inside one partition. Eight buckets spreads the load and keeps any single partition a manageable slice of the whole.

Figure 5 · interactiveClick a project. Whatever it is, its whole history lands in one bucket, versus Figure 1 where it was everywhere. A vacuum or anti-join for it opens one box, not eight.

-- the partition key must be part of the primary key
CREATE TABLE claims (
  project_id  bigint NOT NULL,
  profile_id  bigint NOT NULL,
  -- ... claim columns ...
  PRIMARY KEY (project_id, profile_id)
) PARTITION BY HASH (project_id);

CREATE TABLE claims_p0 PARTITION OF claims
  FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... claims_p1 .. claims_p7 ...

Now the payoff. The dedup anti-join for project 42 runs entirely inside that project's one partition, because every row it compares shares the same project_id. Autovacuum works one leaf at a time, so a project's dead tuples and index churn stay isolated from the other seven. The cache stays warm on the buckets you are actually touching.

Picture itA filing cabinet with eight drawers and one filing rule. Because the clerk always files a project by the same rule, one project's papers always end up together in a single drawer. You never search all eight to find one project.

On removal with hashHash gives you locality, not single-project archival. One project sits in one partition, but that partition also holds many other projects, so dropping it removes a whole bucket at once. That is fine for coarse bulk removal. To retire one slice at a time by DROP, make that slice its own partition: range by time so an old month is one partition (chapter 07), or list so one tenant is one partition (chapter 04).

06 The live knob

Watch the planner skip seven of eight partitions.

Here is pruning you can operate. Pick an operation, then drag the project id. The eight boxes are the hash partitions of claims. Lit means the operation touches it; dimmed means the planner proved it cannot match and skipped it before reading a page.

The lesson is in one contrast. Every operation that names project_id collapses to a single partition. The one operation that does not name it lights all eight. The predicate is what lets the planner prune, and pruning is the entire locality win.

Live · partition pruning (8 hash buckets)SELECT one project

Figure 6 · liveDriven by the controls right below. Change the operation or drag the project id and watch the query, the hash, and which partitions light up. Hover any partition to see what it holds.

project_id42

touches 1 / 8

The bucket a project lands in is shown illustratively as project_id % 8; the real server uses Postgres' hash function, but the behavior, one project to one partition, is exactly this.

Picture itA coat-check where your ticket number tells the clerk exactly which of eight rooms your coat is in. They walk to that room. They do not check the other seven, because the number already ruled them out. The predicate is that ticket.

Name the partition key in your predicate and eight partitions become one. Forget to, and you scan them all.

07 Strategy B · retention

Range-partition the log by time, and old months just drop.

An append-only log wants a different key. Partition claim_events by range on its timestamp, one partition per month, plus a DEFAULT to catch anything that has no month partition yet. Now retention is not a query at all. Scroll through what a month's life looks like.

Picture itA shelf of ring binders, one per month, left to right. New pages go in the rightmost binder. When a month is too old to keep, you lift its whole binder off the shelf. You never re-file the pages inside it.

Figure 7 · interactiveClick any old month to retire it. Writes only touch the current month; retention only unlinks the oldest, a metadata-only DROP. The DEFAULT catches any date without a month yet.

claim_events · partition by range (at)

2026_01

2026_02

2026_03

2026_04

2026_05

2026_06

2026_07

DEFAULT

Step 1

One partition per month.

Each month of events is its own child table. The parent routes a row by its at timestamp. Seven months here, and a DEFAULT at the bottom.

Step 2

New events land in the current month.

Writes for July go straight to claim_events_2026_07. That is the only hot partition. Every insert, index update, and the vacuum that follows stay confined to it.

Step 3

DEFAULT catches what has no home yet.

An event for a month you have not created a partition for still inserts, into DEFAULT, instead of erroring. It is the safety net that keeps writes flowing while you provision months ahead.

Step 4

Retention is a DROP, not a DELETE.

January ages out. You DETACH then DROP the month, a catalog change that unlinks the file. No scan, no dead tuples, no vacuum. The hot partition never even notices.

CREATE TABLE claim_events (
  id  bigint GENERATED ALWAYS AS IDENTITY,
  at  timestamptz NOT NULL,
  -- ... event columns ...
  PRIMARY KEY (id, at)          -- at joins the PK: it is the partition key
) PARTITION BY RANGE (at);

CREATE TABLE claim_events_2026_07 PARTITION OF claim_events
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE claim_events_default PARTITION OF claim_events DEFAULT;

-- retention: instant, metadata-only
ALTER TABLE claim_events DETACH PARTITION claim_events_2026_01;
DROP TABLE claim_events_2026_01;

The live table never bloats, because the writes and the deletes never share a partition. July grows; January leaves. The hot path and the retention path are physically separate slices, so neither one ever waits on the other.

08 The mechanism behind the win

Removal becomes unlinking a file, not deleting a million rows.

Both of these get rid of a month of events. They are not the same size of operation, not even close. Flip between them and read what each one actually asks the database to do.

DELETE vs DROP · click a side, the panel switchesDELETE

Figure 8 · interactiveClick the DELETE side or the DROP side and the panel right below switches to match. DELETE is proportional to the rows removed; DROP is a fixed catalog edit that unlinks the whole file.

Cost bars are qualitative, not benchmarked. The point is the shape: DELETE is work proportional to rows, DROP is a fixed catalog change.

This is why the partition key on the log is chosen so a retention slice is exactly one partition. When the thing you retire lines up with a partition boundary, removal stops being data work and becomes bookkeeping. That is the cheap bulk removal the whole design was reaching for.

Picture itShredding a folder means feeding every page through the shredder, then sweeping up. Recycling the folder means dropping it in the bin. DELETE shreds; DROP bins.

DETACH first, then DROPDetaching removes the partition from the parent as a quick catalog step and lets you keep the table around, to archive to cold storage or inspect, before you DROP it for good. On busy tables prefer DETACH PARTITION … CONCURRENTLY so you do not hold a heavy lock on the parent.

10 When to reach for it

Partition for a slice that grows huge or needs cheap removal. Not before.

Partitioning earns its keep in exactly the two cases we started with. One logical slice grows large enough that per-slice maintenance drags on everyone: hash it out so the work localizes. Or a table accumulates and needs bulk removal on a schedule: range it by that dimension so retiring a slice is a DROP. If neither is true, a well-indexed heap is simpler and usually faster, and every partition you add is another object to plan, vacuum, and reason about.

Match the key to the job. Hash on the owner for locality. Range on time for retention. List when the column is a handful of categories, or when one slice has to be droppable on its own. Keep the partition key in the primary key, set storage params on the leaves, keep a DEFAULT so writes never stall, and validate the constraints your version allows. Get those right and the giant DELETE, and the whole-table vacuum behind it, simply stop being part of your week.

Picture itSorting mail: by date into a calendar, by country into pigeonholes, or by a cloakroom number when you only need it spread evenly. The sort you choose is dictated by what you will later go looking for. Pick the key by the query, not by habit.

Figure 10The decision is mechanical once you name the key's shape. Ordered wants RANGE, a small set of values wants LIST, no natural grouping wants HASH. No pressing slice at all wants a plain heap.

One slice grew huge.
Don't scan the whole table to fix it.