PostgreSQL is based on a client/server model where a server process responds to requests from client programs. PostgreSQL is a multi-process system and it spawns a new process for each client connection. The server coordinates the activities of various internal components to manage data storage and retrieval.
Here is a high-level overview of the major components of PostgreSQL’s internal architecture:
1. Parser: The parsing phase of a SQL query involves turning the string into a parse tree of tokens consisting of nodes like SELECT, INSERT, UPDATE, etc. The PostgreSQL parser reads the SQL input and produces an internal representation of the query in the form of a parse tree.
2. Planner/Optimizer: Once a parse tree for a query has been created, the planner analyzes it to determine how to execute the query. The planner generates a query plan which involves selecting the best possible sequence of internal operations (such as table joins or index scans) to execute the query efficiently. The optimizer is responsible for selecting the most efficient plan given the available indexes and database statistics.
3. Executor: Once a query plan is generated and optimized, the executor component of PostgreSQL takes over. The executor performs the actual work of reading tables, performing joins, aggregating data etc. The executor interacts with the Operating System to read data from disk and write results back.
4. Storage Management: The storage management component of PostgreSQL is responsible for managing how data is stored on disk. It includes functionality for creating and managing tables, indexes, and other objects. PostgreSQL supports several storage management options, including row-based and column-based storage, which can be optimized for particular use cases.
5. Concurrency Control: Concurrency control is the component of PostgreSQL responsible for handling concurrent read and write operations. PostgreSQL uses a high-performance multi-version concurrency control (MVCC) system to provide concurrency control. This allows multiple transactions to read and write data at the same time, without locking tables or blocking other transactions.
6. Background Processes: PostgreSQL has several background processes that handle tasks like updating statistics, managing checkpoints and writing out data to disk. One of the most important processes is the WAL writer process which writes out transaction logs to disk. This ensures that data is recoverable in the event of a crash.
All of these components work together to provide a powerful and flexible relational database management system.