DEV Community: Youssef

PostgreSQL EXPLAIN command

Youssef — Thu, 20 Jul 2023 12:03:24 +0000

The EXPLAIN command in Postgres is a very useful tool, it gives insights into how Postgres will perform queries, which means it helps us understand and compare queries for optimization purposes. In this post i'll explain how to use EXPLAIN command.

Why?

As previously mentioned, the EXPLAIN command will help us understand and optimize queries, to further understand why this is needed we need to look at SQL.
SQL is what is known as a 'Declarative language', which means we Declare what we want, and the database figures out the best way to execute instructions to return the results of our query.
A special module called the Planner is responsible for figuring out the best 'plan' to execute the query, and the EXPLAIN command allows us to see that plan.

Usage

Using the EXPLAIN command is as simple as prepending it to your normal query, for example:

EXPLAIN SELECT * FROM <database_name>;

will return the estimated plan and cost in plain text (though you can change the output format as we will see next).

ANALYZE can also be prepended with EXPLAIN to run the query and output the actual run statistics.

Note: This will also run your query, so be careful not to alter the database!

Interpreting the Output

The output generated by the EXPLAIN command contains details about the sequence of steps that PostgreSQL will take to retrieve the data.

Some of the components we see in the output are:

Node Types: Node types represent the different operations performed during the query execution.
Common node types include: "Seq Scan" (sequential scan), "Index Scan" (index scan), "Nested Loop", ...etc.
Join Types: If the query involves joining multiple tables, you will see the type of join used.
join types include: "Nested Loop Join", "Hash Join", and "Merge Join".
Relation Information: For each node, you'll find details about the involved relations or tables (their names, aliases, and access methods, ...etc).
Filter Conditions: Filter conditions applied during the execution.
Costs: The cost of each operation during query execution, it reflects the computational effort required, and it helps the planner choose the most efficient execution plan.
Execution Order: The order in which nodes are processed.

Performance optimizations

Here are some tips for optimizing queries once you have the output of EXPLAIN:

Look for Sequential Scans: Sequential scans can be slow on large tables. These can be mitigated by creating indexes on columns frequently used in WHERE clauses or JOIN conditions to speed up queries.
Large Sort Operations: Sorting large datasets can be resource-intensive and is best avoided. Ensure you have indexes on columns used in ORDER BY clauses. Consider Caching: If you notice repetitive queries in the output, caching might be beneficial to reduce database load.

References

https://www.crunchydata.com/blog/get-started-with-explain-analyze

PostgreSQL Foreign Data Wrapper (FDW)

Youssef — Thu, 20 Jul 2023 10:01:38 +0000

Recently i've been exposed to the idea of foreign data wrappers in Postgres, and thought i'd share what i've learned so far.

What is it?

The idea behind it is simple: being able to access and interact with data stored in remote (external) databases (i.e databases that live on different machines) as if they were regular database tables.
PostgreSQL provides this feature through an extension called postgres_fdw.

Setup

The general steps to setup and use the extension are as follows:

Install FDW extension, postgres_fdw comes pre-installed with Postgres and is officially supported. But you can also use other extensions.
Create server, use the CREATE SERVER statement to define a connection to the remote data source.
Create user mapping, create a user mapping to associate a local PostgreSQL user with a remote user for authentication purposes.
Create foreign table, use the CREATE FOREIGN TABLE statement to define the structure/schema of the foreign table (column names, data types, ...etc). There is also a command to automatically import the schema.
Querying foreign tables, you can finally interact with foreign tables as if they were regular local tables. Using normal SQL (Select, insert, ...etc). A good tip here would be to optimize performance, you can do so by pushing down operations to the remote server, i.e asking the foreign server to perform the cost estimate (EXPLAIN command) by setting the use_remote_estimate option to true on the server or table level

You can follow a step-by-step tutorial through a helpful guide i found, though one thing to note if you don't have another machine to test fdw is that you can actually use the same machine and 2 seperate databases.

References

Getting comfortable with PostgreSQL

Youssef — Fri, 14 Jul 2023 14:09:58 +0000

In this post i will talk about some Postgres basics.
I will assume you have already installed Postgresql.11+ and that you have basic terminal skills.

Utilities

First, navigate into the postgres directory where it exists, you'll find many files and directories, but we're concerned with the bin/ directory, which is where you'll find postgres utilities that we'll be using.

Clusters

