PostgreSQL workings in one picture.
A graphical overview of Postgres ecosystem
With the below illustration I attempted to capture key processes that encompass Postgres workflow. Here is a quick overview.
There are two main flows that happen in Postgres:
- Servicing client’s connections
- Execution of background processes
Let’s take a look at the diagram and start from the first flow (right left corner). Once client is connected to Postgres, it gets dedicated backend that will provide service to all queries of that client. Each client can send readings and renewal queries to Postgres, that in turn generates Planning and Execution.
During query execution the process of data input/output is performed at the level of tables and indexes. This process is directly involved in updating Shared Buffers area. If data required for the query execution are not found in Shared Buffers they will be loaded from the disc that as a result generates additional input/output data.
Each and every change of the data that is placed in the Shared Buffer area (with the exception of some special cases, such as unlogged tables) will be logged in the WAL (Write Ahead Log) while a result of the query will be reported back to the client.
In addition to servicing client’s queries, another function of Postgres is the execution of the background processes of which there are several, for example Autovacuum Launcher/Worker, BackgroundWriter, Checkpointer, Logger, Stats Collector and so on. The main purpose of these background processes is to support the healthy working of PostgreSQL as a system. These are not necessarily connected to each other’s background services, however, there are two important points to remember:
a. to be able to exchange data they use Shared Buffer area
b. if, as a result of their performance, they modify any data within Shared Buffers, these changes are then logged in the WAL.
Among background processes there are those connected to streaming replication – these are wholly rely on the data in the WAL and are normally transfer data throughout the system. There are also processes that would save dirty pages from Shared Buffers that are in the memory to the persistent storage e.g. disc. These are also track changes that take place in the Shared Buffers area based on WAL and record the performed changes onto the disc.
In the image below I also added stats that monitor all of the above activities, these are helpful for navigation and monitoring of Postgres performance.
I hope you find it helpful!
