how to force postgresql to filll work_mem? i'm currently investigating a situation where my PostgreSQL 9.3 instance was killed by the Linux OOM Killer and went to recovery mode. The machine is EC2 Server with 16 GB of RAM and has rather few connections and is used for Business Analsys purposes only.

My assumption is that the reason for the out of memory situation is on one hand a very huge statement but also the fact that work_mem variable is set to 2GB while at the same time 100 connections are allowed.

And here are my questions:


is my assumption correct
if so, how can i force postgres to fill the session memory in a controlled manner to make the situation repeatable


I hope you guys can help me soon, thanks in advance and have a good week.
You definitely have too high memory settings. What you can do in your situation:
1) Lower work_mem
2) Lower max_connections

If you want to completely get rid of OOM-killer coming to your postgres server, then you should set kernel parameter vm.overcommit = 2 is sysctl.conf. This will forbid kernel to allocate more memory than it really have.

Important notes about this parameter:

1) Tune our memory-related settings. If something will try to allocate memory when it is full, you'll get Can't allocate memory problem. This can end even in no possibility of launch any bash command from command line because of no memory enough to fork().

2) Create relatively small swap area and set vm.swappiness to some low value (like 5 maybe, default is 60). This will save some nerves in case of trouble.