Nuruzzaman Milon

Writing

Database partitioning

2026 · August · 21

I have watched a perfectly normal events table become the thing nobody wants to touch. Inserts still work. A dashboard that only needs last week still takes seconds. Autovacuum runs all afternoon and somehow never catches up. The index that used to sit in memory does not anymore.

The usual reaction is to buy a bigger box, or to start talking about sharding. Most of the time you do not need another server. You need the table cut into pieces the planner can skip, and that you can throw away when a month goes cold.

That is partitioning. Same table name. Same INSERT INTO events. Underneath, Postgres keeps several real tables and puts each row in one of them, based on a key you pick.

It is not sharding. Sharding is several databases. Partitioning is still one Postgres, one WAL, one failover. You paid for smaller heaps to vacuum, index, scan, and drop.

One table, several heaps

In Postgres this is declarative partitioning. You create a parent that does not hold rows, then children that do. Every row belongs to exactly one child. The partition key is how Postgres decides which one.

That key has to show up in the primary key, and in unique constraints. Uniqueness is enforced on each child, not across the whole family. That is the first thing that bites people, so I will say it twice later with SQL.

There are three kinds you will see:

  • RANGE for contiguous slices: dates, timestamps, sometimes integer ids. Event logs, orders, anything you age out. This is the one I actually use.
  • LIST for a small set of labels: region IN ('us', 'eu').
  • HASH when there is no natural order and you just want the piles to be about the same size.

I am going to spend the rest of the post on RANGE, then dump LIST and HASH at the end so the syntax lives in one place.

A table you can actually run

Imagine application events. Almost every query is "this user, in this window." Last month still gets a look. Last year does not. That is a RANGE key on created_at.

CREATE TABLE events (
    id          bigint GENERATED BY DEFAULT AS IDENTITY,
    user_id     bigint NOT NULL,
    kind        text NOT NULL,
    payload     jsonb NOT NULL,
    created_at  timestamptz NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

The parent is empty on purpose. PARTITION BY RANGE (created_at) is the rule: each child owns a slice of that column. The primary key includes created_at because Postgres will not let you use (id) alone on a partitioned table. I will come back to why.

Now the children. Each one is a normal table with a bound:

CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

That is the starting set, not a ritual you repeat by hand on the first of every month. Postgres will not invent next month's child for you. I will come back to how you automate that.

The range is half-open: [from, to). A timestamp of 2026-08-01 00:00:00 goes into events_2026_08, not July. Neighbors must not overlap. A gap is allowed. An insert that lands in a gap errors out, unless you created a default partition.

Put indexes on the parent. Postgres builds the matching index on every child:

CREATE INDEX events_user_created_idx
    ON events (user_id, created_at);

Application code talks to events. It should not name the children.

INSERT INTO events (user_id, kind, payload, created_at)
VALUES (42, 'login', '{}', '2026-08-21 17:02:00+00');

SELECT kind, created_at
FROM events
WHERE user_id = 42
  AND created_at >= '2026-08-01'
  AND created_at <  '2026-09-01';

That insert goes to events_2026_08 because of the timestamp. The select can ignore July and September. The ignoring is the feature. Postgres calls it partition pruning.

How a row finds its partition

On insert, Postgres looks at the partition key and picks the child whose bounds contain it. You did not write a trigger for this. It is in the catalog. If nothing matches:

ERROR:  no partition of relation "events" found for row

You can add a default partition that catches leftovers:

CREATE TABLE events_default PARTITION OF events DEFAULT;

I have used that as a safety net. I do not use it as a plan. Rows that land there never get pruned the way you hoped, and fishing them out later is a rewrite. I would rather let the insert fail, and have a job that creates next month's child a week early.

On select, the planner looks at your WHERE clause. Given created_at >= '2026-08-01' AND created_at < '2026-09-01', it can prove July and September cannot possibly match, so it never opens those files. EXPLAIN makes this obvious:

EXPLAIN (COSTS OFF)
SELECT kind FROM events
WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-09-01';

Append
  ->  Seq Scan on events_2026_08 events
        Filter: ((created_at >= '2026-08-01 00:00:00+00')
             AND (created_at <  '2026-09-01 00:00:00+00'))

One child. If you leave the key out of the WHERE clause, pruning does not happen, and you scan every partition. Partitioning will not make "all events for this user, ever" faster. It can make that query slower: more files, more indexes, a fatter plan.

There is also runtime pruning. A bound that is only known when the query runs, or a join to a small table of dates, can still drop children when execution starts. The rule does not change. The key still has to be constrained.

Detach is the fast delete

The select is nice. The thing I actually wanted, most times I reached for this, was deleting history without scanning a billion-row heap.

ALTER TABLE events DETACH PARTITION events_2026_07;
DROP TABLE events_2026_07;

DETACH takes a short lock and unhooks the child. DROP TABLE then removes that month's data and indexes in one go. Compare that with DELETE FROM events WHERE created_at < …. That version writes a tombstone for every row and leaves vacuum working through the mess for days.

Going the other way, you can load a table on the side and attach it when it is ready, so the parent is not blocked for the whole backfill:

CREATE TABLE events_2026_06 (
    LIKE events INCLUDING DEFAULTS INCLUDING CONSTRAINTS
);

-- load it however you load things, then:

ALTER TABLE events_2026_06
    ADD CONSTRAINT events_2026_06_range
    CHECK (created_at >= '2026-06-01' AND created_at < '2026-07-01')
    NOT VALID;

ALTER TABLE events ATTACH PARTITION events_2026_06
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

ATTACH will scan the child to prove every row belongs, unless a matching CHECK is already there. If the table is large, add the constraint first. NOT VALID skips the scan when you create the constraint. ATTACH still checks. You just get to pick when.

You do not create next month by hand

RANGE on a clock is the one that needs a calendar. LIST and HASH you create once. Declarative partitioning only routes into children that already exist. If August is missing, an August insert fails. Postgres will not grow a new slice for you.

You also do not need to sit down on the first and type CREATE TABLE. Create a couple of months up front, then let a job stay ahead of the clock.

What I actually run is a function that is happy to be a no-op if the child is already there, plus a weekly schedule that asks for this month and next month:

CREATE OR REPLACE FUNCTION create_events_partition(month_start date)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    child text := format('events_%s', to_char(month_start, 'YYYY_MM'));
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF events
         FOR VALUES FROM (%L) TO (%L)',
        child,
        month_start,
        (month_start + interval '1 month')::date
    );
