Inside PostgreSQL: How SQL Queries Are Processed from Start to Finish

Every database has its own SQL execution flowchart, and PostgreSQL is no exception. The diagram below summarizes the entire logic PostgreSQL follows to handle an SQL statement.

1. Main

The Main process is the entry point of the backend. It performs little logic, mainly dispatching work, but it does check whether the current user is "root" because a root user cannot start the PostgreSQL server.

2. Postmaster

The well‑known Postmaster background process implements the entry for all PostgreSQL backend processes. It forks various auxiliary processes such as checkpoint, bgwriter, WAL writer, autovacuum launcher, stats collector, and archiver. It also forks user‑level processes like the postgres backend and vacuum workers. In addition, Postmaster listens on the client port, accepts connections, and forks a dedicated postgres process to handle each client’s SQL statements.

3. postgres

The postgres process is the user‑level backend that actually executes the client’s request. After Postmaster forks it, the client terminal communicates directly with this process.

4. Parse statement

The newly forked postgres process receives the client’s SQL and first identifies the statement type (e.g., INSERT, DELETE, UPDATE, SELECT for DML; CREATE TABLE, CREATE INDEX for DDL). This step only classifies the statement without performing any further work; the traffic‑cop component then dispatches it.

5. traffic cop

The traffic cop routes the statement based on its type. DML and SELECT statements are treated as QUERY and sent to the QUERY processing logic, while DDL statements are treated as Utility Commands and follow a different path.

6. Query Rewrite & Generate Path

Within the QUERY path, the statement undergoes rewriting and basic optimization. The function execute_simple_query performs a raw parse to build a parse tree, then runs analysis and rewrite phases to produce a query tree with semantic checks. During this phase, the optimizer evaluates costs and selects the most efficient execution path.

8. Generate Plan

Based on the optimal path, PostgreSQL creates an execution plan and an execution tree.

9. Execute Plan

The executor walks the execution tree node by node, producing the final result set that is sent back to the client terminal.

The overall flow resembles Oracle’s architecture, though Oracle separates the listener component. Additional components shown in the diagram include Catalog (metadata management), Storage Manager (disk‑page handling), and Access Methods (index scan, heap scan, etc.). PostgreSQL’s process‑based design offers stability advantages over MySQL’s thread‑based model, at the cost of higher memory usage and more complex inter‑process communication.