What Postgres does with your query

What happens between sending SELECT and receiving the first row?

Seven stages, and knowing their names lets you locate any problem precisely.

1. Connection. Postgres forks a backend process per connection (not a thread). Each costs a few megabytes of private memory plus a slot in shared structures, and forking is not free. This is why 5,000 application connections destroy a database that handles the same query volume comfortably through PgBouncer at 50 — the cost is per-connection, not per-query. (Postgres 14+ improved the behaviour of many idle connections, but the ceiling is still real.)

2. Parse. SQL text → a parse tree. Syntax errors surface here. The raw statement is also hashed for pg_stat_statements, with constants replaced by placeholders — which is why that view groups queries by shape.

3. Analyse / rewrite. Names are resolved to actual objects (catalog lookups), views are inlined by substituting their definitions, and rules and row-level security policies are applied — RLS is implemented as a rewrite that appends predicates, which is why an RLS policy calling an expensive function is a per-row cost.

4. Plan. The optimiser enumerates candidate plans and costs each one. This is where almost all interesting behaviour comes from, and the next section covers it.

5. Execute. The plan is a tree of nodes, executed by pulling rows from the top: each node calls its children for "one more row" (a volcano / iterator model). This is why LIMIT can be cheap — the top node stops asking — and why a plan's cost is expressed as startup..total, since some nodes (a sort, a hash build) must consume everything before they can return anything.

6. Buffers. Nodes ask the buffer manager for pages. A hit in shared_buffers is a memcpy; a miss goes to the OS page cache, and only then to disk. EXPLAIN (ANALYZE, BUFFERS) distinguishes shared hit from shared read, which tells you whether you have a CPU problem or an I/O problem.

7. Return. Rows are converted to the wire protocol and streamed to the client in batches. A client that fetches all rows before processing (the default in most drivers) materialises the whole result in its memory — which is why .iterator() / yield_per / server-side cursors exist.

How does the planner actually choose?

It is a cost-based optimiser, not a rule-based one. It estimates a cost — an abstract number, not milliseconds — for each candidate plan and picks the cheapest.

Cost constants (per-tuple and per-page weights) encode the machine's assumed characteristics:

seq_page_cost = 1.0        -- reading a page sequentially
random_page_cost = 4.0     -- reading a page randomly: the DEFAULT ASSUMES SPINNING DISKS
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
effective_cache_size = 4GB -- how much cache the planner assumes is available (a hint, not an allocation)

random_page_cost = 4.0 is the most consequential default in Postgres. On SSDs random reads are perhaps 1.1-1.5× the cost of sequential ones, and leaving it at 4 makes the planner systematically under-value index scans. On SSD-backed instances, setting it to 1.1 is one of the highest-leverage single changes available — and knowing why is a strong answer to "the planner won't use my index".

Cardinality estimates come from statistics gathered by ANALYZE into pg_statistic (readable via pg_stats): the fraction of nulls, the number of distinct values, a most-common-values list with frequencies, and a histogram of the remaining distribution, plus physical/logical correlation for each column.

For WHERE status = 'paid', if 'paid' is in the MCV list the planner uses its recorded frequency directly. If not, it assumes uniform distribution over the non-MCV values. That is the crack most estimate errors fall through.

The multi-column problem. Postgres assumes column independence by default, so WHERE city = 'Paris' AND country = 'France' is estimated as sel(city) × sel(country) — a number wildly too small, because those columns are perfectly correlated. The result is an estimate of 1 row where 50,000 exist, which chooses a nested loop that then runs 50,000 times. The fix is extended statistics:

CREATE STATISTICS city_country (dependencies, ndistinct, mcv)
  ON city, country FROM addresses;
ANALYZE addresses;

Very few candidates know extended statistics exist, and it is the correct answer to a whole family of "the estimate is 10,000× off and ANALYZE doesn't help" problems.

Which join algorithm, and why does it matter?

Three, and the planner picks per join based on estimated cardinality:

Nested loop — for each row of the outer relation, probe the inner. Cost ≈ outer_rows × inner_lookup. Excellent when the outer side is tiny and the inner has an index; catastrophic when the outer estimate was wrong. Almost every "it worked fine until the data grew" incident is a nested loop whose outer side turned out to be 100,000 rows instead of the estimated 10.

Hash join — build a hash table on the smaller relation, then stream the larger past it. Cost is roughly linear in both inputs, needs memory for the hash table, and only works for equality conditions. The failure mode is memory: if the hash exceeds work_mem, it spills to disk in batches, and if the estimate was badly wrong you get many more batches than planned — visible in EXPLAIN ANALYZE as Batches: 17 Memory Usage: ... Disk: 4096kB.

Merge join — sort both sides (or read them in order from indexes) and walk them in lockstep. Good for large, already-sorted inputs and for range conditions; the sort dominates otherwise.

Why is my index not used?

Work through these in order; the answer is almost always in the list.

