Postgresql Architecture – Process

Postgresql Architecture – Process


In the previous blog, we looked at Memory Components in PostgreSql,
Click here to read more. In this post we will discuss the Process of PostgreSQL, Processes in PostgreSQL can be divided mainly into 4 types:

1. Postmaster (Daemon) Process
2. Background (Utility) Process
3. Backend Process
4. Client Process

1. Postmaster (Daemon) Process


Postmaster Process:
Postmaster is the first process which gets starts when the Postgres cluster starts. The postmaster process acts as a supervisor. Postmaster act as a listener at a server-side, any new connection coming in, it will first connect to the postmaster and every time for every connection postmaster creates “postgres” process. Postmaster runs on the default port number 5432 and we can change or reconfigure port no. There is one postmaster for one cluster. Postmaster process is a parent of all in a PostgreSQL Server. If you check the relationships between processes with the pstree command, you can see that the Postmaster process is the parent process of all processes. In the below output, if you notice PID:  88808 (Postmaster process) is a parent of all backend & background processes. 

[root@test-machine02 ~]# ps -ef|grep postgres
postgres  88808      1  0 May20 ?        00:02:52 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres  88810  88808  0 May20 ?        00:00:00 postgres: logger
postgres  88812  88808  0 May20 ?        00:00:02 postgres: checkpointer
postgres  88813  88808  0 May20 ?        00:00:08 postgres: background writer
postgres  88814  88808  0 May20 ?        00:00:08 postgres: walwriter
postgres  88815  88808  0 May20 ?        00:02:59 postgres: autovacuum launcher
postgres  88816  88808  0 May20 ?        00:00:01 postgres: archiver last was 000000010000000000000009
postgres  88817  88808  0 May20 ?        00:06:39 postgres: stats collector
postgres  88818  88808  0 May20 ?        00:00:00 postgres: logical replication launcher
postgres  89163  88808  0 15:14 ?        00:00:00 postgres: postgres postgres [local] idle
root      93311  84373  0 May20 pts/0    00:00:00 su - postgres
postgres  93312  93311  0 May20 pts/0    00:00:00 -bash
postgres  93379  93312  0 May20 pts/0    00:00:00 psql
root      97451  86319  0 17:05 pts/1    00:00:00 grep --color=auto postgres
[root@test-machine02 ~]#
[root@test-machine02 ~]#
[root@test-machine02 ~]# pstree -p 88808
postmaster(88808)ââ¬âpostmaster(88810) (logger)
                  ââpostmaster(88812) (checkpointer)
                  ââpostmaster(88813) (background writer)
                  ââpostmaster(88814) (walwriter)
                  ââpostmaster(88815) (autovacuum launcher)
                  ââpostmaster(88816) (archiver)
                  ââpostmaster(88817) (stats collector)
                  ââpostmaster(88818) (logical replication launcher)
                  ââpostmaster(89163)
[root@test-machine02 ~]#


2. Background (Utility) Process

The list of background processes required for PostgreSQL operation is as follows.


Logger Process:
Writes into the logs file. Basically, all the error logs are recorded by this and written into the log files.


Checkpointer Process :
During the checkpoint, the cache content on the shared memory is written to the database file, so that the state of the memory and the file are consistent. In this way, you can shorten the recovery time from WAL when the system crashes, and also prevent the infinite growth of WAL. The checkpoint_segments and checkpoint_timeout of postgresql.conf can be used to specify the time interval for performing checkpoints. the checkpoint will occur in the following scenarios: pg_start_backup, create a database, pg_ctl stop|restart, pg_stop_backup, and few others.


Writer process:
The Writer process writes the cache on the shared memory to the disk at the appropriate time. Through this process, you can prevent the performance of a large number of writes to disk during checkpoint (checkpoint) from deteriorating, so that the server can maintain relatively stable performance. The Background writer has been resident in memory since it got up, but it has not been working. It will sleep after a period of work. The sleep interval is set by the parameter bgwriter_delay in postgresql.conf. The default is 200 microseconds.


WAL Writer:
The WAL writer process writes the WAL cache on the shared memory to the disk at an appropriate point in time. This way, you can reduce the pressure on the back-end process when writing its own WAL cache and improve performance. The core idea of ​​the write-ahead log is that the modification of the data file must only occur after these modifications have been recorded in the log, that is, the log is written first and then the data is written (log first). Using this mechanism can avoid frequent data writing to the disk and can reduce disk I/O. The database can use these WAL logs to recover the database after a downtime restart.


Autovacuum Launcher:
The autovacuum-worker processes are invoked for the vacuum process periodically (it request to create the autovacuum workers to the Postgres server).


Archive Process:
Archive process transfers WAL logs to the archive location. The archiver process is an optional process, default is OFF. Setting up the database in Archive mode means capturing the WAL data of each segment file once it is filled and save that data somewhere before the segment file is recycled for reuse. The purpose of the archive log is for the database to use the full backup and the archive log generated after the backup so that the database returns to any point in the past (PITR).


Stats Collector:
DBMS usage statistics such as session execution information ( pg_stat_activity ) and table usage statistical information ( pg_stat_all_tables ) are collected. The statistics collector transmits the collected information to other PostgreSQL processes through temporary files. These files are stored in the directory named by the stats_temp_directory parameter, pg_stat_tmp by default.

[root@test-machine02 pg_wal]# pwd
/var/lib/pgsql/13/data/pg_wal
[root@test-machine02 pg_wal]#
[root@test-machine02 pg_wal]# ls -ltr
total 147460
-rw-------. 1 postgres postgres 16777216 May 20 16:44 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 May 20 16:46 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 May 20 16:46 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 May 20 16:47 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 May 20 17:07 000000010000000000000006

postgres=# select name,setting,unit from pg_settings where name like '%stats_temp_directory%';
         name         |   setting   | unit
----------------------+-------------+------
 stats_temp_directory | pg_stat_tmp |
(1 row)

postgres=#

[root@test-machine02 pg_stat_tmp]# pwd
/var/lib/pgsql/13/data/pg_stat_tmp
[root@test-machine02 pg_stat_tmp]#
[root@test-machine02 pg_stat_tmp]# ls -l
total 600
-rw-------. 1 postgres postgres  3899 May 30 13:41 db_0.stat
-rw-------. 1 postgres postgres 20006 May 30 13:41 db_14175.stat
-rw-------. 1 postgres postgres 33635 May 30 13:41 db_16384.stat
-rw-------. 1 postgres postgres 29918 May 30 13:41 db_16713.stat
-rw-------. 1 postgres postgres 23723 May 30 13:41 db_17115.stat
-rw-------. 1 postgres postgres 23723 May 30 13:41 db_17116.stat



3. Backend Process

The maximum number of backend processes is set by the max_connections parameter, and the default value is 100. The backend process performs the query request of the user process and then transmits the result. Some memory structures are required for query execution, which is called local memory. The main parameters associated with local memory are:

  1. work_mem Space used for sorting, bitmap operations, hash joins, and merge joins. The default setting is 4 MB.
  2. Maintenance_work_mem Space used for Vacuum and CREATE INDEX. The default setting is 64 MB.
  3. Temp_buffers Space used for temporary tables. The default setting is 8 MB.


4. Client Process

Client Process refers to the background process that is assigned for every backend user connection. Usually, the postmaster process will fork a child process that is dedicated to serving a user connection.

Click on below to know more about Memory and Physical Storage of PostgreSQL.

PostgreSQL Architecture – Memory Components

PostgreSQL Architecture – Physical Storage Structure


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 169 Total Views,  5 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

6 − four =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver