PostgreSQL Shared memory

Shared memory comprises of the following three important sections.

Shared Buffers.
WAL Buffers.
CLOG Buffers.

PostgreSQL Shared Buffers

It is always faster to read or write data in memory than on any other media. A database server also needs memory for quick access to data, whether it is READ or WRITE access. In PostgreSQL, this is referred to as “shared buffers” and is controlled by the parameter shared_buffers. The amount of RAM required by shared buffers is always locked for the PostgreSQL instance during its lifetime. The shared buffers are accessed by all the background server 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”. Subsequently, the dirty data is written to disk containing physical files to record the data in persistent location and these files are called “data files”.

PostgreSQL.conf file parameter value :shared_buffers = 128MB

PostgreSQL Wal Buffers

The write-ahead log (WAL) buffers are also called “transaction log buffers”, which is an amount of memory allocation for storing WAL data. 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 persistent location called “WAL segments” or “checkpoint segments”.

The WAL buffers memory allocation is controlled by the wal_buffers parameter, and it is allocated from the operating system RAM. Although this memory area is also accessible by all the background server and user processes, it is not part of the shared buffers. The WAL buffers are external to the shared buffers and are very small when compared to shared buffers. The WAL data is first modified (dirtied) in WAL buffers before it is written to WAL segments on disk. If it is left to default settings, then it is allocated with a size of 1/16th of the shared buffers.

PostgreSQL.conf file parameter value : #wal_buffers = -1

PostgreSQL CLOG Buffers

CLOG stands for “commit log”, and the CLOG buffers is an area in operating system RAM dedicated to hold commit log pages. The commit log pages contain log of transaction metadata and differ from the WAL data. The commit logs have 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. This is a shared memory component, which is accessible to all the background server and user processes of a PostgreSQL database.

Memory for Locks / Lock Space

This memory component is to store all heavyweight locks used by the PostgreSQL instance. These locks are shared across all the background server and user processes connecting to the database. A non-default larger setting of two database parameters namely max_locks_per_transaction and max_pred_locks_per_transactionin a way influences the size of this memory component.

Default values for PostgreSQL Lock space in postgresql.conf file.

#deadlock_timeout = 1s
#max_locks_per_transaction = 64
#max_pred_locks_per_transaction = 64
#max_pred_locks_per_relation = -2
#max_pred_locks_per_page = 2