Pilotbase displaying PostgreSQL table partitions, foreign key constraint inheritance, and referential integrity triggers

PostgreSQL 11 added support for foreign keys referencing partitioned tables, and PostgreSQL 12 completed the picture by allowing foreign keys on partitioned tables referencing regular tables. In theory, defining a foreign key on the partitioned parent cascades cleanly down to every child partition. In production, however, partitioning introduces distinct mechanics: inherited constraints that cannot be dropped individually, internal referential integrity triggers multiplied across partitions, validation scans during partition attach operations, and cascading deletes that stall without matching partition indexes.

To measure these costs concretely, we ran tests on a managed PostgreSQL 18.7 instance (2 vCPU, 3 GB RAM, 40 GB disk). We benchmarked a customer-and-orders schema with 50,000 customers and 1,200,000 orders partitioned across 12 monthly tables, measuring partition creation, index creation, cascading DELETE operations, table attachment, and detachment. We also examined the popular Pagila sample database repository to see how real-world migration scripts handle partitioned foreign keys.

The real-world hook: 49 partitions without a foreign key in Pagila

The Pagila sample database (github.com/devrimgunduz/pagila, master commit 6ea53f8) provides a striking example of partition configuration pitfalls. Its payment table is partitioned by range on payment_date into 55 monthly tables (payment_p2022_01 through payment_p2026_07), holding 51,061 rows in total.

However, the parent payment table defines no foreign keys. Instead, the schema DDL individually adds foreign keys for customer_id, rental_id, and staff_id using ALTER TABLE ONLY ... ADD CONSTRAINT—and only for the first six partitions (payment_p2022_01 through payment_p2022_06), creating 18 standalone foreign keys and 36 internal triggers. The remaining 49 partitions (containing 37,346 rows) have no foreign keys at all:

sql
-- In Pagila master schema:
-- Only p2022_01 through p2022_06 have FKs defined via ALTER TABLE ONLY
ALTER TABLE ONLY payment_p2022_01
    ADD CONSTRAINT payment_p2022_01_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id);
-- Partitions p2022_07 through p2026_07 (49 partitions) have no foreign keys!

Because the sample dataset contains no orphan records, the omission remains hidden until invalid data enters. In test transactions, we observed that inserting an invalid payment for nonexistent customer 99999 dated 2022-03-10 was immediately rejected by payment_p2022_03_customer_id_fkey. The exact same row dated 2024-03-10 was accepted without error into payment_p2024_03. Similarly, deleting customer 600 (who has 24 payments between 2024-02 and 2026-07) was blocked solely by rental_customer_id_fkey ... ON DELETE RESTRICT on the rental table, completely masking the fact that the payment records had no integrity protection.

Fixing the entire schema required adding the constraint to the parent table. Running ALTER TABLE payment ADD CONSTRAINT payment_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer took just 59.5 ms. PostgreSQL automatically placed inherited foreign keys on all 55 partitions, merged the 6 existing per-partition constraints while retaining their original names, and immediately blocked any orphan insertion into 2024 partitions. Adding all three foreign keys (customer_id, rental_id, staff_id) to the parent took 121.6 ms, 74.5 ms, and 27.0 ms, establishing 165 inherited foreign keys and 330 internal triggers across the partitions.

What each partition inherits at creation

When you create a partitioned table and declare a foreign key on the parent, each newly attached or created partition inherits the constraint automatically. In our benchmark schema, the tables were defined as:

sql
CREATE TABLE customers (
    id bigint PRIMARY KEY
);

CREATE TABLE orders (
    id bigint NOT NULL,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    ordered_on date NOT NULL,
    amount numeric(10,2) NOT NULL,
    PRIMARY KEY (id, ordered_on)
) PARTITION BY RANGE (ordered_on);

CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Inspecting pg_constraint right after creating the partition reveals what PostgreSQL creates:

• Inherited primary key: orders_2025_01_pkey is created on the partition and linked to the parent primary key (conparentid != 0).
• Inherited foreign key: orders_customer_id_fkey is created on the partition with the same name as the parent constraint, marked inherited.
• NOT NULL constraints: In PostgreSQL 18, NOT NULL constraints are represented as catalog rows in pg_constraint with contype = 'n', marked own (conislocal = true).
• Internal referential integrity (RI) triggers: Each foreign key creates two internal triggers on customers and two internal triggers on each child partition.

Across our 12 monthly partitions, the schema contained 1 own parent foreign key plus 12 inherited foreign keys, 2 own primary keys plus 12 inherited primary keys, 54 NOT NULL constraint rows, and 26 internal triggers. Importantly, an inherited constraint cannot be dropped from a child partition:

