Huge memory consumption on partitioned table with FKs

From: Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Amit Langote <amitlangote09(at)gmail(dot)com>, tatsuhito(dot)kasahara(dot)rd(at)hco(dot)ntt(dot)co(dot)jp, keisuke(dot)kuroda(dot)3862(at)gmail(dot)com
Subject: Huge memory consumption on partitioned table with FKs
Date: 2020-11-24 07:46:28
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

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?

Tatsuro Yamada

Attachment Content-Type Size
repro.sql text/plain 1.2 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Surafel Temesgen 2020-11-24 07:47:25 Re: Evaluate expression at planning time for two more cases
Previous Message Fujii Masao 2020-11-24 07:35:14 Re: walsender bug: stuck during shutdown