Process Architecture

We can divide the processes into 4 main groups.

  1. Postgres Process (Formerly Postmaster)
  2. Background Process
  3. Backend (server) Process
  4. User/Client Process

Note: The Stats collector process is included in the core system with version 15, it is not
a separate process.

Postgres Process

ps -ef | grep postgres

postgres 2450 1 /usr/lib/postgresql/14/bin/postgres -D
/var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
postgres 2451 2450 14/main: logger
postgres 2453 2450 14/main: checkpointer
postgres 2454 2450 14/main: background writer
postgres 2455 2450 14/main: walwriter
postgres 2456 2450 14/main: stats collector
postgres 2457 2450 14/main: pg_cron launcher
postgres 2458 2450 14/main: logical replication launcher

ll /usr/lib/postgresql/14/bin/postmaster
/usr/lib/postgresql/14/bin/postmaster --> postgres

Postgres process in Debian-based Linux (path is different in RHEL-based Linux.)

It is the parent process that first opens in the instance and starts other processes. As
can be seen in the example, the parent process ID is 2450. Other Postgres processes
have the parent ID as 2450.

Postgres process;

  • It takes the memory from the operating system.
  • Starts background processes.
  • If necessary, it performs the recovery process.
  • It listens to the network and accepts the connection requests to the Postgresql
    database (it acts as a listener).
  • By default, it listens to TCP 5432 port, it can be changed (need to stop and
    start the instance).
  • Writes operation or error messages during boot to the log file (/var/log/postgresql/postgresql-…..log).
  • A postgres process can manage one cluster (the concept of cluster is explained
    in the Storage section)

Background (Utility) Processes

pgrep -a postgres
2450 /usr/lib/postgresql/14/bin/postgres -D /var/lib/
postgresql/14/main -c config_file=/etc/postgresql/14/main/
postgresql.conf
2451 postgres: 14/main: logger
2453 postgres: 14/main: checkpointer
2454 postgres: 14/main: background writer
2455 postgres: 14/main: walwriter
2456 postgres: 14/main: stats collector
2457 postgres: 14/main: pg_cron launcher
2458 postgres: 14/main: logical replication launcher

pstree -p 2450
postgres(2450)─┬─postgres(2451)
├─postgres(2453)
├─postgres(2454)
├─postgres(2455)
├─postgres(2456)
├─postgres(2457)
└─postgres(2458)

When we examine the ID of the parent (main) postgres process with pstree,
background and backend/server processes are seen.
These are the processes required for the instance to work.
They are initialized when the instance is started.
Some of them starts working with the activated feature.
BG Writer, Logger, Checkpointer, WAL writer, Autovacuum launcher, Archiver, Stats
collector, WAL sender/receiver etc

Backgorund Writer Proses

When the space in the shared buffer is insufficient, it writes the rarely used dirty buffer
areas to the disk and makes these areas available again.
Writes asynchronously.
When it is not writing (idle), it waits in sleep mode, it does not close itself.
How long it can stay in sleep mode can be set with the bgwriter_delay parameter.
It runs every 200ms, which is the default value.

bgwriter_lru_maxpages: It determines how many blocks (pages) will be
written in each run.
bgwriter_lru_multiplier: It is used to reach the required clean buffer
amount.
(When the multiplier value is 2, and if 50 blocks of clean buffer space is required, 100
blocks of dirty shared buffer space are written to disk and marked as clean buffer.)

Dirty Buffer: New or processed data blocks in memory.
Clean Buffer: Available memory space.

Checkpoint Proses

Checkpoint locates address where the recovery process will start (transaction
sequence).
At checkpoint time, all data blocks in dirty buffer are written to disk and special records
of the checkpoint are written to the log (replorigin) file. (The metadata information of
the changes made in the database beforehand is written to the WAL/REDO files.)

At the same time, transaction information recorded on the disk is entered into the WAL
file. And the blocks in the shared buffer are marked as clean so they can be reused.

For example, during electrical/hardware failure etc. The physical shutdown of the
server causes the database to become inconsistent. In such cases, recovery starts
while the database is opened.

The recovery process receives the latest checkpoint
information and re-runs the transactions that could not be written to the disk from the
WAL files, making the data files consistent and starts the instance.

When the checkpoint occurs, it will write all the data blocks in the dirty buffer to disk,
which will cause I/O load. I/O starts from the checkpoint start point and completes
before the next checkpoint, therefore, the checkpoint process is limited.

