Forem: JoongHyuk Shin

1.2.1 From SQL Text to Raw Parse Tree

JoongHyuk Shin — Wed, 06 May 2026 02:06:12 +0000

A line like SELECT name FROM users WHERE id = 1 arrives at the backend. As we saw in 1.1.1, the backend is a child process forked by the postmaster when the client connects, dedicated to that one client. What this process first holds in its hands is just a byte array. It does not yet know where the keywords end, where the identifiers begin, or where the integer constant lives. Turning that byte array into a tree structure is the front half of the second of the five stages, namely raw parsing. This section covers only that front half. The back half (parse analysis), which consults the catalog to attach meaning, belongs to 1.2.2. (The catalog, in case you need a refresher, is the set of internal tables PostgreSQL maintains to describe itself: which tables have which columns, which functions take which argument types, and so on, all stored as rows in these tables.)

The output of raw parsing is a single RawStmt node per SQL string (or a List, if multiple statements are joined with ;). This RawStmt wraps a raw node like SelectStmt for SELECT, InsertStmt for INSERT, and so on. The name "raw" means it has not seen the catalog. Whether users is actually an existing table, which column id refers to, none of that is known yet. All that has been captured is the grammatical structure: a SELECT keyword followed by a column list, a FROM followed by an identifier, a WHERE followed by a comparison expression.

Two tools dividing the work: flex and Bison

PostgreSQL's raw parser is a collaboration of two tools. One side is the lexer (lexical analyzer), the other is the grammar (syntactic analyzer). The lexer slices the byte array into tokens. The grammar takes that token sequence and groups it into a tree.

PostgreSQL does not write these two by hand. It uses standard generator tools brought in from outside. The lexer is flex, the grammar is Bison. Both are code generators. The developer writes rules, and at build time the tool reads those rules and emits actual working lexer/parser C code.

For flex, a rule means "if this regex pattern comes in, emit this token." For example, "if a letter is followed by alphanumerics, emit an IDENT (identifier) token", "if only digits appear, emit an ICONST (integer constant) token." PostgreSQL keeps these rules in scan.l (about 1,400 lines). At build time flex reads this file and generates the C function that does the tokenizing.

For Bison, a rule means "if this token sequence appears, build this tree node." For example, "if SELECT is followed by a column list, then FROM and a table identifier, build a SelectStmt node." PostgreSQL keeps these grammar rules in gram.y (about 21,000 lines). At build time Bison reads this file and generates the C parser function that groups tokens into a tree.

The driver that ties these two together is the raw_parser() function.

List *
raw_parser(const char *str, RawParseMode mode)
{
    core_yyscan_t yyscanner;
    base_yy_extra_type yyextra;
    int           yyresult;

    scanner_init(str, ...);     /* flex init */
    parser_init(&yyextra);      /* Bison state init */
    yyresult = base_yyparse(yyscanner);   /* one cycle */
    scanner_finish(yyscanner);

    if (yyresult)
        return NIL;             /* syntax error */

    return yyextra.parsetree;   /* List of RawStmt */
}

base_yyparse() is the actual parser function Bison generated. Inside it, whenever a token is needed, it calls the lexer and groups tokens into a tree according to grammar rules. The lexer does not run on its own. It is pulled along by the grammar.

What the lexer (scan.l) sees

What the lexer does is simple. It scans the byte array from the start, finds the longest pattern that matches one of the regex rules, and hands the corresponding token type and value to the grammar. What does "longest matching pattern" mean? When several rules can match starting at the same position, the lexer picks the one with the longer match. For example, if the input contains >=, the lexer does not slice off > alone. It groups >= into a single token (such as GREATER_EQUALS). > alone would also match a comparison-operator rule, but a longer-matching rule for >= exists, so that one wins. In lex terminology this is called longest match. Almost every lexer behaves this way. So when SELECT name FROM users WHERE id = 1 comes in, the lexer emits tokens in this order.

SELECT     → keyword SELECT
name       → identifier (IDENT, value = "name")
FROM       → keyword FROM
users      → identifier (IDENT, value = "users")
WHERE      → keyword WHERE
id         → identifier (IDENT, value = "id")
=          → token '='
1          → integer constant (ICONST, value = 1)

How does the lexer tell identifiers from keywords? Every identifier candidate (a letter followed by alphanumerics) first matches the same regex rule. After that, the matched string is checked against the keyword table by binary search. If it is in the table, it becomes a keyword token. If not, IDENT.

The size and categorization of that keyword table matter. PostgreSQL has 511 keywords, split into four categories.

Category	Count	Meaning
UNRESERVED	346	usable as identifier (e.g. `abort`, `aggregate`)
COL_NAME	64	usable as a column name but not as a function/type name
TYPE_FUNC_NAME	23	usable as a type or function name
RESERVED	78	never usable as identifier (e.g. `select`, `from`, `where`)

This split is why a query like SELECT abort FROM ... works in PostgreSQL: abort is UNRESERVED, so it can also be used as an identifier. By contrast, SELECT select FROM ... is a syntax error. RESERVED never gives way. Compatibility differences with other RDBMSes often come from this split.

There is an interesting comment at the top of scan.l: "rules in this file must be kept in sync with src/fe_utils/psqlscan.l and src/interfaces/ecpg/preproc/pgc.l!" In other words, the same SQL lexer rules live in three places. The psql client has its own lexer, ecpg (the embedded SQL preprocessor) has its own. The reason is that each tool has slightly different lexical needs, but the practical consequence is that changing the lexer rules in PostgreSQL means synchronizing three files.