END;
$$;

SELECT create_events_partition(date_trunc('month', now())::date);
SELECT create_events_partition(
    (date_trunc('month', now()) + interval '1 month')::date
);

Two months of headroom is enough. The painful case is midnight on the first with no child, which is why you create next month while you still have this one. I have been on that page. I did not enjoy it.

To run that inside Postgres, use pg_cron. It needs shared_preload_libraries = 'pg_cron' and a restart, then:

CREATE EXTENSION pg_cron;

SELECT cron.schedule(
    'create-events-partitions',
    '0 3 * * 0',
    $$
    SELECT create_events_partition(date_trunc('month', now())::date);
    SELECT create_events_partition(
        (date_trunc('month', now()) + interval '1 month')::date
    );
    $$
);

That is 03:00 every Sunday. cron.schedule returns a job id. SELECT * FROM cron.job; is how you check it is there. SELECT cron.unschedule('create-events-partitions'); is how you take it off. The job runs in the database where you created the extension, so create pg_cron in the same database as events.

If you do not want to own the function, pg_partman is the usual extension. You still create the partitioned parent yourself. Then you hand it the key, the interval, and how many future children to keep ready. The SQL below is current pg_partman (5.x). Version 4 used create_parent and interval names like monthly. Those are gone, so an old tutorial will not run.

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION pg_partman SCHEMA partman;

SELECT partman.create_partition(
    p_parent_table := 'public.events',
    p_control      := 'created_at',
    p_interval     := '1 month',
    p_premake      := 2
);

p_premake := 2 means keep two months ahead of now. Retention is a row in partman.part_config, not a second function. This keeps a year and drops the rest:

UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

retention_keep_table = false means drop, not detach-and-keep. Use true if you still want the old child around as a plain table.

