Quick Links

ATTACH PARTITION cost grows linearly with pg_constraint size (seqscan in CloneFkReferenced), much worse since not-null constraints are in pg_constraint (PG 18)

From:	Bernhard Wonisch <bernhard(dot)wonisch(at)gmx(dot)at>
To:	pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject:	ATTACH PARTITION cost grows linearly with pg_constraint size (seqscan in CloneFkReferenced), much worse since not-null constraints are in pg_constraint (PG 18)
Date:	2026-09-29 11:38:42
Message-ID:	trinity-08d3329b-f6b2-4c11-91d7-2198e50bf4fc-1790681922354@trinity-msg-rest-gmx-gmx-live-58cc8f554d-c56lm
Views:	Whole Thread \| Raw Message \| Download mbox \| Resend email
Thread:
Lists:	pgsql-hackers

<html><body><div style="font-family: 'verdana'; font-size: 12px; color: #000;">
<div>
<pre>Hi hackers, On PostgreSQL 18.6 we see ATTACH PARTITION and CREATE TABLE ... PARTITION OF become steadily slower as the database grows. That happens even when the partitioned table involved is empty, has only a handful of partitions, and no foreign key references it or goes out from it. The cost per attached partition is proportional to the total size of pg_constraint. 1. Where the time goes ---------------------- Both ATExecAttachPartition() and DefineRelation() (for PARTITION OF) call CloneForeignKeyConstraints() unconditionally, which calls CloneFkReferenced(). That function looks for foreign keys referencing the parent with a heap scan of pg_constraint, because there is no index on confrelid (src/backend/commands/tablecmds.c, REL_18_STABLE): ScanKeyInit(&key[0], Anum_pg_constraint_confrelid, BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(RelationGetRelid(parentRel))); ScanKeyInit(&key[1], Anum_pg_constraint_contype, BTEqualStrategyNumber, F_CHAREQ, CharGetDatum(CONSTRAINT_FOREIGN)); /* This is a seqscan, as we don't have a usable index ... */ scan = systable_beginscan(pg_constraint, InvalidOid, true, NULL, 2, key); So every partition added costs one full scan of pg_constraint. I confirmed this with pg_stat_sys_tables.seq_scan on pg_constraint. 10 x CREATE TABLE ... (LIKE ...) added 0 seq scans, while 10 x ATTACH PARTITION plus 10 x CREATE TABLE ... PARTITION OF added exactly 20. This used to be cheap. Since commit 14e87ffa5 ("Add pg_constraint rows for not-null constraints", PG 18), every NOT NULL column of every table and every partition has a row in pg_constraint. Partitioned schemas in particular now have far more rows there than before. In our database 97.5% of pg_constraint consists of rows belonging to partitions: contype | count --------+-------- n | 639630 p | 39054 c | 30035 f | 1001 u | 123 pg_constraint: 711k rows, 288 MB heap (469 MB total) EXPLAIN (ANALYZE, BUFFERS) -- max_parallel_workers_per_gather = 0 SELECT * FROM pg_constraint WHERE confrelid = '<parent>'::regclass AND contype = 'f'; Seq Scan on pg_constraint (actual time=97.877..97.878 rows=0 loops=1) Filter: ((confrelid = '1151806'::oid) AND (contype = 'f'::"char")) Rows Removed by Filter: 709854 Buffers: shared hit=15015 read=21873 Execution Time: 97.895 ms The same GetParentedForeignKeyRefs() pattern ("XXX This is a seqscan, as we don't have a usable index") exists on the DETACH path. 2. Standalone reproducer ------------------------ Run this in an empty PG 18 database. It attaches 50 fresh, empty partitions to a table without any FK and then grows pg_constraint with unrelated tables that have NOT NULL columns. The nullable filler step is a control: many more tables and columns, but no pg_constraint rows. CREATE TABLE parent (k INTEGER NOT NULL, v TEXT) PARTITION BY LIST (k); CREATE SEQUENCE part_seq; CREATE FUNCTION time_attach(p_n INTEGER) RETURNS NUMERIC LANGUAGE plpgsql AS $f$ DECLARE t0 TIMESTAMPTZ; v_first BIGINT := nextval('part_seq'); i BIGINT; BEGIN PERFORM setval('part_seq', v_first + p_n); FOR i IN v_first .. v_first + p_n LOOP EXECUTE format('CREATE TABLE parent_p%s (LIKE parent)', i); END LOOP; -- one warm-up ATTACH, not measured EXECUTE format('ALTER TABLE parent ATTACH PARTITION parent_p%s FOR VALUES IN (%s)', v_first, v_first); t0 := clock_timestamp(); FOR i IN v_first + 1 .. v_first + p_n LOOP EXECUTE format('ALTER TABLE parent ATTACH PARTITION parent_p%s FOR VALUES IN (%s)', i, i); END LOOP; RETURN ROUND(EXTRACT(EPOCH FROM clock_timestamp() - t0) * 1000 / p_n, 2); END; $f$; CREATE FUNCTION filler(p_prefix TEXT, p_tables INTEGER, p_cols INTEGER, p_not_null BOOLEAN) RETURNS VOID LANGUAGE plpgsql AS $f$ DECLARE v_cols TEXT; BEGIN SELECT string_agg(format('c%s INTEGER%s', c, CASE WHEN p_not_null THEN ' NOT NULL' ELSE '' END), ', ') INTO v_cols FROM generate_series(1, p_cols) c; FOR t IN 1 .. p_tables LOOP EXECUTE format('CREATE TABLE %s_%s (%s)', p_prefix, t, v_cols); END LOOP; END; $f$; CREATE TABLE results (step TEXT, pg_constraint_rows BIGINT, pg_constraint_mb NUMERIC, ms_per_attach NUMERIC, ts TIMESTAMPTZ DEFAULT clock_timestamp()); CREATE FUNCTION measure(p_step TEXT) RETURNS VOID LANGUAGE sql AS $f$ INSERT INTO results (step, pg_constraint_rows, pg_constraint_mb, ms_per_attach) SELECT p_step, (SELECT COUNT(*) FROM pg_constraint), ROUND(pg_relation_size('pg_constraint') / 1048576.0, 1), time_attach(50); $f$; SELECT measure('empty database'); BEGIN; SELECT filler('nullable', 500, 500, false); COMMIT; SELECT measure('+500 tables x 500 nullable columns'); BEGIN; SELECT filler('nn1', 500, 500, true); COMMIT; SELECT measure('+250k NOT NULL constraints'); BEGIN; SELECT filler('nn2', 500, 500, true); COMMIT; SELECT measure('+500k NOT NULL constraints'); BEGIN; SELECT filler('nn3', 500, 500, true); COMMIT; SELECT measure('+750k NOT NULL constraints'); BEGIN; SELECT filler('nn4', 500, 500, true); COMMIT; SELECT measure('+1M NOT NULL constraints'); SELECT step, pg_constraint_rows, pg_constraint_mb, ms_per_attach FROM results ORDER BY ts; Result on 18.6 (x86_64, Alpine/musl build, shared_buffers = 128MB, a local developer machine): step | pg_constraint_rows | pg_constraint_mb | ms_per_attach -----------------------------------+--------------------+------------------+--------------- empty database | 195 | 0.0 | 0.78 +500 tables x 500 nullable columns | 246 | 0.1 | 1.05 +250k NOT NULL constraints | 250297 | 41.6 | 12.99 +500k NOT NULL constraints | 500348 | 83.2 | 29.42 +750k NOT NULL constraints | 750399 | 124.8 | 44.73 +1M NOT NULL constraints | 1000450 | 166.3 | 64.50 ATTACH of an empty partition into a table with no FKs goes from under 1 ms to 64 ms, linear in pg_constraint size. The nullable control shows that the number of tables or columns doesn't matter; only the rows in pg_constraint do. Before PG 18 the same filler tables would have added no pg_constraint rows at all. I haven't measured the same script on PG 17. 3. Real-world impact -------------------- We use list partitioning for our data: 53 tables, all partitioned by the same "partition number", each currently with 700 partitions (38k leaf partitions in total). A maintenance job creates 50 new partition numbers at a time, i.e. 53 x 50 = 2,650 ATTACH PARTITION per run. Each new partition number adds ~990 pg_constraint rows (≈19 per leaf, nearly all not-null constraints), so each run makes the next one slower. Wall-clock times of consecutive runs: 1:03, 1:09, 1:13, 1:21, 1:29, 1:31, 1:55, 2:10, 2:55, 3:46, 4:42, 6:16 Per-step timing shows ATTACH is ~95% of the runtime (~120 ms per ATTACH at 700 partitions per table). CREATE TABLE ... LIKE, triggers, grants and so on stay in the low milliseconds. Switching to CREATE TABLE ... PARTITION OF doesn't help, since it goes through the same CloneFkReferenced(). The curve bends upward once pg_constraint no longer fits in shared_buffers. 4. Possible directions ---------------------- I'm not proposing a patch yet, but a few ideas: a) Avoid the heap scan by finding the referencing FKs through an existing index. An FK constraint records a dependency on the referenced relation's columns, so pg_depend_reference_index (refclassid, refobjid, refobjsubid) with refobjid = parent and classid = pg_constraint yields exactly the candidate constraints. The cost is then proportional to the number of dependents of the parent, not the size of the catalog. b) Cheap early exit: a table referenced by an FK carries the internal RI action triggers (RI_FKey_*_del/_upd). If the parent has no such triggers, CloneFkReferenced() could skip the scan entirely. That covers the common case of partitioned tables that are never referenced. c) A catalog index on pg_constraint (confrelid). This is the most direct fix, but it adds maintenance cost to every constraint insert, and in PG 18 the vast majority of rows have confrelid = 0. The same applies to GetParentedForeignKeyRefs() on the DETACH path. Is this known, or worth a patch? I'm happy to test patches against our workload. Regards, Bernhard Wonisch </pre>
</div>
</div></body></html>

Attachment	Content-Type	Size
unknown_filename	text/html	9.5 KB

Responses

Re: ATTACH PARTITION cost grows linearly with pg_constraint size (seqscan in CloneFkReferenced), much worse since not-null constraints are in pg_constraint (PG 18) at 2026-09-29 12:35:49 from Manu

Browse pgsql-hackers by date

	From	Date	Subject
Next Message	Manu	2026-09-29 11:45:06	Re: BUG #19686: Rolling back SET TABLESPACE
Previous Message	Nisha Moond	2026-09-29 11:35:29	Re: Fix apply worker crash when subscriber table has only a deferrable primary key