Databases — Postgres from the inside out

01The relational model in 60 seconds

Data as relations (tables) of tuples (rows) with typed attributes (columns). Three ideas made it win:

02SQL joins — with worked examples

Two tables. Note Chen has no orders, and order 13 is a guest order with no user:

 users                     orders
 id │ name                 id │ user_id │ total
 ───┼──────                ───┼─────────┼──────
  1 │ Asha                 10 │    1    │ 500
  2 │ Ben                  11 │    1    │ 250
  3 │ Chen                 12 │    2    │  90
                           13 │  NULL   │  40   (guest order)

-- INNER JOIN: only matching pairs (Chen and the guest order vanish)
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id;
-- Asha 500 · Asha 250 · Ben 90

-- LEFT JOIN: all left rows; NULLs where no match (Chen kept)
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;
-- Asha 500 · Asha 250 · Ben 90 · Chen NULL

-- Classic idiom — "users with no orders": LEFT JOIN + IS NULL (anti-join)
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;                            -- → Chen

The LEFT-JOIN-in-WHERE trap

A filter on the right table in the WHERE clause turns a LEFT JOIN back into an INNER — NULLs fail every comparison. Put right-table filters in the ON clause:

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';
-- Chen's row: o.status is NULL,
-- NULL = 'paid' is not true →
-- Chen dropped. LEFT is now INNER.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'paid';
-- Chen survives with NULL total:
-- the filter shapes the match,
-- not the result set.

How the engine executes joins

The planner picks per statistics — reading EXPLAIN ANALYZE and saying which appeared and why is a standout skill:

03Indexes — deeply

Without an index, WHERE email = ? is a sequential scan: read every row. O(N) with hefty constants (disk pages). An index is a redundant, ordered structure that trades write cost + space for read speed.

B-tree structure — the default, in Postgres and nearly everyone

A B-tree (B+ tree in practice) is a short, extremely wide sorted tree of fixed-size pages (8 KB in Postgres). Follow the highlighted path for a lookup of noor:

• Leaves are a sorted linked list → ranges (BETWEEN, >, ORDER BY x LIMIT k, prefix LIKE 'abc%') walk to the start then scan sideways. This is why B-trees beat hash indexes for anything but pure equality.
• Self-balancing: pages split on overflow, height grows from the root — always balanced.
• Leaf entries point to the table row (in Postgres, a heap tuple via its ctid) → after the index finds candidates, fetching other columns requires a heap fetch per row… unless the index is covering (below).

Composite indexes and the leftmost-prefix rule

CREATE INDEX ON orders (user_id, created_at) sorts by user_id, then created_at within each user_id:

 (u1,jan) (u1,feb) (u1,mar) (u2,jan) (u2,apr) (u3,feb) ...
  └────── user 1 ──────┘

• Serves: WHERE user_id = ? · WHERE user_id = ? AND created_at > ? · WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 — the index provides the order, no sort step. That last one is the timeline-query pattern.
• Does not efficiently serve WHERE created_at > ? alone — you would have to jump into every user's section. The leftmost prefix must be constrained.

Covering indexes (index-only scans)

If every column the query needs is in the index, skip the heap entirely:

CREATE INDEX ON orders (user_id, created_at) INCLUDE (total);
-- SELECT total FROM orders WHERE user_id=? ORDER BY created_at DESC LIMIT 20
-- → Index Only Scan: answers straight from index leaves

When indexes hurt

• Every write pays. INSERT/UPDATE/DELETE must maintain every index on the table — 6 indexes ≈ 7× write amplification (plus WAL). Heavy-ingest tables want minimal indexes.
• Low selectivity is useless. An index on status with 3 values returning 30% of the table loses to a seq scan (a random heap I/O per match costs more than streaming the table). The planner knows this from statistics and will ignore the index — correctly.
• Unanchored patterns and functions: LIKE '%foo' cannot use a B-tree (that is what trigram/GIN or OpenSearch is for); WHERE lower(email) = ? needs an expression index on lower(email).
• Space + memory: indexes compete with data for cache; bloat needs reindexing.
• Postgres-specific: updating an indexed column defeats the HOT (heap-only-tuple) update optimization → more index churn.

Toolbox beyond B-tree

04Transactions and ACID — via the WAL

A transaction is a group of operations that executes as one all-or-nothing unit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- both visible atomically, or neither

05Isolation levels and their anomalies

The dial trades correctness for concurrency. The anomalies first:

Level × anomaly grid

* Postgres has no real Read Uncommitted — it behaves as Read Committed. † The SQL standard permits phantoms at RR; Postgres's snapshot implementation prevents them.

