PostgreSQL Query Lifecycle: 2-Minute Interview Answer

Memory trick: C-P-R-P-E-R = Connect, Parse, Rewrite, Plan, Execute, Return

2-Minute Answer

1. Connection: "PostgreSQL uses process-per-connection. The postmaster checks pg_hba.conf and forks a backend for each client."

2. Parser: "First it checks syntax and builds a parse tree. Then it checks tables, columns and types in the system catalogs and builds a query tree."

3. Rewriter: "It applies rules. Views are replaced with their underlying queries here."

4. Planner: "It compares scan types, join methods and join orders using statistics and a cost model, and picks the cheapest plan. I can inspect it with EXPLAIN."

5. Executor: "It runs the plan, reads pages from shared buffers or disk, and uses MVCC snapshots to show the right row versions. For writes, changes go to the WAL first."

6. Result: "Rows are streamed to the client, followed by a command-complete message."

Technical Words Explained Simply

Step 1: Connection

Process-per-connection

Every client that connects gets its own separate server process. One client, one process.

Postmaster

The main PostgreSQL process. It waits for new connections and starts a backend for each one. Think of it as the host at a restaurant door.

pg_hba.conf

A config file that decides who may connect: which user, which database, from which IP address, and with which login method (password, certificate, etc.).

Fork

The operating system creates a new process by copying an existing one. This is how the postmaster creates a backend.

Backend process

The process that serves one client and runs all of that client's queries. Think of it as your personal waiter.

Step 2: Parser

Syntax

The grammar of SQL. Writing SELCT instead of SELECT is a syntax error.

Parse tree

The SQL text converted into a tree structure that shows how the query is organized. At this point, names are just text and are not yet verified.

System catalogs

Internal tables where PostgreSQL stores information about itself, such as which tables, columns, data types and functions exist.

Query tree

The parse tree after validation, where table and column names are replaced with real object IDs from the catalogs.

Step 3: Rewriter

Rule system

A PostgreSQL feature that changes a query before it is planned.

View

A saved query that behaves like a table. When you query a view, PostgreSQL replaces it with the saved query behind it.

Step 4: Planner

Planner (optimizer)

The part that decides the fastest way to run the query.

Scan types

Ways to read a table.

• Sequential scan: reads every row.
• Index scan: uses an index to jump straight to matching rows.

Join methods

Ways to combine two tables.

• Nested loop: checks row by row (good for small data).
• Hash join: builds a lookup table (good for big data).
• Merge join: sorts both sides and walks through them together.

Join order

The order in which tables are joined. A good order can be much faster than a bad one.

Statistics

Information about your data (row counts, common values) collected by ANALYZE. The planner uses it to estimate results.

Cost model

A scoring system that estimates how expensive each plan is (disk reads, CPU work). The plan with the lowest cost wins.

EXPLAIN

A command that shows the plan without running the query. EXPLAIN ANALYZE runs the query and shows real timings.

Step 5: Executor

Executor

The part that actually runs the chosen plan.

Page

The unit PostgreSQL uses to store data: a fixed 8 KB block.

Shared buffers

PostgreSQL's memory cache of pages, shared by all backends. Reading from memory is much faster than reading from disk.

MVCC

Multi-Version Concurrency Control. PostgreSQL keeps several versions of a row so readers and writers don't block each other.

Snapshot

A picture of which data is visible to your transaction at a given moment.

Row version

One copy of a row. An update creates a new version and keeps the old one until cleanup.

WAL (Write-Ahead Log)

A log where every change is written before the data files are updated. After a crash, PostgreSQL replays it to recover. This gives durability.

Step 6: Result

Streamed

Rows are sent to the client as they are produced, instead of waiting for the full result.

Command-complete message

A final message like SELECT 12 telling the client the query finished and how many rows were involved.

Very Short Recap

Connect → Parse (syntax, then meaning) → Rewrite (views and rules) → Plan (cheapest way) → Execute (read data, MVCC, WAL for writes) → Return rows.