DEV Community: JoongHyuk Shin

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.