1. The planner thinks a scan is cheaper, and it may be right. Above roughly 5-10% selectivity, reading pages sequentially beats random index lookups plus heap fetches. Test with SET enable_seqscan = off — as a diagnostic only: if the index plan is then genuinely faster, your cost constants or statistics are wrong, and that is the thing to fix.
2. The predicate is not indexable as written. A function or a cast on the column side (lower(email), created_at::date, id::text) cannot use a plain b-tree — you need an expression index or a rewritten predicate (a half-open range instead of a cast).
3. The leftmost prefix rule. An index on (a, b, c) cannot serve WHERE b = ? alone.
4. Type mismatch. Comparing a bigint column to a numeric parameter forces a cast on the column side.
5. Stale statistics — the planner's estimate is from a different data distribution than the one you have.
6. LIKE '%foo' — a leading wildcard has no prefix to search on. Use pg_trgm with a GIN index, or full-text search.
7. Low selectivity for NULLs or booleans — an index on a column that is 95% false is useless for WHERE flag = false and perfect as a partial index for WHERE flag = true.
8. The index is invalid. A failed CREATE INDEX CONCURRENTLY leaves an INVALID index that is maintained on write but never used for reads — the worst of both worlds. Check pg_index.indisvalid.

How do MVCC and visibility actually work?

Every row version (a tuple) carries hidden system columns: xmin (the transaction that inserted it) and xmax (the transaction that deleted or superseded it). A transaction takes a snapshot — essentially "which transaction ids had committed when I started" — and a tuple is visible to it if xmin is committed and visible in that snapshot and xmax is not.

Consequences that show up everywhere:

• UPDATE is delete + insert. A new tuple version is written; the old one is marked dead. This is why an update-heavy table grows, why every index on that table must also be updated (unless the update is HOT), and why UPDATE ... SET x = x is not free.
• Readers never block writers and writers never block readers, because readers see an older version rather than waiting. This is the central benefit.
• Dead tuples must be reclaimed by VACUUM, which also updates the visibility map (which pages contain only tuples visible to everyone) and the free space map.
• Index-only scans depend on the visibility map. An index scan normally must visit the heap to check visibility, since indexes do not store xmin/xmax. If the visibility map says the page is all-visible, it can skip that — which is why an index-only scan degrades to a normal index scan after heavy writes, until autovacuum catches up. That is the answer to "why did my covering index stop helping?"
• A long-running transaction pins the horizon. Vacuum cannot remove any tuple version that might still be visible to the oldest running snapshot. One forgotten BEGIN in a psql session, or an ORM leaving a connection idle in transaction, prevents cleanup across the whole database and causes table and index bloat that outlives the session. Set idle_in_transaction_session_timeout.
• Transaction id wraparound. XIDs are 32-bit and wrap; if freezing does not keep up, Postgres eventually refuses writes to protect data. Autovacuum's anti-wraparound runs are non-negotiable and cannot be cancelled without consequence.

HOT updates, bloat, and why adding an index slowed down writes

A heap-only tuple update writes the new version to the same page and chains it from the old one, with no index changes at all. It requires two conditions: free space on the page (hence fillfactor, which you can lower to 80-90 on update-heavy tables to reserve room) and — critically — no indexed column was modified.

That second condition is the answer to a very common production mystery. Adding an index on updated_at or status to speed up a read makes every update to those columns non-HOT: now each update writes to every index, generates more WAL, dirties more pages, and leaves index entries for vacuum to clean. The read got faster and the write path got materially worse, and nothing in the index definition hints at it.

Bloat is the accumulation of dead tuples and the resulting empty space. Its costs are indirect but severe: the table occupies more pages, so scans read more, so cache hit ratio falls, so everything slows down together. VACUUM reclaims space within pages for reuse but does not return it to the OS; VACUUM FULL rewrites the table and takes an ACCESS EXCLUSIVE lock (use pg_repack instead, which does it online).

What does a write actually do — WAL, checkpoints and fsync?

Postgres is write-ahead logged: before any change reaches a data page on disk, a record describing it is written to the WAL and flushed.

1. UPDATE modifies the page in shared_buffers, marking it dirty. The data file is untouched.
2. A WAL record is appended to the WAL buffer.
3. At COMMIT, the WAL up to that point is fsynced to durable storage. This fsync is the latency floor for a commit, and it is why commit rate is usually storage-bound rather than CPU-bound.
4. Dirty pages are written to data files later, by the background writer and by checkpoints.

This is why durability survives a crash: replay the WAL from the last checkpoint. And it explains several behaviours:

