How a Database Query Executes — How X Works

► QUERY EXECUTION • DEEP DIVE • HOW-04

SELECT *

WHERE magic = true

↓ 6 stages, ~0.4 ms, a million moving parts ↓

PARSER AST PLANNER OPTIMIZER EXECUTOR BUFFER POOL WAL

🗺️ THE JOURNEY OF A SQL QUERYCH.1 · OVERVIEW

Six Stages Before You See a Row

From string of characters to actual data on your screen

You type SELECT name FROM users WHERE id = 42 and half a millisecond later, you have a row. That half-millisecond contains more engineering ingenuity than most programs run in an entire request. PostgreSQL — and most relational databases — route your query through six internal stages, each with its own data structures, algorithms, and failure modes.

Understanding these stages is not just academic trivia. It is the difference between an application that handles 10,000 queries per second and one that struggles at 100. It is why adding a single index can drop a query from 4 seconds to 0.2 ms. It is why WHERE LOWER(email) = $1 is a silent performance disaster.

Every SELECT you've ever run took this exact path. The query engine never shortcuts, never skips stages. Parser, Rewriter, Planner, Optimizer, Executor — every time.

The Full Architecture

When your application sends a query, here is the full chain of custody:

Application

→

libpq / Driver

→

Connection Pool
PgBouncer

→

Backend Process
postmaster fork

→

Parser

→

Rewriter

→

Planner

→

Optimizer

→

Executor

→

Storage
Heap + Indexes

The Six Stages at a Glance

PARSER — Tokenize & Build AST

Turns the raw SQL string into an Abstract Syntax Tree. Catches syntax errors. Identifies tables, columns, operators. Produces a structured parse tree that the rest of the engine can work with.

REWRITER — Apply Rules & Expand Views

Rewrites the AST according to rules. Most importantly: expands view definitions into their underlying queries. Also injects Row-Level Security policies. Produces a potentially-transformed AST.

PLANNER — Generate Candidate Plans

Uses table statistics (row counts, value distributions) to estimate the cost of different execution strategies. Considers all possible join orders, join methods, and scan types.

OPTIMIZER — Pick the Cheapest Plan

Computes a cost estimate for each candidate plan using a mathematical cost model. Selects the plan with the lowest estimated total cost. For complex queries with many tables, uses a genetic algorithm.

EXECUTOR — Run the Plan

Executes the chosen plan using the Volcano/iterator model. Each node in the plan tree implements Init/GetNext/End. The root node pulls rows from children, which pull from their children recursively.

STORAGE — Read Pages & WAL

The executor reads 8KB pages from the buffer pool (shared_buffers). Cache hits return in microseconds; cache misses go to disk in milliseconds. All writes first go to the Write-Ahead Log.

Reading time: ~12 minutes. You'll come out understanding EXPLAIN ANALYZE output, why your queries are slow, and what to do about it.

⚡ QUERY EXECUTION PIPELINE — ANIMATEDINTERACTIVE

Watch your query flow through each stage. Press Step to advance one stage, Play to auto-run, Reset to start over.

Stage 0 / 6 — Ready

🔤 STAGE 1 — THE PARSERCH.2

From String to Tree

Lexer → Tokens → Abstract Syntax Tree

The SQL query arrives at the backend process as a plain text string — a sequence of bytes over a TCP socket. Before PostgreSQL can do anything useful, it must parse that string into a structured data structure it can reason about. This is exactly the same job that a C compiler or a JavaScript engine does when parsing source code.

Step 1: Lexical Analysis (Tokenizing)

The lexer (also called a scanner or tokenizer) reads the query character by character and groups them into tokens — the atomic units of SQL syntax.

-- Input query:
SELECT name FROM users WHERE id = 42

-- Tokens produced by the lexer:
Token[0]:  SELECT         type=KEYWORD
Token[1]:  name           type=IDENT
Token[2]:  FROM           type=KEYWORD
Token[3]:  users          type=IDENT
Token[4]:  WHERE          type=KEYWORD
Token[5]:  id             type=IDENT
Token[6]:  =              type=OP
Token[7]:  42             type=INTEGER_CONST

The lexer is defined in src/backend/parser/scan.l — a flex file with hundreds of regex rules. It strips whitespace, handles quoted identifiers, recognizes string literals, and normalizes keywords to uppercase internally.

Step 2: Syntax Analysis — Building the AST

The parser takes the token stream and applies grammar rules to build an Abstract Syntax Tree (AST). In PostgreSQL, this AST is called a parse tree and is made up of C structs. The grammar is defined in gram.y — a 17,000-line bison file.

The internal C function pg_parse_query() takes the query string and returns a List* of parse trees (one per SQL statement in a batch). Each node in the tree is a tagged C struct — the tag tells downstream code what kind of node it is.

Syntax Errors Caught Here

If the parser encounters a token that doesn't fit the grammar rules, it throws a syntax error immediately — before any planning or execution happens. These are the errors you see with the telltale "at or near" phrasing:

-- Missing table name:
SELECT * FROM WHERE id = 42;

ERROR:  syntax error at or near "WHERE"
LINE 1: SELECT * FROM WHERE id = 42;
                      ^

