PostgreSQL Memory Buffers

Shared Buffers:

Shared buffers is a modification buffer area. It is always faster to read or write data in memory than on any other media. It hold only image copies of your data pages. The default size is 128MB. If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system.

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

Wal Buffers:(write ahead log – wal)

Wal buffers is mainly used for backup and recovery purpose. Whatever changes made in this server, like any modification queries are entry into wal buffer. It is called for ring buffer. The default size is 4MB. This parameter can only be set at server start.

The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases.

C-Log 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.

work_mem:

Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. Default is 4M. Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value.

maintenance_work_mem:

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, reindex, analyze, backup and ALTER TABLE ADD FOREIGN KEY. Default is 64M. Since only one of these operations can be executed at a time by a database session, and an installation normally doesn’t have many of them running concurrently, it’s safe to set this value significantly larger than work_mem.

temp_buffers:

Sets the maximum number of temporary buffers used by each database session. Default is 8M. The setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session.