Technology deep dives · Chapter 28

PostgreSQL

How PostgreSQL works: MVCC and vacuum, B-tree, GIN and BRIN indexes, isolation levels and their anomalies, replication, pooling, partitioning, JSONB and scaling.

AdvancedInterview priority: High40 min
On this page
  1. The problem: the default database, and the ways it surprises you
  2. MVCC: every update writes a new row version
  3. Indexes: pick the type for the query
  4. Isolation levels and their anomalies
  5. Durability and replication
  6. Connections and pooling
  7. Partitioning, JSONB and the rest
  8. Scaling reads and writes
  9. PostgreSQL, MySQL and the alternatives
  10. In the interview
  11. Quiz

The problem: the default database, and the ways it surprises you

Most systems you design in an interview start with "a relational database", and most of the time that means PostgreSQL. It gives you transactions, joins, constraints, a rich query language and decades of tooling. A single well-tuned server handles thousands to low tens of thousands of simple writes a second and many more reads, which is more than most products ever need.

The trouble comes from not knowing how it works inside. A table that is updated constantly grows to ten times its real size. A report that runs for three hours slows the whole database. Two concurrent transactions both pass a "balance is enough" check and overdraw an account. 2,000 connections exhaust the server's memory. Each of these follows directly from PostgreSQL's design, and each has a standard fix.

This lesson covers the parts that matter for design decisions: how PostgreSQL keeps versions of rows, which index to use, what each isolation level does and does not prevent, and how to scale reads and writes. The basics of indexes, replication and SQL vs NoSQL have their own lessons.

MVCC: every update writes a new row version

PostgreSQL uses MVCC (multi-version concurrency control): instead of locking a row while someone reads it, it keeps several versions and lets each transaction see the version that was current when its snapshot (the set of transactions it treats as committed) was taken. Each row version, called a tuple, records the transaction that created it (xmin) and the one that deleted or replaced it (xmax).

An update writes a new version; each snapshot sees the version that was current for it

The benefit is that plain reads never wait for writes and writes never wait for reads. Writers still lock the rows they change, so two transactions updating the same row take turns.

The cost is garbage. Every UPDATE and DELETE leaves a dead version behind.

VACUUM reclaims dead versions, unless an old transaction still needs them

VACUUM (run automatically by the autovacuum daemon) finds dead tuples and marks their space reusable; it does not usually shrink the file. Two refinements reduce the work:

  • HOT updates (heap-only tuples): if no indexed column changed and the page has free space, the new version goes on the same page and indexes need no new entry. Leaving free space (fillfactor 80–90) on update-heavy tables helps.
  • Freezing: transaction IDs are 32-bit, so they wrap around after about 2 billion transactions. Vacuum "freezes" old tuples so they stay visible forever. If freezing falls far behind, PostgreSQL eventually refuses new writes to protect the data, which is one of the few ways a healthy-looking database stops.

Indexes: pick the type for the query

IndexHow it worksGood forExample
B-tree (default)Balanced sorted treeEquality, ranges, ORDER BY, prefixes with LIKE 'abc%'CREATE INDEX ON orders (customer_id, created_at)
GINInverted index: each element → rows that contain itJSONB containment, arrays, full-text search, trigram LIKE '%abc%'CREATE INDEX ON events USING gin (payload jsonb_path_ops)
GiST / SP-GiSTGeneralised search treesGeometry (PostGIS), ranges, nearest-neighbour, exclusion constraints"no two bookings overlap for one room"
BRINStores min/max per block range (128 pages by default)Huge tables whose rows arrive in order, such as time seriesA 2 TB log table by created_at, index of a few MB
HashHash tableEquality only; rarely better than B-tree

Index features that come up in design discussions:

  • Composite indexes are used left to right: (customer_id, created_at) serves "orders for customer X, newest first" but not "orders created today" alone. PostgreSQL 18 added skip scan, which can use a later column when the leading column has few distinct values, but do not rely on it for your main queries.
  • Partial indexes index only some rows: CREATE INDEX ON jobs (run_at) WHERE status = 'pending' stays tiny however large the table grows.
  • Covering indexes (INCLUDE (total)) let a query be answered from the index alone (an index-only scan).
  • CREATE INDEX CONCURRENTLY builds an index without blocking writes; a plain CREATE INDEX on a busy table blocks writes for the whole build.

Every index makes writes slower (each insert updates every index) and prevents HOT updates on its columns, so index the queries you have, not every column.

Isolation levels and their anomalies

The isolation level decides which effects of concurrent transactions you can see. PostgreSQL implements three (asking for Read Uncommitted gives Read Committed).

LevelEach statement / transaction seesPreventsStill allows
Read Committed (default)A fresh snapshot per statementDirty reads (seeing uncommitted data)Non-repeatable reads, phantoms, lost updates in read-modify-write, write skew
Repeatable ReadOne snapshot for the whole transaction (snapshot isolation)The above plus non-repeatable reads and phantoms; a concurrent update to the same row raises a serialization errorWrite skew
SerializableSnapshot plus conflict tracking (SSI)All anomalies: results equal some serial orderNothing, but transactions can fail with error 40001 and must be retried

