PostgreSQL Process Architecture
Tags: apacheage, database, postgres, agedb
Published: 2023-03-06 23:00:26

In this post, we will discuss the Process Architecture of PostgreSQL. PostgreSQL is a **_client/server type_** relational database management system(**RDMS**) with the multi-process architecture and **_runs on a single host_**.

A **collection of multiple processes** cooperatively managing one database cluster is usually referred to as a **_'PostgreSQL server'_**, and its process tree will be show below figure: 

![Process Tree](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dzx229nkauubyg0612og.png)

## `✔️ Postgres server process:`

- 
It is a parent of all processes related to a database cluster management. Previously, it was called **‘postmaster’**.

- 
By executing the **_pg_ctl_** utility with start option, a postgres server process **starts up**. 


![Postgres server process](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/udmgeey7c28u0yhv7ohy.png)

- 
Then, it **allocates a shared memory** area in memory, starts various background processes, starts replication associated processes and background worker processes if necessary, and _**waits for connection**_ requests from clients. 

- 
After receiving a connection request from a client, it **_starts a backend process_**. After that, Backend process handles all queries issued by the connected client.

- 
A postgres server process work with one network port, the **_default port is 5432_**. Although more than one PostgreSQL server can be run on the same host, then port number can be set as 5432, 5433 etc.

## `✔️ Backend process:`

- 
A backend process, which is 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 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:`
Various background processes perform processes of each feature for database management. Below table shows a list of background processes. 

![Background Process](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lkqlbiok74uuze3s9fbq.png)

## `✔️ Replication associated processes:`

- 
In the replication associated processes, they perform the streaming replication. 

- 
In Streaming Replication, three kinds of processes work cooperatively. A **_walsender process_** on the primary server sends WAL data to standby server; and then, a **_walreceiver_** and a **_startup_** processes on standby server receives and replays these data. A walsender and a walreceiver communicate using a single TCP connection.

## `✔️ Background worker process:`

- 
It can perform any processing **_implemented by users_** or to run user-supplied code in separate processes. Such processes are started, stopped and monitored by postgres, which permits them to have a **_lifetime linked_** to the server's status. 

- 
These processes are attached to PostgreSQL's **_shared memory area_** and have the option to connect to databases internally; they can also run multiple transactions serially, just like a regular client-connected server process. 

- 
Background workers can be initialized at the time that PostgreSQL is started by including the module name in **_shared_preload_libraries_**. 

- 
A module wishing to run a background worker can register it by calling **RegisterBackgroundWorker(BackgroundWorker *worker)** from its **__PG_init()_** function. 

- 
Background workers can also be started after the system is up and running by calling _**RegisterDynamicBackgroundWorker**_ function. 

In the following figure, the details of the first three types of processes are shown:

![Details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/thnrmxw3prvyunee22n5.png)
The actual processes of a PostgreSQL server is shown below. In the following example, one postgres server process (pid is 9687), two backend processes (pids are 9697 and 9717) and the several background processes listed here:
```
postgres> pstree -p 9687
-+= 00001 root /sbin/launchd
 \-+- 09687 postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
   |--= 09688 postgres postgres: logger process     
   |--= 09690 postgres postgres: checkpointer process     
   |--= 09691 postgres postgres: writer process     
   |--= 09692 postgres postgres: wal writer process     
   |--= 09693 postgres postgres: autovacuum launcher process     
   |--= 09694 postgres postgres: archiver process     
   |--= 09695 postgres postgres: stats collector process     
   |--= 09697 postgres postgres: postgres sampledb 192.168.1.100(54924) idle  
   \--= 09717 postgres postgres: postgres sampledb 192.168.1.100(54964) idle in transaction  
```
## `✔️ References:`

1. https://age.apache.org/ 
2. https://github.com/apache/age
3. https://www.interdb.jp/pg/index.html