The tree the grammar (gram.y) builds

As the lexer emits tokens, the grammar takes them in order, matches them against grammar rules, and assembles a tree. It builds bottom-up. Small subtrees are grouped first, those subtrees are then collected into larger nodes, and finally a single node corresponding to the whole statement is completed. This act of "taking a sequence of tokens or subtrees and reducing them into a single parent node" is called reduce in parser terminology. For example, if the token sequence IDENT '=' ICONST matches the "binary comparison expression" rule, those three are reduced into a single subtree. That subtree is then reduced as part of a WHERE clause rule. That WHERE clause is reduced as part of a SelectStmt rule. In the end, one SelectStmt node is built and gets wrapped in a RawStmt.

Here is a picture of the raw parse tree that SELECT name FROM users WHERE id = 1 produces. The tree has the root at the top, leaves toward the bottom.

                        RawStmt
                           │
                       SelectStmt
              ┌────────────┼─────────────┐
              │            │             │
          targetList   fromClause    whereClause
              │            │             │
            IDENT       RangeVar      A_Expr (=)
            "name"      "users"      ┌──────┴──────┐
                                     │             │
                                   IDENT         ICONST
                                   "id"             1

The RawStmt at the top is the output of the raw parser. Keywords like SELECT, FROM, WHERE are only markers that tell the grammar rule which subtree to reduce into, so they do not survive as separate tree nodes. Only meaningful values (identifiers, constants) from the input tokens remain as leaves.

PostgreSQL's top rule is stmtmulti. It expresses that multiple SQL statements may be joined with ;.

stmtmulti:  stmtmulti ';' toplevel_stmt
                { ... lappend($1, makeRawStmt($3, @3)); ... }
          | toplevel_stmt
                { ... list_make1(makeRawStmt($1, @1)); ... }
;

Each toplevel_stmt is one SQL statement, and they all get wrapped by makeRawStmt() into a RawStmt. That is why the raw parser's output is always a List of RawStmt.

Going down into toplevel_stmt and then stmt, you find that stmt is a giant OR rule.

stmt:
        AlterEventTrigStmt
      | AlterCollationStmt
      | AlterDatabaseStmt
      | ...
      | SelectStmt
      | InsertStmt
      | ...
;

More than 200 statement kinds are listed here, one per line. Each then expands into its own sub-rules. SelectStmt alone has dozens of sub-rules, and every SELECT element such as FROM clause, WHERE clause, GROUP BY, set operation unfolds inside it like a tree. The 21,000 lines of gram.y are the result of this unfolding.

A new tree node is built inside the reduce action. At the moment SELECT * FROM users reduces into a SelectStmt node, makeNode(SelectStmt) allocates the node and fills in target list, FROM clause, WHERE, and so on. At this moment we do not know which OID of which table users refers to. The identifier string "users" simply lives inside a RangeVar node. Catalog lookup is the next stage's job.

gram.y also happens to be the file that most directly shows PostgreSQL's history of SQL compatibility changes. When a new PostgreSQL version adds a new SQL feature (such as MERGE or JSON_TABLE), the grammar rules grow accordingly, so dozens to hundreds of lines get added to gram.y each time. Following git blame is enough to see which SQL feature entered which PostgreSQL version. The 21,000-line size is the trace of three decades of SQL standard accumulation in a single file.

The lookahead filter: base_yylex

There is one more detail. The parser Bison generates is LALR(1). Spelling out the acronym, that is Look-Ahead Left-to-right Rightmost-derivation, with 1-token lookahead. The name sounds intimidating, but the gist is simple. The parser scans input from left to right exactly once, and at each step it peeks at exactly one upcoming token ("1-token lookahead") to decide which grammar rule to apply. Being able to decide with one token of lookahead keeps the parser fast and memory-efficient. Almost every mainstream compiler/DB parser works this way.

The catch is that SQL grammar has a few cases that do not fit cleanly into LALR(1). Multi-word tokens like NULLS FIRST and WITH ORDINALITY need to be received by the grammar as single tokens. If NULLS and FIRST were given to the grammar as separate tokens, the grammar could not decide what comes after NULLS based on a single lookahead.

PostgreSQL solves this by inserting one filter layer between the lexer and the grammar. That filter is base_yylex(). The actual lexer flex generates is core_yylex(), but Bison never calls it directly. It always receives tokens through base_yylex(). base_yylex looks at one token, and if this is a case that needs the next token pulled in and merged, it gives the grammar a single combined token. The result is that the grammar can be written cleanly as LALR(1), and the complexity of multi-word tokens is isolated inside base_yylex.

Never touches the catalog

The most important constraint of the raw parser stage is that it never accesses the system catalog. This is not just a convention but a correctness requirement.

The header comment of pg_parse_query() explains why.

Analysis and rewriting cannot be done in an aborted transaction, since they require access to database tables. So, we rely on the raw parser to determine whether we've seen a COMMIT or ABORT command; when we are in abort state, other commands are not processed any further than the raw parse stage.

To follow this comment, you first need to know what "the transaction is in abort state" means. Here is the most intuitive scenario.

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'a');
INSERT INTO users(id, name) VALUES (1, 'b');   -- unique violation!
SELECT * FROM users;

