Understanding The Process and Memory Architecture of PostgreSQL

In my last article, I covered the steps to install PostgreSQL easily and stated that I would also share steps to install AGE. However, I think it is important to share what happens in the background while running PostgreSQL before installing AGE. This post covers the summary of the process and memory architecture of PostgreSQL.

PROCESS ARCHITECTURE
A PostgreSQL server is a series of various processes cooperatively managing a database cluster (recall that to initialize a cluster, initdb cluster_name is executed).
The server contains the following processes which will be explained in detail:

• Postgres server process
• Backend process
• Background process
• Replication associated process
• Background worker process.

Only the first three types are covered here with more emphasis on the first two.

1. Postgres Server Process (earlier called the ‘postmaster’) is the parent process related to the management of the database cluster. It is started by executing pg_ctl -D dbname -l logfile start. It starts various processes except the backend process after allocating a shared memory area in memory. It waits for requests from clients to connect and starts the backend process when receiving that request. The server’s default port is 5432 and this is the only port it listens to.
2. Backend Processes (known as ‘postgres’) is started by the postgres server process which was earlier mentioned. This process handles all queries by a connected client. It only operates on one database; the client has to specify the database when connecting to a server like so pg_ctl -D dbname -l logfile start.
3. Background Processes - this process is made up of some functions that are quite complicated and depends on specific features.

MEMORY ARCHITECTURE
This can be classified into two categories:

• Local memory area
• Shared memory area

1. Local Memory Area - this is allocated by each backend process for processing queries and is divided into sub-areas with fixed or varying sizes. The major sub-areas are work_mem, maintenance_work_mem, and temp_buffers.
2. Shared Memory Area is automatically allocated when the PostgreSQL server starts. Unlike the local memory area with fixed and varying sizes for sub-areas, the sizes for sub-areas of the shared memory area are fixed. The major sub-areas are shared buffer pool, WAL (Write Ahead Logging) buffer, and commit log.

CONCLUSION
Understanding the working processes of the software platform that one is utilizing is highly essential, as it facilitates debugging and efficient utilization. To delve deeper into the architecture of PostgreSQL, please refer to the resources section below for information on the Internals of PostgreSQL.

RESOURCES
The internals of PostgreSQL
Apache AGE website
Apache AGE project