Building on the earlier PostgreSQL Architecture documents, this document covers connection setup, user request processing, and how background processes work.
Connection Setup
First, let's look at how a user connects to PostgreSQL and how a request is processed and answered.
- The postmaster process acts as a listener and waits for new connection requests from clients.
- The postmaster process authenticates a new client connection request and creates a postgres process. Each process gets its own local memory, such as work_mem.
- Once the postgres process is connected to the client, the postmaster process disconnects from the client, and the client sends SQL (query string) to the postgres process.
- The postgres process runs the received query and returns the result to the client.
📌 After disconnecting from the client, the postmaster process waits for connection requests from other clients. For each new connection request, it repeats the cycle of creating a postgres process for that connection and disconnecting from the client.
User Request Processing
The postgres (backend) process uses the database buffer, or RAM, when reading and writing data. The amount of memory is set by the shared_buffers parameter in postgresql.conf and is allocated when the server starts.
User requests fall broadly into simple reads (SELECT) and changes (DML). Both go through the 3 steps below, and changes go through an additional internal step (step 4).
- The postgres process checks whether the desired data block is available in the database's shared buffers.
- If it is not in shared buffers, it requests the block from the OS. If the block is in the OS cache, it may be passed to shared buffers.
- If the data is not in the OS cache either, the desired block is read from disk (the data file) and cached in shared buffers. Physical I/O happens at this point.
- If the user request is a change, the change is made in the shared buffer, and the changed data (dirty data) and change log are later synced to the data files and WAL files when certain conditions are met.
How the Processes Work
Writing Changed Data
As mentioned above, all data changed by user requests is applied in the shared buffer. This changed data must eventually be written to disk, and the processes that do this are the BG Writer and the Checkpointer.
In other words, these two background processes sync dirty blocks by writing them to the data files, and each process does so when the following conditions are met.
- The BG Writer process regularly writes a certain amount of dirty blocks to the data files.
- The Checkpointer process writes all dirty blocks to the data files when a checkpoint occurs.
📌 Oracle's CKPT process only tells the DBWn process to write dirty buffers to the data files, but PostgreSQL's Checkpointer process writes the dirty blocks itself.
Transaction Log Management
Checkpointer
As explained above, when a checkpoint occurs, the Checkpointer process not only writes dirty blocks to the data files but also records a checkpoint record up to that point in a file.
- When the checkpoint finishes, it adds a WAL record containing the LSN (position information) of the checkpoint start to the WAL buffer.
- It updates the $PGDATA/global/pg_control file with a marker for the last completed checkpoint.
If a problem such as a system crash occurs, PostgreSQL finds the checkpoint start position in the pg_control file and reads WAL files from that position to perform recovery. After recovery finishes, the Checkpointer process immediately performs a checkpoint to protect the recovered state.
WAL Writer & Archiver
WAL Writer
The WAL Writer process flushes the contents of the WAL buffers to disk at transaction commit or when the WAL buffers fill up. These WAL files are used to recover the database.
Archiver
When a WAL segment switches, the Archiver process captures that data (WAL file) and copies it to the archive area (archive file). With archive files, you can take a full database backup and recover to a specific point after the backup (PITR: Point-In-Time-Recovery).
Recommended articles
