PostgreSQL Architecture Explained

PostgreSQL is a process-based database server architecture. It uses independent operating system processes for the independent system and user workers.

A PostgreSQL database is configured using its configuration file, found in the root of the data_directory as ‘postgresql.conf’. The data directory is set with the ‘-D’ or ‘–pgdata’ switches when postgres is started using ‘pg_ctl start’.

A running PostgreSQL database typically consists of the postgres server process, a number of background processes, and any needed ‘backend’ processes for database clients:

[postgres@localhost ~]$ pgrep postgres -al
7665 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
7667 postgres: checkpointer
7668 postgres: background writer
7669 postgres: walwriter
7670 postgres: autovacuum launcher
7671 postgres: stats collector
7672 postgres: logical replication launcher

Postgres instance without any backend/user connections

PostgreSQL Architecture: Statement Execution

A database client is connected to a unique, private database process on the server that the  database runs on. This is commonly called a backend.

A connection is created using PostgreSQL’s wire protocol, which is, in this case, the PostgreSQL wire protocol version 3. Because this wire protocol version has been in use since PostgreSQL version 7 (May 2000), it’s the general and universally accepted PostgreSQL wire protocol version used today.

NOTE: As part of the wire protocol, a query can be sent by a client for execution on the server in two query modes:

• simple query protocol mode
• extended query protocol mode

PostgreSQL Architecture: Server Statement Execution

A query is executed by the server backend process based on a memory tree structure called a ‘plan tree’. A plan tree contains all the steps the executor has to perform to execute a query. To build this plan tree, the backend has to perform a number of steps. The complete steps a query has to perform, including execution, are:

1. Parse: Syntactical parse of the statement, which creates a parse tree from the query.
   1. Rewrite / Parse: Semantic parse of the statement (validation of the existence of the table and columns, etc.), which creates a query tree from the query.
   2. Rewrite / Rule: The rule system can rewrite views from the view definition to the table definition (for example).
2. Plan: The planner or optimizer takes a query tree and evaluates all access paths for a given query tree based on ‘cost’. This results in a plan tree of the most optimal path.
3. Execute: The executor takes the plan tree and executes each plan node (if necessary, based on the result of child nodes). This represents a principal step in the plan tree, after which it returns the result to the client.

Statement execution revisited

Now that the steps for execution have been laid out, a database client can choose to use two query modes to execute: the simple query protocol (default) and the extended query protocol.

Simple query protocol

The simple query protocol was the original way to execute a query before the extended query protocol was created. The simple query protocol is, as the name indicates, very simple. It takes the query text in the Postgres backend and performs all four steps in a single go from the database client perspective.

Extended query protocol

The extended query protocol performs the server statement execution steps in a more granular way, breaking these steps into three client executable steps. The naming conventions are almost identical, which is both logical and confusing.

Let’s look into the implementation and usage of the extended query protocol. A client is not required to execute the individual execution steps one at a time, which would imply 3 network round trips. If so, this would disqualify the extended query protocol for a majority of the use cases, most of which are based on performance.

Instead, most extended query protocol implementations perform the parse step, and then piggyback the bind and execute steps, so only two roundtrips are performed. For really simple executions—meaning a very simple and small query and a very small result—the simple query protocol will outperform the extended query protocol. For an execution involving larger queries, involving more data to process and/or a larger set of results, the parse roundtrip probably is negligible.

Prepared statement execution

The benefit of the extended query protocol comes from its ability to materialize the parse step, which is called a ‘prepared statement’.

Once a statement is ‘prepared’, it can repeatedly be executed by performing the bind step to introduce the parameters for the variables in the statement. These are bound to the variable placeholders (called ‘binding’ hence the name bind), which then additionally performs the planning/optimization of the plan. After this it can perform the execute step to actually have the plan tree executed which is the result of the bind/plan step.

There is one last optimization—specifically for the prepared statement execution. Once a statement is prepared, it provides a persistent memory location that can store information about past executions. A prepared statement will store information about the cost of the plan tree for each bind/plan call, and it prepares a so-called ‘generic plan.’ This is a plan that does not take the actual plan value specifics into account.

If it finds five plan execution costs of the non-generic plans (called ‘custom plan’) that match or have a higher cost than the generic plan, it will switch to the generic plan, after which it will not compute a custom plan and the custom plan cost again. This means the bind/plan and execute step has mostly become an execution step.