• synchronous_commit = off stops waiting for the fsync. Commits get dramatically faster and you can lose the last fraction of a second of committed transactions on a crash — without corruption. That is a legitimate, deliberate trade for some workloads, and knowing it is safe-but-lossy (as opposed to fsync = off, which risks corruption and must never be used) is a good discriminator.
• Checkpoint spikes. A checkpoint flushes all dirty buffers; if checkpoint_completion_target is low or checkpoints are too frequent, you get periodic I/O storms and latency spikes. full_page_writes means the first modification of a page after a checkpoint writes the entire page to WAL (to protect against torn pages), so WAL volume spikes right after each checkpoint too.
• Batching helps enormously. Ten thousand single-row inserts in autocommit means ten thousand fsyncs. In one transaction, it means one. That is the mechanism behind "wrap your bulk load in a transaction".
• Replication is WAL shipping. A streaming replica receives WAL and replays it; synchronous_commit = on with a synchronous standby makes the primary wait for the replica's flush, trading write latency for durability guarantees. Replication lag is replay lag, and replay is single-threaded per-record ordering — which is why a huge UPDATE on the primary produces a lag spike on replicas.

How do locks actually work here?

Two systems that people conflate:

Heavyweight locks (pg_locks) are table- and object-level, with eight modes and a conflict matrix. ACCESS SHARE (taken by SELECT) conflicts only with ACCESS EXCLUSIVE (taken by ALTER TABLE, DROP, VACUUM FULL, non-concurrent CREATE INDEX). Crucially, the lock queue is ordered: a waiting ACCESS EXCLUSIVE request blocks every subsequent SELECT behind it. That is the mechanism by which a fast ALTER TABLE plus one slow reader equals a total outage on that table — and why SET lock_timeout before DDL is essential.

Row locks are stored in the tuple header (xmax plus flags), not in a lock table, which is why Postgres can lock millions of rows without exhausting memory. SELECT ... FOR UPDATE sets them; conflicting writers wait on the transaction, using a heavyweight lock on the transaction id as the wait target. That indirection is why pg_blocking_pids() is the tool for finding who is blocking whom.

Deadlock detection is a periodic (deadlock_timeout, default 1s) check for cycles in the wait-for graph; on detection, one transaction is aborted with SQLSTATE 40P01. Deadlocks are a normal consequence of concurrency, which is why the application needs a retry loop rather than treating them as an alarm.

Reading EXPLAIN (ANALYZE, BUFFERS) like someone who has done it

Nested Loop  (cost=0.86..8452.12 rows=1 width=64) (actual time=0.05..1240.11 rows=48210 loops=1)
  Buffers: shared hit=142033 read=8210
  ->  Index Scan using orders_tenant_idx on orders  (cost=0.43..12.4 rows=1 width=32)
        (actual time=0.03..18.2 rows=48210 loops=1)
        Index Cond: (tenant_id = 42)
  ->  Index Scan using customers_pkey on customers  (cost=0.43..8.4 rows=1 width=32)
        (actual time=0.02..0.02 rows=1 loops=48210)
Planning Time: 0.31 ms
Execution Time: 1251.7 ms

What an experienced reader takes from this in five seconds:

• rows=1 estimated vs rows=48210 actual on the first scan. A 48,000× error. Everything downstream is planned for the wrong world.
• loops=48210 on the inner node. Its actual time=0.02 is per loop — the real cost is 0.02 × 48210 ≈ 960 ms. People consistently misread per-loop timings as totals; this is the single most common EXPLAIN reading error.
• The nested loop was chosen because the estimate said one row. With a correct estimate, a hash join would have been picked and the query would take milliseconds.
• Buffers: shared read=8210 — 64 MB read from outside shared_buffers, so there is an I/O component too.
• The fix is not "add an index". It is to fix the estimate: ANALYZE, raise the statistics target on tenant_id, or add extended statistics — because the index is already there and being used.

Other tells worth knowing: Rows Removed by Filter (work done then discarded — usually a missing index), Sort Method: external merge Disk: 84MB (raise work_mem or provide an ordered index), Heap Fetches on an index-only scan (visibility map is stale — vacuum), Batches: 8 on a hash join (spilled to disk), and Workers Planned: 4 / Launched: 2 (parallel workers unavailable because the pool was exhausted).

What should you tune, and in what order?

1. The query and the schema. Indexes, N+1s, SELECT *, deep offsets, missing constraints. Ninety per cent of database problems end here, and no amount of configuration compensates for a missing index.
2. Statistics. ANALYZE, statistics targets, extended statistics for correlated columns.
3. shared_buffers (~25% of RAM as a starting point, not more — the OS page cache does useful work too) and effective_cache_size (~50-75% of RAM; it is only a planner hint).
4. random_page_cost to match your storage — 1.1 on SSD.
5. work_mem modestly, raised per-session for known heavy queries.
6. Autovacuum, more aggressively than the defaults on large or write-heavy tables (autovacuum_vacuum_scale_factor of 0.1 means a 100M-row table waits for 10M dead rows before vacuuming — far too late).
7. Connection pooling — PgBouncer in transaction mode, with the caveat that it breaks session-level features (prepared statements without care, advisory locks, SET), which is why asyncpg needs statement_cache_size=0 behind it.
8. Checkpoint and WAL settings (max_wal_size, checkpoint_completion_target) to spread out I/O.