PostgreSQL Data Flow

PostgreSQL Startup Sequence

Introduction

• PostgreSQL follows a process-per-user client/server model.
• Every time a user connects to the database, PostgreSQL creates a separate process for that user.
• This process stays active until the user disconnects.
• PostgreSQL has a group of processes and memory structures called an instance.
• Each instance has its own memory and background processes.
• A single server can run multiple instances.
• Each instance works independently, which means:
  • Memory cannot be shared between instances.
  • Processes cannot be shared between instances.
• Client applications connect to a PostgreSQL instance to read or write data.
• The default port number for PostgreSQL is 5432.
• The port can be changed:
  • During installation
  • By editing the postgresql.conf configuration file
• After changing the port, the PostgreSQL service must be restarted.

Process Architecture

Postmaster Process

• Acts as a supervisor — monitors all other processes and restarts them if they crash or stop.
• Listens for new connection requests from clients on port 5432.
• Handles authentication and authorization — checks the user's credentials and IP address against the configuration files, and verifies whether the user has permission to access the requested database.
• For each new client connection, it creates a new dedicated process called a backend process (Postgres).

[postgres@pgdb02 ~]$ ps -ef | grep postgres
root        4366    3019  0 20:50 pts/0    00:00:00 su - postgres
postgres    4367    4366  0 20:50 pts/0    00:00:00 -bash
postgres 4559 1 0 21:32 ? 00:00:00 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data
postgres    4560    4559  0 21:32 ?        00:00:00 postgres: logger
postgres    4561    4559  0 21:32 ?        00:00:00 postgres: checkpointer
postgres    4562    4559  0 21:32 ?        00:00:00 postgres: background writer
postgres    4564    4559  0 21:32 ?        00:00:00 postgres: walwriter
postgres    4565    4559  0 21:32 ?        00:00:00 postgres: autovacuum launcher
postgres    4566    4559  0 21:32 ?        00:00:00 postgres: logical replication launcher
postgres    4568    4367  0 21:33 pts/0    00:00:00 ps -ef
postgres    4569    4367  0 21:33 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ pstree -p 4559
postgres(4559)─┬─postgres(4560) ----- postgres: logger
               ├─postgres(4561) ----- postgres: checkpointer
               ├─postgres(4562) ----- postgres: background writer
               ├─postgres(4564) ----- postgres: walwriter
               ├─postgres(4565) ----- postgres: autovacuum launcher
               └─postgres(4566) ----- postgres: logical replication launcher
[postgres@pgdb02 ~]$

Dedicated Backend Process

• There is one backend process per client connection.
• The backend process handles the full lifecycle of a session:
  • Parses the SQL query
  • Creates an execution plan
  • Executes the query
  • Returns results to the client
• It can access shared memory but also has its own private memory.
• The process ends automatically when the client disconnects.

PostgreSQL Background Processes

1. Background Writer (bgwriter)

• Runs continuously in the background.
• Wakes up every bgwriter_delay milliseconds (default: 200ms).
• Gradually writes dirty pages to avoid sudden heavy disk activity.
• Works ahead of time so that backend processes do not have to write data themselves.
• At Checkpoint

• Prevents sudden spikes in disk I/O.
• Reduces delays for user queries.
• Keeps free buffers available in shared memory for new data.

2. WAL Writer

• Runs continuously in the background.
• Wakes up every wal_writer_delay (default: 200ms).
• Also triggered immediately when:
  • A transaction is committed.
  • WAL buffers become full.
  • A backend process forces a WAL flush for durability.

• Ensures transaction durability.
• WAL data must be safely written to disk before a commit is confirmed.
• Follows the rule: "Write WAL first, then write data files."

CREATE TABLE wal_demo (
    id      SERIAL PRIMARY KEY,
    fname   TEXT
);

SHOW synchronous_commit;

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());



BEGIN;

INSERT INTO wal_demo (fname) VALUES ('RAJASEKHAR AMUDALA');


SELECT * FROM wal_demo;


SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

-- SELECT pg_switch_wal();


-- Open 2nd terminal 
strings walfile | grep -i "RAJASEKHAR"


-- Come back to the first terminal 

ROLLBACK;
SELECT * FROM wal_demo;


BEGIN;
INSERT INTO wal_demo (fname) VALUES ('RAJASEKHAR AMUDALA - COMMITTED COPY');
COMMIT;

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());

SELECT * FROM wal_demo;


strings  | grep -i "RAJASEKHAR"
xxd walfile | grep -i -B2 -A2 "raja"

3. Checkpointer

1. Writes dirty pages to disk – All modified data pages (dirty buffers) in the shared buffer area are written to the data files on disk.
2. Creates a checkpoint record in WAL – A special checkpoint record is written to the Write-Ahead Log (WAL).
3. Updates the control file – The control file (pg_control) is updated with the location (the LSN – Log Sequence Number) of the new checkpoint record.

• Reduces crash recovery time.
• Ensures all committed data is safely written to disk.
• Creates a known safe recovery point for the database.

