---
title: "How to partition a 1 TB PostgreSQL table without downtime: the default-partition method"
description: "Partition a live 1 TB Postgres table without downtime: attach it as the DEFAULT partition, copy history out with Oban jobs, then swap in one short transaction."
author: Federico Meini
date: 2026-08-18
tags: [postgresql, partitioning, performance, elixir, oban]
language: en
url: https://fedme.dev/blog/partitioning-a-1tb-postgres-table-without-downtime
---

# How to partition a 1 TB PostgreSQL table without downtime: the default-partition method

To partition a huge, live PostgreSQL table without downtime, make the existing table the DEFAULT partition of a new partitioned parent in one short, catalogue-only transaction, send new writes to real time-range partitions straight away, and move the history out of the default in the background. At Turn.io I partitioned production tables of more than 1 TB this way to get performance back, as message data grew by millions of rows a day: the old table became the default partition, and Oban jobs moved its data into dedicated partitions gradually.

## What I did at Turn.io

The migration I ran at Turn.io was very similar to the approach in this post:

- **The old table became the default partition,** and new data went straight into proper time-range partitions.
- **No pg_partman.** Creating partitions ahead of time and moving the history were plain Oban jobs in our Elixir codebase.
- **Oban jobs copied the history into new tables in the background,** and each finished table was attached as a partition in one atomic operation, so readers never saw a gap or a duplicate.
- **CHECK constraints did most of the heavy lifting.** I added a lot of them `NOT VALID` and validated them afterwards with `VALIDATE CONSTRAINT`, which only takes a `SHARE UPDATE EXCLUSIVE` lock. That way Postgres could trust the ranges on attach instead of scanning terabytes under a blocking lock.
- **Foreign keys were dropped for the duration of the move.** Partitioning forces foreign keys to include the partition key, and they get in the way of attaching and detaching. We already had application-level checks and triggers enforcing the same relationships, so removing the constraints for the migration was a safe trade.

Below is how I'd run it today, rebuilt on PostgreSQL 18.6 with a 4-million-row `messages` table under constant pgbench load. Timings are from that laptop test. For keys, pruning, indexes and retention, see [PostgreSQL partitioning in practice](https://fedme.dev/blog/postgresql-partitioning-in-practice).

## TL;DR

- **Prep online:** unique index on `(id, inserted_at)`, matching indexes, no incoming foreign keys, and a validated `CHECK (inserted_at < boundary)`.
- **Cutover:** one transaction attaches the old table `AS DEFAULT`. Milliseconds.
- **New writes:** the CHECK lets Postgres skip scanning the default when you add partitions.
- **Backfill:** moving one range at a time out of a big default scans the whole default under `ACCESS EXCLUSIVE`. Instead, copy history into unattached partitions with Oban, sync them with a trigger, and swap once (about 50 ms of locking).

## Step 1: prepare the old table while it's live

**The partition key must be in every primary key and unique constraint**, so build the future primary key's index first:

```elixir
defmodule MyApp.Repo.Migrations.AddMessagesIdInsertedAtIndex do
  use Ecto.Migration

  @disable_ddl_transaction true
  @disable_migration_lock true

  def change do
    create unique_index(:messages, [:id, :inserted_at], concurrently: true)
  end
end
```

**Match the indexes.** On attach, Postgres adopts an equivalent index for each parent index and builds missing ones under the lock, so every parent index must already exist on the old table.

**Add the boundary CHECK**, at a month start a few days ahead. Without it, every partition created while the old table is the default scans all of it. `NOT VALID` makes adding it instant; validating takes `SHARE UPDATE EXCLUSIVE`, which doesn't block reads or writes (617 ms on 700 MB):

```elixir
defmodule MyApp.Repo.Migrations.AddMessagesLegacyRangeCheck do
  use Ecto.Migration

  def change do
    create constraint(:messages, :messages_legacy_before_2026_10,
             check: "inserted_at < '2026-10-01 00:00:00+00'",
             validate: false
           )
  end
end
```

