Postgres internals: UPDATE never updates, and a janitor keeps it working.

A table is a heap — a file of fixed 8KB pages, each holding row versions (tuples) plus a small item-pointer directory. Rows have no inherent order; a row's physical address (the ctid: page number + slot) is where indexes point. Values too big for a page get sliced into a side table by TOAST — which is why a 5MB JSON blob doesn't wreck the page geometry of its neighbors.

Hold onto the page as the unit of thought: I/O happens in pages, the buffer cache holds pages, and the planner prices queries in page reads. "How many pages will this touch?" is the question behind nearly every performance mystery below.

Concurrent readers and writers are the defining database problem. Locking readers against writers murders throughput; Postgres chose Multi-Version Concurrency Control instead: keep multiple versions of each row, let every transaction see its own consistent snapshot.

All of visibility is one predicate on two integers. A tuple stamped (xmin, xmax) is visible to a snapshot s exactly when it was born in your past and hasn't expired in your past:

Take two snapshots at different times over the same logical row and this predicate hands each a different physical version — that's snapshot isolation, and it needs no locks. It also explains dead tuples exactly: once every live snapshot has moved past a tuple's xmax, nothing can select it again, so it is pure garbage. The runnable model below is this predicate plus a tiny heap; watch an UPDATE serve an old and a new reader at once and leave one dead tuple behind.

The prize: readers never block writers, writers never block readers — a long analytics query and a burst of updates coexist peacefully, each in its own snapshot. The price: expired versions — dead tuples — accumulate in the heap, and every index entry pointing at them lives on too. Someone has to clean up.

VACUUM walks tables reclaiming dead tuples so their space gets reused, and maintains the visibility map (which pages are all-visible — crucial for index-only scans, next section). Autovacuum triggers it per table when dead tuples cross thresholds. When churn outruns it, you get bloat: tables and indexes physically far larger than their live data, wasting cache, slowing scans — the classic silent Postgres degradation. Watch pg_stat_user_tables.n_dead_tup; tune autovacuum more aggressive, not less, on hot tables.

The default index is a B-tree mapping key → ctid. But an index hit isn't the end: the tuple's visibility lives in the heap, so a normal index scan does index then heap — two page trips. The visibility map earns its keep here: if the target page is marked all-visible, Postgres can skip the heap trip entirely — an index-only scan, the fastest read Postgres has. (Well-vacuumed tables make them possible; bloated ones don't.)

MVCC's tax collects here too: every UPDATE creates index entries for the new version in every index — unless nothing indexed changed and the new tuple fits the same page (a HOT update). Fewer indexes and non-indexed churn columns = more HOT = less bloat.

Writing dirty 8KB pages to random disk locations on every commit would be slow and torn-write fragile. Postgres instead appends every change to the write-ahead log — sequential, fsynced before commit returns. Data pages are updated lazily in memory and flushed at checkpoints.

Crash? Replay WAL from the last checkpoint — perfect recovery. Replicas? They're just servers replaying your WAL live (streaming replication), or logically decoding it (logical replication, CDC). Point-in-time recovery? A base backup plus archived WAL replayed to any chosen second. Durability, HA and backups: one log.

SQL says what; the planner picks how: which indexes, which join algorithm (nested loop for tiny inner sides, hash join for big unsorted ones, merge join for pre-sorted inputs), which order. It prices candidate plans with statistics — histograms and distinct-counts per column, refreshed by ANALYZE — and picks the cheapest estimate.

Which is why every slow-query investigation is the same ritual: EXPLAIN ANALYZE, then compare estimated rows vs actual rows. Off by 1000×? The stats are stale or the column correlations exceed what histograms capture — and the planner chose a nested loop for a million rows. Seq scan on a "should be indexed" query? Often correct: past ~5-10% selectivity, reading the whole table sequentially beats a million random heap trips. The planner is rarely wrong given its inputs; fix the inputs.

Postgres forks an OS process per connection — robust isolation, expensive arithmetic: each backend carries megabytes, and creation isn't cheap. A serverless app opening 2,000 lambda connections meets reality fast. Hence PgBouncer (or a built-in pooler): thousands of client connections multiplexed onto a few dozen real backends, in transaction-pooling mode. Treat it as part of Postgres, not an optimization.

Memory in one line each: shared_buffers — Postgres's own page cache (classic advice: ~25% of RAM; the OS page cache handles the rest); work_mem — per sort/hash operation, not per connection, so big values multiply frighteningly under concurrency; maintenance_work_mem — feed VACUUM and index builds generously.