• Read Committed (what your app almost certainly runs): each statement sees a fresh snapshot of committed data. Fine for most CRUD; read-modify-write across statements can still lose updates → fix with atomic UPDATE ... SET x = x + 1, SELECT ... FOR UPDATE, or optimistic version columns (UPDATE ... WHERE id=? AND version=?, check rowcount).
• Repeatable Read: one snapshot for the whole transaction — consistent reports/exports. Concurrent-write conflicts raise serialization errors → retry loop required.
• Serializable: as if transactions ran one at a time. Postgres uses SSI (serializable snapshot isolation — optimistic, tracks read/write dependencies, aborts cycles) rather than pessimistic range locks. Costs throughput + mandatory retry logic; reach for it when invariants span multiple rows (bookings, inventory, on-call).

06Locking vs MVCC — how Postgres actually does it

Two strategies for isolation:

readers lock rows they read;
writers block readers and
vice versa. Safe — but read/
write workloads serialize.

writers make NEW VERSIONS
instead of overwriting; each
snapshot decides per version
"committed before me?" — reads
take no row locks at all.

Every row version carries xmin/xmax (creator/deleter transaction ids):

 row id=7:  [v1  xmin=100 xmax=205]──►[v2  xmin=205 xmax=∅]
             ▲ old txns' snapshots       ▲ new txns see this
             still see v1 (consistent read, no lock!)

The costs — and the vocabulary that proves you run Postgres:

• Dead tuples & VACUUM: old versions accumulate (an UPDATE is insert-new + mark-old); autovacuum reclaims them. Update-heavy tables bloat if vacuum can't keep up; long-running transactions pin old snapshots and block cleanup — the "one stuck idle in transaction connection bloats the whole DB" incident.
• Every UPDATE also touches indexes (unless HOT applies), and transaction-id wraparound requires vacuum too.
• Explicit locks still exist when you want pessimism: SELECT ... FOR UPDATE (the queue-worker "claim a job" pattern, especially with SKIP LOCKED), advisory locks (app-level distributed mutex), DDL table locks (why ALTER TABLE on a hot table needs care and CREATE INDEX CONCURRENTLY exists).

07The N+1 query problem

The ORM classic: fetch N parents, then lazily fetch children per parent. 101 round trips at ~1 ms each is 100+ ms of pure network chatter — invisible locally (fast loopback, small data), brutal in prod.

users = session.query(User)
        .limit(100).all()   # 1 query
for u in users:
    print(u.orders)         # 100 more
                            # queries!

users = session.query(User)
  .options(selectinload(User.orders))
  .limit(100).all()
# query 1: the users
# query 2: WHERE user_id IN (...)
# joinedload → a single JOIN

Detection: query logs / APM showing bursts of identical queries with different ids; SQLAlchemy echo=True in dev. Fixes:

1. Eager load / JOIN: selectinload(User.orders) (1 query for users + 1 WHERE user_id IN (...)) or joinedload (single JOIN). 101 → 2.
2. Write the join/aggregate in SQL and let the DB do it — it is better at it.
3. Batch pattern: collect ids, one IN query, stitch in memory — the same idea as GraphQL DataLoader.

08Connection pooling

Postgres connections are expensive: each is a forked process with its own memory; TCP + TLS + auth handshake to create; even idle ones consume server resources. The practical ceiling is hundreds, not tens of thousands — while your K8s deployment happily scales to dozens of pods.

Pool = keep K warm connections, lease them per query/transaction:

 40 uvicorn workers × 100s of in-flight requests
        │  acquire (or wait — the pool is a semaphore)
        ▼
 [ app pool: size 10/pod ] ──► optionally PgBouncer ──► Postgres
                               (shared cluster-wide pool,    max_connections
                                transaction-mode pooling)

• App-side pools (asyncpg / SQLAlchemy pool_size + max_overflow) cap per-pod usage; a cluster-wide pooler (PgBouncer, transaction mode) is how many pods share few DB connections.
• Sizing is counterintuitive: throughput usually peaks near (cores × 2) + spindle-ish on the DB — tens, not thousands. Oversized pools just create lock contention and context switching at the DB.
• Failure modes you can narrate: pool exhaustion under a slow-query pileup (requests queue at acquire, p99 explodes, timeouts cascade); leaked connections from un-returned sessions; transaction-mode pooling breaking session state (prepared statements, SET, advisory locks).

09NoSQL taxonomy — and when each fits

Polyglot persistence = your actual architecture: Postgres (truth) + Redis (speed) + OpenSearch (search) + Kafka (movement). The cost of every derived store is keeping it in sync — dual writes drift; CDC (Debezium → Kafka) or a transactional outbox are the grown-up answers.

10ORMs — tradeoffs

· productivity + type safety on CRUD
· migrations tooling (Alembic)
· parameterization by default
  (SQL-injection resistant)
· unit-of-work / session batching
· portability (least important
  in practice)

· hides the query — N+1s, accidental
  cartesian joins, SELECT * of wide
  rows, invisible until you read
  the emitted SQL
· leaky past medium complexity
  (window fns, CTEs, ON CONFLICT)
· impedance mismatch: objects are
  graphs, tables are sets; lazy
  loading is the awkward bridge
· session/identity-map state
  ("detached instance") is its
  own bug category

11Interview questions you should be able to answer