The moment the third line violates the unique constraint and errors out, PostgreSQL marks this transaction as "already broken." That is the abort state. The next line, SELECT * FROM users;, is grammatically fine and the table exists in the catalog, but PostgreSQL refuses to execute it. Instead, every subsequent SQL gets the same one-line error.

ERROR:  current transaction is aborted, commands ignored until end of transaction block

This is the situation in psql where, after one query inside a transaction fails, every following query is rejected with the same message. To unlock it, the client must explicitly send ROLLBACK (or COMMIT, which in abort state has the same effect as rollback). In other words, the only SQL that PostgreSQL effectively accepts in abort state is ROLLBACK.

Now the relationship between the raw parser and the catalog matters. ROLLBACK still arrives as SQL text, so it has to be parsed somehow. But in abort state, even catalog reads are blocked. What if the raw parser depended on the catalog? Parsing ROLLBACK itself would then error out, and the client would have no way to unwind the transaction. Killing and reopening the connection would be the only escape.

The design choice that avoids this from the start is the raw parser's catalog ban. The raw parser must work in any transaction state, so it never touches the catalog. All identifier-level meaning analysis is deferred to the next stage (parse analysis). 1.2.2 covers that story.

What this means in practice

First, "parsing is fast" really means raw parsing is fast. PostgreSQL's raw parser is pure string work without catalog lookup, so it is very fast. But the bulk of what a prepared statement (1.1.2) caches is not raw parsing. It is the next stage (parse analysis). Parse analysis is where catalog lookups, function-overload resolution, and type checking happen. When people talk about the per-query parse cost of the simple query protocol being a burden, they almost always mean parse analysis cost, not raw parse cost. To diagnose accurately, separate which stage's cost you are looking at.

Second, keyword categories are a hidden trap in PostgreSQL compatibility and migration. A word that other RDBMSes happily allow as a column or function name may be RESERVED in PostgreSQL. If your migration tool does not automatically wrap such names in quotes ("name"), you get a syntax error. Conversely, identifiers PostgreSQL allows freely (such as abort) may be blocked in another DB. When reviewing a migration, comparing the keyword tables on both sides is the safe move.

Third, the raw parser only catches syntax errors. Errors like "table does not exist", "function signature does not match", "column is ambiguous" are all thrown by the next stage, parse analysis. So when a query has both a syntax error and a semantic error, the syntax error is reported first. The semantic error only surfaces once syntax is clean. If your error message suddenly changes during debugging, that may be a signal that the syntax issue cleared and you are now seeing the next stage's error.

1.2 Parser and Analyzer: How SQL Gets Its Meaning

JoongHyuk Shin — Wed, 06 May 2026 02:05:24 +0000

A line like SELECT name FROM users WHERE id = 1 is just text when the client sends it. The first thing the backend does after receiving it is figure out "what do these characters mean." This chapter covers the second of the five stages we saw in 1.1.1: the parser and analyzer.

