Engineering Atlas › System Design Interviews

PostgreSQL for system design interviews

15 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Process model

The postmaster listens on port 5432 and forks one backend process per client connection. Shared state lives in shared memory: the buffer pool (shared_buffers), WAL buffers, the lock table and the commit status cache. Background processes do the housekeeping: the checkpointer, background writer, WAL writer, autovacuum launcher and its workers, and one walsender per replica.

Tables, indexes, and MVCC

A table is a heap: an unordered file of 8 KB pages holding row versions (tuples). Indexes, B-tree by default, map keys to a tuple ID, the (block, offset) pair called the ctid. Every tuple records xmin, the transaction that created it, and xmax, the transaction that deleted or replaced it. A query takes a snapshot and shows only tuples whose creator committed before the snapshot and whose deleter did not. An UPDATE is therefore an insert of a new version plus setting xmax on the old one.

Vacuum

Old versions stay in the page after every snapshot that could see them is gone. VACUUM scans for these dead tuples, removes their index entries, frees the space for reuse and updates two maps: the free space map and the visibility map, which marks pages where every tuple is visible to everyone. Autovacuum runs it per table when dead tuples exceed autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the table's row count. PostgreSQL 18 caps that figure with autovacuum_vacuum_max_threshold (100 million dead tuples by default), so a 2-billion-row table no longer waits for 400 million dead rows before vacuum starts (Postgres docs). Version 18 also lets ordinary vacuums freeze some all-visible pages early, so the later anti-wraparound pass has less to do (PostgreSQL 18 release notes).

Replication and high availability

A standby connects to the primary, and a walsender streams WAL records that the standby's startup process replays, page by page. Standbys can serve read-only queries (hot standby). Replication is asynchronous by default; synchronous_standby_names makes commits wait for chosen standbys. Postgres has no built-in failover manager; Patroni with etcd, or a managed service, handles leader election and promotion.

Down the stack

Layer 1The contract

A committed transaction survives a crash once its commit record is flushed to WAL, with synchronous_commit = on (the default). Isolation defaults to Read Committed, where each statement sees a fresh snapshot. Repeatable Read gives snapshot isolation for the whole transaction and aborts with a serialization error if you try to update a row changed by a concurrent committed transaction. Serializable uses serializable snapshot isolation (SSI), which tracks read-write dependencies and aborts one transaction in a dangerous cycle, so clients must retry (Postgres docs). Repeatable Read still allows write skew, and even SSI has shipped bugs: Jepsen found a conflict-detection error present since 9.5 that let Serializable transactions form cycles, fixed in the August 2020 minor releases (Jepsen). On an asynchronous standby, a read can be arbitrarily stale and a failover can lose recently committed transactions; with synchronous replication and remote_apply, a standby read after commit sees the write. The level can be set per transaction, so only the writes that need it pay the extra round trip (Postgres docs).

Layer 2The mechanism

Page layout. Each 8 KB page has a 24-byte header, an array of 4-byte line pointers growing from the front, and tuples growing from the back. A tuple header is 23 bytes plus padding and holds t_xmin, t_xmax, t_cid, t_ctid (pointer to the newer version) and infomask bits, including hint bits that cache "creator committed" so later readers skip the commit log lookup. Values over about 2 KB are compressed or moved out of line to a TOAST table (Postgres docs). The header's pd_checksum field is now filled in by default: since PostgreSQL 18, initdb enables data checksums unless you pass --no-data-checksums, and pg_upgrade needs both clusters to match.

Snapshots. A snapshot holds xmin (oldest transaction still running), xmax (first unassigned ID) and the list of in-progress IDs. A tuple is visible if its xmin committed and is not in the list, and its xmax is empty, aborted, or still invisible. Commit status lives in pg_xact as two bits per transaction.

HOT updates. Because indexes point at physical tuple locations, a new version normally needs a new entry in every index. If no indexed column changed and the new version fits on the same page, Postgres makes a heap-only tuple (HOT): the old line pointer redirects within the page and no index is touched. Columns covered only by a BRIN index do not count, because BRIN summarises ranges instead of pointing at tuples (Postgres docs). Lowering fillfactor to 80 or 90 leaves room for this, and pg_stat_all_tables shows the HOT ratio.

Freezing. Transaction IDs are 32 bits and compared modulo 2^32, so about 2 billion transactions after a tuple was written its xmin would appear to be in the future. Vacuum freezes old tuples to mark them visible to all. When a table's oldest unfrozen ID reaches autovacuum_freeze_max_age (200 million), an anti-wraparound vacuum runs even if autovacuum is disabled. If that still loses the race, the server warns at 40 million IDs from the limit and refuses new write transactions with fewer than 3 million left (Postgres docs).

WAL and checkpoints. Every change is logged to 16 MB segment files in pg_wal before the dirty page may be written. A checkpoint flushes all dirty buffers and records a redo point; by default it runs every checkpoint_timeout (5 minutes) or when WAL reaches max_wal_size (1 GB). The first change to a page after a checkpoint logs the full page image, protecting against torn 8 KB writes on 4 KB storage, which is why WAL volume spikes right after each checkpoint.

Replication slots and logical decoding. A slot records how far a consumer has confirmed, and the primary keeps WAL until then. With wal_level = logical, the pgoutput plugin decodes WAL into inserts, updates and deletes per publication, which powers logical subscribers, Debezium CDC and near-zero-downtime major version upgrades.

Layer 3The machine