Definitions you need:

  • Lost update: two transactions read a value, both compute a new one in the application, both write; one change vanishes.
  • Write skew: two transactions read the same rows, each decides based on what it read, and each writes different rows. Classic case: two doctors on call each check "is someone else on call?", both see "yes", and both go off call.
-- Lost update at Read Committed: do the arithmetic in the database...
UPDATE accounts SET balance = balance - 30 WHERE id = 7 AND balance >= 30;

-- ...or lock the row you read before deciding.
BEGIN;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;
-- application checks the balance, then:
UPDATE accounts SET balance = balance - 30 WHERE id = 7;
COMMIT;

Durability and replication

Every change is written to the write-ahead log (WAL) before the data files. At commit, PostgreSQL flushes the WAL to disk (with the default synchronous_commit = on), so a committed transaction survives a crash. The same log feeds replication.

The WAL makes commits durable and is streamed to replicas

KindWhat is copiedUse forLimits
Streaming (physical)The WAL byte for byteRead replicas, failover standbysWhole cluster only; same major version; replicas are read-only
LogicalRow changes per table (publications and subscriptions)Upgrades across major versions, copying some tables, CDC into Kafka or a search indexSchema changes and sequences are not replicated automatically

PostgreSQL does not fail over by itself. Tools such as Patroni (with etcd or a similar store for leader election; see ZooKeeper and etcd) or a managed service (RDS, Cloud SQL, Aurora) detect a dead primary and promote a replica, typically in 10–60 seconds. With asynchronous replicas, failover can lose the last few committed transactions; a synchronous standby closes that gap at the cost of one extra round trip on every commit.

Connections and pooling

PostgreSQL starts one operating-system process per connection. Each uses several MB of memory, and thousands of them cost a lot in memory and scheduling even when idle. The default max_connections is 100; a few hundred is common; thousands is a warning sign.

A pooler lets thousands of clients share a few dozen server connections

A useful rule of thumb is that the number of active connections should be near a small multiple of the CPU cores. PgBouncer in transaction mode, or a pooler built into your platform, is standard in front of any busy PostgreSQL. In transaction mode, features that rely on the session (session-level SET, temporary tables, advisory locks held across transactions, LISTEN) do not work reliably; protocol-level prepared statements are supported by PgBouncer 1.21 and later.

Partitioning, JSONB and the rest

Declarative partitioning

A partitioned table is split into child tables by range, list or hash of a column, all on the same server:

CREATE TABLE events (id bigint, created_at timestamptz NOT NULL, payload jsonb)
    PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Queries that filter on created_at skip irrelevant partitions (partition pruning), vacuum and index builds work on smaller pieces, and deleting a month of old data becomes DETACH PARTITION and DROP TABLE, which is instant and leaves no dead tuples. Limits: primary keys and unique constraints must include the partition key, and partitioning does not spread load across machines; that is sharding.

JSONB

jsonb stores JSON in a parsed binary form. You can query inside it (payload->>'country' = 'IN', payload @> '{"plan":"pro"}') and index it with GIN, or with a B-tree on an expression such as ((payload->>'country')). It is ideal for attributes that vary per row (product specs, event payloads). It is a poor replacement for columns you filter or join on constantly: there are no per-key statistics, so the planner guesses, and every update rewrites the whole value.

Scaling reads and writes

NeedFirst stepsThen
More readsIndexes, query fixes, a cacheRead replicas (watch replication lag: read-your-writes from the primary)
More writesBatching, fewer indexes, bigger machine, partitioning for maintenanceSharding: in the application, or with Citus; or move the hottest data to a store built for it
More dataPartitioning, archiving cold data to object storageSharding
More connectionsPgBouncerFewer, smarter app-side pools

Worked example. An orders service peaks at 3,000 writes/s (each a transaction of ~3 statements) and 40,000 reads/s.

  • Writes: 3,000 transactions/s is comfortable for one modern primary with SSDs (WAL flushes are grouped across concurrent commits).
  • Reads: a cache absorbing 80% leaves 8,000 reads/s; at ~5,000–10,000 indexed reads/s per replica for this workload, two replicas plus the primary cover it with room for one to fail.
  • Data: 3,000 orders/s is ~260 M rows a day at peak rates, so a few billion rows a month: partition by month and archive after a year. If writes grow 10×, shard by customer_id.

See scaling reads and scaling writes for the general patterns.

PostgreSQL, MySQL and the alternatives

