PostgreSQL
How PostgreSQL works: MVCC and vacuum, B-tree, GIN and BRIN indexes, isolation levels and their anomalies, replication, pooling, partitioning, JSONB and scaling.
On this page
- The problem: the default database, and the ways it surprises you
- MVCC: every update writes a new row version
- Indexes: pick the type for the query
- Isolation levels and their anomalies
- Durability and replication
- Connections and pooling
- Partitioning, JSONB and the rest
- Scaling reads and writes
- PostgreSQL, MySQL and the alternatives
- In the interview
- 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 (
fillfactor80–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
| Index | How it works | Good for | Example |
|---|---|---|---|
| B-tree (default) | Balanced sorted tree | Equality, ranges, ORDER BY, prefixes with LIKE 'abc%' | CREATE INDEX ON orders (customer_id, created_at) |
| GIN | Inverted index: each element → rows that contain it | JSONB containment, arrays, full-text search, trigram LIKE '%abc%' | CREATE INDEX ON events USING gin (payload jsonb_path_ops) |
| GiST / SP-GiST | Generalised search trees | Geometry (PostGIS), ranges, nearest-neighbour, exclusion constraints | "no two bookings overlap for one room" |
| BRIN | Stores min/max per block range (128 pages by default) | Huge tables whose rows arrive in order, such as time series | A 2 TB log table by created_at, index of a few MB |
| Hash | Hash table | Equality 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 CONCURRENTLYbuilds an index without blocking writes; a plainCREATE INDEXon 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).
| Level | Each statement / transaction sees | Prevents | Still allows |
|---|---|---|---|
| Read Committed (default) | A fresh snapshot per statement | Dirty reads (seeing uncommitted data) | Non-repeatable reads, phantoms, lost updates in read-modify-write, write skew |
| Repeatable Read | One 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 error | Write skew |
| Serializable | Snapshot plus conflict tracking (SSI) | All anomalies: results equal some serial order | Nothing, 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
| Kind | What is copied | Use for | Limits |
|---|---|---|---|
| Streaming (physical) | The WAL byte for byte | Read replicas, failover standbys | Whole cluster only; same major version; replicas are read-only |
| Logical | Row changes per table (publications and subscriptions) | Upgrades across major versions, copying some tables, CDC into Kafka or a search index | Schema 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
| Need | First steps | Then |
|---|---|---|
| More reads | Indexes, query fixes, a cache | Read replicas (watch replication lag: read-your-writes from the primary) |
| More writes | Batching, fewer indexes, bigger machine, partitioning for maintenance | Sharding: in the application, or with Citus; or move the hottest data to a store built for it |
| More data | Partitioning, archiving cold data to object storage | Sharding |
| More connections | PgBouncer | Fewer, 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
| PostgreSQL | MySQL (InnoDB) | Cassandra / DynamoDB | |
|---|---|---|---|
| Storage | Heap table plus separate indexes; MVCC versions in the table | Rows stored in the primary key's B-tree (clustered); old versions in an undo log, purged automatically | LSM tree, partitioned by key |
| Good at | Rich SQL, extensions (PostGIS, pgvector), JSONB, strict correctness | Simple high-volume OLTP, mature sharding (Vitess), primary-key range scans | Write volume and scale-out across machines |
| Weak at | Vacuum tuning, process per connection | Fewer index types and SQL features | Ad-hoc queries, transactions |
| Default isolation | Read Committed | Repeatable 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
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.
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.
- 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.
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?
MVCC keeps dead tuples while any open snapshot might need them, and the snapshot pins the cleanup horizon for every table. Reads do not lock tables against updates.
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?
Snapshot isolation only detects conflicting writes to the same row; here the rows differ. Serializable, explicit row locks or a constraint prevent it.
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?
BRIN works because physical order matches time order, so each block range covers a narrow window; it is megabytes for terabytes. Hash indexes cannot do ranges, and GIN is for multi-valued data.
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?
Each connection is a process with its own memory, and most are idle. A pooler multiplexes them. Raising the limit makes it worse, and writes still need the primary.
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?
The atomic update reads and writes under the row lock, so concurrent withdrawals queue. A transaction at Read Committed still lets both read the old balance before either writes.
6. The team deletes 200 million rows of old events every night, and the table keeps growing. What is the better design?
Dropping a partition is instant and leaves no dead tuples. VACUUM FULL rewrites the table under an exclusive lock that blocks writes for the whole time.
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?
With asynchronous replication, the primary confirms commits before the replica has the WAL, so the last ones can be lost. A synchronous standby prevents that at the cost of latency.
Score 60% or more and the lesson is marked complete.