Huge memory consumption on partitioned table with FKs

[Tatsuro Yamada]

Hi Hackers,

My company (NTT Comware) and NTT OSS Center did verification of
partitioned table on PG14dev, and we faced a problem that consumed
huge memory when we created a Foreign key constraint on a partitioned
table including 500 partitioning tables and inserted some data.

We investigated it a little to use the "pg_backend_memory_contextes"
command and realized a "CachedPlan" of backends increased dramatically.
See below:

Without FKs
==============================
CachedPlan 0kB

With FKs (the problem is here)
==============================
CachedPlan 710MB


Please find the attached file to reproduce the problem.

We know two things as following:
  - Each backend uses the size of CachedPlan
  - The more increasing partitioning tables, the more CachedPlan
    consuming

If there are many backends, it consumes about the size of CachedPlan x
the number of backends. It may occur a shortage of memory and OOM killer.
We think the affected version are PG12 or later. I believe it would be
better to fix the problem. Any thoughts?

Regards,
Tatsuro Yamada

DROP TABLE IF EXISTS pr CASCADE;
DROP TABLE IF EXISTS ps CASCADE;
CREATE TABLE ps (c1 INT PRIMARY KEY) PARTITION BY RANGE(c1);
CREATE TABLE pr (c1 INT, c2 INT REFERENCES ps(c1)) PARTITION BY RANGE(c1);

-- Show memory usage of 'Cached%' 
SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM 
pg_backend_memory_contexts
WHERE name LIKE 'Cached%' GROUP BY name;

-- Procedure for creating partitioned table
CREATE OR REPLACE PROCEDURE part_make(tbl text, num int) AS $$
 DECLARE width int := 10; next int :=1;
 BEGIN
   FOR i in 1..num LOOP
       EXECUTE 'CREATE TABLE ' || tbl || '_' || i || ' partition of ' || tbl || 
' FOR VALUES FROM (' || next || ') TO (' || i * width
           || ');';
               next := i * width;
   END LOOP;
 END;
$$ LANGUAGE plpgsql;

-- Create partitioned tables named ps and pr. The each table has 500 
partitioning tables. 
CALL part_make('ps', 500);
CALL part_make('pr', 500);

-- Insert data
INSERT INTO ps SELECT generate_series(1,4999);
INSERT INTO pr SELECT i, i from generate_series(1,4999)i;

-- Show memory usages of 'Cached%' again
-- You can see 'CachedPlan 710MB'
SELECT name, sum(used_bytes) as bytes, pg_size_pretty(sum(used_bytes)) FROM 
pg_backend_memory_contexts
WHERE name LIKE 'Cached%' GROUP BY name;