Understanding the process and memory architecture in PostgreSQL

PostgreSQL is a client/server type relational database management system.

• It has multi-process architecture .

• Runs on single host.

Now we will discuss the types of processes in it.

Processes in PostgresSQL server

1. Postgres Server Process

The parent process of all the process is Postgres server process, also known as postmaster in previous versions.

• All other types of processes are initiated by this process.

• The default port number on which Postgres server listens is 5432.

• More than one PostgreSQL servers can run on the same host but their port number should be different.

2. Backend Process
Backend Process (also known as postgres) is started by postgres server process. Its responsibility is to handle all the queries of the connected client.

• As it is allowed to operate only one database, you have to specify the database while connecting to the server.

• It is connects to clients by a single TCP connection

• PostgresSQL allows multiple clients connection simultaneously but you have specify the maximum number of clients that can connected at a time and this configuration is done through max_connections parameter. The default value of this parameter is 100.

3. Background Process
Each feature/function has its own background process. For example:

• CHECKPOINT is used to write dirty buffers from memory to disks.

• VACUUM is used to remove the dead tuples to free up the space.

4. Replication Associated Process
The processes that are used to manage the replication of database are known are replication associated processes.

• They are used to perform the streaming replication in which a change in primary database is also done in other replicas of it.

5. Background Worker Process
These custom background processes that are implemented by user are known are background worker process.

• In addition to the conventional database operations, these processes can also do other tasks. In short, user can add customized tasks in addition to regular operations

Memory architecture in PostgresSQL server

Next comes the memory architecture of PostgreSQL that has two types

1. Local Memory Area

• This area is allocated by backend processes for their own use.
• It is further divided into sub-areas whose sizes are either fixed or variable.

2. Shared Memory Area

• This area is allocated by PostgreSQL server when it starts up.

• It is divided into fixed sub-areas.

References:

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