From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: OOM killer while pg_restore
In-reply-to: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>
References: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>
Comments: In-reply-to =?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr>
	message dated "Thu, 03 Mar 2022 09:59:03 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3161845.1646321519.1@sss.pgh.pa.us>
Date: Thu, 03 Mar 2022 10:31:59 -0500
Message-ID: <3161846.1646321519@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3161846.1646321519%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr> writes:
> We have a pg_restore which fails due to RAM over-consumption of the 
> corresponding PG backend, which ends-up with OOM killer.
> The table has one PK, one index, and 3 FK constraints, active while 
> restoring.
> The dump contains over 200M rows for that table and is in custom format, 
> which corresponds to 37 GB of total relation size in the original DB.

The FKs would result in queueing row trigger events, which would occupy
some memory.  But those should only need ~12 bytes per FK per row,
which works out to less than 10GB for this number of rows, so it may
be that you've hit something else that we would consider a leak.

Does memory consumption hold steady if you drop the FK constraints?

If not, as others have noted, we'd need more info to investigate
this.  The leak is probably independent of the specific data in
the table, so maybe you could make a small self-contained example
using a script to generate dummy data.

			regards, tom lane