pg_partman does not wake itself up unless you run its background worker. Pairing it with pg_cron is the setup I see most:

SELECT cron.schedule(
    'partman-maintenance',
    '0 3 * * *',
    $$CALL partman.run_maintenance_proc()$$
);

That nightly call creates the next children up to premake, and drops anything past retention. That is the production answer when you have more than one partitioned table and you are tired of copying the same function.

A default partition is a safety net, not an auto-partitioner. Rows that miss a monthly child land in one pile that never prunes, and you have to split them out later. I would rather the insert fail and the job be a week early.

A trigger that CREATE TABLE on the first insert of a new month can work. It also races, takes locks, and surprises the planner. I would not do that.

LIST and HASH, briefly

LIST is RANGE with named buckets instead of bounds.

CREATE TABLE invoices (
    id       bigint GENERATED BY DEFAULT AS IDENTITY,
    region   text NOT NULL,
    total    numeric NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE invoices_us PARTITION OF invoices FOR VALUES IN ('us');
CREATE TABLE invoices_eu PARTITION OF invoices FOR VALUES IN ('eu');
CREATE TABLE invoices_other PARTITION OF invoices DEFAULT;

WHERE region = 'us' reads one child. Leave region out and you read all of them. Same pruning rule as RANGE.

HASH is for when the key has no useful order and you want even size. Postgres hashes the key and takes modulo N.

CREATE TABLE sessions (
    id         uuid NOT NULL,
    user_id    bigint NOT NULL,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (id)
) PARTITION BY HASH (id);

CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Every child has to use the same modulus. Adding another remainder later means rewriting the set. HASH can help autovacuum and index bloat because no single heap grows without bound. It will not help a time-range query, because time is not the key. I use it less than RANGE. I do not use it as a fake shard key I have not thought through.

The bits that surprise people

  • The partition key has to be in the primary key. This is the first error you will hit. It also changes what id means. If id is not the partition key, it is not unique by itself. Two children can both have id = 1 unless you used one sequence and still put the key in the PK. In the events example, IDENTITY still hands out one sequence. The extra column is there for the constraint, not because we needed two ways to generate ids.

  • Unique indexes are per partition. UNIQUE (user_id) on the parent does not mean one user in the whole system, unless user_id is the partition key. "This email is unique" is not something partitioning will do for you. Keep a small unpartitioned lookup table, or enforce it in the app.

  • Other tables can foreign-key to a partitioned parent in current Postgres. The PK still has to match, which means the key is in it. Pointing a child at a parent is fine. Pointing a parent at a child of something else is the design I draw on paper twice before I ship it.

  • An update that changes the partition key moves the row. Postgres deletes from one child and inserts into another. Triggers fire. It is not free. If created_at never changes in your domain, keep it that way. A column people go back and correct is a bad RANGE key.

  • Too many partitions has a cost. The planner has to consider children. A few hundred is normal. Tens of thousands of daily partitions, on a table you query without a tight bound, will make planning itself slow. Monthly RANGE for a log that spans a few years is the version that stays quiet.

What I actually partition

Append-only data with a clock. Events, impressions, audit rows, webhook deliveries. The query always has a time window. Old slices get DETACH, then maybe object storage, or just DROP.

A table that is large because it is the product, and every query is "this id" with no extra filter: I leave it. A better index, a partial index, FILLFACTOR, or a second table you archive into will do more than RANGE on a column nobody puts in the WHERE clause.

I do not partition because a dashboard showed a large row count. I partition when vacuum, bloat, or deletes have a calendar, and when the key behind that calendar is in the queries I care about.

The part I want you to remember

Partitioning is a routing rule and a pruning rule. It is not a new kind of database. You pick a key. Postgres stores each slice as its own table. Inserts go to one child. Selects skip the children the WHERE clause cannot match. Drops become detach plus drop, instead of a million-row DELETE.

The key has to be in the primary key, in the writes, and in the reads you hope to make cheap. Miss any of those three and you have more tables, the same sequential scan, and a migration to undo. Get them right and last month's events are a table you can pick up and throw away.

#databases

← Back to Writing