-- Missing closing parenthesis:
SELECT * FROM users WHERE (id = 42;

ERROR:  syntax error at or near ";"
LINE 1: SELECT * FROM users WHERE (id = 42;
                                           ^

What the Parser Does NOT Do

The parser only checks syntax — grammar rules. It does not check whether the table users actually exists, whether the column name is in that table, or whether the data types are compatible. Those are semantic checks done during the planning phase.

Important: SELECT * FROM nonexistent_table WHERE id = 42 passes the parser successfully. The "relation does not exist" error only appears later when the planner tries to look up table metadata.

🔄 STAGE 2 — THE REWRITERCH.3

Rules, Views & Row Security

Transforming the AST before planning begins

The rewriter takes the parse tree from the parser and potentially transforms it. For most simple queries, the rewriter passes the AST straight through untouched. But it plays a critical role for views, row-level security, and rule-based rewrites.

View Expansion — The Big One

When you define a view in PostgreSQL, you are storing a query. When you query that view, the rewriter replaces the view reference in your parse tree with the view's underlying query definition. This is transparent to the planner.

-- You defined this view:
CREATE VIEW active_users AS
  SELECT id, name, email
  FROM   users
  WHERE  is_active = true;

-- You query:
SELECT name FROM active_users WHERE id = 42;

-- Rewriter expands it to:
SELECT name
FROM   (SELECT id, name, email
        FROM   users
        WHERE  is_active = true) v
WHERE  v.id = 42;

The planner then sees the fully-expanded query. Because PostgreSQL's optimizer is smart, it will often push the WHERE id = 42 predicate down into the subquery and use an index on the users table directly — a technique called predicate pushdown.

Row-Level Security (RLS)

Row-Level Security is one of PostgreSQL's most powerful — and most misunderstood — features. When RLS is enabled on a table, the rewriter automatically injects the applicable security policies into the query as additional WHERE clauses or WITH CHECK constraints.

-- RLS policy defined as:
CREATE POLICY user_isolation ON orders
  USING (user_id = current_setting('app.current_user_id')::int);

-- Your app runs:
SELECT * FROM orders;

-- Rewriter produces:
SELECT * FROM orders
WHERE  user_id = current_setting('app.current_user_id')::int;

RLS Use Cases

✅ Multi-tenant SaaS applications
✅ Healthcare: patients see only their records
✅ Financial: users see only their transactions
✅ Team isolation in shared databases

RLS Performance Notes

⚠️ Policy conditions must be indexable
⚠️ Complex policies can hurt performance
⚠️ BYPASSRLS role skips all policies
⚠️ EXPLAIN shows the injected predicates

The Rule System

PostgreSQL has an older rule system (CREATE RULE) that also operates at this stage. Rules can replace or augment queries with arbitrary SQL. They predate triggers and are generally considered legacy — views are implemented using rules internally, but for application code, triggers are almost always preferred over explicit rules.

Semantic Analysis: The rewriter also calls into the semantic analyzer (parse_analyze()) which resolves table and column names against the system catalogs, assigns OIDs to relation references, and checks type compatibility. This is where "column does not exist" errors are thrown.

📊 STAGE 3 — THE QUERY PLANNERCH.4

Generating Candidate Plans

Statistics, join strategies, and scan types

The planner is where PostgreSQL earns its reputation. Given a rewritten query, the planner's job is to enumerate all possible ways to execute it and select the best one. For a query joining 3 tables, there might be dozens of candidate plans. For a query joining 8 tables, there could be thousands.

Table Statistics: The Planner's Input

The planner doesn't look at your actual data to make decisions — that would be too slow. Instead it uses statistics collected by the ANALYZE command and stored in the pg_statistic system catalog (exposed via the friendlier pg_stats view).

-- Check stats for a table column:
SELECT
  attname,
  n_distinct,
  correlation,
  most_common_vals,
  most_common_freqs
FROM   pg_stats
WHERE  tablename = 'orders'
  AND  attname   = 'status';

-- Example output:
attname     | status
n_distinct  | 4                    -- 4 unique values
correlation | 0.12                 -- physical ordering
mcv         | {pending,shipped,completed,cancelled}
mcf         | {0.45, 0.30, 0.20, 0.05}

n_distinct

Number of distinct values in the column. If positive, it's the actual count. If negative (e.g. -0.95), it's the fraction of rows with distinct values. Critical for estimating cardinality after a GROUP BY.

correlation

Statistical correlation between physical row order and logical column order. 1.0 = perfect correlation (sorted). 0 = random. High correlation means an index scan will be very efficient (few random I/O jumps).

most_common_vals

The N most common values and their frequencies. Lets the planner know "50% of orders have status='pending'" so it can accurately estimate how many rows WHERE status = 'pending' will return.

Scan Types the Planner Considers

Scan Type	When Used	Cost Profile
Sequential Scan	No usable index, or when fetching most of the table (>~10-20% of rows)	Low per-page cost, predictable, benefits from OS read-ahead
Index Scan	Selective filter (<10% of rows), good index available	Random I/O per row — expensive for many rows, fast for few
Index-Only Scan	All needed columns in the index (covering index), visibility map is clean	Cheapest for covered queries — avoids heap entirely
Bitmap Index Scan	Moderate selectivity, especially with multiple indexes to combine (OR conditions)	Builds a bitmap in memory, then reads heap pages in order

Join Strategies

Join Type	Algorithm	Best For
Nested Loop Join	For each row in outer, scan inner for matches. O(n×m).	Small inner relation, or inner has index on join column
Hash Join	Build hash table from smaller relation, probe with larger. O(n+m).	Large equi-joins, no useful index. Needs work_mem.
Merge Join	Sort both sides, merge in-order. O(n log n + m log m).	Both sides already sorted (e.g. indexed), or large equi-joins

Animated Plan Comparison

For the query SELECT * FROM users WHERE id = 42, the planner generates (at least) two candidate plans. Watch the optimizer compare their costs:

Stale Statistics = Bad Plans! If you TRUNCATE a table and insert 10 million new rows without running ANALYZE, the planner still thinks the table has the old row count. It may choose a sequential scan when an index scan would be 1000× faster. Always run ANALYZE after bulk loads. autovacuum handles routine cases.

💰 STAGE 4 — THE COST MODELCH.5

How the Optimizer Thinks in Numbers

seq_page_cost, random_page_cost, cpu_tuple_cost

The optimizer doesn't time actual query execution to pick a plan. Instead, it assigns a cost — an abstract dimensionless number — to each candidate plan using a mathematical model. The plan with the lowest cost wins. Costs are calibrated relative to reading a single 8KB page sequentially from disk, which is assigned the cost of 1.0.

The Cost Formula

Total cost is the sum of I/O costs and CPU costs:

-- Core cost formula:
total_cost = (pages_read_seq  × seq_page_cost)
           + (pages_read_rand × random_page_cost)
           + (rows_processed  × cpu_tuple_cost)
           + (rows_filtered   × cpu_operator_cost)

-- Default PostgreSQL values (postgresql.conf):
seq_page_cost    = 1.0    -- reading next page in sequence
random_page_cost = 4.0    -- jumping to a random page (HDD)
cpu_tuple_cost   = 0.01   -- processing one row
cpu_index_tuple  = 0.005  -- processing one index entry
cpu_operator_cost= 0.0025 -- evaluating one operator (=, <, etc.)
parallel_tuple_cost = 0.1 -- cost of passing row between workers

Why random_page_cost = 4.0?

On a spinning hard disk, a random read requires a physical seek — the disk arm moves to a new track, then waits for the disk to rotate to the right sector. This takes ~5-10ms on average. A sequential read just continues from where it left off: no seek, no rotational wait. The 4× ratio approximates this difference for HDDs.

On SSDs, the random I/O penalty is much smaller — there's no mechanical seek. A well-tuned PostgreSQL on SSD should use random_page_cost = 1.1 or even 1.0. Using the default 4.0 on an SSD causes the optimizer to avoid index scans when it shouldn't, forcing unnecessary sequential scans.

Tuning tip: If your database is on SSD, set random_page_cost = 1.1. If it's on NVMe, set random_page_cost = 1.0. This single change can dramatically improve query plan quality.

A Worked Example: Cost Calculation

-- Table: 10,000 rows, 100 pages (100 × 80 rows per 8KB page)
-- Query: SELECT * FROM users WHERE id = 42
-- Index on id (B-tree), 3 levels deep

-- Plan A: Sequential Scan
cost = 100 pages × 1.0 (seq) + 10000 rows × 0.01 (cpu)
     = 100 + 100 = 200.00

-- Plan B: Index Scan (finds 1 row)
cost = 0.42 (startup: index traversal, 3 levels)
     + 1 page × 4.0 (random: heap fetch)
     + 1 row × 0.01 (cpu)
     = 0.42 + 4.0 + 0.01 = 4.43

-- Optimizer picks Plan B (cost 4.43 vs 200.00)

Join Reordering and the Geqo Algorithm

For queries joining N tables, there are N! possible join orderings (left-deep trees alone give N! permutations). PostgreSQL exhaustively tests all orderings for N ≤ 8 tables (controlled by join_collapse_limit). For N > 8, it switches to the Genetic Query Optimizer (GEQO).

Dynamic Programming (≤8 tables)

Tests all possible join orderings and all possible access methods for each table. Optimal result guaranteed — the globally cheapest plan is always found.

geqo_threshold = 12 (switch to GEQO at 12+ tables)

GEQO (8+ tables)

Genetic algorithm: represents each join order as a "genome", performs crossover and mutation. Finds a good plan, not necessarily optimal. Fast for large numbers of tables.

geqo_effort = 5 (1-10 scale, 5 is default)

Statistics and Danger of Stale Stats

The planner's row estimates are only as good as the statistics. When ANALYZE runs, it samples a random subset of the table (default 300 × default_statistics_target = 30,000 rows) and computes histograms, MCV lists, and correlation values.

-- Force analyze on a specific table:
ANALYZE orders;

-- Check when statistics were last collected:
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
WHERE  tablename = 'orders';

-- Increase stats target for a column with skewed distribution:
ALTER TABLE orders
  ALTER COLUMN status
  SET STATISTICS 1000; -- default is 100

Classic disaster scenario: You do a one-time bulk insert of 50 million rows into a table that normally has 100,000. Autovacuum hasn't run yet. The planner still estimates 100,000 rows. It chooses a Nested Loop join that would be fine for 100K rows — but with 50M rows it runs for hours. Always ANALYZE after bulk loads.

⚙️ STAGE 5 — THE EXECUTORCH.6

The Volcano Model — Pull-Based Execution

Iterator trees, work_mem, and parallel workers

The executor takes the plan produced by the optimizer and actually executes it. PostgreSQL uses the Volcano iterator model (also called the iterator model or pipeline model), a design from a 1994 paper by Goetz Graefe that has become the standard for relational query engines.

The Volcano Model: How It Works

Every node in the plan tree implements three operations:

Init()

Called once. Allocates memory, opens relations, initializes child nodes. Like a constructor.

GetNext()

Called repeatedly. Returns one tuple (row) each time, or NULL when done. The core of the model — pull semantics.

End()

Called once at completion. Releases resources, closes files, frees memory. Like a destructor.

The root node of the plan tree is called by the executor driver. It calls GetNext() on its child, which calls GetNext() on its child, and so on down to the storage layer. Data flows upward through the tree one tuple at a time.

-- Example plan tree for:
-- SELECT * FROM users u JOIN orders o ON u.id=o.user_id WHERE u.active LIMIT 10

Limit (rows=10)
  └── Hash Join (u.id = o.user_id)
        ├── Seq Scan on users u        ← Filter: active = true
        └── Hash
              └── Seq Scan on orders o

-- Execution flow (pull-based):
Limit.GetNext()
  → HashJoin.GetNext()
      → (probe hash table with next user row)
  → HashJoin.GetNext()
      → SeqScan_users.GetNext()  ← returns next active user
  → ... repeat until 10 rows found

Plan Node Types

Node Type	What It Does	Key Parameter
SeqScan	Read all pages of a heap table sequentially	relation OID
IndexScan	Traverse B-tree index, fetch heap pages per entry	index OID, scan keys
IndexOnlyScan	Traverse B-tree index, return from index leaf (no heap)	visibility map must be clean
BitmapIndexScan	Build in-memory bitmap of matching TIDs from index	Used with BitmapHeapScan
BitmapHeapScan	Read heap pages in order per the bitmap	recheck_cond if lossy
NestLoop	Nested loop join: outer × inner	join_filter
HashJoin	Build hash table, probe it	work_mem budget
MergeJoin	Merge-sort join of two sorted streams	merge keys
Sort	Sort tuples by key columns	work_mem, sort keys
HashAggregate	GROUP BY using a hash table	work_mem
Limit	Pass through N rows, then stop	count, offset
Gather	Merge results from parallel workers	num_workers

work_mem: The Hidden Bottleneck

The work_mem parameter controls how much memory each sort operation and each hash table can use before spilling to disk. The default is a shockingly low 4MB. A query with a sort, a hash join, and a group by uses up to 3 × work_mem = 12MB. With 100 concurrent connections each running such a query, that's 1.2GB just for these operations.

-- Check current work_mem:
SHOW work_mem;  -- probably 4MB

-- Increase for a specific session/query:
SET work_mem = '64MB';
EXPLAIN ANALYZE SELECT ...;  -- re-run your expensive query

-- When you see this in EXPLAIN ANALYZE output:
Sort Method: external merge  Disk: 24576kB
-- It means work_mem was too small — sort spilled to disk
-- Increasing work_mem eliminates the disk spill

Parallel Query Execution

Since PostgreSQL 9.6, the executor can spawn parallel worker processes to split large sequential scans, joins, and aggregates across multiple CPU cores. The planner decides how many workers to use based on the estimated size of the work and the max_parallel_workers_per_gather setting.

-- Example plan with parallel workers:
Gather (cost=1000.00..25000.00 rows=1000000)
  Workers Planned: 4
  → Parallel Seq Scan on huge_table
      Filter: (created_at > '2024-01-01')

-- Key settings:
max_parallel_workers_per_gather = 4  -- workers per query
max_parallel_workers             = 8  -- global worker pool
parallel_tuple_cost              = 0.1 -- cost of moving tuple between workers
min_parallel_table_scan_size    = 8MB  -- min size to consider parallelism

🌳 INDEXES DEEP DIVECH.7

B-Tree, Hash, GiST, GIN, BRIN

Every index type, when to use it, and the hidden costs

An index is a separate data structure that trades write overhead and storage for faster reads. Without indexes, every query that filters rows must scan the entire table — O(n). With the right index, the same query runs in O(log n) or even O(1). Choosing the wrong index type — or putting an index on the wrong column — can actually make things worse.

B-Tree Index (The Default)

When you run CREATE INDEX without specifying a type, you get a B-tree. It is the workhorse of relational databases, invented by Rudolf Bayer and Edward McCreight in 1972 and still the best general-purpose index structure available.

B-Tree: Best For

✅ Equality: WHERE id = 42
✅ Range: WHERE price BETWEEN 10 AND 50
✅ Sort: ORDER BY created_at
✅ LIKE prefix: WHERE name LIKE 'Ali%'
✅ Composite indexes: (user_id, created_at)
❌ NOT GOOD for: full-text, arrays, JSON

Composite Index Column Order

Index on (user_id, created_at) can answer:

✅ WHERE user_id = 5
✅ WHERE user_id = 5 AND created_at > now() - '7d'
❌ WHERE created_at > now() - '7d' (can't use index — leading column missing)

Leading column must appear in WHERE or the index can't be used for filtering.

Hash Index

Hash indexes use a hash function to map each indexed value to a bucket. This gives O(1) lookup for exact equality matches — theoretically faster than O(log n) B-tree for equality. However, hash indexes cannot do range scans (there's no ordering among hash buckets), cannot support ORDER BY, and were not WAL-logged until PostgreSQL 10 (making them unsafe on older versions).

In practice, B-tree indexes are so fast for equality (just 3-4 levels for millions of rows) that hash indexes are rarely worth the limitation. Use them only when you have a specific high-throughput equality-only workload and have benchmarked the improvement.

GiST — Generalized Search Tree

GiST is a framework for building custom index structures. It's used for:

Geometric types: point, polygon, circle — spatial queries like "find all locations within 10km"
Full-text search: tsvector with @@ operator
Range types: daterange, int4range — overlap and containment queries
PostGIS: the geospatial extension uses GiST heavily for geometry indexing

GIN — Generalized Inverted Index

GIN is an inverted index — like the index at the back of a book, it maps each element (token, key) to the set of rows containing it. GIN is used for:

Full-text search: maps each lexeme to the set of document TIDs containing it
JSONB: indexes every key-value pair in a JSON document (@>, ? operators)
Arrays: WHERE tags @> ARRAY['postgres', 'performance']

-- Full-text search with GIN:
CREATE INDEX idx_articles_fts
  ON articles
  USING gin(to_tsvector('english', title || ' ' || body));

SELECT title
FROM   articles
WHERE  to_tsvector('english', title || ' ' || body)
       @@ to_tsquery('postgresql & performance');

-- JSONB with GIN:
CREATE INDEX idx_orders_meta ON orders USING gin(metadata);
SELECT * FROM orders WHERE metadata @> '{"status": "vip"}';

BRIN — Block Range Index

BRIN (Block Range Index) is a tiny index designed for massive tables with natural physical ordering — think time-series data, sensor readings, log entries. Instead of storing individual values like B-tree, BRIN stores the min and max value for each range of consecutive disk pages.

-- Perfect use case: 10 billion row sensor table ordered by time
CREATE INDEX idx_sensor_time
  ON sensor_readings
  USING brin(recorded_at)
  WITH (pages_per_range = 128);

-- BRIN index stores: [page 0-127: min=2024-01-01, max=2024-01-15]
--                   [page 128-255: min=2024-01-15, max=2024-01-31]
--                   ...

-- For WHERE recorded_at = '2024-06-15':
-- BRIN skips all page ranges where max < target or min > target
-- A 10B row table with 1MB BRIN vs 20GB B-tree!

BRIN Caveat: BRIN only works if your data has physical locality — rows with similar values are physically near each other on disk. For a table with random inserts across all time periods, BRIN provides zero benefit and the optimizer can't skip any ranges.

Index Bloat and VACUUM

Every UPDATE and DELETE in PostgreSQL is non-destructive. The old row version is marked as "dead" but the space is not immediately reclaimed. Dead tuples accumulate in both the heap (table data) and in all indexes pointing to those tuples. This is called index bloat.

-- Check index bloat:
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM   pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- An idx_scan of 0 means the index is never used!
-- Unused indexes still slow down every INSERT/UPDATE/DELETE.

-- Manual VACUUM on a specific table:
VACUUM (ANALYZE, VERBOSE) orders;

-- VACUUM FULL reclaims disk space (requires full table lock):
VACUUM FULL orders;  -- WARNING: takes ExclusiveLock!

🗂️ THE BUFFER POOL (SHARED BUFFERS)CH.8

PostgreSQL's In-Memory Cache

Cache hits in microseconds, cache misses in milliseconds

Every page of data that PostgreSQL reads from disk is stored in the buffer pool (called shared_buffers in postgresql.conf). The buffer pool is a region of shared memory divided into 8KB slots — one slot per page. When the executor needs a page, it first checks the buffer pool. A cache hit returns the data in ~1 microsecond. A cache miss requires a disk read: 0.1ms on NVMe, 0.5ms on SSD, 5-10ms on HDD — up to 10,000× slower.

Cache Hit (In Buffer Pool)

⚡ ~1 microsecond access time
⚡ Just a pointer lookup in shared memory
⚡ No system call required
⚡ ~1000 MB/s effective throughput
⚡ The fast path for all hot data

Cache Miss (Disk Read Required)

💾 NVMe SSD: ~0.1ms (100 microseconds)
💾 SATA SSD: ~0.5ms (500 microseconds)
💾 HDD: 5-10ms (seek + rotational latency)
💾 Requires OS system call
💾 Context switch, scheduler involvement

LRU Eviction Policy

When the buffer pool is full and a new page needs to be loaded, PostgreSQL must evict an existing page. It uses a variant of the Least-Recently-Used (LRU) algorithm called the clock sweep. Each buffer has a "usage count" that increments when accessed and decrements during the clock sweep. Buffers with usage count of 0 are candidates for eviction.

Dirty pages (pages modified but not yet flushed to disk) must be written to disk before eviction. This is handled by the bgwriter (background writer) process, which proactively flushes dirty pages to reduce I/O pressure during eviction.

Interactive Buffer Pool Demo

Watch cache hits (green) and misses (red/orange) in action. The cache hit ratio should be >99% in a healthy production database.

BUFFER POOL — 8 SLOTS

EMPTY—

MISSES: 0

HIT RATIO: —

Ready. Click a button to simulate queries.

Sizing shared_buffers

The default shared_buffers = 128MB is absurdly small for any production workload. The standard recommendation is 25% of total system RAM. On a 32GB server, set shared_buffers = 8GB. On a 128GB server, 32GB. PostgreSQL also relies on the OS page cache for the other 75% of RAM, so it's a two-layer cache.

-- Check what's currently in the buffer pool (requires pg_buffercache extension):
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT
  c.relname,
  count(*) AS buffers,
  round(count(*) * 8 / 1024.0, 1) AS buffer_mb,
  round(100.0 * count(*) /
    (SELECT setting::int FROM pg_settings WHERE name = 'shared_buffers'), 1) AS pct_of_cache
FROM   pg_buffercache b
JOIN   pg_class c ON c.relfilenode = b.relfilenode
WHERE  b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

Effective Cache Size

The optimizer uses effective_cache_size (NOT shared_buffers) as its estimate of total memory available for caching. This includes both shared_buffers and the OS page cache. Set it to 75% of total RAM. This helps the optimizer correctly judge whether an index-only scan will have its pages in cache.

📜 WRITE-AHEAD LOG (WAL)CH.9

Durability Without Slowness

Sequential writes, crash recovery, and replication

The Write-Ahead Log is PostgreSQL's durability engine. The fundamental rule: before any data page is modified, the change must first be written to the WAL. This is not a backup — it's an append-only sequential log of every change that enables crash recovery, point-in-time recovery, and streaming replication.

Why WAL Makes Writes Fast

Without WAL, making a write durable would require doing a fsync() on every data page you modified — potentially many random writes spread across many data files. With WAL, you only need to fsync() the WAL file, which receives all changes as sequential appends. Sequential I/O is 10-100× faster than random I/O on both HDDs and SSDs.

-- What happens during a single UPDATE:

1. Transaction begins
2. Copy old row → UNDO log (for MVCC visibility)
3. Modify page in buffer pool (in-memory only)
4. Write WAL record: "UPDATE users SET name='Bob' WHERE id=42"
5. COMMIT is written to WAL
6. WAL is fsync()'d to disk  ← durability guaranteed here
7. Return success to client
8. Modified buffer page is written to data file later (async)

-- If the server crashes between steps 7 and 8:
-- WAL replay on startup re-applies the change to the data file
-- Zero data loss for committed transactions

WAL LSN (Log Sequence Number)

Every WAL record has a unique Log Sequence Number (LSN) — a 64-bit byte offset into the WAL stream. LSNs are monotonically increasing. Every data page in PostgreSQL stores the LSN of the last WAL record that modified it, enabling precise crash recovery — replaying from the last checkpoint's LSN is sufficient.

-- Check current WAL position:
SELECT pg_current_wal_lsn();
-- Returns: 0/3FA20B0 (hex format: segment/offset)

-- Check WAL lag on a standby server:
SELECT
  application_name,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS lag_bytes
FROM   pg_stat_replication;

WAL Levels

wal_level	What's Logged	Use Case
minimal	Only enough for crash recovery. No replication possible.	Single-server, maximum write performance (rarely appropriate)
replica	Full change data. Enables physical/streaming replication.	Default for production. Use for standby servers, PITR.
logical	Everything in replica + enough for logical decoding.	Logical replication, Change Data Capture (CDC), Debezium.

Checkpoints

A checkpoint is when PostgreSQL writes all dirty pages (modified in-memory buffers) to the actual data files on disk and records the checkpoint LSN in the control file. After a checkpoint, WAL replay on crash recovery only needs to start from the checkpoint LSN — earlier WAL can be recycled.

Checkpoint Triggers

Time-based: every checkpoint_timeout (default 5 min)
WAL-size-based: when WAL fills max_wal_size (default 1GB)
Manual: CHECKPOINT command
On shutdown (smart/fast)

Checkpoint Spread

checkpoint_completion_target = 0.9 means PostgreSQL aims to write dirty pages over 90% of the checkpoint interval (not all at once), smoothing out I/O spikes. Set this to 0.9 in production.

WAL as the Foundation of Everything

★

Streaming Replication

Standby servers connect to the primary and receive WAL records in real time. They replay WAL to stay in sync. When the primary fails, a standby can be promoted in seconds.

★

Point-in-Time Recovery (PITR)

Archive WAL files continuously. To recover to "yesterday at 3:47pm", restore a base backup, then replay archived WAL up to that LSN. Can recover from accidental data deletion.

★

Logical Replication & CDC

With wal_level=logical, the WAL decoder can produce row-level change events (INSERT/UPDATE/DELETE with old and new values). Tools like Debezium, pglogical, and Postgres logical replication slots consume this stream.

🔍 EXPLAIN ANALYZE — READING THE OUTPUTCH.10

X-Ray Vision for Your Queries

Every number annotated, every node explained

EXPLAIN shows the query plan the optimizer chose. EXPLAIN ANALYZE actually executes the query and shows both the optimizer's estimates and the real execution numbers. The gap between estimated and actual rows is the first thing to look for — big gaps mean stale statistics.

The Three Commands

`EXPLAIN`

Shows the plan the optimizer picked. Estimated costs and row counts only. Does NOT execute the query. Safe to run on any query including destructive ones.

`EXPLAIN ANALYZE`

Executes the query AND shows the plan. Shows both estimated and actual rows/time. Running on UPDATE/DELETE actually makes the changes! Use in a transaction and ROLLBACK if needed.

`EXPLAIN (BUFFERS, ANALYZE)`

The most detailed output. Shows buffer hits (shared_buffers), reads (disk/OS cache), and dirty/written counts. Essential for diagnosing cache performance.

Full Annotated EXPLAIN ANALYZE Output

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM   users    u
JOIN   orders   o ON u.id = o.user_id
WHERE  u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT  10;

Output (color-coded: costs, actual times, row counts):

Limit (cost=234.50..234.53 rows=10 width=40) (actual time=5.234..5.237 rows=10 loops=1) ↑ Top node. Optimizer estimated cost 234.50 to start returning, 234.53 when all 10 rows done. Actual: 5.234ms start, 5.237ms done. Perfect estimate here.

→

Sort (cost=234.47..234.50 rows=10 width=40) (actual time=5.230..5.232 rows=10 loops=1) ↑ Sort Key: count(o.id) DESC — sorting the aggregated results. Sort Method: quicksort Memory: 25kB (all fit in work_mem, no disk spill).

→

HashAggregate (cost=233.70..233.80 rows=10 width=40) (actual time=5.180..5.210 rows=247 loops=1) ↑ GROUP BY u.name. Optimizer estimated 10 groups (off!), actual was 247. This estimate mismatch means stats for the name column may be stale. Batches: 1, Memory Usage: 40kB.

→

Hash Join (cost=32.50..220.40 rows=2660 width=28) (actual time=0.540..4.820 rows=2718 loops=1) ↑ Hash Cond: (o.user_id = u.id). Estimated 2660 rows, got 2718 — close! Hash Batches: 1, Original Hash Batches: 1, Memory Usage: 128kB. Hash table fit in work_mem.

├─→

Seq Scan on orders (cost=0.00..185.00 rows=9500 width=8) (actual time=0.010..1.840 rows=9500 loops=1) ↑ Full sequential scan of orders table (9500 rows). No filter applied here — all order rows are fetched for the join. If orders were larger, an index on user_id would help.

└─→

Index Scan using idx_users_created_at on users (cost=0.28..32.20 rows=280 width=24) (actual time=0.030..0.450 rows=312 loops=1) ↑ Index Cond: (created_at > '2024-01-01'::date). Estimated 280, got 312 (slightly off but acceptable). The index on created_at is being used effectively here.

Planning Time: 0.842 ms ↑ Time spent in the parser + planner + optimizer. Usually <1ms for simple queries. Can be 50ms+ for very complex plans. If planning time dominates, consider prepared statements.

Execution Time: 5.492 ms ↑ Total execution time including all nodes. This is wall-clock time. Note: does NOT include the time for the client to receive results over the network.

Key Things to Look For

🚨 Red Flags

❌ Rows estimate vs actual: 100× off — stale statistics, run ANALYZE
❌ "Sort Method: external merge Disk:" — increase work_mem
❌ "Hash Batches: 8" — hash table spilled to disk, increase work_mem
❌ "Seq Scan on large_table" with Filter — missing index
❌ "loops=10000" on inner side of Nested Loop — N+1 problem
❌ Planning Time > Execution Time — overly complex query or pg_stat_statements overloaded

✅ Green Flags

✅ Index Scan / Index Only Scan — selective filter using index
✅ Sort Method: quicksort Memory: NkB — sort in memory
✅ Hash Batches: 1 — hash join fully in memory
✅ Estimated rows ≈ Actual rows — good statistics
✅ Execution Time < 10ms for OLTP queries
✅ Parallel workers active for analytical queries

💥 COMMON PERFORMANCE MISTAKESCH.11

The Hall of Shame

Mistakes that kill database performance in production

1. The N+1 Query Problem

The single most common database performance disaster. You fetch a list of 1000 users with one query, then for each user you issue a separate query to fetch their orders. Result: 1001 round trips to the database.

-- ❌ WRONG: N+1 problem (1001 queries for 1000 users)
users = db.query("SELECT * FROM users LIMIT 1000")
for user in users:
    orders = db.query(f"SELECT * FROM orders WHERE user_id = {user.id}")
    # 1000 queries for 1000 users = 1000 round trips!

-- ✅ CORRECT: Single JOIN (1 query)
SELECT u.*, o.*
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
LIMIT  1000;

-- ✅ OR: Use IN/ANY with a subquery:
SELECT * FROM orders
WHERE  user_id = ANY(SELECT id FROM users LIMIT 1000);

2. Missing Index on Foreign Key Columns

PostgreSQL does NOT automatically create indexes on foreign key columns (only on primary keys and unique constraints). A JOIN on orders.user_id = users.id will do a sequential scan of the orders table for every user if there's no index on orders.user_id.

-- ❌ Missing index: this JOIN does Seq Scan on orders
SELECT * FROM users u
JOIN   orders o ON u.id = o.user_id  -- no index on orders.user_id!

-- ✅ Add the index:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- CONCURRENTLY lets you create the index without locking the table!

3. SELECT * Fetching Unused Columns

SELECT * forces PostgreSQL to read and return every column of every row, including large TEXT, JSONB, and BYTEA columns. This increases I/O, network bandwidth, and memory usage. It also prevents Index-Only Scans since the executor must fetch the full row from the heap even when all needed data is in the index.

-- ❌ Fetches 50 columns, most unused
SELECT * FROM products;

-- ✅ Fetch only what you need
SELECT id, name, price FROM products;

-- ✅ AND create a covering index for the hottest queries:
CREATE INDEX idx_products_id_name_price ON products(id) INCLUDE (name, price);
-- Now SELECT id,name,price FROM products WHERE id=42 is Index-Only Scan!

4. OFFSET Pagination on Large Tables

OFFSET 50000 LIMIT 10 does NOT magically skip 50,000 rows at the storage level. PostgreSQL must read and discard the first 50,000 rows before returning the 10 you want. For page 5000 of search results, you're reading 50,000 rows to return 10. This gets worse with every page.

-- ❌ OFFSET pagination: O(n) for page n
SELECT * FROM products ORDER BY id OFFSET 50000 LIMIT 10;
-- Must read 50010 rows to return 10!

-- ✅ Cursor / keyset pagination: O(log n) always
-- Page 1:
SELECT * FROM products ORDER BY id LIMIT 10;
-- Returns last id = 1042. Next page:
SELECT * FROM products WHERE id > 1042 ORDER BY id LIMIT 10;
-- Uses index on id, always O(log n) regardless of page number!

5. Function Calls in WHERE Clauses

Wrapping a column in a function call prevents PostgreSQL from using an index on that column. The index stores the raw column values, not the result of the function. PostgreSQL cannot efficiently find LOWER(email) = 'ali@example.com' using an index on email because the index stores mixed-case values.

-- ❌ Kills index on email column:
WHERE LOWER(email) = 'ali@example.com'
-- PostgreSQL must compute LOWER() for every row → Seq Scan

-- ✅ Use a functional index:
CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));
-- Now WHERE LOWER(email) = '...' uses the index!

-- ❌ Type cast on column kills index:
WHERE created_at::date = '2024-01-01'
-- Casts every timestamp to date → Seq Scan

-- ✅ Use a range condition instead:
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-01-02'
-- Uses the index on created_at!

6. Implicit Type Coercion

When your application sends a string '42' but the column is integer, PostgreSQL casts the string to integer for each comparison. This usually works but can be catastrophic with indexes — if the column is text and your parameter is integer, the cast goes the wrong direction and kills the index.

-- ❌ Column is TEXT, value is integer — bad cast direction:
SELECT * FROM products WHERE sku = 12345;
-- PostgreSQL must cast sku::integer for every row → Seq Scan!

-- ✅ Always match parameter type to column type:
SELECT * FROM products WHERE sku = '12345'; -- string = string ✓

-- In application code: always use typed bind parameters
db.query("SELECT * FROM products WHERE sku = $1", ["12345"]);
-- Not: db.query("... WHERE sku = $1", [12345])  ← integer vs text

The missing index diagnosis checklist: (1) Check EXPLAIN ANALYZE for "Seq Scan" with "Filter" — this means a full table scan with a filter applied row-by-row. (2) Check pg_stat_user_indexes for idx_scan=0 — that's a dead index wasting write overhead. (3) Check pg_stat_user_tables for n_live_tup vs n_dead_tup — high dead tuple ratio means VACUUM hasn't run enough.

📋 CHEAT SHEETCH.12

Quick Reference — Save This

EXPLAIN commands, monitoring views, index types, memory settings

EXPLAIN COMMANDS

EXPLAIN SELECT ...
Show plan only, no execution
EXPLAIN ANALYZE SELECT ...
Execute and show actual stats
EXPLAIN (ANALYZE, BUFFERS) ...
+cache hit/miss counts
EXPLAIN (ANALYZE, FORMAT JSON) ...
Machine-readable output for explain.depesz.com
EXPLAIN (ANALYZE, TIMING OFF) ...
Faster: skip per-node timing
BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;
Safe explain on destructive queries

KEY MONITORING VIEWS

pg_stat_user_tables
Seq/idx scans, dead tuples, last vacuum
pg_stat_user_indexes
Index scans, rows read/fetched
pg_stat_activity
Active queries, wait events, PIDs
pg_stat_statements
Aggregate stats per query fingerprint (must enable extension)
pg_locks
Current lock holders and waiters
pg_stat_bgwriter
Checkpoint stats, buffer writes
pg_stat_replication
Standby lag, WAL LSN positions

INDEX TYPES QUICK REF

B-tree — Default. Equality, range, ORDER BY, LIKE prefix
Hash — Equality only. Rarely worth it over B-tree
GIN — Arrays, JSONB, full-text search (@@)
GiST — Geometry, ranges, full-text. Lossy lookups possible
BRIN — Huge sorted tables (time-series). Min/max per block range
Partial index — CREATE INDEX ON t(col) WHERE active=true — tiny index for subset of rows
Covering index — CREATE INDEX ON t(id) INCLUDE (name, email) — enables Index-Only Scan

MEMORY SETTINGS TO TUNE

shared_buffers = 25% of RAM
Default 128MB is too small
effective_cache_size = 75% of RAM
Planner estimate of total cache
work_mem = 64–256MB per session
Default 4MB causes disk sort spills
maintenance_work_mem = 1–2GB
For VACUUM, CREATE INDEX, ALTER TABLE
wal_buffers = 64MB
Default -1 = auto, usually fine
random_page_cost = 1.1 (SSD) or 4.0 (HDD)
Critical for plan quality on SSD!

DANGEROUS ANTI-PATTERNS

WHERE LOWER(col) = $1 — use functional index
WHERE col::date = $1 — use range instead
OFFSET n LIMIT 10 large n — use keyset pagination
SELECT * on wide tables — select needed columns
No index on FK columns — always index FK columns
N+1 queries — use JOIN or batch fetch
VACUUM FULL in production — causes full table lock
Default work_mem on analytical queries — set higher

QUERY PIPELINE SUMMARY

1. Parser — SQL string → AST. Catches syntax errors.
2. Rewriter — Expand views, inject RLS policies.
3. Planner — Generate candidate plans using pg_statistics.
4. Optimizer — Cost model selects cheapest plan.
5. Executor — Volcano/iterator model, pulls rows from tree.
6. Buffer Pool — 8KB page cache. Hit = µs, Miss = ms.
7. WAL — Sequential writes for durability + replication.

You now understand what happens in those 0.4 milliseconds between sending SELECT and receiving your rows. Parser turns your characters into a tree. Rewriter expands views. Planner considers every possible strategy using table statistics. Optimizer assigns costs and picks the winner. Executor pulls rows through the plan tree. Buffer pool serves pages from RAM. WAL ensures no committed data is ever lost. Every single time.