The frequency of checkpoint operation is determined by the checkpoint_timeout
parameter. The default value is 5 minutes. So, the checkpointer starts the checkpoint
process every 5 minutes or at max_wal_size, whichever is reached first.
If WAL has not been written since the previous checkpoint, that is, there is no
transaction, the checkpoint is not performed even if the checkpoint_timeout period
has passed.

If the checkpoint_timeout and/or max_wal_size values are reduced,
frequent checkpoints will occur, but the recovery time will be shortened. Since
frequent checkpoints will be costly, it should be kept in balance. The database needs to
be adjusted according to its intended use and load.
To identify if checkpoint is happening too frequently, the checkpoint_warning
parameter can be set, which will record the warning messages in the log file.

WAL Writer Proses

The Checkpointer process frequently performs the checkpoint operation. Changes in
data are written to disk asynchronously.
Modified data blocks are kept in the shared buffer.
The metadata records of the change are kept in the WAL buffer.
When the commit happens, the REDO records in the WAL buffer are written to the WAL
segments.

wal_level: Defines which records WAL files contain.
The default value is replica. This value is sufficient for WAL archiving, and hot standby
(read only).
In minimal, it deletes all logs except the information required in crash and immediate
shutdown situations. Not suitable for base backup. The database does not contain
the necessary information for base backup restore and recovery operations. For base
backup, it should be a replica or logical.
In addition to replica in logical, it also contains decoding information required for
logical standby.

wal_writer_delay: Determines how often the wal buffer is flushed and its
contents are written to disk.

wal_keep_size: Determines the minimum WAL size that should be kept in the
pg_wal directory. If your standby servers lag behind for any reason, it updates itself
with the WAL files in pg_wal.

min_wal_size: As long as the disk usage of WAL files is smaller than this size, it
will be overwritten and reused in checkpoint operations instead of deleting old WAL
files. Thus, the disk space specified here is reserved. The default value is 80 MB.

max_wal_size: The size that is allowed to grow during the automatic checkpoint
process. It is the soft limit, under heavy load, when archive_command receives an error
and cannot archive, or when wal_keep_size is given a high value, it can exceed the
size specified here.

WAL Archiver Proses

The WAL archiver process archives WAL files to keep backup and standby servers up
to date. It is disabled by default. It is an optional parameter. It is activated with the
archive_mode parameter. It has two modes; ‘on’ and ‘always’.
There is no difference in normal operation. But if ‘always’ is selected, it will continue to
archive even in recovery and standby mode.
wal_level determines how much details are written to WA files.
Its minimal value is not sufficient for archiving, so it cannot be enabled.

archive_command = 'rsync -av %p /RA/archive/'
Pgbackrest and other backup tools take different values according to the environments used. Example: archive_command = 'pgbackrest --stanza=mx_stanza archive-push %p'

Note:

1. Because online backup and standby servers are essential for critical databases,
enabling WAL archiving is almost mandatory.

2.The database activities like a dump, restore, batch, etc. can be considered for these
settings. Stopping archiving before heavy WAL-generating operations will be beneficial
for performance.
(archive_mode off, wal_level = minimal and max_wal_senders =0). It needs instance restart after the setting.

Logger Proses

It is the process that writes the events in the database to the current log file.
Logging collector is turned off by default and only stderr messages are directed to the
system’s default log directory when it is turned off.
(/var/log/postgresql/postgresql-14-main.log)
When enabled with logging_collector = on (restart required), the logger
process opens and starts writing log files to the directory specified in the log_directory
parameter.

For example: /var/lib/postgresql/14/main/log/postgresql-09-23.log
The full path can also be given (the postgres user must have write access to the folder).
If path is not given, it is recorded by default to the location /var/log/postgresql.

Autovacuum Launcher Proses

It is an optional process. It is enabled by default (autovacuum=on).
Autovacuum worker processes make idle(dead) areas in datafiles usable again.
Autovacuum launcher runs these worker processes.

Vacuum

When a row in a table is deleted, Postgresql does not immediately delete these lines
from the data file.
These lines are marked as deleted.
Likewise, updating a row roughly corresponds to a delete and an insert operation.
The state of the row before the update is still in the data file. As a result, idle(dead)
areas that cannot be used in data files are created. These idle fields are called dead
records.
The vacuum process marks dead records as reusable.
The vacuum process does not lock the table.
The vacuum command can be run manually