Then, in a separate migration: `execute "ALTER TABLE messages VALIDATE CONSTRAINT messages_legacy_before_2026_10"`.

**Incoming foreign keys.** At Turn.io we dropped foreign keys for the duration of the migration, because application-level checks and triggers already enforced the same relationships. If you can't make that trade, plan carefully. Postgres will attach a referenced table, but its foreign keys keep pointing at that one partition and block dropping the old primary key. Drop them and enforce the relationship in code, or add the timestamp to the referencing table for a composite foreign key to the new parent (PostgreSQL 12+).

**Sequences and identity.** `LIKE ... INCLUDING DEFAULTS` copies Ecto's `nextval('messages_id_seq')` default; move the sequence's ownership, or dropping the old table drops it. PostgreSQL 18 won't attach a table with its own identity column: drop it on the old table during the cutover and `RESTART` the parent's above `max(id)`, as [pg_partman's guide](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto.md) shows.

## Step 2: the cutover transaction

```sql
BEGIN;
SET LOCAL lock_timeout = '2s';
LOCK TABLE messages IN ACCESS EXCLUSIVE MODE;

ALTER TABLE messages RENAME TO messages_legacy;
ALTER TABLE messages_legacy
  DROP CONSTRAINT messages_pkey,
  ADD CONSTRAINT messages_legacy_pkey PRIMARY KEY USING INDEX messages_id_inserted_at_index;
ALTER INDEX messages_contact_id_inserted_at_index RENAME TO messages_legacy_contact_id_inserted_at_index;
ALTER INDEX messages_inserted_at_index RENAME TO messages_legacy_inserted_at_index;
ALTER TABLE messages_legacy RENAME CONSTRAINT messages_contact_id_fkey TO messages_legacy_contact_id_fkey;

CREATE TABLE messages (LIKE messages_legacy INCLUDING DEFAULTS) PARTITION BY RANGE (inserted_at);
ALTER TABLE messages ADD CONSTRAINT messages_pkey PRIMARY KEY (id, inserted_at);
CREATE INDEX messages_contact_id_inserted_at_index ON messages (contact_id, inserted_at);
CREATE INDEX messages_inserted_at_index ON messages (inserted_at);
ALTER TABLE messages ADD CONSTRAINT messages_contact_id_fkey
  FOREIGN KEY (contact_id) REFERENCES contacts (id);

ALTER TABLE messages ATTACH PARTITION messages_legacy DEFAULT;
ALTER SEQUENCE messages_id_seq OWNED BY messages.id;

CREATE TABLE messages_p2026_10 PARTITION OF messages
  FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');
COMMIT;
```

Skip `INCLUDING CONSTRAINTS`, or the boundary CHECK lands on the parent. (I use `timestamptz`; with Ecto's default `timestamp` columns, drop the `+00` from bounds.) The primary key swap is required: otherwise the attach fails with "multiple primary keys for table "messages_legacy" are not allowed".

**Why attaching as DEFAULT is cheap.** A default partition holds whatever no other partition takes. With no other partitions there's nothing to validate: 1.1 ms. Creating `messages_p2026_10` would normally scan the default, but the CHECK rules October out; with `client_min_messages = debug1` Postgres logs "updated partition constraint for default partition "messages_legacy" is implied by existing constraints".

**Locks.** Held until `COMMIT`: `ACCESS EXCLUSIVE` on the old table, the new parent and partition, and, to my surprise, on `contacts`: on PostgreSQL 18, attaching a table with its own foreign key locks the referenced table too. The Ecto migration took 70 to 140 ms, and pgbench clients using prepared statements, like Postgrex, saw no failures. Keep `lock_timeout`: behind a long query your `LOCK TABLE` waits, and every new query waits behind you.

## Step 3: route new writes to real partitions

From the boundary on, rows land in real partitions. Keep a few months ahead, never for a range still in the default:

```elixir
defmodule MyApp.Workers.CreateMessagePartitions do
  use Oban.Worker, queue: :maintenance, max_attempts: 5

  alias MyApp.Repo

  @impl Oban.Worker
  def perform(%Oban.Job{}) do
    # From next month on: earlier runs created the current one.
    next_month(Date.utc_today())
    |> Stream.iterate(&next_month/1)
    |> Enum.take(3)
    |> Enum.each(&ensure_partition/1)
  end

  defp ensure_partition(from) do
    name = "messages_p" <> Calendar.strftime(from, "%Y_%m")

    {:ok, _} =
      Repo.transaction(fn ->
        Repo.query!("SET LOCAL lock_timeout = '2s'")

        if Repo.query!("SELECT to_regclass($1)", [name]).rows == [[nil]] do
          Repo.query!("CREATE TABLE #{name} (LIKE messages INCLUDING DEFAULTS)")

          Repo.query!("""
          ALTER TABLE messages ATTACH PARTITION #{name}
            FOR VALUES FROM ('#{from} 00:00:00+00') TO ('#{next_month(from)} 00:00:00+00')
          """)
        end
      end)
  end

  defp next_month(date), do: date |> Date.end_of_month() |> Date.add(1)
end
```

Run it daily with `Oban.Plugins.Cron`, or use pg_partman's `run_maintenance()`. `ATTACH` needs only `SHARE UPDATE EXCLUSIVE` on the parent, unlike `CREATE TABLE ... PARTITION OF` ([docs](https://www.postgresql.org/docs/current/sql-createtable.html)), but still locks a default partition exclusively, scan or not. With the CHECK, a new partition took 2 ms; without it, 700 ms, growing with the default.

## Step 4: move the history out of the default

### What pg_partman does

pg_partman's [online partitioning guide](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto.md) uses this same setup, then `partition_data_proc()`. Each batch is one transaction: `DELETE ... RETURNING` a whole child interval from the default into a temporary table, create and `ATTACH` the child, insert the rows back. Batches can't be smaller than the partition interval ("Custom intervals are not allowed when moving data out of the DEFAULT partition"), since the child can't exist while its rows are in the default. It commits per batch, can pause between batches (`p_wait`), and can lock rows first (`p_lock_wait`).

With pg_partman 5.5.0, moving one month (248k rows) took 7 seconds, and readers touching the default were blocked for about 2.5 of them: the attach locks the default, scans it all, and holds the lock through the insert. With concurrent updates to old rows, my first try deadlocked. [Crunchy Data](https://www.crunchydata.com/blog/postgres-partitioning-with-a-default-partition) and pg_partman's docs both say to keep the default small. On a terabyte, that scan repeats every month.

### Why tightening a CHECK doesn't help

The tempting fix: copy a month into a detached table, then delete it from the default and attach the copy in one short transaction, oldest first, with a CHECK on the default ruling the range out. But the attach needs proof: a scan, or a *validated* CHECK (`NOT VALID` doesn't count; I checked), which can only be validated once the rows are gone. In the same transaction that's a full scan under the lock; in a separate one, the rows are briefly visible nowhere.

### Copy out, capture changes, swap once

So never ask Postgres to prove anything about the big heap. Oban jobs copy history into unattached partitions, a trigger keeps them in sync, and one transaction detaches the old default and attaches everything. Each partition's CHECK skips its validation, and with no default there's no default scan. It's the trigger-plus-background-copy idea of [GitLab's partitioning helpers](https://docs.gitlab.com/development/database/partitioning/date_range/), applied only to the history.

```sql
CREATE TABLE messages_backfill (LIKE messages INCLUDING DEFAULTS) PARTITION BY RANGE (inserted_at);
ALTER TABLE messages_backfill ADD PRIMARY KEY (id, inserted_at);

-- One per month; the oldest uses FROM (MINVALUE) and CHECK (inserted_at < ...).
CREATE TABLE messages_p2026_03 (LIKE messages INCLUDING DEFAULTS);
ALTER TABLE messages_p2026_03 ADD CONSTRAINT messages_p2026_03_bounds
  CHECK (inserted_at >= '2026-03-01 00:00:00+00' AND inserted_at < '2026-04-01 00:00:00+00');
ALTER TABLE messages_backfill ATTACH PARTITION messages_p2026_03
  FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE messages_backfill_changes (
  seq bigserial PRIMARY KEY, id bigint NOT NULL, inserted_at timestamptz NOT NULL
);

CREATE FUNCTION messages_backfill_capture() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    INSERT INTO messages_backfill_changes (id, inserted_at) VALUES (OLD.id, OLD.inserted_at);
  END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    INSERT INTO messages_backfill_changes (id, inserted_at) VALUES (NEW.id, NEW.inserted_at);
  END IF;
  RETURN NULL;
END $$;

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

CREATE TABLE messages_backfill_progress (
  id int PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  last_id bigint NOT NULL DEFAULT 0,
  rows_copied bigint NOT NULL DEFAULT 0,
  changes_applied bigint NOT NULL DEFAULT 0,
  target_max_id bigint
);
INSERT INTO messages_backfill_progress (target_max_id) SELECT max(id) FROM messages_legacy;
```

Create the trigger before the first copy. Two functions do the work:

```sql
CREATE FUNCTION messages_backfill_copy_batch(batch_size int) RETURNS int
LANGUAGE sql AS $$
  WITH batch AS (
    SELECT id, contact_id, direction, status, body, inserted_at, updated_at
    FROM messages_legacy
    WHERE id > (SELECT last_id FROM messages_backfill_progress)
    ORDER BY id
    LIMIT batch_size
  ), copied AS (
    INSERT INTO messages_backfill (id, contact_id, direction, status, body, inserted_at, updated_at)
    SELECT * FROM batch
    ON CONFLICT (id, inserted_at) DO NOTHING
  )
  UPDATE messages_backfill_progress
  SET last_id = coalesce((SELECT max(id) FROM batch), last_id),
      rows_copied = rows_copied + (SELECT count(*) FROM batch)
  RETURNING (SELECT count(*) FROM batch)::int;
$$;

CREATE FUNCTION messages_backfill_apply_changes(batch_size int) RETURNS int
LANGUAGE sql
-- The planner can't estimate CTE sizes and may hash-join against a full scan.
SET enable_hashjoin = off
SET enable_mergejoin = off
AS $$
  WITH claimed AS (
    DELETE FROM messages_backfill_changes
    WHERE seq IN (SELECT seq FROM messages_backfill_changes ORDER BY seq LIMIT batch_size)
    RETURNING id, inserted_at
  ), keys AS (
    SELECT DISTINCT id, inserted_at FROM claimed
  ), removed AS (
    DELETE FROM messages_backfill b USING keys k
    WHERE b.id = k.id AND b.inserted_at = k.inserted_at
      AND NOT EXISTS (SELECT 1 FROM messages_legacy m
                      WHERE m.id = k.id AND m.inserted_at = k.inserted_at)
  ), upserted AS (
    INSERT INTO messages_backfill (id, contact_id, direction, status, body, inserted_at, updated_at)
    SELECT m.id, m.contact_id, m.direction, m.status, m.body, m.inserted_at, m.updated_at
    FROM messages_legacy m JOIN keys k ON m.id = k.id AND m.inserted_at = k.inserted_at
    ON CONFLICT (id, inserted_at) DO UPDATE
    SET contact_id = EXCLUDED.contact_id, direction = EXCLUDED.direction,
        status = EXCLUDED.status, body = EXCLUDED.body, updated_at = EXCLUDED.updated_at
  )
  UPDATE messages_backfill_progress
  SET changes_applied = changes_applied + (SELECT count(*) FROM claimed)
  RETURNING (SELECT count(*) FROM claimed)::int;
$$;
```

Both are idempotent: the copy advances its cursor in the same transaction and skips existing rows; the replay copies each changed row as it is *now*, or removes it. Without the `SET` lines, replaying 10,000 changes took 2 to 4 seconds (the plan scanned every staging partition); with them, 0.3 seconds, whatever the table size.

The worker runs one batch per execution and snoozes until the swap drops its tables:

```elixir
defmodule MyApp.Workers.MessagesBackfill do
  use Oban.Worker,
    queue: :partition_backfill,
    max_attempts: 20,
    unique: [period: :infinity, states: :incomplete]

  require Logger
  alias MyApp.Repo

  # Shared with the swap. Any constant nothing else uses.
  @lock_key 820_417

  @impl Oban.Worker
  def perform(%Oban.Job{}) do
    if staging_exists?() do
      config = Application.get_env(:my_app, __MODULE__, [])
      batch_size = Keyword.get(config, :batch_size, 5_000)

      case Repo.transaction(fn -> run_batch(batch_size) end, timeout: :timer.seconds(90)) do
        {:ok, :busy} -> {:snooze, 10}
        # Copy done, nothing to replay: keep the backlog small until the swap.
        {:ok, {0, 0}} -> {:snooze, 5}
        {:ok, {copied, applied}} -> report_progress(copied, applied)
      end
    else
      :ok
    end
  end

  defp run_batch(batch_size) do
    %{rows: [[locked?]]} = Repo.query!("SELECT pg_try_advisory_xact_lock($1)", [@lock_key])

    if locked? do
      Repo.query!("SET LOCAL lock_timeout = '2s'")
      Repo.query!("SET LOCAL statement_timeout = '60s'")
      %{rows: [[applied]]} = Repo.query!("SELECT messages_backfill_apply_changes($1)", [batch_size])
      %{rows: [[copied]]} = Repo.query!("SELECT messages_backfill_copy_batch($1)", [batch_size])
      {copied, applied}
    else
      :busy
    end
  end

  defp staging_exists? do
    Repo.query!("SELECT to_regclass('messages_backfill_changes') IS NOT NULL").rows == [[true]]
  end

  defp report_progress(copied, applied) do
    %{rows: [[last_id, target]]} =
      Repo.query!("SELECT last_id, target_max_id FROM messages_backfill_progress")

    Logger.info("messages backfill: id #{last_id}/#{target}, +#{copied}, #{applied} replayed")
    {:snooze, Keyword.get(Application.get_env(:my_app, __MODULE__, []), :pause_seconds, 1)}
  end
end
```

`unique` with `states: :incomplete` makes a second insert a no-op. Open-source Oban's queue limits are per node, so the advisory lock is what stops batches overlapping across nodes. `{:snooze, n}` re-runs the same job, and in Oban 2.24 snoozing doesn't consume attempts. Batch size and pause live in config, to slow down when replicas lag.

After the copy, give each staging partition the parent's other indexes with `CREATE INDEX CONCURRENTLY` (in a migration with both flags above, since the worker still writes), plus its foreign key `NOT VALID`, then validate and `ANALYZE`. Anything missing would be built under the swap's lock.

### The swap

```sql
BEGIN;
SET LOCAL lock_timeout = '2s';
DO $$ BEGIN
  IF (SELECT count(*) FROM messages_backfill_changes) > 20000 THEN
    RAISE EXCEPTION 'change backlog too large, let the backfill worker catch up';
  END IF;
END $$;
SELECT pg_advisory_xact_lock(820417);

-- Catch up while everyone can still read and write.
DO $$ BEGIN
  WHILE messages_backfill_apply_changes(10000) > 500 LOOP END LOOP;
END $$;

LOCK TABLE ONLY messages IN ACCESS EXCLUSIVE MODE;
LOCK TABLE messages_legacy IN ACCESS EXCLUSIVE MODE;

-- Replay the last few changes, now that nobody can write.
DO $$ BEGIN
  WHILE messages_backfill_apply_changes(10000) > 0 LOOP END LOOP;
END $$;

ALTER TABLE messages DETACH PARTITION messages_legacy;

DO $$
DECLARE parts record;
BEGIN
  FOR parts IN
    SELECT c.relname AS name, pg_get_expr(c.relpartbound, c.oid) AS bound
    FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
    WHERE i.inhparent = 'messages_backfill'::regclass
  LOOP
    EXECUTE format('ALTER TABLE messages_backfill DETACH PARTITION %I', parts.name);
    EXECUTE format('ALTER TABLE messages ATTACH PARTITION %I %s', parts.name, parts.bound);
  END LOOP;
END $$;

DROP TRIGGER messages_backfill_capture ON messages_legacy;
DROP TABLE messages_backfill, messages_backfill_changes, messages_backfill_progress;
DROP FUNCTION messages_backfill_copy_batch, messages_backfill_apply_changes, messages_backfill_capture;
COMMIT;
```

What I measured under load:

- `count(*)` through the parent was identical in all 143 samples across copy, index builds and swap.
- After 246,000 concurrent updates, deletes and back-dated inserts during the copy, `EXCEPT` both ways between the detached table and the new partitions returned nothing.
- The locked part of the swap took about 50 ms, with no failed transactions.
- A first attempt blocked everything for 4.6 seconds: 16,000 changes had piled up and were replayed under the lock. Hence the guard and catch-up pass.

### Trade-offs

- **Locks:** only the swap takes strong ones, briefly.
- **WAL and replication:** the whole history is written again, heap and indexes; throttle and watch replica lag. pg_partman also writes every row a second time.
- **Disk:** room for a second copy until the old table is dropped.
- **Bloat and VACUUM:** nothing is deleted from the default, so nothing to vacuum; it goes in one `DROP TABLE`.
- **Rows changing mid-move:** the trigger and replay handle it, at a small cost to writes on old rows.
- **All at once:** history goes live in one swap. Keep the detached table until you've verified the data.

pg_partman's route is fine when the default is small or you can afford the lock in a quiet window.

## Step 5: clean up and keep the default empty

Verify against the detached table, then drop it; the sequence survives because `messages.id` owns it. Drop the `_bounds` CHECKs, as the [docs recommend](https://www.postgresql.org/docs/current/ddl-partitioning.html), and run `ANALYZE messages` yourself: autovacuum never analyzes a partitioned parent.

I prefer no default afterwards: unroutable rows fail loudly and `DETACH PARTITION ... CONCURRENTLY` works (it refuses to while a default exists). To never reject an insert, keep a small `messages_default` and alert when it has rows. Either way, alert when fewer than two future partitions exist:

```sql
SELECT EXISTS (SELECT 1 FROM messages_default) AS rows_in_default;

SELECT count(*) AS future_partitions
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'messages'::regclass
  AND c.relname > 'messages_p' || to_char(now() AT TIME ZONE 'UTC', 'YYYY_MM');
```

## The alternative: attach the old table as one range partition

If history mainly needs keeping until retention removes it, skip the backfill: with the same validated CHECK and `inserted_at NOT NULL`, attach the old table as one range partition (2 ms, no scan):

```sql
ALTER TABLE messages ATTACH PARTITION messages_legacy
  FOR VALUES FROM (MINVALUE) TO ('2026-10-01 00:00:00+00');
```

No copy, WAL or trigger, but one huge partition whose indexes and vacuum work stay as big as today until you drop it. I'd pick it when old data is rarely queried, and the backfill when queries on history must get faster too.

## If you're facing this migration

Rehearse all of it on a copy of production first. If your Postgres (or Elasticsearch) cluster is slowing down as data grows and you'd like someone who has done this in production to plan and run it with your team, [I help teams with exactly this](https://fedme.dev/services/postgres-elasticsearch).