PostgreSQL Architecture – Memory Components

In this post we will discuss Memory Components of PostgreSQL, Memory Components in PostgreSQL can be divided mainly into two category memory region 1). Shared Memory 2). Process Memory or Backend Memory

Shared Memory

Shared Memory: Shared Memory refers to the memory reserved for database caching and transaction log caching. This area is used by all processes of a PostgreSQL server. When the server gets started, it occupies some of the memory from the RAM. Depending upon the various parameters like shared_buffers, wal_buffers, etc.

Shared Memory can be further classified as follow:

Shared Buffer: Sets the amount of memory the database server uses for shared memory buffers. The purpose of Shared Buffer is to minimize DISK IO. The shared buffers are accessed by all the background servers and user processes connecting to the database. The data that is written or modified in this location is called “dirty data” and the unit of operation being database blocks (or pages), the modified blocks are also called “dirty blocks” or “dirty pages”. Shared buffers are controlled by the parameter shared_buffers.

WAL (Write Ahead Log) Buffer: The WAL buffer is a buffer that temporarily stores changes to the database. This WAL data is the metadata information about changes to the actual data and is sufficient to reconstruct actual data during database recovery operations. The WAL data is written to a set of physical files in a persistent location called “WAL segments“. The WAL buffer’s memory allocation is controlled by the wal_buffers parameter.

Clog Buffer: CLOG stands for “commit log” CLOG buffers is an area to hold commit log pages. The commit log pages contain a log of transaction metadata and differ from the WAL data. The commit logs have the commit status of all transactions and indicate whether or not a transaction has been completed (committed). There is no specific parameter to control this area of memory. This is automatically managed by the database engine in tiny amounts.

Lock Space (Memory for Locks): This memory component is to store all heavyweight locks used by the PostgreSQL instance. These locks are shared across all the background servers and user processes connecting to the database. A setting of two database parameters namely max_locks_per_transaction and max_pred_locks_per_transaction in way influences the size of this memory component.

Process Memory or Backend Memory

Process Memory: This memory area is allocated by each backend process for its own use. It is temporarily used privately by each Postgres process. By default, each session will take size of 4Mb. Eg.: If there are 100 sessions, then they will consume 400 Mb.

Process Memory can be further classified as follow:

Vacuum Buffers: 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. All these parameter settings only come into play when the auto vacuum daemon is enabled. This memory component is not shared by any other background server or user process.

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 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 numbers 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.

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.

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. The temp buffers defined by the temp_buffers parameter. The temp buffers are only used for access to temporary tables in a user session.

postgres=# select name,setting,unit from pg_settings where name like '%buffer%';
      name      | setting | unit
----------------+---------+------
 shared_buffers | 16384   | 8kB
 temp_buffers   | 1024    | 8kB
 wal_buffers    | 512     | 8kB
(3 rows)

postgres=# select name,setting,unit from pg_settings where name like '%work_mem%';
           name            | setting | unit
---------------------------+---------+------
 autovacuum_work_mem       | -1      | kB
 logical_decoding_work_mem | 65536   | kB
 maintenance_work_mem      | 65536   | kB
 work_mem                  | 4096    | kB
(4 rows)

Click on below to know more about Processes and Physical Storage of PostgreSQL.

Postgresql Architecture – Process
Postgresql Architecture – Physical Storage Structure

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBsGuru