Migrate existing Postgres table with foreign key references to declarative partitioning

Question

I want to apply declarative partitioning to a Postgres table referenced by many other tables with foreign key constraints.

As far as I can tell, Postgres provides limited support for this. The entire procedure seems to be:

1. Rename the target table to target_old
2. Create a new partitioned table, CREATE TABLE target (...) PARTITION BY RANGE (tenant_id).
3. Create the partitions.
4. Remove all foreign key references to the original target, which is now named target_old.
5. Move data from target_old into partitions.
6. Re-add all foreign key references to point to the new target table.
7. Copy any important row-level triggers from target_old to target so they're inherited by the partitions.

Is there a better way?

Demo

The following demo creates tmp.invoice and tmp.invoice_state and attempts to partition tmp.invoice.

CREATE SCHEMA IF NOT EXISTS tmp;
CREATE TABLE tmp.invoice (
  tenant_id   bigint,
  invoice_id  bigint,
  total_cents bigint,
  PRIMARY KEY (tenant_id, invoice_id)
);
-- Generate 30 invoices with 5 tenants.
INSERT INTO tmp.invoice (tenant_id, invoice_id, total_cents)
SELECT t % 5 AS tenant_id, t AS invoice_id, t AS total_cents
FROM generate_series(0, 29) t;
-- Create invoice states so there's a foreign key constraint on tmp.invoice.
CREATE TABLE tmp.invoice_state (
  tenant_id  bigint,
  invoice_id bigint,
  state      text,
  PRIMARY KEY (tenant_id, invoice_id),
  FOREIGN KEY (tenant_id, invoice_id) REFERENCES tmp.invoice (tenant_id, invoice_id)
);
-- Generate a state for each tenant.
INSERT INTO tmp.invoice_state (tenant_id, invoice_id, state)
VALUES (0, 0, 'created'),
       (1, 1, 'draft'),
       (2, 2, 'draft'),
       (3, 3, 'finalized'),
       (4, 4, 'finalized'),
       (0, 5, 'created');
-- Show the states.
SELECT tenant_id, invoice_id, state, total_cents
FROM tmp.invoice
  JOIN tmp.invoice_state USING (tenant_id, invoice_id);
-- +---------+----------+---------+-----------+
-- |tenant_id|invoice_id|state    |total_cents|
-- +---------+----------+---------+-----------+
-- |0        |0         |created  |0          |
-- |1        |1         |draft    |1          |
-- |2        |2         |draft    |2          |
-- |3        |3         |finalized|3          |
-- |4        |4         |finalized|4          |
-- |0        |5         |created  |5          |
-- +---------+----------+---------+-----------+
-- Original table will become a partition to reduce data movement.
ALTER TABLE tmp.invoice
  RENAME TO invoice_pt0;
CREATE TABLE tmp.invoice (
  LIKE tmp.invoice_pt0 INCLUDING ALL
) PARTITION BY RANGE (tenant_id);
CREATE TABLE tmp.invoice_pt2 PARTITION OF tmp.invoice FOR VALUES FROM (2) TO (4);
CREATE TABLE tmp.invoice_pt4 PARTITION OF tmp.invoice FOR VALUES FROM (4) TO (MAXVALUE);
-- Violates foreign key constraints because tmp.invoice_state references
-- the original tmp.invoice, renamed to tmp.invoice_pt0.
-- ERROR: update or delete on table "invoice_pt0" violates foreign
--        key constraint "invoice_state_tenant_id_invoice_id_fkey"
--        on table "invoice_state"
-- Detail: Key (tenant_id, invoice_id)=(2, 2) is still referenced
--         from table "invoice_state".
WITH
  del AS (
    DELETE FROM tmp.invoice_pt0
    WHERE 2 <= tenant_id AND tenant_id < 4
    RETURNING *
  )
INSERT INTO tmp.invoice (tenant_id, invoice_id, total_cents)
SELECT * FROM del;

References

• Postgres dynamically update constraint foreign key: Programatically alter foreign keys.

• PgPartman native partition guide: Seems like PgPartman could automate most of the tediousness. Notes that accepting downtime is unavoidable.

Taking the partitioned table offline is the only method that realistically works when you have foreign keys TO the table being partitioned

• How to partition existing table in postgres?: trigger-based partitioning, doesn't handle foreign keys.

• Add partitioning to unpartitioned table with minimal downtime: For SQL server, recommends using a view. Doesn't handle foreign keys.

• Efficiently attach a partition to a table with a foreign key to a partitioned table in PostgreSQL on Google Cloud: has the reverse problem, foreign keys point from the table being partitioned to another table.

• Partitioning in PostgreSQL when partitioned table is referenced: predates declarative partitioning, answers hint at indexes and foreign-key workarounds.