Internals

Page layout (roughly universal)
┌────────────────────────────────────────────┐
│ header: checksum, free-space pointers, LSN │
├────────────────────────────────────────────┤
│ slot array →  ptr ptr ptr ptr …            │  ← indirection, so a row
├────────────────────────────────────────────┤    can move within the page
│                free space                 │    without changing its ID
├────────────────────────────────────────────┤
│  … row  row  row  row                      │  ← rows fill from the end
└────────────────────────────────────────────┘

The slot array is why a row identifier stays stable while the row is
reorganised inside its page — and why compaction after deletes is cheap.

Big values (a long TEXT, a blob) don't fit in one page, so they go to
overflow storage — TOAST in Postgres. Consequence worth knowing: a table
with a wide TEXT column can be fast to scan if you don't SELECT that
column, because the main page holds only a pointer. That is a real
argument for naming your columns instead of SELECT *.

The buffer pool

The database keeps recently-used pages in memory and manages that cache itself. This is the single most important performance structure in a database: a page found in the buffer pool costs ~100 ns, and one that must be read from SSD costs ~100 µs — a thousandfold difference. A healthy OLTP system runs above a 99% buffer hit rate, and a dropping hit rate is usually the first sign of trouble.

Row store vs column store

Hence the standard architecture: a row store for transactions, streaming into a column store for analytics. Running heavy analytical queries against your transactional database is the mistake this separation exists to prevent — those scans evict the buffer pool's hot set and slow down every user-facing query.

Clustered vs secondary

InnoDB always clusters on the primary key, which has two consequences worth stating: a random primary key (UUIDv4) causes page splits and fragmentation on insert because rows land in the middle of the tree, whereas a monotonic key appends cleanly; and a wide primary key is stored in every secondary index, inflating all of them. That's the argument for a narrow, time-ordered surrogate key like a bigint or UUIDv7. PostgreSQL is different — it stores rows in a heap and all indexes are secondary, so it has no clustered index and the tradeoffs shift.

Composite indexes and the prefix rule

CREATE INDEX idx ON orders (customer_id, status, created_at);

Can serve:                                    Cannot serve:
  WHERE customer_id = 1                         WHERE status = 'paid'
  WHERE customer_id = 1 AND status = 'paid'     WHERE created_at > '...'
  … AND created_at > '2026-01-01'               (no leading customer_id)
  ORDER BY customer_id, status, created_at

LEFTMOST PREFIX RULE: the index is sorted by the first column, then by
the second within equal firsts, and so on. So it can't help a query that
doesn't constrain the leading column — same reason a phone book sorted
by (surname, forename) is useless for finding everyone called "Priya".

Column ORDER guidance:
  1. Equality predicates before range predicates. An index on
     (status, created_at) serves `status = 'paid' AND created_at > x`;
     (created_at, status) does not use the status part efficiently.
  2. Put the column you ORDER BY last, so the index provides the sort
     and the planner can skip the sort step entirely.
  3. Higher selectivity earlier, all else equal.

Index types and special cases

From text to result

SQL text
  → parse       syntax check, build a tree
  → bind        resolve names against the catalogue, type-check
  → rewrite     expand views, flatten subqueries, push predicates down,
                  eliminate provably-unneeded joins
  → plan        enumerate access paths and join orders, ESTIMATE the
                  cost of each using statistics, pick the cheapest
  → execute     run the chosen plan, usually as a tree of iterators

SQL is DECLARATIVE — you state the result, not the method — which is
what makes the optimiser possible and why the same query can get faster
after an upgrade without you changing anything.

Join algorithms — the three to know

Cost estimation and statistics

The optimiser is cost-based: it estimates I/O and CPU for each candidate plan from statistics kept about each table — row counts, column cardinality, most-common values, histograms of value distribution, and the fraction of NULLs. Those statistics are what ANALYZE collects, and they go stale after bulk loads or large deletes.

Reading EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01';

Hash Join  (cost=340..8210 rows=1200 width=40)
           (actual time=2.1..48.7 rows=98000 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0..6100 rows=1200)
                            (actual time=0.1..30.2 rows=98000)
        Filter: (created_at > '2026-01-01')
        Rows Removed by Filter: 402000
  ->  Hash  (cost=190..190 rows=12000) (actual rows=12000)
        ->  Seq Scan on customers c

Read it INSIDE-OUT and BOTTOM-UP — children run before parents.

Two red flags here:
1. estimated rows=1200 vs actual rows=98000 — an 80× underestimate.
   Statistics are stale, or the date predicate is correlated with
   something. Run ANALYZE; consider extended statistics.
2. Seq Scan on orders removing 402,000 rows by filter — an index on
   created_at would let it read only the 98,000 that match.

What to look for generally:
  · Seq Scan on a big table with a selective filter  → missing index
  · Nested Loop with a huge actual outer row count   → bad estimate
  · Sort with "external merge Disk: ..."             → raise work_mem
  · Hash "Batches: 8"                                → hash spilled to disk
  · Rows Removed by Filter very high                 → index the filter
  · estimated ≫ or ≪ actual                          → fix statistics first

EXPLAIN alone shows the PLAN and estimates.
EXPLAIN ANALYZE actually RUNS the query and shows real timings —
so never run it on a destructive statement outside a transaction
you intend to roll back.