Re: large numbers of inserts out of memory strategy

On Tue, 28 Nov 2017 11:17:07 -0600
Ted Toth <txtoth(at)gmail(dot)com> wrote:

> I'm writing a migration utility to move data from non-rdbms data
> source to a postgres db. Currently I'm generating SQL INSERT
> statements involving 6 related tables for each 'thing'. With 100k or
> more 'things' to migrate I'm generating a lot of statements and when I
> try to import using psql postgres fails with 'out of memory' when
> running on a Linux VM with 4G of memory. If I break into smaller
> chunks say ~50K statements then thde import succeeds. I can change my
> migration utility to generate multiple files each with a limited
> number of INSERTs to get around this issue but maybe there's
> another/better way?

Chunking the loads has a number of advantages other than avoiding
OOM errors, not the least of which are possibly parallel loading
and being able to restart after other failures without re-processing
all of the input data.

Note that 4GiB of core is not all that much by today's standards.
You might want to run top/vmstat and ask if the PG daemons are using
all/most of the available memory. If PG is sucking up all of the core
as it is then tuning the database may not have much of an effect; if
there is lots of spare memory then it'll be worth looking at ways to
tune PG.

Note also that "out of memory" frequently means virutal memory.

Q: Does the VM have swap configured?

If not then add 8GiB and see if that solves your problem; if so then
how much swap is in use when you get the OOM error?

--
Steven Lembark 1505 National Ave
Workhorse Computing Rockford, IL 61103
lembark(at)wrkhors(dot)com +1 888 359 3508