Le 03/03/2022 à 19:43, Tom Lane a écrit :
=?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@xxxxxxxxx> writes:
Le 03/03/2022 à 16:31, Tom Lane a écrit :
Does memory consumption hold steady if you drop the FK constraints?
Actually the number of rows is 232735712.
Accordingly the RAM consumption would be x12 x3 = 7.8 GiB.
This is close to the 8,1g I reported earlier (actually it was closer to
7.8 GB, due to GiB vs. GB confusion).
So there is no memory leak.
It took 16 hours on my box to reach that RAM consumption, and then the
COPY failed when checking the first FK (as the referenced table was empty).
I'm guessing it was swapping like mad :-(
We've long recommended dropping FK constraints during bulk data loads,
and then re-establishing them later. That's a lot cheaper than retail
validity checks, even without the memory-consumption angle. Ideally
that sort of behavior would be automated, but nobody's gotten that
done yet. (pg_restore does do it like that during a full restore,
but not for a data-only restore, so I guess you were doing the latter.)
regards, tom lane
Did the test without the 3 FK, but with PK and index:
I took 9.5 hours and consumed 1GB of RAM (vs. 16 hours and 8 GB).
Thanks you for the explanations.
I assume there is currently no GUC to limit RAM consumption of a backend ?
Marc
