Engineer partitioned indexes, cross-partition uniqueness, foreign keys, trigger cloning, and low-lock index rollout workflows on large partition hierarchies.
Indexes, Constraints, Foreign Keys, Triggers, and Uniqueness Across Partitions
Engineer partitioned indexes, cross-partition uniqueness, foreign keys, trigger cloning, and low-lock index rollout workflows on large partition hierarchies.
Learning outcomes
Partitioning changes the physical enforcement surface for indexes and constraints. A parent-level index is virtual and represented by child indexes; cross-partition uniqueness is possible only when the partition layout itself guarantees that duplicates cannot hide in different leaves. Foreign keys and row-level triggers work with declarative partitioning, but their behavior still follows the current PostgreSQL major's rules.
Explain why parent UNIQUE/PRIMARY KEY constraints must include all partition-key columns.
Create partitioned indexes and distinguish them from their physical child indexes.
Use a low-lock CREATE INDEX ON ONLY + CONCURRENTLY + ATTACH workflow.
Create a foreign key that can reference a partitioned key.
Observe row-trigger cloning and understand why a BEFORE INSERT trigger cannot redirect routing.
1. Cross-partition uniqueness must include the partition key
Each leaf has its own physical unique index. PostgreSQL can enforce global-looking uniqueness on the partitioned table only when the partition key guarantees that equal constraint keys must land in the same leaf.
ALTER TABLE app.ch16_work_ordersADD CONSTRAINT ch16_work_orders_bad_pkPRIMARY KEY (work_order_id);-- ERROR: unique constraint on partitioned table must include all partitioning columnsALTER TABLE app.ch16_work_ordersADD CONSTRAINT ch16_work_orders_pkPRIMARY KEY (opened_on, work_order_id);SELECT conname, contype, conrelid::regclassFROM pg_constraintWHERE conname = 'ch16_work_orders_pk';
This does not mean work_order_id is globally unique
by itself. Two leaves could contain the same ID on different
opened_on values because the declared key is the
pair.
2. A parent index is virtual; leaf indexes do the work
CREATE INDEX ch16_work_orders_status_opened_idxON app.ch16_work_orders (status, opened_on);SELECT i.indexrelid::regclass AS index_name, i.indrelid::regclass AS table_name, i.indisvalidFROM pg_index AS iWHERE i.indexrelid IN ( SELECT indexrelid FROM pg_index WHERE indrelid IN ( SELECT relid FROM pg_partition_tree('app.ch16_work_orders'::regclass) ))ORDER BY table_name::text, index_name::text;Creating the index on the parent creates matching indexes on existing leaves and causes future partitions to receive matching indexes automatically. Storage resides in those child index relations.
3. Large hierarchy: parent CREATE INDEX CONCURRENTLY is not available
PostgreSQL does not support
CREATE INDEX CONCURRENTLY directly on a partitioned
parent. On a busy hierarchy, build a metadata parent index on
ONLY, create each leaf index concurrently, then
attach every leaf index. The parent becomes valid when its
complete hierarchy is attached.
CREATE INDEX ch16_work_orders_customer_idxON ONLY app.ch16_work_orders (customer_id);SELECT indexrelid::regclass, indisvalidFROM pg_indexWHERE indexrelid = 'app.ch16_work_orders_customer_idx'::regclass;CREATE INDEX CONCURRENTLY ch16_wo_06_customer_idxON app.ch16_work_orders_2026_06 (customer_id);CREATE INDEX CONCURRENTLY ch16_wo_07_customer_idxON app.ch16_work_orders_2026_07 (customer_id);CREATE INDEX CONCURRENTLY ch16_wo_08_customer_idxON app.ch16_work_orders_2026_08 (customer_id);CREATE INDEX CONCURRENTLY ch16_wo_default_customer_idxON app.ch16_work_orders_default (customer_id);ALTER INDEX app.ch16_work_orders_customer_idxATTACH PARTITION app.ch16_wo_06_customer_idx;ALTER INDEX app.ch16_work_orders_customer_idxATTACH PARTITION app.ch16_wo_07_customer_idx;ALTER INDEX app.ch16_work_orders_customer_idxATTACH PARTITION app.ch16_wo_08_customer_idx;ALTER INDEX app.ch16_work_orders_customer_idxATTACH PARTITION app.ch16_wo_default_customer_idx;SELECT indexrelid::regclass, indisvalidFROM pg_indexWHERE indexrelid = 'app.ch16_work_orders_customer_idx'::regclass;
Do not wrap the concurrent builds in
BEGIN/COMMIT; PostgreSQL forbids
CREATE INDEX CONCURRENTLY inside a transaction
block. If one build fails, inspect and clean that leaf index
before attaching rather than dropping the entire parent
hierarchy.
4. Foreign keys can reference a partitioned key
Because the partitioned primary key is
(opened_on, work_order_id), a referencing table
must carry both columns to reference that parent key.
CREATE TABLE app.ch16_work_order_event ( event_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, opened_on date NOT NULL, work_order_id bigint NOT NULL, event_type text NOT NULL, FOREIGN KEY (opened_on, work_order_id) REFERENCES app.ch16_work_orders (opened_on, work_order_id));INSERT INTO app.ch16_work_order_event(opened_on, work_order_id, event_type)SELECT opened_on, work_order_id, 'inspection'FROM app.ch16_work_ordersWHERE opened_on >= DATE '2026-08-01' AND opened_on < DATE '2026-09-01'ORDER BY work_order_idLIMIT 1;INSERT INTO app.ch16_work_order_event(opened_on, work_order_id, event_type)VALUES (DATE '2026-08-01', 999999999, 'impossible');-- Expected: foreign key violationPartitioning does not weaken referential integrity; it changes which unique key can be declared on the referenced parent.
5. Parent row-level triggers are cloned to partitions
CREATE TABLE app.ch16_partition_audit ( audit_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, work_order_id bigint NOT NULL, leaf regclass NOT NULL, action text NOT NULL, audit_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE OR REPLACE FUNCTION app.ch16_audit_work_order()RETURNS triggerLANGUAGE plpgsqlAS $$BEGIN INSERT INTO app.ch16_partition_audit(work_order_id, leaf, action) VALUES (NEW.work_order_id, TG_RELID, TG_OP); RETURN NEW;END$$;CREATE TRIGGER ch16_work_order_auditAFTER INSERT ON app.ch16_work_ordersFOR EACH ROWEXECUTE FUNCTION app.ch16_audit_work_order();SELECT c.oid::regclass AS table_name, t.tgname, t.tgenabledFROM pg_trigger AS tJOIN pg_class AS c ON c.oid = t.tgrelidWHERE t.tgname = 'ch16_work_order_audit'ORDER BY table_name::text;INSERT INTO app.ch16_work_orders(work_order_id, customer_id, region, status, opened_on, labor_minutes, payload)VALUES (169100, 5100, 'east', 'queued', DATE '2026-08-20', 0, 'trigger-test');SELECT * FROM app.ch16_partition_auditWHERE work_order_id = 169100;Creating a row-level trigger on the parent clones it to existing leaves and to partitions created/attached later. When a partition is detached, cloned triggers are removed from that standalone table.
6. BEFORE INSERT triggers cannot change the final partition
A BEFORE ROW trigger may change ordinary column
values, but PostgreSQL explicitly does not allow it to redirect
the row by changing the partition key to a different final
partition. If routing logic belongs in application rules, encode
it before INSERT or model the partition key correctly; do not
try to outsmart declarative tuple routing.
Parent indexes/constraints are convenience and metadata; physical enforcement occurs at leaves. Plan uniqueness and foreign-key shape before partitioning, because changing a business key merely to satisfy the partition scheme is often a sign that the partition key is wrong.
7. Verify index and constraint coverage
SELECT inh.inhparent::regclass AS parent, inh.inhrelid::regclass AS childFROM pg_inherits AS inhWHERE inh.inhparent IN ( 'app.ch16_work_orders'::regclass, 'app.ch16_work_orders_customer_idx'::regclass, 'app.ch16_work_orders_status_opened_idx'::regclass)ORDER BY parent::text, child::text;SELECT conname, contype, conrelid::regclassFROM pg_constraintWHERE conrelid IN ( SELECT relid FROM pg_partition_tree('app.ch16_work_orders'::regclass))ORDER BY conrelid::regclass::text, conname;Check your understanding
- Why must the parent primary key include opened_on?
- Where is a partitioned index's actual storage?
- Why use CREATE INDEX ON ONLY plus per-leaf concurrent builds?
- What columns must the event table reference in this lab?
- What happens to a parent row-level trigger when new partitions are created?
Review the answers
Including the partition key guarantees equal constrained keys cannot exist in different leaves. Child indexes hold storage. The ONLY/concurrent/attach workflow reduces lock impact because parent CREATE INDEX CONCURRENTLY is unsupported. The foreign key references opened_on plus work_order_id. Parent row-level triggers are cloned to existing and future/attached partitions.
Authoritative references
Partitioning behavior is planner-, lock-, constraint-, and version-sensitive. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.