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