Process and Memory Management

Processes in PostgreSQL

PostgreSQL is a client/server type relational database management system with multi-process architecture, and it runs on a single host. A 'PostgreSQL Server' is a collection of multiple processes that cooperatively manage one database cluster.
Types of Processes:

1. Postgres server process: parent of all processes on the database cluster management
2. Backend Process: handles all queries and statements issued by a client
3. Background Processes: performs process of each feature, e.g. VACUUM and CHECKPOINT
4. Replication Associated Processes: performs the streaming replication
5. Background Worker Process: performs any processing implemented by users

We will now see 3 of the most important process in detail.

Postgres Server Process

It is the parent of all the process in the PostgreSQL server. After start up it allocates a shared memory area in memory, starts various background process, starts replication associated processes and background worker processes. It also waits for the connection requests from the clients and on receiving a request it starts the backend process which in turn handles all the requests.

Backend Process

It is the process which handles all the queries by connected clients. It is started by Postgres Server Process. TCP is used for the communication with the clients. This process is allowed to operate on a single database, so it is important to specify the database for connection. There can be multiple connections up to the max_connection.

Background Process

These processes performs specific functions of the PostgreSQL and all the processes depends on the specific features. For example, background writer writes dirty pages on a shared buffer pool are written to a persistent storage, checkpointer performs the checkpoint function, WAL writer periodically writes and flushes WAL data from the WAL buffer to the persistent storage, logger writes error messages into log files.

Memory Management in PostgreSQL

PostgreSQL has broadly two types of memory:

1. Local Memory Area: This is the memory which is allocated by each backend process for its local use like for query processing. The memory is divided into sub-areas with either fixed or variable sizes. For example, work_mem for operations like ORDER BY, DISTINCT or joining, temp_buffers for storing temporary tables.
2. Shared Memory Area: It is allocated by a PostgreSQL server at start up. This memory is also divided into sub-areas with either fixed or variable sizes. For example, share buffer pool for loading pages within tables, WAL buffer to implement Write Ahead Logic, commit log to keep states of all transactions. PostgreSQL also allocates several areas for access control mechanisms (e.g. semaphores, locks), for background processes and transaction processing.