Summary
Highlights
The video introduces the architecture of PostgreSQL, focusing on the various processes it spins up to handle auxiliary, maintenance, and I/O tasks. It promises to shed light on all components of PostgreSQL and references a related Medium article for further reading.
The Postmaster process is introduced as the parent process for almost everything in PostgreSQL, acting as the listener for client connections on port 5432. The video discusses PostgreSQL's unique design choice of Multiversion Concurrency Control (MVCC) with an append-only model, where updates create new 'tuples' instead of modifying in place. It also highlights PostgreSQL's use of processes instead of threads for stability, a design choice from earlier times that has implications for resource usage and CPU performance due to separate virtual memory spaces for each process.
For every client connection, PostgreSQL spins up a dedicated backend process. This design is contrasted with web servers that use a fixed pool of processes. PostgreSQL manages this by limiting the maximum number of connections (defaulting to 100), acknowledging that a process-per-connection model can be resource-intensive. The Postmaster forks a new process for each connection, utilizing 'copy-on-write' optimization for memory efficiency.
All processes in PostgreSQL share memory, specifically called 'shared buffers' or 'buffer pool.' This crucial memory segment, allocated using 'mmap' in Linux, stores Wall records and data pages. It's essential for inter-process communication and requires mutexes and semaphores to prevent race conditions during concurrent access.
Background workers, introduced around PostgreSQL 9.6, are a pool of processes designed for parallel execution of queries. When a backend process receives a query, it can outsource its execution to these workers, providing a more predictable and scalable approach to handling CPU-intensive operations compared to individual backend processes doing all the work.
The video details auxiliary processes, starting with the 'Background Writer.' Its main job is to flush dirty data pages from shared memory to the operating system's file system cache to free up space. The 'Checkpoint' process, on the other hand, is responsible for forcing all changes (data pages and WAL records) directly to disk, ensuring data durability and creating a consistent recovery point.
The 'Logger' process writes logs to disk. 'Auto Vacuum Launchers' and 'Auto Vacuum Workers' are critical for maintenance. PostgreSQL's MVCC design leads to 'bloat' with old tuples; vacuum processes clean these up, freeing space and performing other maintenance tasks. Finally, the video discusses 'WAL Archiver' (for backing up transaction logs), 'WAL Receiver' (for replicas), 'WAL Writer' (for flushing committed changes to disk for durability), and 'WAL Senders' (for pushing WAL changes to replicas).
The 'Startup Process' is the very first process that runs, even before the Postmaster. Its purpose is to ensure data consistency after a crash. It identifies the last consistent checkpoint and then 'redoes' all changes recorded in the Write-Ahead Log (WAL) since that checkpoint, applying them to the data pages in shared memory before the database accepts new connections. This is why the WAL is often referred to as the 'redo log'.