Re: How to troubleshoot high mem usage by postgres?

Chris <ctlajoie(at)gmail(dot)com> writes:
> Hi, I'm having an issue where a postgres process is taking too much
> memory when performing many consecutive inserts and updates from a PHP
> script (running on the command line). I would like to know what sort
> of logging I can turn on to help me determine what is causing memory
> to be consumed and not released.

Are you doing all these inserts/updates within a single transaction?

If so, I think the odds are very good that what's eating the memory
is the list of pending trigger actions, resulting from either
user-created triggers or foreign-key check triggers. The best way
of limiting the problem is to commit more often.

If you want to try to confirm that, what I would do is run the
postmaster under a more restrictive ulimit setting, so that it
runs out of memory sometime before the system starts to swap.
When it does run out of memory, you'll get a memory context map
printed to postmaster stderr, and that will show which context
is eating all the memory. If it's "AfterTriggerEvents" then my
guess above is correct --- otherwise post the map for inspection.

regards, tom lane