By the time this stage finishes, the SQL text has been transformed twice. First into a raw parse tree that captures the grammatical structure, then into a Query tree with meaning attached after consulting the catalog. The catalog, in case you need a refresher, is the set of internal tables that PostgreSQL keeps to describe itself. Which tables exist, what columns they have, what argument types each function accepts, what data types are defined: all of it lives as rows in these tables. PostgreSQL treats user data and metadata uniformly, both as ordinary tables. The raw stage looks only at form (does this follow SELECT syntax, where are the IDENTs). The Query stage looks at substance (this identifier users is which table in which schema and what is its OID, id is which column and what is its type, can 1 be coerced to that column's type).

1.2 splits into three sections.

1.2.1 From SQL text to raw parse tree (lexer, grammar): how the flex-based lexer and Bison-based grammar turn an SQL string into a tree. This stage is pure syntactic work, no catalog access.
1.2.2 Semantic analysis: name resolution, type checking, catalog lookup: take the raw parse tree, dig into the catalog to find what each identifier really refers to, check types, resolve function overloads. This is the body of how PostgreSQL gives meaning to SQL text.
1.2.3 Query tree node types (Query, RangeTblEntry, TargetEntry): the core nodes of the Query tree, which is the output of semantic analysis. This node structure is the standard input format that rewriter, planner, and executor all consume.

By the end of this chapter, you should have a clear picture of how SQL text meets the catalog and acquires meaning, and what data structures carry that meaning into the next stage.

1.1.3 Optimizable vs Utility

JoongHyuk Shin — Tue, 05 May 2026 07:36:20 +0000

Inside the five-stage pipeline from 1.1.1, there is another fork right after the parser. PostgreSQL classifies every SQL command into one of two camps. One side holds the optimizable queries, the other holds the utility commands. The classification is decided by a single field on the Query node, commandType, and from that point on the two camps travel completely different paths. One goes through the rewriter, the planner, and the executor. The other bypasses all three.

This fork was a single line in the 1.1.1 picture, but it shapes the entire internal structure of PostgreSQL, so it earns its own section.

Five optimizables, and everything else

PostgreSQL defines its command types as a single enum.

typedef enum CmdType
{
    CMD_UNKNOWN,
    CMD_SELECT,
    CMD_UPDATE,
    CMD_INSERT,
    CMD_DELETE,
    CMD_MERGE,
    CMD_UTILITY,
    CMD_NOTHING,
} CmdType;

Of these, CMD_SELECT, CMD_INSERT, CMD_UPDATE, CMD_DELETE, and CMD_MERGE are the optimizable ones. As the name suggests, these are queries the planner can do meaningful work on. It rearranges join order using a cost model, picks indexes, and chooses scan methods.

CMD_UTILITY is the catch-all for everything else: CREATE TABLE, ALTER TABLE, DROP, VACUUM, BEGIN/COMMIT/ROLLBACK, COPY, NOTIFY, LISTEN, CLUSTER, REINDEX, GRANT, SET, SHOW, TRUNCATE, LOCK, FETCH, CHECKPOINT, PREPARE TRANSACTION, CREATE INDEX, CREATE FUNCTION, and many more. What they share is a single property: the planner has no room to produce a better plan via cost comparison.

A command like CREATE TABLE foo (id int) cannot have two different paths. It just inserts a few rows into the system catalog and asks the storage manager to allocate a new relfilenode. BEGIN similarly nudges the transaction state by one step; there is no choice in "how to BEGIN." VACUUM walks a target table page by page and cleans up dead tuples through a fixed procedure. The point is that cost comparison is meaningless here.

The two camps are wired through different code paths. The first split happens in the analyzer, right after the parser hands over a raw parse tree.

The fork lives in transformStmt's switch

When the raw parse tree arrives, transformStmt() runs a large switch on the node tag (src/backend/parser/analyze.c).

switch (nodeTag(parseTree))
{
    /* Optimizable statements */
    case T_InsertStmt:
        result = transformInsertStmt(...);
        break;
    case T_SelectStmt:
        result = transformSelectStmt(...);
        break;
    /* ... UPDATE, DELETE, MERGE ... */

    /* Special cases (utility wrappers around an optimizable inside) */
    case T_DeclareCursorStmt:
    case T_ExplainStmt:
    case T_CreateTableAsStmt:
    case T_CallStmt:
        /* transform the inner query separately */
        ...

    default:
        /* every other utility */
        result = makeNode(Query);
        result->commandType = CMD_UTILITY;
        result->utilityStmt = parseTree;
        break;
}

PostgreSQL does meaningful semantic analysis on the five optimizable statement types and a handful of special cases. Everything else falls through to the default branch, gets stamped commandType = CMD_UTILITY, and the raw parse tree is stored verbatim in the utilityStmt field. Nothing was actually analyzed; a Query shell was wrapped around the raw tree with a "this is utility" sticker.

The next stage, the rewriter, also reads that sticker (src/backend/tcop/postgres.c).

if (query->commandType == CMD_UTILITY)
    querytree_list = list_make1(query);   /* don't rewrite utilities */
else
    querytree_list = QueryRewrite(query);

If the query is utility, the rewriter does not touch it. The rule system, view expansion, and RLS policy application all live on the optimizable side and have no meaning for utility commands.

The planner is the same.

if (query->commandType == CMD_UTILITY)
{
    /* Utility commands require no planning. */
    stmt = makeNode(PlannedStmt);
    stmt->commandType = CMD_UTILITY;
    stmt->utilityStmt = query->utilityStmt;
    ...
}
else
{
    stmt = pg_plan_query(query, ...);   /* invoke the planner */
}

Utility commands never call into the planner. An empty PlannedStmt wrapper is built, and the raw parse tree is dropped into it. A PlannedStmt with no plan tree.

The executor stage is split too. Optimizable statements feed their plan tree into the executor proper, which produces rows. Utility statements get handed off to ProcessUtility(), which dispatches to a per-statement handler. The dispatch logic and the individual handlers belong to later chapters (DDL in 1.6, transaction commands in chapter 4).

When you lay out the four stages side by side, the asymmetry is sharp.

Stage	Optimizable (5 types)	Utility (everything else)
Parse analysis	Dedicated transform function	Wrapped in a Query shell
Rewriter	Rule system applied	Skipped (passes through)
Planner	Plan tree generated	Skipped (empty PlannedStmt holding the raw tree)
Executor	`ExecutorRun()` (walks the plan tree)	`ProcessUtility()` (per-statement handler)

The whole sophistication of the planner exists for those five types; utility bypasses it entirely. This is not an efficiency choice. It is a structural asymmetry, because utility commands have no alternative paths to compare.

Two species sharing one system

Once you see how this asymmetry is wired, the two camps look almost like two species inside the same engine.

Hooks live on different paths. A hook in PostgreSQL is a function pointer exposed at a key point in the execution path so that external code (typically an extension) can plug in. An extension installs its own function address into the hook, and PostgreSQL calls it whenever the hook is non-null at the appropriate moment. Which camp the hook applies to depends on where it sits. planner_hook is invoked just before the planner runs, so it only affects optimizable queries. Utility never enters the planner, so planner_hook never fires for utility. On the other side, ProcessUtility_hook is invoked just before ProcessUtility() runs, so it only applies to utility commands. That is how an audit logging extension like pgaudit intercepts DDL and DCL. You need both hooks together to cover every SQL execution path. If you write extensions, you have to know up front which camp your hook is intercepting.

Statistics are split too. PostgreSQL accumulates usage patterns about which SQL ran how often and for how long, and DBAs use that data to find slow queries and pick tuning targets. The recording channels, however, are split between the two camps. pg_stat_statements records SELECT/INSERT/UPDATE/DELETE/MERGE executions along with plan-level information. Some utility commands (especially DDL) need a separate channel like log_statement = ddl. A monitoring tool that wants to draw "what is happening in this system" has to read both channels.

Prepared statements mean different things in each camp. The prepared statements from 1.1.2 cache plans for optimizable queries. Utility commands can be wrapped with PREPARE too, but since there is no plan, there is nothing to cache. The server just keeps the raw tree around and routes each EXECUTE through ProcessUtility again. Same name, different semantics.

EXPLAIN's reach is asymmetric. EXPLAIN SELECT ... draws a plan tree. EXPLAIN ALTER TABLE ... does not work; there is no plan tree to draw. The exception is the special-case group from the switch above (T_DeclareCursorStmt, T_ExplainStmt, T_CreateTableAsStmt, T_CallStmt). They are classified as utility on the outside but contain an optimizable query that goes through the regular pipeline. That hybrid path is why EXPLAIN ANALYZE INSERT INTO ... SELECT ... works. The outer wrapper is utility; the SELECT and INSERT inside are optimizable.

I once tried to debug "why is this long ALTER TABLE so slow" by analyzing the plan. The plan had no answer. The bulk of the cost lived outside the plan tree: lock waits, catalog updates, full-table rewrites, and WAL volume. That was when I learned why utility needs its own dedicated path. Some costs are invisible to the plan-level cost model, and to see them you need a different stage of tools.

What this means in practice

First, planner-related tuning and monitoring tools have no meaning for utility. EXPLAIN, plan-level metrics in pg_stat_statements, auto_explain, plan_cache_mode, all of these are optimizable-side tools. Looking at the plan to debug a slow DDL or DCL is pointless because there is no plan. By the same logic, an ORM or migration tool that wraps ALTER TABLE in a prepared statement assuming "the plan will be cached anyway" has the wrong mental model. Utility commands have no plan; every call grabs and releases catalog locks while running directly. Slow utility is almost always lock contention or I/O cost, not a plan choice issue, so the diagnostic path is to set log_min_duration_statement to capture timings, watch lock waits in pg_stat_activity, and look at wait_events to see where the command is stuck.

Second, audit and security requirements split into two camps and attach to different mechanisms. Tracking "who changed which schema and when" lives on the utility side. pgaudit captures ALTER/CREATE/DROP/GRANT into an audit log because it sits on ProcessUtility_hook. Row-level access control like "this user can only see a subset of rows in this table" lives on the optimizable side. RLS (Row-Level Security) runs in the rewriter, automatically attaching extra WHERE conditions to SELECT/UPDATE/DELETE. The two requirements get bundled under the same security umbrella, but they hook into completely different stages of the pipeline. RLS cannot stop a schema change, and ProcessUtility_hook cannot filter rows. When a compliance requirement comes in, the first task is to classify it as schema-level tracking versus row-level access control. Only then do the candidate tools fall into place, and you almost always need both mechanisms together to leave no gaps.

1.1.2 Simple vs Extended

JoongHyuk Shin — Tue, 05 May 2026 04:35:38 +0000

The fork visible in 1.1.1 (simple query protocol on one side, extended on the other) is the subject of this section, one level deeper. 1.1.1 set the skeleton: simple is one message, extended is four. The job here is to show how that split translates into four distinct outcomes: plan reuse, parameter safety, pipelining, and error handling.

Message sequence: the shape of one cycle is different

Putting the message sequences side by side makes the difference visible at a glance.

Simple:

Client                          Server
  │                               │
  │── 'Q' (SQL text) ────────────▶│
  │                               │ parse → analyze/rewrite → plan
  │                               │ → create portal → execute → drop portal
  │◀── RowDescription, DataRow*, CommandComplete, ReadyForQuery

Extended:

Client                          Server
  │                               │
  │── 'P' (SQL template) ────────▶│ parse + analyze, store prepared statement
  │◀── ParseComplete                
  │                               │
  │── 'B' (parameter values) ────▶│ choose plan, create portal
  │◀── BindComplete                
  │                               │
  │── 'E' (execute) ─────────────▶│ run portal, send rows
  │◀── DataRow*, CommandComplete   
  │                               │
  │── 'S' (Sync) ────────────────▶│ close transaction
  │◀── ReadyForQuery

Simple finishes one cycle in a single message. Extended slices the cycle into four messages, and that slicing is what produces the four capabilities below.

Capability one: execution plans get reused

The central concept that lets extended split the stages is the prepared statement.

A prepared statement is a SQL template that has already been parsed and analyzed. The places where values would go are left blank with placeholders like $1, $2, and at execution time only the actual values get plugged into those slots. Take INSERT INTO users (id, name) VALUES ($1, $2). Once you turn that into a prepared statement, you can run it later by sending only the values: (1, 'Alice'), (2, 'Bob'). The full SQL text isn't reparsed each time. Give it a name and it becomes a named prepared statement you can call back during the session. Send it without a name and it's an unnamed prepared statement, automatically discarded the moment the next 'P' arrives.

The four messages of the extended protocol are exactly that flow, sliced.

Message	What it does
`'P'` Parse	Take the SQL template, finish parse and analysis, store as a prepared statement
`'B'` Bind	Bind actual parameter values to the prepared statement and prepare for execution (create a portal)
`'E'` Execute	Run the prepared portal and send result rows
`'S'` Sync	End of the cycle, send ReadyForQuery

What this means is that the same prepared statement can be re-executed many times with different parameters by repeating just 'B' + 'E'. Take inserting 1,000 users.

# Driver pseudocode: 1000 INSERTs via a prepared statement
stmt = conn.prepare("INSERT INTO users (id, name) VALUES ($1, $2)")
for i in range(1000):
    stmt.execute(i, f"user{i}")

conn.prepare(...) corresponds to a single 'P' message. Parsing and analysis of the SQL text happen there. Each of the 1000 stmt.execute(...) calls corresponds to a 'B' + 'E' pair. Parse and analyze run only once on the first call; the remaining 999 do bind and execute only. With simple query, the same INSERT text would be sent 1000 times and reparsed 1000 times.

Internally, a prepared statement is held in a structure called CachedPlanSource, which keeps the raw parse tree and the analysis result. When the same prepared statement gets another 'B' + 'E', the backend starts from the saved CachedPlanSource, only redecides the execution plan, and runs. Parsing and analysis are skipped.

Generic plan vs custom plan

One step further. Plan reuse is real, but to be precise there are two kinds of plan.

Custom plan: recomputed every time using the bound parameter values. Helpful when the optimal path differs by value. Take WHERE status = $1. Suppose status='pending' matches 1% of rows and status='completed' matches 99%. A distribution where the value-by-value ratios are this lopsided is what's usually called a skewed distribution. Index scan is fast for 'pending'; sequential scan is fast for 'completed'. Custom plan looks at the value on every call and picks the path that fits it. (Plan construction is the entire subject of chapter 1.4; the kinds and behavior of scan nodes are covered in 1.5.2.)
Generic plan: planned once without knowing the parameters and cached. Every EXECUTE from then on reuses the cached plan, so from each call's point of view the cost of "planning this one" is zero. The trade-off is that the same path is forced for every parameter value.

PostgreSQL decides between the two on every EXECUTE. The decision function is choose_custom_plan(), and the default policy is:

For the first 5 EXECUTEs, always use a custom plan. Collect actual cost measurements.
From the 6th onward, compare the average custom plan cost against the generic plan cost. The custom average includes the cost of planning every time, while the generic side has that cost as zero (for the reason above), so the comparison is intentionally asymmetric.
If generic is cheaper, switch to generic. Otherwise, stay on custom.

The decision can be forced via the plan_cache_mode GUC. auto (default) runs the policy above; force_custom_plan always uses custom; force_generic_plan always uses generic.

Working on another RDBMS engine, the first time I saw the "5 customs, then start comparing" rule I spent a while looking for the reason behind that 5. The conclusion: it's an arbitrary constant. The PG source comment literally says "until we have done at least 5 (arbitrary)". Other engines tend to be stricter with plan cache policy (e.g. lock the first plan in as the generic one) and let you override via a knob, while PG chose to decide dynamically on every call. The result is that a PG prepared statement isn't simply a "plan cache"; it's "automatic switching driven by statistics." This is one reason that even ORM code that uses prepared statements automatically can show much less plan caching than people expect: if a statement is called fewer than 5 times, it gets recomputed as a custom plan every time.

Capability two: SQL injection is structurally blocked

In simple query, putting a parameter into a query means embedding the value inside the SQL text, something like f"SELECT * FROM users WHERE id = {user_input}". If user_input is untrusted, you've just opened the door to SQL injection.

Extended separates the SQL template from the parameter values into different messages. 'P' carries only the template, like SELECT * FROM users WHERE id = $1. 'B' carries the values that fill those slots, in binary or text form. Those values never go through the SQL parser. They're plugged into the already-parsed plan tree as data.

When JDBC PreparedStatement, libpq PQexecParams, or psycopg2 supports ? or $1 placeholders, that's the path being used internally. The real mechanism for SQL injection prevention lives here. It isn't "remember to escape on the client"; it's a structure where the parser has no chance to interpret a user-supplied value as a SQL token.

Capability three: messages can be batched (pipelining)

Simple sends ReadyForQuery back the moment each 'Q' is processed. The client can't send the next query until that response arrives. One query equals one round-trip.

Extended only sends ReadyForQuery when an 'S' (Sync) arrives. That means a sequence like 'P', 'B', 'E', 'B', 'E', 'B', 'E', 'S' can go out as a single batch. 100 INSERTs in one round-trip. In environments with significant network latency (cross-region cloud calls, for instance), the throughput difference is large.

Built on top of this mechanism, PG 14 introduced an official pipeline mode in libpq (PQpipelineSync, PQenterPipelineMode, etc.). The wire-level capability existed before, but the libpq client API for it wasn't clean.

Capability four: a partial error doesn't break the whole batch

Simple, on error, immediately sends ErrorResponse plus ReadyForQuery. The cycle closes right away and the backend is ready for the next 'Q'. As noted above, simple is a 1-round-trip structure, so when the backend returns to normal mode there's nothing queued in the buffer behind the failed message. Closing out and waiting for the next 'Q' is enough.

Where extended runs into real trouble is the batch case. As we saw in capability three, a typical client pushes 'B', 'E', 'B', 'E', ..., 'S' into the wire all at once. Suppose you send 100 INSERTs by pipelining: one 'P', followed by 100 pairs of 'B' + 'E' and a single 'S' all line up in the backend's buffer. While the backend is processing the 1st 'B', the 51st and 70th messages are already sitting in that buffer waiting their turn.

Now suppose the 50th 'B' fails with something like a unique violation. If the backend behaved like simple (immediately sending ErrorResponse + ReadyForQuery and returning to normal mode), it would pull the 51st 'B' out of the buffer and start processing it next. But that 51st 'B' was sent by the client under the assumption that the first 50 had succeeded. The transaction is already aborted, so processing the 51st errors out too. Same for 52, 53, ..., 100. The client ends up tracking the original error plus 50 more downstream errors.

PG avoids this chaos with a different strategy. The moment an error occurs, the backend enters a special state called ignore_till_sync. While in that state, every message that arrives is dropped without being processed until the client explicitly sends an 'S' (Sync). No additional error responses go out. Once 'S' arrives, the backend finally sends ReadyForQuery and starts accepting messages normally again.

The result is that the client receives exactly two responses: one ErrorResponse (the 50th failure) and one ReadyForQuery (in reply to 'S'). A clean boundary forms: "the batch failed somewhere, and everything past that point was discarded." ignore_till_sync is, in essence, the byproduct that makes pipelining safe.

All four in one table

Compressing the four capabilities into a single comparison.

Area	Simple	Extended
Message count	1 (`'Q'`)	4+ (`'P'`, `'B'`, `'E'`, `'S'`)
Plan reuse	None (parse + plan every time)	Yes (`CachedPlanSource` + auto generic/custom)
Parameters	Inline in SQL text	Separated as data at bind time
SQL injection	Client is responsible for escaping	Prevented at the protocol level
Round-trips	1 per query	Batched (1 per Sync)
Error handling	Immediate ReadyForQuery	ignore_till_sync (wait until Sync)

What this means in practice

First, don't assume that "the ORM uses prepared statements" means you're getting full plan caching. PG plans a custom plan for the first 5 EXECUTEs of every prepared statement. If a statement is called only once or twice inside a short transaction, the plan caching benefit is essentially zero. The real benefit shows up in workloads that call the same prepared statement dozens to hundreds of times with different parameters. The ratio of calls to plans in pg_stat_statements, plus a forced plan_cache_mode setting, are the two diagnostic tools.

Second, the answer to "why isn't my prepared statement going generic?" is the wall of 5. Forcing plan_cache_mode = force_generic_plan brings planning cost to zero but locks every parameter value to the same path. With skewed data this can actually be slower. The opposite, force_custom_plan, pays planning cost every time. The default auto, which decides dynamically from the 6th call, is usually safest, but there are environments where explicitly choosing generic is worth the GUC tweak. For example, environments where prepared statements have very short lifetimes due to PgBouncer transaction pooling.

Third, in environments with significant network latency, pipelining is the real lever. Cross-region RDS calls in the cloud, or even same-region setups where there's millisecond-level latency between application and database, will turn 100 simple INSERTs into 100 round-trips. libpq pipeline mode, or JDBC addBatch() + executeBatch(), can collapse that to a single round-trip. Just keep in mind that the error-handling complexity goes up (you need to understand what ignore_till_sync means), so it pays to design batch-level transaction boundaries and a retry policy at the same time as the pipelining itself.

1.1.1 Life of a Query

JoongHyuk Shin — Mon, 04 May 2026 10:13:22 +0000

This section is the map for the rest of the book. The five stages introduced in the 1.1 chapter overview (parse, analyze/rewrite, plan, portal, execute) are traced here through the actual code: which functions implement each stage, and in what order they get called. The mechanics of each of the five stages are unpacked in later chapters. Here, only the skeleton matters: how a backend starts up, how it receives messages, and where the first fork in the road appears.

One backend process owns one query

Every time a client connects, PostgreSQL forks a backend process for it (the parent is postmaster). That process stays alive until the client disconnects, and it handles every query that client sends, by itself. Unlike the thread-pool model common in other RDBMSs, PG uses one OS process per connection. The reasons behind that decision are taken up in 6.1.1.

The actual entry point of that backend is a function called PostgresMain. The name is grand; what it does is unexpectedly simple. Two things, then off it goes.

First, it installs signal handlers. Signals are asynchronous notifications the OS delivers to a process (for example, SIGTERM is a request to shut down, SIGUSR1 is for PG-internal communication). A backend has to react to signals from postmaster and from other backends, so each signal is wired to a handler ahead of time. Signals and IPC in general are covered in 6.3.

Second, it initializes the transaction system. Every SQL statement in PG, even without an explicit BEGIN, runs inside some transaction. The transaction system is the core PG machinery that tracks BEGIN/COMMIT boundaries, MVCC visibility, XID assignment, and so on. Transactions and MVCC are the subject of all of chapter 3. For now, it's enough to know that this machinery is set up before the backend ever sees a SQL statement.

Once those two preparations are done, the real work of the backend begins. An infinite loop.

for (;;)
{
    ...
    ReadyForQuery(whereToSendOutput);
    ...
    firstchar = ReadCommand(&input_message);
    ...
    switch (firstchar)
    {
        case PqMsg_Query:        // 'Q', simple query
            exec_simple_query(query_string);
            break;
        case PqMsg_Parse:        // 'P', extended: parse
            exec_parse_message(...);
            break;
        case PqMsg_Bind:         // 'B', extended: bind
            exec_bind_message(&input_message);
            break;
        case PqMsg_Execute:      // 'E', extended: execute
            exec_execute_message(portal_name, max_rows);
            break;
        case PqMsg_Sync:         // 'S', end of an extended cycle
            finish_xact_command();
            send_ready_for_query = true;
            break;
        ...
    }
}

This loop is the entire life of a backend.

"Announce that I'm ready, read one message, dispatch on its type." Repeat forever. When the client closes the connection, an 'X' (Terminate) message arrives, the loop exits, and the process dies.

The first fork in the road is visible right here. There's the 'Q' path and the 'P' / 'B' / 'E' path. That split is the difference between the simple query protocol and the extended query protocol.

Simple vs extended

Simple is the case where a single message contains the SQL text in full. Type SELECT 1; into psql and hit enter, and that's what flies across the wire. The backend receives that one message and runs the full five-stage cycle (parse, analyze and rewrite, plan, portal, execute) before returning the result.

Extended does the same job but splits it into four messages ('P', 'B', 'E', 'S'). Splitting the stages opens up plan reuse, parameter safety, and pipelining. The semantic differences between the two protocols and how they play out in practice are unpacked in section 1.1.2.

Optimizable vs utility

Everything described so far assumes optimizable statements: SELECT/INSERT/UPDATE/DELETE. These have paths to optimize. The planner decides between sequential and index scan, hash join and nested loop, one join order or another.

But statements like CREATE TABLE, VACUUM, SET, and BEGIN (the utility statements) are different. There's nothing for a cost model to optimize. They're DDL or system commands, with no path to choose. In that case the planner produces only an empty shell of a plan and hands the actual work to a utility-statement handler. The executor never gets called on this path.

The detailed branching is the subject of 1.1.3. The takeaway here is just one thing: not every query in PG goes through the planner.

The big picture

We can now compress the journey of a SQL line into a single diagram.

Client
   │
   │  'Q' (or 'P' + 'B' + 'E')
   ▼
PostgresMain main loop
   │
   ▼
exec_simple_query
   │
   ├─ pg_parse_query           → raw parse tree     (1.2.1, 1.2.3)
   │
   ├─ pg_analyze_and_rewrite   → list of Query nodes (1.2.2, 1.3)
   │
   ├─ pg_plan_queries          → execution plan      (1.4 chapter)
   │     └─ utility produces an empty shell          (1.1.3)
   │
   ├─ PortalStart + PortalRun  → tuple pulling       (1.5)
   │
   └─ PortalDrop + finish_xact_command
   │
   ▼
ReadyForQuery → back to the top of the loop

Each box in this diagram corresponds to a chapter in the book. 1.2 is parser and analyzer, 1.3 is rewriter, 1.4 is planner, 1.5 is the executor. All of part 1 is essentially one zoomed-in view of this diagram.

Working on another RDBMS engine, I once found this aspect of PG surprising. PG accepts a multi-statement query like SELECT 1; SELECT 2; as a single simple-query message. What's even more surprising is the transaction handling. Without an explicit BEGIN/COMMIT, all those statements get bundled into a single implicit transaction block, and if even one of them fails, the whole batch rolls back.

At first I assumed this was just standard behavior. Comparing the client protocols of other major databases made it clear this is a PG-specific decision. MySQL has CLIENT_MULTI_STATEMENTS off by default, so multi-statement queries are simply rejected (you have to flip the flag explicitly because of SQL injection risk). Even with the flag on, statements are processed sequentially, and because autocommit is the default, each one commits as its own transaction. Oracle accepts only one statement per OCI call, so to bundle multiple statements you have to wrap them in an anonymous PL/SQL block (BEGIN ... END;). SQL Server accepts multiple statements in a T-SQL batch, but atomic handling still requires an explicit BEGIN TRANSACTION. None of the three does what PG does: bundle automatically as soon as the message arrives.

What this means in practice

This five-stage skeleton turns out to be the foundation for two diagnostic tools you'll use in operations.

First, you can see exactly where EXPLAIN's output comes from. EXPLAIN runs only as far as stage 4 (plan); it skips stage 5 (execute). EXPLAIN ANALYZE actually runs through stage 5 and measures it. That's why EXPLAIN ANALYZE produces real load and shouldn't be casually run in production: an EXPLAIN ANALYZE UPDATE ... actually updates rows. The familiar BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK idiom exists for exactly this reason.

Second, the fact that one backend means one process means one query at a time explains why connection pooling matters so much. A backend's main loop is essentially single-threaded. While one client runs a long query, that backend can't do anything else. Connection counts therefore drive memory and scheduling costs linearly, and a pooler like PgBouncer becomes effectively mandatory. The answer to "why are PostgreSQL connections so expensive?" lives inside this one-line main loop.

1.1 Where Does a Query Go?

JoongHyuk Shin — Mon, 04 May 2026 09:57:44 +0000

Suppose a client sends SELECT * FROM users WHERE id = 1. The path that single line travels before coming back as a result row is longer than you might expect. Inside the PostgreSQL backend, that SQL goes through a five-stage pipeline. The five stages are exactly the five chapters of Chapter 1.

1.1 Where Does a Query Go?: the backend decides which processing path the client message should follow.
1.2 Parser and Analyzer: How SQL Gets Its Meaning: the SQL text is parsed, and the catalog is consulted to give it meaning.
1.3 Rewriter: How a Query is Rewritten: the RULE system expands views, injects RLS policies, and otherwise transforms the query tree.
1.4 Planner: Which Path to Take: a cost model explores possible execution paths and picks the best one.
1.5 Executor: How Results Come Back: the chosen plan is walked, pulling tuples up and sending them to the client.

Chapter 1.1, the one you're reading, splits into three sections.

1.1.1 Life of a Query: compresses all five stages into a single diagram. The map for the rest of the book.
1.1.2 Simple vs Extended: looks at the semantic difference between the two protocols.
1.1.3 Optimizable vs Utility: shows how SELECT/INSERT/... and CREATE/VACUUM/... take different paths.

After this chapter, it should be clear how the backend's main loop receives a client message and dispatches it to the right function.