Converting a Postgres Table to Declarative Partitioning

The events table has 1.4 billion rows, adds 8 million a day, and keeps 13 months of history. Deleting old rows every night takes longer than the night. The team has agreed to partition it by month, and the first design — create a partitioned events_new, copy everything over with INSERT ... SELECT, swap — would take days of copying, double the storage, and need a dual-write mechanism to keep up with inserts during the copy. There is a much shorter path. PostgreSQL can attach an existing table to a partitioned parent as one of its partitions without moving a single row, provided you prepare the ground so the attach does not scan. This guide performs that adoption end to end, including the details the simple version skips: the composite primary key, sequences, grants, views and foreign keys that follow the old table when it is renamed. It expands the procedure in Partitioning Live Tables Without Downtime.

Symptom / Error Signatures

The conversion is on the table (literally) when you see:

• Retention jobs deleting millions of rows nightly, with rising runtime, WAL volume and table bloat, as tracked in pg_stat_user_tables.n_dead_tup.
• VACUUM and index maintenance on the table running for many hours.
• Most queries filtering on a time column that would make an obvious partition key.

And these errors mean a conversion attempt skipped a preparation step:

ERROR:  unique constraint on partitioned table must include all partitioning columns
ERROR:  partition constraint of relation "events_legacy" is violated by some row
ERROR:  table "events_legacy" contains column "payload" not found in parent "events"

Root Cause Analysis

Attaching a table as a partition has four preconditions, each of which becomes a step:

Renaming has side effects that the naive procedure ignores. PostgreSQL objects reference tables by OID, not by name, so when events is renamed to events_legacy: views that select from events now select from events_legacy (only the old data); foreign keys referencing events now reference events_legacy; the sequence owned by events.id stays owned by events_legacy.id; grants stay on events_legacy; and triggers stay on events_legacy. Each must be recreated or moved to the new parent in the swap, or queries silently see only historical data.

Immediate Mitigation

1. List everything that references the table before you start.

-- PostgreSQL · read-only · views, FKs, sequences and grants tied to events
SELECT DISTINCT v.relname AS view FROM pg_depend d
  JOIN pg_rewrite r ON r.oid = d.objid JOIN pg_class v ON v.oid = r.ev_class
  WHERE d.refobjid = 'events'::regclass AND v.relname <> 'events';
SELECT conname, conrelid::regclass FROM pg_constraint WHERE confrelid = 'events'::regclass;
SELECT pg_get_serial_sequence('events', 'id');
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'events';

2. Prepare the legacy table online. Validate the range check, then build the composite unique index concurrently.

-- PostgreSQL 14+ · migration role · brief lock + online scan, then a concurrent build
SET lock_timeout = '3s';
ALTER TABLE events ADD CONSTRAINT events_legacy_range
  CHECK (created_at IS NOT NULL AND created_at < '2026-10-01') NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT events_legacy_range;
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS events_id_created_uidx ON events (id, created_at);

3. Create the parent and the next partitions.

-- PostgreSQL 14+ · new objects only
CREATE TABLE events_p (LIKE events INCLUDING DEFAULTS INCLUDING GENERATED INCLUDING STORAGE)
  PARTITION BY RANGE (created_at);
ALTER TABLE events_p ADD CONSTRAINT events_p_pkey PRIMARY KEY (id, created_at);
CREATE TABLE events_2026_10 PARTITION OF events_p FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events_p FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

When the legacy table is attached, PostgreSQL matches the parent’s primary-key index to the legacy table’s existing (id, created_at) unique index and attaches it instead of building a new one.

Permanent Fix / Long-Term Pattern

4. Swap in one short transaction, carrying the dependents across. Time it shortly before the boundary of the first new partition, so that if you need to roll back, few rows live outside the legacy table.

-- PostgreSQL 14+ · migration role · all catalog changes; brief ACCESS EXCLUSIVE on events
-- WARNING: include every dependent found in step 1, or views and grants will point at the legacy table only.
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE events RENAME TO events_legacy;
ALTER TABLE events_p RENAME TO events;
ALTER TABLE events ATTACH PARTITION events_legacy FOR VALUES FROM (MINVALUE) TO ('2026-10-01');
ALTER TABLE events ALTER COLUMN id SET DEFAULT nextval('events_id_seq');
ALTER SEQUENCE events_id_seq OWNED BY events.id;
GRANT SELECT, INSERT, UPDATE, DELETE ON events TO app;
CREATE OR REPLACE VIEW recent_events AS SELECT * FROM events WHERE created_at > now() - interval '7 days';
COMMIT;
-- ROLLBACK PATH: detach events_legacy, swap names back, restore view/grants — see the parent topic.

5. Recreate foreign keys that referenced the old table against the new parent, NOT VALID then validated, as in adding foreign keys with NOT VALID and VALIDATE CONSTRAINT. Note that referencing columns must now reference the composite key or a unique key that includes created_at.

6. Automate future partitions and retention, and let the legacy partition age out or be split later. The events_legacy_range constraint can be dropped after attaching; the partition bound now enforces the same rule. Operational follow-ups are covered in automating partition creation with pg_partman.

Verification Checklist

• events_legacy_range was validated and the composite unique index was valid before the swap.
• The swap transaction completed in under a second, and EXPLAIN of a recent-date query touches only new partitions.
• Views, grants, sequence defaults and triggers were recreated against the new parent and verified by the application.
• Foreign keys that referenced the old table were recreated against the partitioned parent and validated.
• Partitions exist for at least the next several intervals, created automatically.

Frequently Asked Questions

Does attaching the old table copy any data? No. The existing table becomes a partition in place; its rows, indexes and storage stay exactly where they are. Only new rows go to new partitions.

Why must the primary key include created_at? PostgreSQL enforces uniqueness per partition, so it can only guarantee uniqueness for keys that include the partition key. If id alone must be globally unique, rely on the sequence to generate unique values, and accept that the database enforces (id, created_at).

What happens to views that selected from the old table? They follow the renamed table by OID and would show only historical data. Recreate them against the new parent inside the swap transaction.

Can I split the huge legacy partition later? Yes. Detach it, move its rows into properly bounded partitions in batches (or create partitions and move data range by range), and reattach any remainder — or simply let it age out if the retention period will eventually cover it.