Re: Reducing memory usage of insert into select operations?

On 11:25 am 07/18/08 Richard Huxton <dev(at)archonet(dot)com> wrote:
> I'm wondering whether it's memory usage either for the trigger
> itself, or for the function (pl/pgsql?).

Good point.

> If you're doing something
> like: INSERT INTO partitioned_table SELECT * FROM big_table
> then that's not only taking place within a single transaction, but
> within a single statement.

Correct.
I have kept decreasing work_mem and that does not seem to help.

> Without being a hacker, I'd say it's entirely plausible that PG might
> clean up triggers at the end of a statement meaning you would need
> memory for 200million+ triggers.

Sure hope that is not the case.

> Alternatively, it could be a memory-leak somewhere in the pl/pgsql or
> trigger code. Wouldn't have to be much to affect this particular case.

Will post an strace.

> What happens if you do the insert/select in stages but all in one
> transaction?

Will test.
The data is about a year worth of data. I will try to do one month at a
time, within a single transaction.

A single month finishes fine.

> Do you see PG's memory requirement stay constant or grow
> in steps. That will show whether the memory is growing over the
> duration of a statement or a transaction.

Right now for the single statement/transaction (the one big process) it is
growing slowly over time. It may be a leak. It seems to start growing
somewhere between the 1st and 2nd hower. It seems to always be failing
around 4 hours.

I wrote a little process that shows the amount of free memory every 15
minutes..

I will post strace for the big process and then will try breaking the
process down by month, but within a single transaction and report that
later when I get some results.