EXEM provides specialized, differentiated consulting services built on the IT system performance management experience and research capabilities it has accumulated since its founding.
PostgreSQL is an open source object-relational DBMS. It supports both RDB and NoSQL, and it is one of the databases drawing attention in cloud environments lately.
Key Processes
PostgreSQL accepts user requests by creating connections through server processes called postmaster and postgres, and these requests are handled by various background processes.
In this article, we look at these server processes and the main background processes that play key roles.
📌 The parameters mentioned in this section can be set in the postgresql.conf file. For details on the parameters, see Reference) Configuration File : postgresql.conf .
postmaster : Supervisor Daemon Process
The essential process for starting and stopping the PostgreSQL server, and the first process to start.
- The postmaster process allocates the shared memory area and starts the various background processes.
- The postmaster process waits for connection requests from clients. When a client requests a connection, postmaster creates a postgres process for that client.
- As the top-level process, it checks whether its child processes are behaving abnormally and restarts them when problems occur.
postgres : Backend Process
- A process that handles the SQL and commands requested by a client; it ends when the client disconnects.
- It is started by the postmaster process and has a 1:1 relationship with the client.
- Up to max_connections(Default:100) clients can connect at the same time.
Utility Process(Background Process)
PostgreSQL's background processes vary by version, but here we cover the 2 most important required processes (BG Writer, WAL Writer) and 5 optional processes.
BG Writer
- Similar to Oracle's DBWR process, it writes dirty blocks in the shared buffer to disk.
- BG Writer writes up to bgwriter_lru_maxpages (100 Pages) to disk every bgwriter_delay (200 ms).
- When BG Writer periodically writes dirty blocks to disk, fewer dirty blocks need to be flushed at checkpoint time, which keeps I/O stable.
WAL(Write Ahead Log/Xlog) Writer
- A process that periodically checks the WAL buffer and writes all unwritten transaction records to disk (WAL files).
- WAL Writer writes the WAL buffer to disk (WAL files) when a transaction commits or when the log file space is full, and WAL files are used for database recovery.
📌 The core idea of WAL is that changes to data files must be made only after they have been recorded in the transaction log.
Checkpointer
- The process that performs checkpoints, added in PostgreSQL 9.2.
- In PostgreSQL 9.1 and earlier, BG Writer performed checkpoints periodically, but starting with 9.2 the Checkpointer process was added and this function was split off from BG Writer.
- When the PostgreSQL server goes down or crashes, recovery starts by checking the last checkpoint record.
A checkpoint occurs under the following conditions.
1. When checkpoint_timeout(5 min) has passed since the previous checkpoint
2. When the size of WAL files exceeds max_wal_size
3. When the database server is shut down in smart or fast mode
4. When a backup is started with pg_basebackup or pg_start_backup
5. When a superuser runs the checkpoint command
📌 However, if no WAL has been written since the previous checkpoint, no new checkpoint occurs even if checkpoint_timeout has been exceeded.
Archiver
- Like Oracle's ARCH (archive process), it performs archiving.
- Archiving copies a WAL file to the archive area when the WAL segment switches, and the copied WAL file is called an archive file.
- The archive area path is set with archive_command, and instead of a copy you can configure an scp command or a file backup tool to send files to another host.
- These files are used for PITR (Point In Time Recovery), that is, restoring the database to a specific state at a specific point in time.
Stats Collector
- A process that collects PostgreSQL database statistics.
- It collects DBMS usage statistics, such as session information (pg_stat_activity) and table statistics (pg_stat_all_tables), and updates the information in pg_catalog.
- The optimizer refers to this information to build the optimal query execution plan.
- When the server shuts down cleanly, a copy of the statistics is saved in the pg_stat subdirectory, so statistics can be kept even after the server restarts.
Autovacuum Launcher
- The process that runs and manages autovacuum.
- It is a daemon process made up of several processes called Autovacuum Workers, and it automates vacuum execution.
- The Autovacuum Launcher process periodically calls Autovacuum Workers processes to run vacuum and vacuum analyze.
- Every autovacuum_naptime(1 min), the Autovacuum Launcher process checks the databases and calls autovacuum_max_workers(3) Autovacuum Workers processes.
📌 When PostgreSQL updates a row, it does not change the row physically but allocates and uses new space, so the space of the old row is neither reused nor freed. Cleaning up this space (VACUUM) is essential and should be done periodically.
Logger
- A process that writes error messages to log files.
- It records information about utility processes, backend processes and postmaster daemon activity, and all process information is stored under $PGDATA/pg_log.
Memory Areas
At server start, PostgreSQL allocates shared memory, a common memory space, and also allocates local memory for backend processes.
Shared memory is a space that caches information such as data blocks and transaction logs, and it is shared by all processes of the PostgreSQL server. Process, lock and global statistics information also live in this area.
Separately, there is an area called local memory that is allocated per backend process and cannot be shared. It is the work area for handling requests such as Sort and Vacuum.
Shared Memory
Shared memory is shared by all processes and is similar to Oracle's SGA. Let's look at 4 representative areas that make up shared memory.
Shared Buffer
- Similar to Oracle's buffer cache, this area caches data and data changes in block units to speed up I/O.
- Its size is set by the shared_buffers parameter in postgresql.conf. The default is 128MB, and for servers with 1GB or more of RAM, 25% of system memory is recommended.
- Data is written to the shared buffer in units of block_size (Default 8K).
📌 PostgreSQL mostly uses the term page, but it is also used interchangeably with block. Some distinguish the two terms by where they exist, but in this article they mean the same thing.
WAL Buffer(Write Ahead Log Buffer)
- A space that caches change logs for transactions run by sessions, so that data can be reconstructed during recovery.
- Its size is set by the wal_buffers parameter in postgresql.conf. The default is -1, which sets it to 1/32 of shared_buffers.
Clog Buffer(Commit Log Buffer)
- A space that caches the status of each transaction (in_progress, committed, aborted). It holds the status of all transactions so you can check whether each has completed.
- There is no parameter to set its size; it is managed automatically by the database engine.
Lock Space
- The part of shared memory that holds lock-related information. It stores all types of lock information used by the PostgreSQL instance. (Lock information is shared by all background and user processes.)
- When a user accesses a table, the activity must be tracked so that no other transaction drops or modifies (DDL) that table. The space that stores this activity information is the Lock Space.
📌 The number of locks the Lock Space can hold is max_locks_per_transaction * (max_connections + max_prepared_transactions).
Local Memory(Process Memory)
Space allocated to and used by each backend process, similar to Oracle's PGA. The values for this area mean the size of each individual space, so set them with the total number of connections in mind.
Local memory is allocated per session by default, but it can also be adjusted per transaction.
Maintenance Work Memory
- Memory used for maintenance operations; the default for maintenance_work_mem is 64MB.
- Space used for vacuum-related work, index creation, table alteration, adding foreign keys and similar operations.
- To improve the performance of these operations, increase the size of this area.
Temp Buffer
- Space used for temporary tables; the default for temp_buffers is 8MB.
- This area is allocated only when temp tables are used. It is non-shared memory allocated per session, so heavy temp table use can cause problems.
- This area has nothing to do with temp files. (See Work Memory)
Work Memory
- Space used before temp files are used when heavy sort/hash operations occur; the default for work_mem is 4MB.
- As with other local memory, it can cause problems when many sessions run heavy sort/hash operations at the same time.
📌 Sort operations include ORDER BY, DISTINCT and MERGE JOIN, and hash operations include HASH JOIN, HASH AGGREGATION and IN SUBQUERY.
Catalog Cache
- Space used when working with system catalog metadata. Sessions query metadata often, and reading it from disk each time can slow things down, so it lives in per-process memory.
Optimizer & Executor
- Memory space that builds the optimal execution plan for executed queries and carries out execution according to that plan.
Related articles
