PostgreSQL: Local Memory

Local memory is further classified into

1. work mem
2. Maintenance work mem
3. Temp buffers

PostgreSQL: Work Memory

This is the amount of memory reserved for either a single sort or hash table operation in a query and it is controlled by work_mem database parameter. A sort operation could be one of an ORDER BY, DISTINCT or Merge join and a hash table operation could be due to a hash-join, hash-based aggregation or an IN subquery.

A single complex query may have any number of such sort or hash table operations, and as many chunks of memory allocations defined by the work_mem parameter will be created for each of those operations in a user connection. It is for this reason, that work_mem should not be declared to a very big value as it might lead to aggressively utilising all the available memory from the operating system for a considerably huge query, thereby starving the operating system of RAM which might be needed for other processes.

PostgreSQL.conf file parameter value : #work_mem = 4MB

PostgreSQL Maintenance Work Memory

This is the maximum amount of memory allocation of RAM consumed for maintenance operations. A maintenance operation could be one of the VACUUM, CREATE INDEX or adding a FOREIGN KEY to a table. The setting is controlled by the maintenance_work_mem database parameter.

A database session could only execute any of the abovementioned maintenance operations at a time and a PostgreSQL instance does not normally execute many such maintenance operations concurrently. Hence, this parameter can be set significantly larger than work_mem parameter. A point of caution is to not set this memory to a very high value, which will allocate as many portions of memory allocations as defined by the autovacuum_max_workers parameter in the event of not configuring the autovacuum_work_mem parameter.

PostgreSQL.conf file parameter value : #maintenance_work_mem = 64MB

PostgreSQL Temp Buffers

A database may have one or more temporary tables, and the data blocks (pages) of such temporary tables need a separate allocation of memory to be processed in. The temp buffers serve this purpose by utilising a portion of RAM, defined by the temp_buffers parameter. The temp buffers are only used for access to temporary tables in a user session. There is no relation between temp buffers in memory and the temporary files that are created under the pgsql_tmp directory during large sort and hash table operations.

PostgreSQL.conf file parameter value : #temp_buffers = 8MB

Vacuum Buffers(Optional)

This is the maximum amount of memory used by each of the autovacuum worker processes, and it is controlled by the autovacuum_work_mem database parameter.

The memory is allocated from the operating system RAM and is also influenced by the autovacuum_max_workers database parameter. The setting of autovacuum_work_mem should be configured carefully as autovacuum_max_workers times this memory will be allocated from the RAM. All these parameter settings only come into play when the auto vacuum daemon is enabled, otherwise, these settings have no effect on the behaviour of VACUUM when run in other contexts. This memory component is not shared by any other background server or user process.

PostgreSQL.conf file parameter value :autovacuum_work_mem = -1

Note: As local memory is local to the user’s backend process, the parameters can be changed at session level with below parameters.

postgres=# set local work_mem=’3GB’;
SET

postgres=# set maintenance_work_mem=’5GB’;
SET

postgres=# set temp_buffers=’3GB’;
SET