Module 3 · Postgres & Data · Deep Dive

Postgres Performance & Optimization

Reading query plans, indexing for real, killing N+1, pooling connections, and making DocChat's retrieval fast. "This endpoint is slow — fix it" is the most heavily-probed backend interview ground for a 2026 full-stack role. This is how you own it.

IntermediateAdvancedBuild

1 · Reading query plans with EXPLAIN

You met EXPLAIN in the advanced lesson; here we go deeper, because reading a plan well is the single most useful database skill you can demonstrate. EXPLAIN shows the plan Postgres intends to use, with cost estimates, without running the query. EXPLAIN ANALYZE actually executes it and adds real timings and real row counts. For tuning, almost always reach for EXPLAIN (ANALYZE, BUFFERS) — BUFFERS shows how many disk/cache pages each node touched, which is often the real story.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM documents WHERE owner_id = 42;

A plan is a tree of nodes, read inside-out and bottom-up: the deepest, most-indented node runs first, feeding its parent. Each node line looks like this:

Seq Scan on documents  (cost=0.00..2310.00 rows=118 width=96)
                       (actual time=0.30..41.2 rows=120 loops=1)
  Filter: (owner_id = 42)
  Rows Removed by Filter: 99880

Decode it field by field:

Scan types the three you must recognise

• Seq Scan — reads every row. Correct on a tiny table or when you genuinely need most rows. On a big table with a selective WHERE, it's the red flag: a missing or unusable index.
• Index Scan — walks the index to the matching rows, then fetches each from the table (the "heap"). Best when few rows match. Fetching from the heap costs a random read per row, so it's a loss when many rows match.
• Bitmap Heap Scan (with a Bitmap Index Scan below it) — the middle ground. Postgres builds a bitmap of matching row locations from the index, then reads the heap in physical order. The planner picks this when a moderate number of rows match — too many for a plain Index Scan, too few for a Seq Scan.

So the planner has a spectrum: few matches → Index Scan; some → Bitmap; most → Seq Scan. When you see a Seq Scan you didn't expect, the question is always: is the filter selective, and is there an index it can use?

2 · Indexes in depth

An index is a sorted side-structure that lets Postgres find rows without scanning the table. The default and the workhorse is the B-tree — perfect for equality (=) and range (<, BETWEEN, ORDER BY) on scalar columns. The art is choosing the right index for the query shape.

Composite indexes — column order is everything

CREATE INDEX idx_chunks_doc_idx
ON chunks (document_id, chunk_index);

A multi-column index is sorted by the first column, then the second within each value of the first. The leftmost-prefix rule: this index serves a filter on document_id, or on document_id AND chunk_index — but not on chunk_index alone, because that column is only ordered inside each document. Rule of thumb: equality columns first, then the range/sort column last. A query like WHERE owner_id = 42 ORDER BY created_at DESC wants (owner_id, created_at DESC) so the index satisfies both the filter and the sort.

Partial indexes — index only the rows you query

-- you only ever search live documents
CREATE INDEX idx_docs_owner_live
ON documents (owner_id)
WHERE deleted_at IS NULL;

The WHERE on the index definition means Postgres indexes only matching rows — smaller, faster to scan, cheaper to maintain on every write. Ideal when queries always carry the same condition (WHERE deleted_at IS NULL, WHERE status = 'active').

Covering indexes — INCLUDE to skip the table

CREATE INDEX idx_docs_owner_inc
ON documents (owner_id) INCLUDE (title, created_at);

Normally an Index Scan finds the row in the index, then visits the heap to fetch the other columns. If every column the query needs lives in the index, Postgres can answer from the index alone — an Index Only Scan, no heap visit. INCLUDE bolts extra columns onto the index leaf without making them part of the sort key. When you see Index Only Scan in a plan, you've nailed it.

GIN — for JSONB & full-text

-- fast metadata @> containment on a big table
CREATE INDEX idx_docs_metadata
ON documents USING GIN (metadata);

B-trees index scalar values. For "does this JSONB contain that key/value?" or "does this text match these terms?" you need GIN (Generalized Inverted Index) — it indexes the contents of composite values, mapping each element back to the rows holding it. It's the right index for jsonb @> queries and for tsvector full-text (both below).

HNSW — for pgvector similarity

-- approximate nearest-neighbour over embeddings (pgvector 0.8, PG 16/17)
CREATE INDEX idx_chunks_embedding
ON chunks USING hnsw (embedding vector_cosine_ops);

Vector search ("which chunks are semantically closest to this question?") can't use a B-tree — there's no useful linear order in 1,536 dimensions. HNSW (Hierarchical Navigable Small World) is a graph index that finds approximate nearest neighbours fast. As of 2026 it's the default choice over the older IVFFlat: better recall, no training step, robust as data grows. Match the operator class to your distance metric — vector_cosine_ops for cosine (<=>), vector_l2_ops for Euclidean (<->). We build exactly this for DocChat below.

3 · Selectivity — why a perfect index gets ignored

A common shock: you add the obvious index and Postgres still does a Seq Scan. It's usually not a bug — the planner decided the index would be slower. The governing idea is selectivity: what fraction of the table the filter keeps.