Next, run the initdb utility inside the bin/ directory and provide the name for your database cluster using the -D flag
bin/initdb -D your-cluster-name

A Database Cluster is a collection of databases. Each cluster is managed by a PostgreSQL server which runs on a single host.
Clusters are stored as a directory referred to as base directory. You can refer to my guide for more information if you're interested.

This will initialize a database cluster which we will be using to create databases.

pg_ctl

The pg_ctl utility is used to manage Postgres servers and processes.
Run bin/pg_ctl -D your-cluster-name to start the Postgres server for the cluster you just created. By default, Postgres will use port 5432

psql

The psql utility is used to start an interactive shell with your database cluster so that you can use SQL like you would with any other DBMS.
Run bin/psql -l to list all available databases in your cluster. In Postgres version 11+ clusters are created with 3 default databases: Postgres, Sample0, Sample1
Run bin/psql postgres.

you can exit this shell using \q
type 'help' to see the help prompt

SQL commands

Let's create a new database using the command
CREATE DATABASE your-database-name; (Don't forget the semicolon!)
now run \l to view all the databases again (from inside the shell this time), you can see the new database was created.
Now instead of exiting and reconnecting to the new database, you can instead use \c your-database-name to directly switch to it.

This should be enough to get you started exploring Postgres.

PostgreSQL Summary Pt8

Youssef — Mon, 10 Jul 2023 11:48:28 +0000

Continuing on part 7, i'll be discussing XLOG records, and the part they play in database recovery.

Writing of XLOG Records

We'll explore the internal function exec_simple_query() by invoking the following query: INSERT INTO tbl VALUES ('A');

The pseudo code of exec_simple_query():

exec_simple_query() @postgres.c

(1) ExtendCLOG() @clog.c                  /* Write the state of this transaction
                                           * "IN_PROGRESS" to the CLOG.
                                           */
(2) heap_insert()@heapam.c                /* Insert a tuple, creates a XLOG record,
                                           * and invoke the function XLogInsert.
                                           */
(3)   XLogInsert() @xlog.c (9.5 or later, xloginsert.c)
                                          /* Write the XLOG record of the inserted tuple
                                           *  to the WAL buffer, and update page's pd_lsn.
                                           */
(4) finish_xact_command() @postgres.c     /* Invoke commit action.*/   
      XLogInsert() @xlog.c  (9.5 or later, xloginsert.c)
                                          /* Write a XLOG record of this commit action 
                                           * to the WAL buffer.
                                           */
(5)   XLogWrite() @xlog.c                 /* Write and flush all XLOG records on 
                                           * the WAL buffer to WAL segment.
                                           */
(6) TransactionIdCommitTree() @transam.c  /* Change the state of this transaction 
                                           * from "IN_PROGRESS" to "COMMITTED" on the CLOG.
                                           */

Explanation:

ExtendCLOG() writes the state of this transaction IN_PROGRESS in the (in-memory) CLOG.
heap_insert() inserts a heap tuple into the target page on the shared buffer pool, creates this page's XLOG record, and invokes XLogInsert().
XLogInsert() writes the XLOG record created by the heap_insert() to the WAL buffer at LSN_1, and then updates the modified page's pd_lsn from LSN_0 to LSN_1.
finish_xact_command(), which is invoked to commit this transaction, creates this commit action's XLOG record, and then XLogInsert() writes this record into the WAL buffer at LSN_2.
XLogWrite() writes and flushes all XLOG records on the WAL buffer to the WAL segment file. If the parameter wal_sync_method is set to 'open_sync' or 'open_datasync', the records are synchronously written because the function writes all records with the open() system call specified the flag O_SYNC or O_DSYNC. If the parameter is set to 'fsync', 'fsync_writethrough' or 'fdatasync', the respective system call – fsync(), fcntl() with F_FULLFSYNC option, or fdatasync() – will be executed. In any case, all XLOG records are ensured to be written into the storage.
TransactionIdCommitTree() changes the state of this transaction from IN_PROGRESS to COMMITTED on the CLOG.

WAL Writer Process

It is a background process to check the WAL buffer periodically and write all unwritten XLOG records into the WAL segments.

The purpose of this process is to improves performance by avoiding bursts of of XLOG records writes when a large amount of data are committed at once.

This process cannot be disabled, and it checks the WAL buffer every 200 ms by default, this interval can by changed using the configuration parameter wal_writer_delay.'

Checkpoint Processing in PostgreSQL

Checkpointing in PostgreSQL is performed by the checkpointer background process, it has two responsibilities:

Preparation of database recovery.
Cleaning of dirty pages on the shared buffer pool.

It starts when one of the following occurs:

A superuser issues CHECKPOINT command manually.
The interval time set for checkpoint_timeout from the previous checkpoint has exceeded 300secs.
(version 9.4 or earlier) The number of WAL segment files (3 by default) set for checkpoint_segments has been consumed since the previous checkpoint.
(version 9.5 or later) The total size of the WAL segment files in the pg_xlog (called pg_wal in version 10 or later) has exceeded the value of the parameter max_wal_size (1GB (64 files) by default).
PostgreSQL server stops in smart or fast mode.

pg_control File

This file contains fundamental information required by the checkpointer process, which is essential for database recovery.

This file stores over 40 items, however i'll be talking about three specific items:

State: The state of database server at the time of the latest checkpointing starts.
There are seven states in total, some of which are:
1. start up is the state that system is starting up.
1. shut down is the state that system is going down normally by the shutdown command
2. in production is the state that system is running.
Latest checkpoint location: LSN Location of the latest checkpoint record.
Prior checkpoint location (deprecated in version 11): LSN Location of the prior checkpoint record .

A pg_control file is stored in the global subdirectory under the base-directory; its contents can be shown using the pg_controldata utility.

To summarize

The checkpointer creates the checkpoint record which contains the REDO point, stores the checkpoint location into the pg_control file.
This allows PostgreSQL to recover itself by replaying WAL data from the REDO point which is obtained from the checkpoint record provided by the pg_control file.

References

https://www.interdb.jp/pg/pgsql09.html

PostgreSQL Summary Pt7

Youssef — Sat, 08 Jul 2023 22:29:20 +0000

Write Ahead Log (WAL) is a transaction logging mechanism implemented in PostgreSQL 7.1.
I'll be discussing some things needed to understand WAL.

Why is it useful?

It ensures that no data is lost even when a system failure occurs.
It also makes possible the implementation of Point-in-Time Recovery (PITR) and Streaming Replication (SR).

How?

The log contains information about each transaction executed, allowing database server to recover the database cluster by replaying changes and actions.

Here are some points that will be useful later:

Postgres writes all modifications as history data (aka XLOG records or WAL data) to persistent storage.
XLOG records are written into a buffer in memory called WAL buffer. When a transaction commits or aborts (and others), they are immediately written into a WAL segment file on the storage.
As mentioned in part 2, LSN (Log Sequence Number) is the unique id of an XLOG record and represents the location where it is written on the transaction log.
Postgres starts to recover from the REDO point, which is the location to write the XLOG record at the moment when the latest checkpoint is started.
The database recovery process and the checkpoint process always occur together.

Insertion with WAL

The following are the steps that Postgres performs when an INSERT statement is issued:

Whenever the checkpointer starts, it writes an XLOG record called checkpoint record to the current WAL segment. This record contains the location of the latest REDO point.
Issuing the first INSERT statement, PostgreSQL loads the database table's page into the shared buffer pool, inserts a tuple into the page, creates and writes a XLOG record of this statement into the WAL buffer at the location LSN_1, and updates the table's LSN from LSN_0 to LSN_1.
As this transaction commits, PostgreSQL creates and writes a XLOG record of this commit action into the WAL buffer, and then, writes and flushes all XLOG records on the WAL buffer to the WAL segment file, from LSN_1.
Issuing the second INSERT statement, PostgreSQL inserts a new tuple into the page, creates and writes this tuple's XLOG record to the WAL buffer at LSN_2, and updates the table's LSN from LSN_1 to LSN_2.
When this statement's transaction commits, PostgreSQL operates in the same manner as in step (3).

WAL Segment structure

A WAL segment is a 16 MB file, by default, and it is internally divided into pages of 8192 bytes (8 KB).

The first page has a header-data defined by the structure XLogLongPageHeaderData, while the headings of all other pages have the page information defined by the structure XLogPageHeaderData.

Following the page header, XLOG records are written in each page from the beginning in descending order.

XLOG Record Structure

An XLOG record comprises the general header portion and each associated data portion.

Header Structure

The header structure is the same for all XLOG records. It consists of the following fields:

version: The XLOG record version number.
type: The type of XLOG record.
timestamp: The timestamp of the XLOG record.
length: The length of the XLOG record.

Data Portion (Version 9.4 or Earlier)

The data portion of an XLOG record in version 9.4 or earlier is specific to the type of XLOG record. For example, the data portion of a COMMIT XLOG record contains the transaction ID of the committed transaction.

Data Portion (Version 9.5)

The data portion of an XLOG record in version 9.5 has a common format. It consists of the following fields:

data_header: A header that describes the data portion.
data: The actual data.

The data_header field contains the following information:

data_format: The data format version number.
data_length: The length of the data field.

The data field can contain any type of data. The specific format of the data is determined by the data_format field.

References

PostgreSQL Summary Pt6

Youssef — Thu, 29 Jun 2023 11:11:07 +0000

In this part i'll be discussing the VACUUM process in Postgres.

VACUUM is a maintenance process that facilitates the persistent operation of PostgreSQL. Its two main tasks are removing dead tuples and freezing transaction ids.

To remove dead tuples, vacuum processing provides two modes:

Concurrent VACUUM: Often called VACUUM, removes dead tuples for each page of the table file. Other transactions can read the table while this process is running.
Full VACUUM: Removes dead tuples and defragments live tuples. Other transactions *cannot access tables while Full VACUUM is running.

Outline of Concurrent VACUUM

Vacuum processing performs the following tasks for specified tables or all tables in the database.

Removing dead tuples
- Remove dead tuples and defragment live tuples for each page.
- Remove index tuples that point to dead tuples.
Freezing old txids
- Freeze old txids of tuples if necessary.
- Update frozen txid related system catalogs (pg_database and pg_class).
- Remove unnecessary parts of the clog if possible.
Others
- Update the FSM and VM of processed tables.
- Update several statistics (pg_stat_all_tables, etc).

Following is pseudocode of concurrent VACUUM, and it can be described in three blocks.

FOR each table
    Acquire ShareUpdateExclusiveLock lock for the target table
    /* The first block */
    Scan all pages to get all dead tuples, and freeze old tuples if necessary 
    Remove the index tuples that point to the respective dead tuples if exists

    /* The second block */
    FOR each page of the table
        Remove the dead tuples, and Reallocate the live tuples in the page
        Update FSM and VM
    END FOR

  /* The third block */
   Clean up indexes
   Truncate the last page if possible
   Update both the statistics and system catalogs of the target table
   Release ShareUpdateExclusiveLock lock
END FOR

/* Post-processing */
Update statistics and system catalogs
Remove both unnecessary files and pages of the clog if possible

First Block

This block performs freeze processing and removes index tuples that point to dead tuples.
First, PostgreSQL scans a target table to build a list of dead tuples and freeze old tuples if possible. The list is stored in maintenance_work_mem in local memory.
Then it performs a cleanup stage, where it removes index tuples by referring to the dead tuple list.
This process is costly. In version 10 or earlier, the cleanup stage is always executed. In version 11 or later, if the target index is B-tree, whether the cleanup stage is executed or not is decided by the configuration parameter vacuum_cleanup_index_scale_factor.

Second Block

This block removes dead tuples and updates both the FSM and VM for each page of the table.

Note: unnecessary line pointers are not removed and they will be reused in future. This is because, if line pointers are removed, all index tuples of the associated indexes must be updated.

Third Block

The third block performs the cleanup after the deletion of the indexes, and also updates both the statistics and system catalogs related to vacuum processing for each target table. Moreover, if the last page has no tuples, it is truncated from the table file.

Post-processing

When vacuum processing is complete, PostgreSQL updates both several statistics and system catalogs related to vacuum processing, and it removes unnecessary parts of the clog if possible.

Visibility Map

Since vacuum processing involves scanning whole tables, it is a costly process. VM was introduced in version 8.4 to reduce this cost.

Each table has an individual visibility map that holds the visibility of each page in the table file, allowing Postgres to quickly identify dead tuples. This allows VACUUM to process only the dead tuples and skip the rest.

Freeze Processing

Freeze processing has two modes:

Lazy Mode

freeze processing scans only pages that contain dead tuples using the respective VM of the target tables.

When starting freeze processing, PostgreSQL calculates the freezeLimit txid and freezes tuples whose t_xmin is less than the freezeLimit txid.
The freezeLimit txid is defined as:
freezeLimit_txid = (OldestXmin − vacuum_freeze_min_age)
where OldestXmin is the oldest txid among currently running transactions.

Eager Mode

scans all pages regardless of whether each page contains dead tuples or not, and it also updates system catalogs related to freeze processing and removes unnecessary parts of the clog if possible.

Eager mode is performed when the following condition is satisfied.
pg_database.datfrozenxid < (OldestXmin − vacuum_freeze_table_age)
where pg_database.datfrozenxid represents the columns of the pg_database system catalog and holds the oldest frozen txid for each database.

Autovacuum Daemon

Vacuum processing has been automated with the autovacuum daemon; thus, the operation of PostgreSQL has become extremely easy.

References

https://www.interdb.jp/pg/pgsql06.html

PostgreSQL Summary Pt5

Youssef — Wed, 28 Jun 2023 13:55:50 +0000

In this part i will discuss basic information required for understanding concurrency control in Postgres.

PostgreSQL uses a variation of Multi-version Concurrency Control (MVCC) called Snapshot Isolation (SI).

In MVCC, Each write operation creates a new version of a data item while retaining the old version. When a transaction reads a data item, the system selects one of the versions to ensure isolation of the individual transaction. The main advantage of MVCC is that readers don’t block writers, and writers don’t block readers

In SI, A new data item is inserted directly into the relevant table page. When reading items, PostgreSQL selects the appropriate version of an item in response to an individual transaction by applying visibility check rules (will talk about this later).

Transaction ID

Whenever a transaction begins, a unique identifier, referred to as a transaction id (txid), which is a 32-bit unsigned integer assigned by the transaction manager.

The built-in txid_current() function can be used after a transaction starts to check the current txid.

However there are three special txids reserved by Postgres:

0:Invalid txid.
1:Bootstrap txid, which is only used in the initialization of the database cluster.
2:Frozen txid.

Txids are sequential, and for a given id n, all ids > n are invisible to it, and all ids < n are visible to it.

Tuple Structure

A heap tuple consists of three parts:

HeapTupleHeaderData structure
NULL bitmap
user data

Here are four relevant fields of the HeapTupleHeaderData:

t_xmin holds the txid of the transaction that inserted this tuple.
t_xmax holds the txid of the transaction that deleted or updated this tuple. If this tuple has not been deleted or updated, t_xmax is set to 0, which means INVALID.
t_cid holds the command id (cid), which means how many SQL commands were executed before this command was executed within the current transaction beginning from 0.
t_ctid holds the tuple identifier (tid) that points to itself or a new tuple. tid is used to identify a tuple within a table. When this tuple is updated, the t_ctid of this tuple points to the new tuple; otherwise, the t_ctid points to itself.

Free Space Map (FSM)

When inserting a heap or an index tuple, PostgreSQL uses the FSM of the corresponding table or index to select the page which can be inserted it.

Commit Log (clog)

PostgreSQL holds the statuses of transactions in the Commit Log (often called the clog). It is allocated to the shared memory, and is used throughout transaction processing.

Transaction Status

PostgreSQL defines four transaction states:

IN_PROGRESS
COMMITTED
ABORTED
SUB_COMMITTED (for sub-transactions)

How it works

The clog comprises one or more 8 KB pages in shared memory. The clog logically forms an array. The indices of the array correspond to the respective transaction ids, and each item in the array holds the status of the corresponding transaction id.

Maintenance of the Clog

When PostgreSQL shuts down or whenever the checkpoint process runs, the data of the clog are written into files stored under the pg_xact subdirectory (called pg_clog in Version 9.6 or earlier.) These files are named 0000, 0001.
When PostgreSQL starts up, the data stored in the pg_xact's files are loaded to initialize the clog.
The size of the clog continuously increases on each new page, but since not all data in the clog are necessary, the VACUUM process regularly removes old data (both the clog pages and files).

Transaction Snapshot

A transaction snapshot is a dataset that stores information about whether all transactions are active at a certain point in time for an individual transaction. Here an active transaction means it is in progress or has not yet started.

When using the obtained snapshot for the visibility check, active transactions in the snapshot must be treated as in progress even if they have actually been committed or aborted. This rule is important because it causes the difference in the behaviour between READ COMMITTED and REPEATABLE READ.

Visibility Check Rules

Visibility check rules are a set of rules used to determine whether each tuple is visible or invisible using both the t_xmin and t_xmax of the tuple, the clog, and the obtained transaction snapshot.

Following are some minimal rules:

Status of t_xmin is ABORTED

Rule 1: A tuple whose t_xmin status is ABORTED is always invisible because the transaction that inserted this tuple has been aborted.

Status of t_xmin is IN_PROGRESS

Rules 3 & 4: A tuple whose t_xmin status is IN_PROGRESS is invisible, except under one condition.

Status of t_xmin is COMMITTED

A tuple whose t_xmin status is COMMITTED is visible (Rules 6,8, and 9), except under three conditions.

References

https://www.interdb.jp/pg/pgsql05.html

PostgreSQL Summary Pt3

Youssef — Sun, 25 Jun 2023 20:57:31 +0000

In this post we'll talk about Processes in Postgres as well as Memory Management.

Process Management

Postgres uses a collection of processes usually referred to as a PostgreSQL server to manage a database database cluster.

It contains the following types of processes:

A postgres server process is a parent of all processes related to a database cluster management.
Each backend process handles all queries and statements issued by a connected client.
Various background processes perform processes of each feature (e.g., VACUUM and CHECKPOINT processes) for database management.
In the replication associated processes, they perform the streaming replication.
In the background worker process supported from version 9.3, it can perform any processing implemented by users.

Postgres Server Process

The parent of all in a PostgreSQL server. In the earlier versions, it was called postmaster.

By executing the pg_ctl utility with start option, a postgres server process starts up.

Allocates a shared memory area in memory.
Starts various background processes.
Starts replicating associated processes and background worker processes if necessary.
Waits for connection requests from clients. On receiving a connection request from a client, it starts a backend process (which handles all queries issued by the connected client.)

A postgres server process listens to one network port, the default port is 5432. Although more than one PostgreSQL server can be run on the same host.

Backend Processes

A backend process, (also called postgres), is started by the postgres server process and handles all queries issued by one connected client. It communicates with the client by a single TCP connection, and terminates when the client gets disconnected.

As it is allowed to operate only one database, you have to specify a database you want to use explicitly when connecting to a PostgreSQL server.

PostgreSQL allows multiple clients to connect simultaneously; the configuration parameter max_connections controls the maximum number of the clients (default is 100).

If many clients such as Web applications frequently repeat the connection and disconnection with a PostgreSQL server, it increases both costs of establishing connections and of creating backend processes because PostgreSQL has not implemented a native connection pooling feature. Such circumstance has a negative effect on the performance of database server. To deal with such a case, a pooling middleware (either pgbouncer or pgpool-II) is usually used.

Background Processes

Here are the detailed description of some processes.

process	description
background writer	Writes dirty pages on the shared buffer pool gradually and regularly to a persistent storage. (In version 9.1 or earlier, it was also responsible for checkpoint process.)
checkpointer	(version 9.2+) Performs checkpointing.
autovacuum launcher	The autovacuum-worker processes are invoked for vacuum process periodically.
WAL writer	Writes and flushes periodically the WAL data on the WAL buffer to persistent storage.
statistics collector	Collects statistics information such as for pg_stat_activity and for pg_stat_database, etc.
logger	Writes error messages into log files.
archiver	Archiving logging.

Memory Management

Memory architecture in PostgreSQL can be classified into two broad categories:

1. Local memory area

Each backend process allocates a local memory area for query processing; each area is divided into several sub-areas – whose sizes are either fixed or variable.

sub-area	description
work_mem	Executor uses this area for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.
maintenance_work_mem	Some kinds of maintenance operations (e.g., VACUUM, REINDEX) use this area.
temp_buffers	Executor uses this area for storing temporary tables.

2. Shared memory area

A shared memory area is allocated by a PostgreSQL server when it starts up, and is used by all processes of a PostgreSQL server. This area is also divided into several fix sized sub-areas.

sub-area	description
shared buffer pool	PostgreSQL loads pages within tables and indexes from a persistent storage to here, and operates them directly.
WAL buffer	Buffering area of the WAL data before writing to a persistent storage. To ensure that no data has been lost by server failures.
commit log	Commit Log (CLOG) keeps the states of all transactions (e.g., in_progress, committed, aborted) for Concurrency Control (CC) mechanism.

In addition to them, PostgreSQL allocates several other areas such as:

Sub-areas for the various access control mechanisms. (e.g., semaphores, lightweight locks, shared and exclusive locks, etc)
Sub-areas for the various background processes, such as checkpointer and autovacuum.
Sub-areas for transaction processing such as save-point and two-phase-commit.