The order handler needs a customer, cart, product, or existing order. In application code that need may be one line:
const order = await db.query(
'select id, status, total from orders where id = $1',
[orderId],
);The application code is one line because PostgreSQL is doing the larger job underneath it. The database has to understand the SQL, choose an execution plan, find the right pages, apply the transaction’s visibility rules, and return the result. If the query changes data, PostgreSQL also needs a recoverable record of that change.
The query usually waits for a connection first
Opening a database connection requires network setup, authentication, and resources inside PostgreSQL. Applications avoid repeating that work by keeping a pool of connections they can reuse.
The pool has a limit. If all connections are busy, a new query waits in the pool’s queue before PostgreSQL sees it. This distinction matters during an incident:
request latency
= pool wait
+ network time
+ database execution
+ result transfer
+ application processingIf a trace wraps the whole database-driver call, it may combine the time spent waiting for a pool connection with the time PostgreSQL spent running the query. The database can report a fast query while the application shows a slow database span because most of the delay happened before PostgreSQL received anything.
Increasing the pool size can make this worse. When every application instance opens more connections, PostgreSQL may receive more active sessions and concurrent work than it can execute efficiently.
A PostgreSQL query first waits for a pooled connection, then moves through planning, execution, page access, visibility checks, and result transfer
Parse, analyze, rewrite, plan
PostgreSQL first parses SQL into an internal structure. It then resolves referenced tables, columns, operators, and types. Rules or views may rewrite the query. The planner evaluates possible execution strategies and estimates their costs.
For a lookup, alternatives may include:
- scanning every visible row;
- walking an index and fetching matching heap tuples;
- using a bitmap to combine many index matches before visiting table pages;
- choosing different join orders and join algorithms for multi-table queries.
The planner does not execute every possible strategy to see which one wins. It estimates their cost using statistics about the data. If those statistics are stale or do not represent the real distribution, the chosen plan may look cheap to PostgreSQL and still run slowly.
Parameterized queries keep the SQL structure separate from the values supplied by the application. This protects against SQL injection and can help PostgreSQL reuse parts of the query workflow, but it does not guarantee that the selected plan will be fast.
The executor asks for pages
PostgreSQL stores table and index data in fixed-size pages. The executor requests the pages needed by the plan. PostgreSQL’s shared buffer cache may already contain them. If not, the operating system and storage subsystem must supply them.
When we say a query “hit disk,” there may still be another cache involved. A page missing from PostgreSQL’s shared buffers can already be in the operating system’s cache. Reading from physical storage is much slower than reading from memory, and scattered random reads behave differently from sequential reads.
The same query can therefore be fast on a warm system and slower after a restart, or when the working set grows beyond available memory.
MVCC decides which version you can see
PostgreSQL uses multiversion concurrency control. Updating a row generally creates a new row version rather than overwriting the old version in place. Transactions use snapshots and tuple metadata to decide which versions are visible.
This allows readers and writers to make progress without every read blocking every write. It also means PostgreSQL may examine several physical versions before returning the one logical row visible to the current transaction.
Old versions eventually become dead tuples after no active transaction can need them. Vacuum reclaims that space for reuse and maintains supporting metadata. Long-running transactions can delay cleanup and allow bloat to grow.
MVCC decides which row version each transaction can see. It does not stop two transactions from making conflicting business decisions if the application reads a value and later writes a decision without the right condition, lock, or isolation level.
A write creates several forms of work
Suppose the order is inserted:
insert into orders (id, customer_id, status, total)
values ($1, $2, 'pending', $3);PostgreSQL must update the table and every affected index. It also produces write-ahead log records. WAL describes changes in a form PostgreSQL can replay during crash recovery and stream to replicas.
Under normal durable commit settings, the transaction is not acknowledged as committed until the required WAL has reached durable storage. The modified table page itself can be written later. This ordering is central to recovery: the log becomes durable before a dirty data page depends on it.
The time spent committing can therefore include a storage synchronization and, depending on the configuration, waiting for replication. PostgreSQL can acknowledge sooner with weaker durability guarantees, but that changes what the product can promise after a failure.
Transactions create an all-or-nothing boundary
An order workflow may need several related changes:
begin;
insert into orders (...);
update inventory set available = available - 1 where sku = $1;
insert into order_items (...);
commit;A transaction makes these database changes atomic. They either commit together or remain uncommitted. A payment call or queue publication still sits outside that boundary, so coordinating it with database state needs another pattern such as a transactional outbox and idempotent consumers.
Transactions should also remain short. Holding a transaction open while calling a remote payment API keeps a connection and database state alive while waiting on something the database cannot control.
The result still crosses the network
PostgreSQL encodes rows in its wire protocol and sends them to the client. Large result sets cost memory, bandwidth, serialization, and application processing time.
Selecting every column “just in case” can prevent an index-only plan and send data the endpoint never uses. Even if PostgreSQL returns one million rows quickly, the application still has to receive, allocate, and process all of them.
Pagination and streaming help, but they need stable ordering and clear consistency expectations. An offset into a changing dataset does not represent a permanent location.
Read the plan, not the SQL’s appearance
EXPLAIN shows the planned operations. EXPLAIN ANALYZE actually executes the statement and reports observed timings and row counts, so use it carefully for writes.
I usually compare the estimated row count with the actual row count first. A large difference means the planner expected a different amount or distribution of data, which can push it toward the wrong join, scan, or memory strategy.
That one-line query is really a pipeline:
pool → connection → parse → plan → execute → pages → visibility → resultThe next episode looks at how an index helps PostgreSQL narrow the search instead of reading the whole table.