A migration checklist for Postgres partitioning

The largest of the three was about 1.8 TB and four billion rows. The steps assume Postgres 13 or newer; a couple of them are more pleasant on 15+. The example table is events, partitioned by month on created_at.

0. Be sure you need it

Partitioning buys you three things: dropping old data is a DROP TABLE on one partition instead of a DELETE that generates WAL and bloat for hours; indexes are per partition, so they stay small and mostly in cache; and time-bounded queries can skip partitions entirely. It does not buy you faster lookups by id. Those get slightly slower, because unless the query mentions the partition key the planner has to look in every partition. If your table is large but you never delete and every query is by primary key, a BRIN index on the timestamp may be all you wanted.

1. Pick the key and accept what it does to your constraints

Every unique constraint, including the primary key, must include the partition key. PRIMARY KEY (id) becomes PRIMARY KEY (id, created_at). Anything that relied on id alone being unique has to change: ON CONFLICT (id) in application code, foreign keys from other tables pointing at id, ORMs that introspect the primary key and assume it's one column. Grep the codebase for ON CONFLICT before you start, not after.

2. Build the new table next to the old one

CREATE TABLE events_p (
  id          bigint      NOT NULL,
  created_at  timestamptz NOT NULL,
  customer_id bigint      NOT NULL,
  kind        text        NOT NULL,
  payload     jsonb,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_p_2026_03 PARTITION OF events_p
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- one per month back to the oldest row, plus three months ahead

Don't add a DEFAULT partition unless you have a plan to get rid of it. While one exists, attaching a new partition has to scan the default to prove none of its rows belong in the new range, under an ACCESS EXCLUSIVE lock on the default. Create partitions ahead of time instead, and alert when the next one is missing (step 8).

3. Keep the two tables in sync while you copy

A row-level trigger on the old table that mirrors every write into the new one. It's boring and it works, and unlike logical replication it doesn't need a publication, a slot, or anyone's permission.

CREATE OR REPLACE FUNCTION events_mirror() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    DELETE FROM events_p
     WHERE id = OLD.id AND created_at = OLD.created_at;
    RETURN OLD;
  END IF;
  INSERT INTO events_p SELECT NEW.*
  ON CONFLICT (id, created_at) DO UPDATE
    SET customer_id = EXCLUDED.customer_id,
        kind        = EXCLUDED.kind,
        payload     = EXCLUDED.payload;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER events_mirror
  AFTER INSERT OR UPDATE OR DELETE ON events
  FOR EACH ROW EXECUTE FUNCTION events_mirror();

SELECT NEW.* requires identical column order in both tables. Check it; a dropped-and-re-added column on the old table will bite you here.

4. Copy in batches, by the partition key

One day at a time: INSERT INTO events_p SELECT * FROM events WHERE created_at >= $1 AND created_at < $2 ON CONFLICT DO NOTHING. The ON CONFLICT DO NOTHING is what lets the trigger and the copy coexist. Watch replication lag on the replicas while this runs; it generates a lot of WAL. I slept between batches whenever lag went over thirty seconds. The 1.8 TB table took about forty hours that way, which is fine, because nobody is waiting for it.

5. Indexes: after the copy, and concurrently

CREATE INDEX on a partitioned table builds the index on every partition, but it can't be CONCURRENTLY, and it takes a SHARE lock on each partition for the duration. On a live table, the dance is:

-- parent index, marked invalid until every partition has one attached
CREATE INDEX events_p_customer_idx
  ON ONLY events_p (customer_id, created_at);

-- for each partition:
CREATE INDEX CONCURRENTLY events_p_2026_03_customer_idx
  ON events_p_2026_03 (customer_id, created_at);
ALTER INDEX events_p_customer_idx
  ATTACH PARTITION events_p_2026_03_customer_idx;

Script it. Forty months is forty pairs of statements and you'll want to run them overnight.

6. The swap

SET lock_timeout = '3s';
BEGIN;
ALTER TABLE events   RENAME TO events_old;
ALTER TABLE events_p RENAME TO events;
ALTER SEQUENCE events_id_seq OWNED BY events.id;
ALTER TABLE events ALTER COLUMN id
  SET DEFAULT nextval('events_id_seq');
COMMIT;

The lock_timeout is not optional. RENAME needs ACCESS EXCLUSIVE; it will queue behind any long-running SELECT, and every query that arrives after it queues behind it. With a timeout, a failed attempt costs three seconds. Without one, a failed attempt is an incident. Retry in a loop until it goes through, then drop the mirror trigger and its function.

7. Check that pruning actually happens

Run the top queries with EXPLAIN and confirm the plan touches only the partitions it should. Pruning needs the partition key in the WHERE clause, compared with something the planner can evaluate: a constant, a parameter, or a stable function. created_at >= now() - interval '7 days' prunes at execution time and is fine. created_at::date = '2026-03-01' does not prune, because the cast hides the column. WHERE id = 42 with no timestamp walks every partition's primary key index; that was the conversation in step 1.

8. Automate new partitions and alert on their absence

A daily job that creates partitions three months out, and a check that fails if now() + interval '1 month' has nowhere to go. An insert into a range with no partition is an error, and it will happen at 00:00 on the first of the month, which is not when you want to learn this. pg_partman does all of it if you'd rather not; I've used both and have no strong view.

9. Drop the old table when you're bored of it

A week is plenty. DROP TABLE events_old is instant and hands back 1.8 TB in one statement, which is a satisfying way to end.