Understand PostgerSQL’s Portal – Executor vs Process Utility

Enterprise PostgreSQL Solutions

Comments are off

Understand PostgerSQL’s Portal – Executor vs Process Utility


When you send a query to PostgreSQL, it normally would go through stages of query processing and return you the results at the end. These stages are known as:

  • Parse
  • Analyze
  • Rewrite
  • Plan
  • Execute

I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In this blog. There is one that focuses primarily on the planner module that you can find here. In this blog, we will focus on the executor part of the query processing, which is the entry point to “communicate” with other PostgreSQL internal modules to collect the correct data for the query.

In fact, executor may not be the best term to describe this stage. In the PostgreSQL source code, it is actually “packaged” in another object called “portal”. Often, and it has 2 potential paths to take, the “executor” path, or the “process utility” path. Very often, we just call this stage as “executor” rather than “portal”, because executor handles most of the DML query types that involve SELECT, INSERT, UPDATE, DELETE…etc and it has to process them according to the query plan created by the “planner” module.

Process Utility, on the other hand, does not have a plan from “planner” to process and it handles DDL and other non-DML queries such as VACUUM, CREATE, PREPARE …etc.

The portal (or executor) is not as complicated as planner in terms of the logics involved, but it does involve a lot of other modules in PostgreSQL that cooperate together to produce the right output. Let’s take a look.

Where it all start

the function exec_simple_query() in postgres.c is where the query processing stages take place. We will focus on what happens after PortalStart().

Relationship Between Portal, Executor and ProcessUtility

As mentioned above, portal is an object that encapsulates “executor” and “process utility” where:

  • executor is responsible for executing the “plan” created by planner – SELECT, UPDATE, INSERT…etc
  • process utility is responsible for processing other non-DMLs not associated with a planner plan – CREATE, SHOW, CURSOR…etc

They can be visualized as:

The executor normally talks to table and index access methods, which in term access the buffer manager and eventually the disk to perform data manipulations. More on access methods here.

Process utility, on the other hand, has to look at the “nodeTag” and decide which module to access in order to complete the request. For example:

  • nodeTag=TRANS_STMT_BEGIN – access transaction manager to start a transaction
  • nodeTag=T_CreateTableSpaceStmt – access table space manager to create a new table space
  • nodeTag=T_TruncateStmt – access table access method to truncate a table
  • nodeTag=T_CreateRoleStmt – access user manager to create a new role
  • .. and many many others.

Portal – the Executor Path

We know the objective of a portal, but in terms of source code, what exactly is happening? The call stack diagram below illustrates the important functions call to handle different types of scans (SELECT). This will trigger the portal to choose the “executor” path.

The important functions are ExecInitNode and ExecProcNode, where the executor takes a plan created by the planner module and transform it into a data structure that executor can understand. This is done created in a tree structure just like its plan where there is a master and several sub execProcNodes. For example, for parallel sequential scan, the master execProcNode has a type of “gather” while the sub execProcNode could be of type “seqScan”. It is laid out like this because Gather nodes need to collect tuple data from all of its seqScan sub nodes. Note that a snapshot is created for the duration of PortalRun. This is needed for computing a tuple’s visibility while a tuple is being fetched. More on visibility here.

Portal – the Process Utility Path

The process utility path is not as complicated as the executor counter part. It does not need to create a execProcNode based on the plan. It basically calls the right function from the right module to handle different kinds of NodeTags.


executor and process utility together form the core of PostgreSQL database that we know today. Almost every single query type is to be handled by either executor or process utility. Well, they cannot handle everything themselves, so their job is to pass on the right module for the right job. This means that if you introduce a new, custom module in PostgreSQL, chances are you will need to modify the executor / process utility as well. I hope this blog could help you get familiar with the internal of PostgreSQL.