sql
ALTER TABLE orders_2025_01 DROP CONSTRAINT orders_customer_id_fkey;
-- ERROR: cannot drop inherited constraint "orders_customer_id_fkey" of relation "orders_2025_01"

To drop or alter the foreign key, the command must target the parent table, or the partition must first be detached.

DELETE on the parent: why child partition indexes matter

A standard foreign key trap is deleting rows from a parent table when the child table lacks an index on the foreign key column. When the child table is partitioned, this penalty multiplies across every partition.

In our test database of 50,000 customers and 1,200,000 orders (100,000 rows in each of the 12 monthly partitions), we ran EXPLAIN (ANALYZE, BUFFERS) DELETE FROM customers WHERE id IN (...) with and without an index on orders(customer_id):

WorkloadNo index on orders(customer_id)With index (12 partition indexes)
10 customers with orders714.1 ms (trigger 713.9 ms, calls=10)6.2 ms (trigger 6.1 ms, calls=10)
10 customers with no orders701.7 ms (trigger 701.6 ms, calls=10)1.4 ms (trigger 1.3 ms, calls=10)
1,000 customers with orders(not run; estimated > 70s)124.9 ms (trigger 122.4 ms, calls=1000)

In every test, the actual Delete on customers plan node required only 0.04 to 0.05 ms. Over 99.9% of the execution time was consumed by the internal foreign key trigger (RI_FKey_cascade_del). Without an index, each customer deleted forced sequential scans across all 12 partitions. Even when deleting customers who had zero orders, Postgres had to scan every partition to prove no matching child rows existed, taking 701.7 ms for 10 customers.

Creating the index on the parent table automatically creates a matching index on every child partition:

sql
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
-- Took 578 ms; created 12 partition indexes

For 100,000 rows per partition, each partition table consumed 5,888 kB and its foreign key index took 1,968 kB. With partition indexes in place, deleting 10 customers dropped from 714.1 ms to 6.2 ms (a 115x speedup), and deleting 1,000 customers completed in 124.9 ms.

When inspecting execution plans in Postgres, these triggers appear at the bottom of the EXPLAIN (ANALYZE) output as Trigger for constraint orders_customer_id_fkey: time=6.132 calls=10. Tools like explainViewer parse these trigger lines directly from text or JSON plans and highlight the trigger execution overhead. For deeper background on unindexed child tables and locking implications, see Foreign keys without an index.

ATTACH PARTITION: costs across five validation setups

Attaching an existing standalone table as a partition is common in data ingestion and ETL pipelines. However, attaching a table to a partitioned table that enforces foreign keys requires PostgreSQL to verify both partition bounds and foreign key constraints. We measured the attachment of a 1,000,000-row standalone table with primary key and foreign key index already present (build time 3.4 to 6.3 s) under five configurations:

CaseStandalone table preparationATTACH durationResulting foreign key state
C1: Plain tableNone extra289.9 msInherited orders_customer_id_fkey added and validated
C2: Own matching FK + CHECKFK 157.1 ms, CHECK 59.8 ms21.4 msOwn FK transformed into inherited constraint (no duplicate FK)
C3: CHECK bound onlyCHECK 80.5 ms142.6 msInherited FK added and validated during ATTACH
C4: Own FK ON DELETE RESTRICTFK 168.0 ms331.8 msBoth kept: own FK + inherited CASCADE FK (6 RI triggers)
C5: Own FK NOT VALID + CHECKFK 6.6 ms179.4 msTransformed into inherited constraint, validated during ATTACH

Notice the difference between C1 and C2: attaching without pre-validation took 289.9 ms while holding an ACCESS EXCLUSIVE lock on the partitioned table. In C2, adding a matching foreign key and a CHECK constraint matching the partition bounds in advance allowed PostgreSQL to skip table scans entirely, completing the ATTACH PARTITION in only 21.4 ms—a 13.5x reduction in lock duration.

Two subtle behaviors emerged from C4 and C5:

• Conflicting action rules (C4): When the standalone table had an existing foreign key with ON DELETE RESTRICT instead of ON DELETE CASCADE, PostgreSQL did not merge or replace it. It retained both constraints, doubling trigger overhead to 6 internal RI triggers on that partition.
• NOT VALID constraints (C5): Creating the standalone foreign key with NOT VALID took only 6.6 ms, but attaching forced full validation during the ATTACH PARTITION command (179.4 ms), negating the lock-avoidance benefit.

If a standalone table contains orphan records, ALTER TABLE ... ATTACH PARTITION fails immediately:

