Postgres SQL for Dummies: The Architecture (Part 2)

Link to prev: Link
In the second in a series post, we will try to explore the Architecture of Postgres which can be primarily divided in two sub Architectures:

1. The Process Architecture
2. The Memory Architecture

The Process architecture

Postgres essentially works as a client-server application with multi-process support. The master of all the processes is the server process itself henceforth is was called postmaster before. It is the parent process. It starts up by allotting shared memory, launching background processes, and waiting for client connection requests. It manages the entire database cluster. Its also responsible for spinning up the backend processes for clients. After the server process we can divide the rest of the processes in two types as I like to remember them:

1. Backend processes
2. Background processes: Background writer, checkpointer, etc.

Backend processes

Backend processes handle all the queries and statements from connected clients. Each connection has its own backend process.

Background processes

The background processes have specific tasks and they are 7 in total. You can read up further on them but here I'll just mention a few to give you an idea. Background writer writes dirty pages to persistent storages such as SSD. Checkpointer as the name suggests checkpoints. Achiever logs the archiving logs and so on. TCP connection is used for connection with client and only one database can utilize the background processes and it needs to be specified by the client first.

The Memory Architecture

Postgres has two types of memories: The local memory and the global or shared memory. Local memory is allocated to each backend process whereas Shared Memory is used by all the server processes.

The local memory is allocated by the backend processes for its own use and are divided into sub-areas for different purposes, such as work memory and maintenance work memory while the shared memory is allocated the server itself and is allocated by the server at startup. Shared memory is also divided into sub-areas for storing pages, commit logs, etc.

The memory of Heap Tables can be read either as a Sequential Scan or a more complex but efficient B-tree index scan.