53200 Out of Memory

You may encounter an error message like following.

ERROR: out of memory
DETAIL: Failed on request of size 536870912.

Explanation:

This low-level out-of-memory (OOM) error occurs when PostgreSQL is unable to allocate the memory required for a query to run, or any other internal operation to complete.

Note that depending on your operating system configuration you might also see the server process was terminated by signal 9: Killed message, which can occur when the operating system’s OOM killer process goes into action due to the whole system running out of memory.

PostgreSQL is usually good at handling the explicit out of memory errors, so if you only have a momentary out of memory condition it will recover without a restart, and without crashing.

On the other hand if you get the OOM killer message that indicates a process was terminated by the kernel, PostgreSQL will restart and then enter into recovery mode.

In general it is not advised to operate your database under a recurring OOM condition, as you may see unexpected error messages at any time, and processes are at risk of being terminated by the OS.

Recommended Action:

First of all, if you see this log event, it makes sense to capture the full message, including the last part that starts with TopMemoryContext and describes individual blocks in detail, for later analysis. This quite length memory statistics dump can help to determine which part of PostgreSQL was using RAM.

More generally, often out of memory occurs when operating a combination of too high work_mem together with a high number of active connections.

If you are getting frequent OOM errors a good first step is to reduce work_mem, which should improve stability at the cost of more sort/hash operations going to disk instead.

It also often makes sense to monitor the number of active connections at the time of the issue. To know the current number of connections, run the following query.

SELECT count(*) FROM pg_stat_activity;

Additionally, if you absolutely need more RAM to work with, you can evaluate reducing shared_buffers to provide more available RAM for memory directly used by connections. This should be done carefully, and whilst actively watching Buffer Cache Hit Ratio statistics.