PostgreSQL: Background writer

postgres: Background writer is started by the postmaster process during the start of instance.

According to the documentation, There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. It writes shared buffers so server processes handling user queries seldom or never need to wait for a write to occur.

In other words, background writer also writes the dirty buffers to disk which helps checkpointer process to do less IO when CHECKPOINT occurs.(we know checkpointer process sometimes writes dirty buffers to data files.)

The main objective of background writer process is to make sure that free buffers are available for use.

The algorithm that background writer process uses when writing dirty buffers is as follows.

PostgreSQL: writer process parameters

bgwriter_delay [200ms by default , 10ms – 10s possible] – This parameter specifies how long the process should wait between successive executions.

bgwriter_lru_maxpages [100 pages by default, 0 – 1000 possible] – The bgwriter_lru_maxpages parameter specifies the maximum number of buffers that will be written by the process in each iteration.

bgwriter_lru_multiplier [2.0 by default, 0-10.0 possible] – multiplier ratio determining how many pages should be cleaned for every incoming dirty page, based on counts from last delay periods. For example, if the value is set to 2, and if incoming buffers pages are 10, dirty buffers will be cleared until there are 20 buffers available or until bgwriter_lru_maxpages has been written.

Default settings in postgresql.conf file

# – Background Writer –

#bgwriter_delay = 200ms        # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100   # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB  # measured in pages, 0 disables

PostgreSQL: WAL writer process

When we make changes to the data, the changes are first written to the buffers and records of these changes are written to the WAL buffer, the changes are flushed to the WAL segments when the changes are committed. Here writer process is responsible for flushing the changes made to wal files.

To find the current wal location file, we query

postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
————————–
0000000100000003000000EC
(1 row)

postgres=#

The name is not a random collection of digits and numbers. It’s comprised of three parts of 8 characters each:

0000000100000003000000EC

The digits are classified as follows:

1. The first 8 digits identifies the timeline(00000001)
2. The following 8 digits identifies the (logical) xlog file(00000003)
3. The last ones represent the (physical) wal file (Segment) (000000EC)

# WRITE-AHEAD LOG SETTINGS

# – Settings –

wal_level = logical            # minimal, replica, or logical
                               # (change requires restart)
#fsync = on                    # flush data to disk for crash safety
                               # (turning this off can cause
                               # unrecoverable data corruption)
#synchronous_commit = on       # synchronization level;
                               # off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync       # the default is the first option
                               # supported by the operating system:
                               # open_datasync
                               # fdatasync (default on Linux)
                               # fsync
                               # fsync_writethrough
                               # open_sync
#full_page_writes = on         # recover from partial page writes
#wal_compression = off         # enable compression of full-page writes
#wal_log_hints = off           # also do full page writes of non-critical updates
                               # (change requires restart)
#wal_buffers = -1              # min 32kB, -1 sets based on shared_buffers
                               # (change requires restart)
#wal_writer_delay = 200ms      # 1-10000 milliseconds
#wal_writer_flush_after = 1MB  # measured in pages, 0 disables

#commit_delay = 0              # range 0-100000, in microseconds
#commit_siblings = 5           # range 1-1000

PostgreSQL : bgworker: logical replication launcher

The logical replication launcher background process is used in logical replication purpose. If you configure publish and subscribe model, On the subscriber the “logical replication launcher” background process launched a worker process and syncs the table automatically.

Log on a standby

postgres 19465 19079 19 11:58 ?        00:00:04 postgres: SUBSCRIBER: bgworker: logical replication worker for subscription 16390 sync 16384