sql
-- Attempting to attach a partition containing an invalid customer_id:
ALTER TABLE orders ATTACH PARTITION orders_2026_03
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
-- ERROR: insert or update on table "orders_2026_03" violates foreign key constraint "orders_customer_id_fkey"
-- DETAIL: Key (customer_id)=(999999) is not present in table "customers".

Similarly, if the foreign key column in the standalone table is nullable while the parent constraint requires NOT NULL, attachment aborts with ERROR: column "customer_id" in child table "orders_2026_04" must be marked NOT NULL.

DETACH PARTITION: what happens to inherited constraints

Detaching a partition flips constraint inheritance. Running a plain detach on a 100,000-row partition took 6.9 ms:

sql
ALTER TABLE orders DETACH PARTITION orders_2025_01;

Following the detach, orders_2025_01_pkey and orders_customer_id_fkey transformed from inherited constraints into standalone, local constraints owned by orders_2025_01. The detached table continues to enforce referential integrity: inserts with nonexistent customer IDs are rejected, and deleting a customer cascades to rows in the detached table as expected.

Once detached, the foreign key can be dropped directly from the standalone table (12.6 ms in our tests). If the table is later re-attached, PostgreSQL re-establishes orders_customer_id_fkey as an inherited, validated constraint (taking 55.8 ms).

When detaching concurrently with ALTER TABLE orders DETACH PARTITION orders_2025_02 CONCURRENTLY (14.1 ms), PostgreSQL avoids aggressive locks on the parent table and automatically adds a CHECK constraint representing the partition bound (orders_2025_02_ordered_on_check). This allows subsequent analytical queries to benefit from constraint exclusion. For data retention strategies using partition detach instead of bulk deletes, see Deleting millions of rows.

Foreign keys pointing AT a partitioned table

The inverse relationship—an outside table referencing a partitioned table—has strict prerequisites in PostgreSQL. Because unique and primary key constraints on partitioned tables must include the partition key, any foreign key pointing at a partitioned table must include the partition column in its key tuple:

sql
CREATE TABLE invoices (
    id bigint PRIMARY KEY,
    order_id bigint NOT NULL,
    ordered_on date NOT NULL,
    invoice_number text NOT NULL,
    CONSTRAINT invoices_order_fk
        FOREIGN KEY (order_id, ordered_on)
        REFERENCES orders (id, ordered_on)
        ON DELETE CASCADE
);

Creating this reference across 11 attached partitions created 12 pg_constraint entries on invoices (one referencing the parent and one referencing each partition) along with 2 additional internal triggers per partition (4 triggers total per partition).

Crucially, having an external table reference a partitioned table restricts partition management:

• Dropping a partition is blocked: DROP TABLE orders_2025_04 failed with ERROR: constraint invoices_order_fk on table invoices depends on table orders_2025_04, even when the invoices table contained zero records for April.
• Detaching a partition with referencing rows is blocked: Attempting ALTER TABLE orders DETACH PARTITION orders_2025_03 failed with ERROR: removing partition "orders_2025_03" violates foreign key constraint "invoices_order_fk_2". Only after deleting referencing rows from invoices did the detach succeed (5.5 ms).

Summary of practical rules

1. Define foreign keys on the partitioned parent: Do not rely on per-partition ALTER TABLE ONLY scripts, which leave subsequent partitions unprotected as seen in Pagila.
2. Index the foreign key column on the parent table: CREATE INDEX ON parent(fk_col) automatically indexes all current and future partitions, preventing multi-partition sequential scans during parent deletions.
3. Pre-validate before ATTACH: Adding a matching foreign key and partition bound CHECK on standalone tables reduced attach time from 289.9 ms to 21.4 ms.
4. Inspect trigger lines in EXPLAIN ANALYZE: When parent deletes take hundreds of milliseconds, check trigger execution times and verify partition indexing using tools like explainViewer.

Inspect your PostgreSQL table schemas, partition trees, foreign key relationships, and query execution plans with Pilotbase.

Get Pilotbase

Analyze your EXPLAIN (ANALYZE) plans directly in your browser: inspect trigger execution times, child table index scans, and buffer usage without sending data to an external server.

Open explainViewer

Sources: PostgreSQL Documentation on Table Partitioning, PostgreSQL ALTER TABLE Reference, and Pagila Sample Database Repository (master commit 6ea53f8). Measurements: conducted 17 November 2026 on a managed PostgreSQL 18.7 instance (2 vCPU, 3 GB RAM, 40 GB storage, Linux). Single benchmark run; individual query durations and trigger execution times are indicative.