The memoryThe PostgreSQL's memory structure is not complex like other databases. In this section we'll take a to the various parts.
The segment is formatted in blocks with the same size of the data file's blocks, usually 8192 bytes. Each backend connected to the cluster is attached to this segment. Because usually its size is a fraction of the cluster's size, a simple but very efficient mechanism keeps in memory the blocks using a combination of LRU and MRU.
Since the the version 8.3 is present a protection mechanism to avoid the massive block eviction when intensive IO operations, like vacuum or big sequential reads, happens.
Each database operation, read or write, is performed moving the blocks via the shared buffer. This ensure an effective caching process and the memory routines guarantee the consistent read and write at any time.
PostgreSQL, in order to protect the shared buffer from potential corruption, if any unclean disconnection happens, resets by default all the connections.
This behaviour can be disabled in the configuration file but exposes the shared buffer to data corruption if the unclean disconnections are not correctly managed.
If the operation's memory usage exceeds the work_mem value then the PostgreSQL switches to a disk sort/hash.
Increasing the work_mem value results generally in better performances for sort/hash operations.
Because is a per user memory segment, the potential amount of memory required in a running instance is max_connections * work_mem. It's very important to set this value to a reasonable size in order to avoid any risk of out of memory error or unwanted swap.
In complex queries is likely to have many sort or hash operations in parallel and each one consumes the amount of work_mem for the session.