Hello guys! This time im back with The recap of the 2nd Chapter of The Internals of PostgreSQL book. Today its about the process & memory architecture of the PostgreSQl database.
2.1 Process Architecture
PostgreSQL is a relational database management system that uses a multi-process architecture and runs on a single host. A PostgreSQL server consists of multiple processes that cooperatively manage a database cluster. These processes include a postgres server process, backend processes, background processes, replication processes, and background worker processes.
2.1.1. Postgres Server Process
The postgres server process is the parent of all processes in a PostgreSQL server. It starts up by executing the pg_ctl utility with a start option, allocates a shared memory area in memory, starts various background processes, replication associated processes, and background worker processes, and waits for connection requests from clients. Whenever receiving a connection request from a client, it starts a backend process that handles all queries issued by the connected client.
2.1.2. Backend Processes
Backend processes are started by the postgres server process and handle all queries issued by one connected client. They communicate with the client through a single TCP connection and terminate when the client gets disconnected. PostgreSQL allows multiple clients to connect simultaneously, and the configuration parameter max_connections controls the maximum number of clients.
2.1.3. Background Processes
Background processes in PostgreSQL include the background writer, checkpointer, autovacuum launcher, WAL writer, statistics collector, logging collector, and archiver. These processes perform specific tasks related to database management, such as writing dirty pages on the shared buffer pool to persistent storage, performing checkpoint processes, invoking autovacuum-worker processes periodically, and writing and flushing the WAL data to persistent storage.
In summary, PostgreSQL's multi-process architecture enables efficient database management and includes a variety of processes that perform specific tasks related to database management. These processes work together to manage a PostgreSQL server, handle queries issued by clients, and perform essential tasks such as replication and background processing.
2.2. Memory Architecture
PostgreSQL employs a memory architecture that can be divided into two broad categories: local memory area and shared memory area.
2.2.1 Local Memory
Each backend process of PostgreSQL allocates a local memory area for query processing, which is divided into several sub-areas with fixed or variable sizes. The major sub-areas include work_mem, maintenance_work_mem, and temp_buffers, which are used for sorting tuples, maintenance operations, and storing temporary tables, respectively.
2.2.2 Shared Memory
On the other hand, a shared memory area is allocated by the PostgreSQL server upon start-up and is used by all processes. The shared memory area is also divided into several sub-areas with fixed sizes, including the shared buffer pool, WAL buffer, and commit log. The shared buffer pool is where PostgreSQL loads pages within tables and indexes from a persistent storage and operates on them directly. The WAL buffer is used for buffering the WAL data before writing it to a persistent storage to ensure that no data is lost by server failures. The commit log keeps the states of all transactions for Concurrency Control (CC) mechanism.
In addition to the major sub-areas, PostgreSQL also allocates sub-areas for various access control mechanisms, background processes, and transaction processing, such as semaphores, lightweight locks, shared and exclusive locks, checkpointer, autovacuum, save-point, and two-phase-commit. All of these sub-areas are essential for the proper functioning of PostgreSQL and ensure efficient query processing, maintenance operations, and transaction handling.
So thats all for today. I hope you guys like it and soon I'll be trying to upload about a little more complex and advanced topics as I learn them
Top comments (0)