Vacuum Full

It deletes the records marked as reusable by the Vacuum process, reorganizes the
table, and restores the idle areas to the system.
Vacuum full creates an empty data file.
It copies the non-dead record to the new datafile and empties the original file.
Note, vacuum full operation locks the table in exclusive mode. It should be preferred
when the transaction is less. If necessary, it should be stopped.
There must be enough space on the disk as it will create a copy of the table.

Vacuum analyze

It reads the number of records in the tables and generates statistics for the query
planner.
The Autovacuum launcher process also automates these vacuum commands.

Stats Collector Proses

It is an optional process, and enabled by default.
It is a subsystem that collects and reports activity information in the database.
It can aggregate the number of accesses to tables and indexes at the disk block and row
level.
It can check the number of rows in each table, do analysis and vacuum operations.
It can track the number of times user-defined functions are called/executed and how
long they take.

Postgresql can report all the instantaneous activities in the database.
For example, commands, connections, and sessions run by server processes. This
property is independent of the collector process.

These statistics are kept in the directory listed in the stats_temp_directory
parameter. In databases operating under heavy load, keeping this directory on fast
disks such as RAM-based disk/SSD will increase performance. When the Instance is
stopped, a permanent copy of the statistics is copied to the $PGDATA/pg_stat_tmp directory. Thus, stats are not lost when it is stopped and started.

If the instance is stopped inconsistently (immediate shutdown, server crash), statistics
will be lost as it will be started with recovery. Likewise, it is lost in the point-in-time
recovery process. (With version 15, the core is integrated into the system, it no longer
works as a separate process.)

#Activate/deactivate statistics;
alter system set track_counts='on';

#Statistics of commands run by session / server processes
alter system set track_activities='on/off';

#User defined functions
alter system set track_functions = 'none, pl, all';

# I/O monitoring
alter system set track_io_timing= 'on/off';

#Default değerlere geri dönmek için;
alter system reset track_activities;
alter system reset track_counts;
alter system reset track_functions;
alter system reset track_io_timing;

Gathering statistics will add overhead to the database. When the detailed analysis is
required, statistics can be enabled and then disabled.
track_counts: Determines whether to collect activity statistics in the database,
and statistics about indexes and tables. It is enabled by default because autovacuum
needs this information.
track_activities: It controls the monitoring of the commands currently
executed by the server process. It is enabled by default. Superuser can make changes.
The superuser and session owner can query the reports here. It does not create a
security vulnerability.
track_functions: Enables tracking of user-defined functions.
track_io_timing: Enables monitoring of block read and write operations.

Statistics System Views

postgres=# \d pg_stat
pg_stat_activity              pg_statistic
pg_stat_all_indexes           pg_statistic_ext
pg_stat_all_tables            pg_statistic_ext_data
pg_stat_archiver              pg_statistic_ext_data_stxoid_index
pg_stat_bgwriter              pg_statistic_ext_name_index
pg_stat_database              pg_statistic_ext_oid_index
pg_stat_database_conflicts    pg_statistic_ext_relid_index
pg_stat_gssapi                pg_statistic_relid_att_inh_index
pg_statio_all_indexes         pg_stat_progress_analyze
....

Statistics collected from dynamic statistics views can be queried.
If we connect with psql and type \pg_stat and press tab twice, the above list
will be displayed.
Note: By examining this statistical information, performance improvement can be
made by moving tables or tablespaces with a lot of I/O operations to the fast disks.

WAL Sender/Receiver

These processes perform replication in active/passive cluster architectures. The WAL
receiver process runs on the slave(standby) server(node) and connects to the master
(primary) server(node) via TCP/IP.

The master node has the WAL sender process. WAL sender is responsible for sending
WAL files to slave node. The WAL receiver is also responsible for receiving these WAL
files.

Backend (Server) Proses

Each session corresponds to a backend/server process.
It is the process that executes users’ requests (select, insert, update etc.)
on the Postgresql side.
Each session takes its own backend memory space from the system for its use.
Maximum number of backend/server processes is limited by the max_connections
parameter.
The default value is 100.

Client/User Proses

A program or tool that connects to a database server or network.
Example: Pgadmin, psql web application, java application etc.

Kategori seçin...