4. Autovacuum

• Removes dead rows left behind by UPDATE and DELETE operations.
• Prevents table bloat (tables growing unnecessarily large).
• Updates table statistics used by the query planner.
• Freezes old transaction IDs to prevent transaction ID wraparound.

• The number of dead rows in a table exceeds:

autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × table_size)

• A table needs statistics update (ANALYZE).
• To protect against transaction ID (XID) wraparound — a critical condition that can cause data corruption.

• PostgreSQL uses MVCC (Multi-Version Concurrency Control), which creates multiple versions of rows during updates and deletes.
• Old row versions must be regularly cleaned up.
• Prevents database corruption from XID wraparound.

5.  Logger

• Writes server messages and events to log files.
• Logs include:
  • Errors and warnings
  • Checkpoint events
  • Connection and disconnection events
  • Slow queries (if enabled)

• Triggered whenever a loggable event occurs.
• Controlled by configuration parameters:
  • log_min_messages — sets the minimum message level to log
  • log_min_duration_statement — logs queries that take longer than a set time
  • logging_collector — enables or disables log collection

• Monitoring — tracks database activity
• Troubleshooting — helps find and fix errors
• Auditing — keeps a record of events for security and compliance

6. Archiver (Optional)

• Only active when archive_mode = on in postgresql.conf.
• Triggered when a WAL segment is completed.
• Uses the archive_command to copy the file to the archive location.

archive_command = 'cp %p /archive/%f'

• Enables Point-In-Time Recovery (PITR) — restoring the database to any specific moment.
• Essential for a continuous backup strategy.
• Protects against data loss in case of failure.

7. Stats Collector (Available till PostgreSQL v14 and older )

Note: In newer versions of PostgreSQL, statistics are stored directly in shared memory instead of using a separate collector process.

• Collects statistics about:
  • Table usage
  • Index usage
  • Row counts
  • Query activity
• Updates system catalog tables (pg_catalog).

• Runs continuously.
• Updates statistics when:
  • Queries are executed
  • Tables are scanned
  • Rows are inserted, updated, or deleted
• Information becomes visible after the query completes.

• Helps the Query Planner choose the best and most efficient execution plan.
• Improves overall database performance.

Background Process Summary

Memory Architecture

Main Memory Areas

• Shared Buffers
• WAL Buffers
• CLOG Buffers
• Work Memory
• Maintenance Work Memory
• Temp Buffers

Shared Buffers

• Users cannot directly read or write data files on disk.
• All database operations (SELECT, INSERT, UPDATE, DELETE) go through the shared buffer area.
• When data is modified in memory, it is called dirty data.
• Dirty data is later written to disk by the Background Writer process.
• Parameter: shared_buffers in postgresql.conf
• Default: 128 MB (can be increased based on system needs)

WAL Buffers (Write-Ahead Log Buffers)

• Also called transaction log buffers.
• WAL stores metadata about changes (not the actual data).
• This metadata is enough to rebuild data during crash recovery.
• WAL data is written to physical files on disk called WAL segments.
• Written from memory to disk by the WAL Writer process.
• Parameter: wal_buffers in postgresql.conf
• Default: -1 (PostgreSQL automatically sets the size)

CLOG Buffers (Commit Log Buffers)

• CLOG stands for Commit Log.
• Stores the commit status of every transaction.
• Indicates whether a transaction has been committed or not.
• CLOG buffers are kept in system memory (RAM).

Work Memory

• Used for sorting and hash operations during query execution.
• Examples: Queries with ORDER BY, DISTINCT, hash joins, and merge joins.
• Allocated per operation, not per user or database.
• Parameter: work_mem in postgresql.conf
• Default: 4 MB

Maintenance Work Memory

• Reserved for maintenance operations such as:
  • VACUUM
  • Index rebuild
  • ANALYZE
• Parameter: maintenance_work_mem in postgresql.conf
• Default: 64 MB (or system-defined)

Temp Buffers

• Used for accessing temporary tables during a user session.
• Also used during large sort and hash operations.
• Parameter: temp_buffers in postgresql.conf
• Default: 8 MB

Physical Files in PostgreSQL

Data Files

• Store the actual database data — tables, indexes, and other objects.
• Do not contain any code or executable instructions.
• These are the core storage files of the database.

WAL Files (Write-Ahead Log Files)

• Store committed transaction records before they are written to data files.
• Ensures data safety — if the server crashes, WAL files are used to recover unsaved data.
• Follows the rule: "Write the log first, then write the data."

Log Files

• Store all server messages and events.
• Types of log output:
  • stderr — Error messages
  • csvlog — Logs in CSV format (easy to import and analyze)
  • syslog — System-level log messages
• Useful for debugging, monitoring, and troubleshooting.

Archive Logs (Optional)

• When archive mode is enabled, completed WAL files are copied to a separate archive directory.
• Used for backup and point-in-time recovery.
• Allows restoring the database to any specific point in time.