A backend is a forked process with its own memory for catalog caches, sort and hash space (work_mem, 4 MB per operation by default, and twice that for hashes through hash_mem_multiplier) and query plans; idle ones commonly use several MB, active ones far more. Thousands of connections mean thousands of processes, context switches, and contention on the shared snapshot and lock structures, which is why max_connections defaults to 100 and PgBouncer in transaction mode multiplexes thousands of client connections onto a few dozen server processes. Transaction pooling used to break named prepared statements; since PgBouncer 1.21 (October 2023) it tracks and re-prepares them on whichever server connection a client gets, up to max_prepared_statements (200 by default) per connection (PgBouncer changelog, config).

shared_buffers defaults to 128 MB; the manual's starting point is 25 percent of RAM, and it warns that more than 40 percent rarely helps, because Postgres reads through the OS page cache and relies on it for the rest (Postgres docs). That means double buffering, and it means a cold page read is a pread that the kernel may satisfy from memory in microseconds or from an SSD in about 100 us. PostgreSQL 18 added asynchronous I/O for sequential scans, bitmap heap scans and vacuum: io_method = worker (the default) hands reads to background I/O workers, io_uring submits them straight to the kernel on Linux, and sync keeps the old one-read-at-a-time behaviour. The project reports up to 3x faster reads from storage in some scans (PostgreSQL 18 announcement). Commits call fdatasync on the WAL; group commit lets one flush cover many transactions, so a cloud volume with 1 ms fsync latency can still commit tens of thousands of small transactions per second when many sessions commit concurrently.

Why it's built this way

  1. Readers must not block writers, so each change creates a new version instead of locking the old one.
  2. Keeping versions in the heap makes rollback free (just mark the transaction aborted) but leaves garbage, so vacuum must exist.
  3. Indexes point at physical locations so lookups take one hop, but that makes every non-HOT update write to every index.
  4. Random page writes are slow and can tear, so changes go first to a sequential WAL with full-page images, and pages are flushed lazily at checkpoints.
  5. The WAL already describes every change in order, so shipping it is the cheapest possible replication protocol.

Diagram

flowchart LR
    C["Clients"] --> PB["PgBouncer"]
    PB --> BE["Backend process per connection"]
    BE --> SB["shared_buffers (8 KB pages)"]
    BE --> WB["WAL buffers"]
    WB --> WAL["pg_wal segments (fsync on commit)"]
    SB --> CK["Checkpointer writes heap files"]
    AV["Autovacuum workers"] --> SB
    WAL --> WS["walsender"]
    WS --> ST["Standby: replay, hot reads"]
    WAL --> LD["Logical decoding (pgoutput)"]
    LD --> CDC["Subscribers, Debezium"]

Worked example

A ride app keeps a driver_location table of 2 million rows (about 150 bytes each, 300 MB) updated 20,000 times per second, with an index on driver_id and one on geohash.

Every update changes geohash, so HOT cannot apply: each update writes a new heap tuple and two index entries, about 60,000 page modifications per second. Autovacuum triggers at 50 + 0.2 x 2,000,000 = 400,050 dead tuples, which accrue every 20 seconds, so vacuum on this table never stops. Now an analyst opens a 30-minute report on a replica with hot_standby_feedback = on. The primary cannot remove anything newer than that query's snapshot: 36 million dead tuples, roughly 6 GB of bloat on a 300 MB table, and indexes grow similarly.

Fixes, cheapest first: drop the geohash index and keep the spatial index in Redis or a separate table so updates become HOT with fillfactor = 70; set per-table autovacuum_vacuum_scale_factor = 0.01; cap report queries with statement_timeout; and send analytics to a logical replica or a warehouse instead of a hot standby.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
PostgreSQLRich SQL, extensions, strict correctness, JSONB, PostGISProcess per connection, vacuum tuning, write amplification on updatesDefault system of record, complex queries, geo or vector needs
MySQL (InnoDB)Clustered primary key, cheap connections, mature sharding (Vitess)Weaker SQL features historically, purge lag from long transactionsHuge PK-lookup OLTP, existing MySQL estate
Postgres plus CitusDistributed tables, keeps SQLCross-shard transactions cost more, operational weightMulti-tenant SaaS outgrowing one node
Aurora PostgreSQLStorage-level replication, fast failover, replicas share storageCost, vendor lock-in, I/O billingManaged HA on AWS
DynamoDB or CassandraHorizontal writes, predictable latencyNo joins or ad hoc queriesKnown access patterns at very high scale

Pitfalls in production

Leadership lens

Check yourself

  1. Why does updating one column of a row with five indexes cost more in Postgres than in MySQL?

    Show answer

    Postgres writes a new tuple at a new physical location, so unless the update is HOT every index gets a new entry; InnoDB updates the clustered row in place and only touches secondary indexes whose columns changed.

  2. A replica runs a 2-hour query and the primary's table doubles in size. Explain.

    Show answer

    With hot_standby_feedback, the replica reports its snapshot's xmin, so vacuum on the primary cannot remove dead tuples newer than it and bloat accumulates.

  3. Why can a rolled-back transaction in Postgres finish instantly, even after writing a million rows?

    Show answer

    Rollback only marks the transaction aborted in pg_xact; its tuples become invisible and vacuum removes them later.

  4. Why does WAL volume jump right after a checkpoint?

    Show answer

    The first modification of each page after a checkpoint writes a full 8 KB page image to protect against torn writes.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →