Horton

Huge memory consumption on partitioned table with FKs

Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>

View thread View on postgr.es
From: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Amit Langote <amitlangote09@gmail.com>, tatsuhito.kasahara.rd@hco.ntt.co.jp, keisuke.kuroda.3862@gmail.com
Date: 2020-11-24T07:46:28Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Avoid creating duplicate cached plans for inherited FK constraints.