An Index Scan pays a random-access cost per matching row (find it in the index, jump to the heap). If your filter matches 60% of the table, doing that 600,000 times is far slower than reading the whole table sequentially once. So when a filter isn't selective, a Seq Scan is genuinely the faster plan — and the planner is right.

Other reasons an index is skipped — each shows up in EXPLAIN:

• Function on the column: WHERE lower(email) = 'a@b.com' can't use a plain index on email. Fix: index the expression — CREATE INDEX ON users (lower(email));
• Leading wildcard: LIKE '%term' defeats a B-tree (it can't seek without a known prefix). Use full-text or a trigram index.
• Type mismatch: comparing a bigint column to a quoted string can prevent index use.
• Stale statistics: the planner mis-estimates selectivity. ANALYZE the table to refresh.
• Tiny table: a Seq Scan of a few hundred rows beats index overhead; the planner correctly ignores the index.

4 · The N+1 problem & SQLAlchemy 2.0

The N+1 query problem is the most common ORM performance bug, and a near-guaranteed interview question. You run 1 query to fetch a list of parents, then — usually inside a loop — N more queries, one per parent, to fetch each one's children. One page render becomes hundreds of round-trips.

In DocChat, loading documents and listing each one's chunks:

from sqlalchemy import select

# 1 query: fetch the documents
docs = session.scalars(select(Document)).all()

for doc in docs:
    # N queries: each .chunks access fires a fresh SELECT!
    print(doc.title, len(doc.chunks))

20 documents → 21 queries. In an EXPLAIN-style trace you'd see the same child query with loops=20, or in the SQL log the identical SELECT … FROM chunks WHERE document_id = ? repeated. The fix in SQLAlchemy 2.0 is an eager-loading strategy passed to select() via .options():

from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload

# selectinload: ONE extra query loads ALL chunks via WHERE document_id IN (...)
stmt = select(Document).options(selectinload(Document.chunks))
docs = session.scalars(stmt).all()   # 2 queries total, regardless of N

Two strategies, and knowing when to use each is the real answer:

Why not always joinedload? On a one-to-many it multiplies rows — 20 docs × 50 chunks = 1,000 rows, the parent columns repeated 50 times each, then de-duplicated in Python. selectinload avoids that fan-out. Rule: selectinload for collections, joinedload for single related objects.

5 · Connection pooling

Opening a Postgres connection is expensive — a TCP handshake, authentication, a backend process forked on the server. Doing that per request would crush you, and Postgres has a hard max_connections ceiling (often ~100). A connection pool keeps a set of open connections and hands them out and back, so requests reuse warm connections instead of paying setup each time.

SQLAlchemy pools by default. Tune it on the engine:

from sqlalchemy import create_engine

engine = create_engine(
    DB_URL,
    pool_size=10,        # persistent connections kept open
    max_overflow=5,      # temporary extras under burst (so 15 max)
    pool_timeout=30,     # seconds to wait for a free conn before erroring
    pool_recycle=1800,   # recycle conns older than 30 min (avoid stale ones)
    pool_pre_ping=True,  # test a conn is alive before handing it out
)

The key relationship to state in an interview: pool_size + max_overflow, multiplied by the number of app processes/workers, must stay under Postgres' max_connections. Four Gunicorn workers each allowed 15 connections = 60, plus headroom for migrations and admin tools.

6 · JSONB & full-text vs vector search

You met JSONB in the advanced lesson. Here's the performance angle plus the search question DocChat actually faces. JSONB stores parsed binary JSON — perfect for semi-structured data that doesn't deserve its own columns (per-document metadata, flexible settings). The operators that matter:

SELECT metadata ->> 'source'            -- field as text:  pdf
FROM documents
WHERE metadata @> '{"source":"pdf"}';  -- "contains" — indexable by GIN

• -> extracts a field as JSON (chainable); ->> extracts it as text (use when comparing to a string).
• @> is "contains" — the workhorse filter, and it's what the GIN index from §2 accelerates.

The performance trap is the same as before: if you @> the same key on every request, it deserves its own typed, B-tree-indexed column. JSONB is for genuinely variable data, not for dodging schema design.

Full-text search tsvector & tsquery

For keyword search, LIKE '%term%' is a Seq Scan and can't rank. Postgres' full-text search tokenises text into a tsvector (normalised, stemmed lexemes) and matches it against a tsquery:

-- match documents containing "retrieval" & "vector", ranked
SELECT id, title,
       ts_rank(search_vec, query) AS rank
FROM documents, to_tsquery('english', 'retrieval & vector') query
WHERE search_vec @@ query
ORDER BY rank DESC;

-- index the tsvector with GIN to make @@ fast
CREATE INDEX idx_docs_fts
ON documents USING GIN (search_vec);

@@ is the match operator. Store the tsvector as a generated column so it stays in sync, and GIN-index it. Stemming means a search for "running" also matches "run".

7 · VACUUM, dead tuples & partitioning

Because of MVCC (from the advanced lesson), an UPDATE or DELETE doesn't remove the old row — it marks it as a dead tuple, a version no live transaction can see. Left alone, dead tuples accumulate as bloat: the table grows on disk, scans read more pages, performance sags.

VACUUM reclaims dead tuples so their space can be reused; ANALYZE refreshes the statistics the planner relies on. Autovacuum runs both automatically in the background — but on a hot, churn-heavy table it can fall behind. Signs to know:

-- check dead tuples and last (auto)vacuum per table
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

• A high n_dead_tup relative to n_live_tup means bloat — tune autovacuum to run more aggressively on that table (autovacuum_vacuum_scale_factor).
• VACUUM FULL rewrites the table to fully reclaim disk, but takes an exclusive lock — schedule it, don't run it casually on a live table.
• After a big bulk load, run ANALYZE manually so the planner has fresh stats immediately.

Partitioning for very large tables

When a table reaches tens or hundreds of millions of rows, partitioning splits it into smaller physical child tables under one logical parent — commonly by date range. Queries that filter on the partition key let Postgres skip whole partitions (partition pruning), and you can drop old data by dropping a partition instead of a slow mass DELETE.

CREATE TABLE events (id bigint, created_at timestamptz, payload jsonb)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

It's a tool for genuinely large or time-series tables — don't reach for it at 100,000 rows; a good index is all you need there.

8 · Locking basics & keyset pagination

Two practical performance topics that bite real apps.

Locking the minimum you must know

Thanks to MVCC, reads never block writes and vice versa. But two transactions updating the same row serialise — the second waits for the first to commit. When you need to read a row and then update it safely under concurrency, lock it explicitly:

BEGIN;
SELECT credits FROM accounts WHERE id = 7 FOR UPDATE;  -- row-lock
-- decide, then …
UPDATE accounts SET credits = credits - 1 WHERE id = 7;
COMMIT;

FOR UPDATE locks the selected rows until commit so no one else can change them mid-decision. Remember the deadlock cure from the advanced lesson: consistent lock ordering and short transactions.

Keyset pagination why OFFSET gets slow

The obvious "page 500" query — LIMIT 20 OFFSET 10000 — forces Postgres to read and discard the first 10,000 rows before returning 20. Cost grows linearly with the offset, so deep pages crawl. The fix is keyset (a.k.a. cursor or seek) pagination: instead of an offset, remember the last row you saw and ask for rows after it.

-- SLOW: OFFSET reads then throws away 10,000 rows
SELECT id, title, created_at FROM documents
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

-- FAST: keyset — seek straight to "after the last row I saw"
SELECT id, title, created_at FROM documents
WHERE (created_at, id) < ('2026-06-01 09:00+04', 8421)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The keyset query uses the index on (created_at DESC, id DESC) to jump directly to the start of the page — same speed for page 1 or page 50,000. The trade-off: you can only go next/previous, not jump to an arbitrary page number. For infinite-scroll and "load more" feeds (DocChat's chat history, a document list), keyset is the right call. Include a unique tie-breaker (id) so rows with equal timestamps page deterministically.

9 · Build: make DocChat retrieval fast

The retrieval query: given a question's embedding, find the 5 nearest chunks that belong to PDF-sourced documents.

-- before: no vector index, no metadata index
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.content,
       c.embedding <=> '[0.01, -0.02, …]' AS distance
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.metadata @> '{"source":"pdf"}'
ORDER BY distance
LIMIT 5;

The "before" plan is grim: a Seq Scan on chunks computing the distance for every chunk, a Seq Scan on documents for the metadata filter, then a Sort over all of it — actual time in the hundreds of milliseconds and climbing with the table. Add the two indexes:

-- HNSW for approximate nearest-neighbour on the embedding
CREATE INDEX idx_chunks_embedding
ON chunks USING hnsw (embedding vector_cosine_ops);

-- GIN for the JSONB metadata containment filter
CREATE INDEX idx_docs_metadata
ON documents USING GIN (metadata);

ANALYZE chunks;
ANALYZE documents;

Re-run the EXPLAIN ANALYZE. Now the plan shows an Index Scan using idx_chunks_embedding (HNSW walking the graph to the 5 nearest, no full sort) and a Bitmap Index Scan on the metadata GIN index — actual time drops from hundreds of ms to single digits. You can trade recall for speed with SET hnsw.ef_search = 100; (higher = more accurate, slightly slower). That before/after pair — plan and timing — is exactly what you show an interviewer.

Now the application side. The route that lists documents with their chunk counts had an N+1; fix it in SQLAlchemy 2.0:

app/retrieval.py

from sqlalchemy import select
from sqlalchemy.orm import selectinload

def list_documents_with_chunks(session):
    # one-to-many → selectinload: 2 queries total, no N+1, no fan-out
    stmt = (
        select(Document)
        .options(selectinload(Document.chunks))
        .order_by(Document.created_at.desc())
    )
    return session.scalars(stmt).all()

With echo=True you'll see exactly two queries: one for documents, one … WHERE document_id IN (…) for all their chunks — no matter how many documents. You've made the database fast and the ORM honest. That's the whole job.

10 · Check yourself

Answer from memory — retrieval is what moves this from "I read it" to "I know it".