<snip>
> ... under the periods of heavy swapping, one or more of the postgres
>processes would be way up there (between 500MB and 1000MB (which would
>easily explain the swapping)) ... the question is: why aren't all of the
>processes sharing the same pool of shared memory since I thought that's what
>I'm doing when adjusting the shared_buffers property?
>
>
<snip>
I seem to remember hearing that the memory limit on certain operations,
such as sorts, is not "enforced" (may the hackers correct me if I am
wrong); rather, the planner estimates how much a sort might take by
looking at the statistics for a table.
If the statistics are wrong, however, the sort doesn't actually stay
within sort memory, and so the process consumes a very large amount of
memory, much more than the sort_mem configuration parameter should allow
it to.
If the other suggestions given (to reduce the shared buffers) don't fix
it, I suggest running ANALYZE all your tables and see if the erratic
memory usage goes away. If that doesn't help, then try to figure out
what query is causing the high memory usage, and run EXPLAIN ANALYZE on
just that query to see if it is returning drastically more rows than the
planner thinks it will.
Paul Tillotson