PostgreSQLMySQL (InnoDB)Cassandra / DynamoDB
StorageHeap table plus separate indexes; MVCC versions in the tableRows stored in the primary key's B-tree (clustered); old versions in an undo log, purged automaticallyLSM tree, partitioned by key
Good atRich SQL, extensions (PostGIS, pgvector), JSONB, strict correctnessSimple high-volume OLTP, mature sharding (Vitess), primary-key range scansWrite volume and scale-out across machines
Weak atVacuum tuning, process per connectionFewer index types and SQL featuresAd-hoc queries, transactions
Default isolationRead CommittedRepeatable Read (with gap locks)Per-request consistency levels

The differences matter less than knowing one well. In an interview, "PostgreSQL" or "MySQL" are both fine answers for the system of record; say why you need a relational database (transactions, constraints, queries), not which brand.

When not to use PostgreSQL as the main store: write volumes far beyond one primary with no natural shard key (consider Cassandra); full-text relevance search at scale (Elasticsearch); large files (object storage); analytics over billions of rows (a column-store warehouse); and sub-millisecond hot counters (Redis).

In the interview

Your turn: a booking table

A meeting-room app must never let two bookings for the same room overlap in time. 200 bookings a minute, mostly for the coming week; the main query is "this room's bookings between two times". Design the table, the index and how you stop overlaps under concurrency.

Compare with our answer

Store bookings(id, room_id, during tstzrange, user_id). Add an exclusion constraint: EXCLUDE USING gist (room_id WITH =, during WITH &&) (needs the btree_gist extension). The database then rejects any insert that overlaps an existing booking for the same room, even when two transactions race, at any isolation level. The same GiST index serves "bookings for room X overlapping this range". An application-side check ("SELECT overlaps, then INSERT") is not safe at Read Committed or Repeatable Read, because two transactions can both see no overlap. 200 writes a minute is trivial for one server.

A strong answer mentions: a range type for the time slot · an exclusion constraint (or Serializable with retries, or a row lock per room) · why check-then-insert races · an index that matches the query · that the load is small.

Your turn: the requirement changes

An events table gets 5,000 inserts a second and is now 4 TB. Queries look at the last 7 days; data older than 90 days must be deleted. The team runs DELETE FROM events WHERE created_at < now() - interval '90 days' nightly, and the database grows anyway while queries get slower. What is happening, and what would you change?

Compare with our answer

Each nightly DELETE creates hundreds of millions of dead tuples and heavy WAL; vacuum cannot keep up, so the table and its indexes bloat, and the delete itself competes with inserts. Partition by day or month on created_at, drop whole partitions after 90 days (instant, no dead tuples), and let queries prune to recent partitions. A BRIN index on created_at is tiny and works because rows arrive in time order; keep B-tree indexes only for selective lookups. Consider archiving dropped partitions to object storage first.

A strong answer mentions: dead tuples and vacuum as the cause · partitioning by time · dropping partitions instead of deleting rows · partition pruning for recent queries · BRIN for append-ordered data · archiving if the data must be kept.

PostgreSQL on one page
  • Default system of record: transactions, constraints, joins. One primary: thousands to low tens of thousands of simple writes/s.
  • MVCC: updates write new tuple versions; readers and writers don't block each other; VACUUM reclaims dead tuples. Long-open transactions cause bloat.
  • Indexes: B-tree (default, ranges, order), GIN (JSONB, arrays, full text), GiST (geo, ranges, exclusion), BRIN (huge append-ordered tables). Partial, covering, CONCURRENTLY.
  • Isolation: Read Committed (default, per-statement snapshot) → Repeatable Read (snapshot isolation; write skew possible) → Serializable (SSI; retry on 40001).
  • Fix lost updates with atomic UPDATE … SET x = x - 1, SELECT … FOR UPDATE, or constraints.
  • WAL for durability; streaming replication for replicas and failover (Patroni or managed); logical replication for CDC and upgrades.
  • Process per connection: always pool (PgBouncer, transaction mode).
  • Partition by time for retention and pruning; shard (Citus or the app) for write scale.
  • JSONB with GIN for flexible attributes, not for core columns.
Check what stuck

1. A report query holds a transaction open for six hours on the primary. Meanwhile, a heavily updated sessions table grows from 2 GB to 15 GB. Why?

2. Two transactions at Repeatable Read each check that at least two doctors are on call, see three, and each set a different doctor off call. Both commit. Which anomaly is this?

3. A table of 3 billion log rows arrives in time order and is queried by time range. Which index gives good range filtering at a tiny size?

4. Forty app servers each open a pool of 50 connections to one PostgreSQL server, which runs short of memory. What is the standard fix?

5. A balance update does SELECT balance, subtracts in the application, then UPDATE … SET balance = :new at Read Committed. Under load some withdrawals vanish. What is the simplest correct fix?

6. The team deletes 200 million rows of old events every night, and the table keeps growing. What is the better design?

7. An orders database is asynchronously replicated, and the primary's disk fails. The replica is promoted within 30 seconds. What should the team expect?

Score 60% or more and the lesson is marked complete.

Passing the quiz also marks it complete. Progress is saved in this browser only.