Start practising

SYSTEM DESIGN · TECHNOLOGIES · 1 OF 12

PostgreSQL: The Relational Database to Reach for First

What PostgreSQL is and why it is the safe default for most designs: tables and constraints, the write-ahead log, MVCC row versions and vacuum, B-tree, GIN and BRIN indexes, connection pooling, streaming replication and failover, partitioning and sharding with Citus, where it hurts, the numbers worth knowing and how to justify it in an interview.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
1 / 16

Press play to hear the narration, or step through at your own pace.

Notes

What it is

PostgreSQL is an open-source relational database with full transactions. Your services send SQL to one primary server that takes every write, and copies of it can serve reads. Every major cloud runs it for you. And it has a rich extension system: PostGIS for maps, pgvector for embeddings, Timescale for time series, Citus for sharding.

PostgreSQL in one line
  • open-source relational DB,
  • full ACID transactions
  • one primary takes writes,
  • replicas serve reads
  • managed: RDS, Aurora, Cloud
  • SQL, Azure, Neon, Supabase
  • extensions: PostGIS, pgvector,
  • TimescaleDB, Citus

Data model

The data model is tables of typed rows. You declare a primary key, foreign keys to other tables, and check constraints, and the database refuses data that breaks them. That is the real value: correctness is enforced in one place, not in every service. When part of a record really is free-form, a JSONB column holds it, and you can still index inside it.

Schema example
CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  user_id    bigint REFERENCES users(id),
  status     text NOT NULL,
  total_cents int CHECK (total_cents >= 0),
  attrs      jsonb,
  created_at timestamptz DEFAULT now()
);
CREATE INDEX ON orders (user_id,created_at);

Write path

Here is the write path. Postgres changes the data page in memory, appends a record describing the change to the write-ahead log, and on commit it flushes that log to disk. Only then does it reply. The actual table pages are written later, in the background. If the server crashes, it replays the log from the last checkpoint. Sequential log writes are why commits are fast and safe.

How a write survives a crash
  • 1. change the page in memory
  • (shared_buffers)
  • 2. append a WAL record
  • 3. fsync the WAL on COMMIT
  • 4. reply "committed"
  • dirty pages reach the table
  • files later, at checkpoints
  • crash? replay WAL from the
  • last checkpoint

MVCC

Postgres uses multi-version concurrency control. An update does not overwrite the row. It writes a new version and marks the old one as dead from that transaction onward. A query that started earlier keeps reading the old version, so readers never block writers. The price is dead rows piling up, which a background process called vacuum cleans. Long-running transactions stop vacuum and cause bloat.

MVCC: row versions
UPDATE orders SET status = 'paid'

v1  status=new   xmax=901  (dead)
v2  status=paid  xmin=901  (live)

a reader that started before
txn 901 still sees v1

readers never block writers
VACUUM later frees dead v1

Indexes

Indexes are where most performance comes from. The default B-tree handles equality, ranges and sorting. GIN indexes the inside of JSONB, arrays and full-text documents. GiST handles geometry, which is how PostGIS finds nearby places. BRIN is a tiny index for huge tables ordered by time. Partial and covering indexes let you index only hot rows and answer a query without touching the table. Concepts episode 17 covers why.

Index types
  • B-tree: equality, ranges, sort
  • (the default, most cases)
  • GIN: JSONB keys, arrays,
  • full-text search
  • GiST: geo, ranges (PostGIS)
  • BRIN: huge append-only tables
  • ordered by time, tiny index
  • partial: WHERE status='open'
  • covering: INCLUDE (total)

Connections

Something interviewers like to probe: Postgres starts a separate operating system process for every connection, each using a few megabytes. Two thousand app threads connecting directly will hurt. So you put a pooler such as PgBouncer in front. In transaction mode, thousands of clients share about a hundred real connections, each borrowed only for the length of one transaction.

Why you need a pooler
  • each connection = an OS process
  • a few MB of RAM each
  • 2,000 app threads directly
  • → memory and context switches
  • PgBouncer in transaction mode:
  • thousands of clients share
  • a hundred real connections

Replication

Replication reuses the same log. Replicas receive the write-ahead log as a stream and replay it, so each is an exact copy that can serve reads. By default this is asynchronous: commits are fast, but a replica may lag, and a failover can lose the last few commits. Synchronous mode makes commit wait for a replica, trading latency for zero data loss. Logical replication copies chosen tables, which helps with upgrades and change data capture.

Streaming replication
  • replicas receive the WAL and
  • replay it: an exact copy
  • async (default): fast commit,
  • replica may lag, may lose the
  • last few commits on failover
  • sync: commit waits for a
  • replica, zero loss, slower
  • logical replication: per table

Failover

When the primary dies, plain Postgres will not promote anyone by itself. A manager does that: Patroni uses etcd to elect a replica and promote it, or your cloud provider handles it, typically within a minute or two on RDS and faster on Aurora. Clients find the new primary through DNS or the pooler. Say this explicitly in an interview, because many people assume it is automatic.

Failover
  • core Postgres has no built-in
  • automatic failover
  • Patroni + etcd: elects and
  • promotes a replica
  • RDS Multi-AZ: about 1-2 min
  • Aurora: usually under 30 s
  • clients reconnect via DNS
  • or the pooler

Scaling writes

One primary takes every write, so how far does it go? Further than most people think. First scale up; a large instance handles a lot. Move reads to replicas and a cache. Partition huge tables by month, so dropping old data is instant. When writes truly exceed one machine, shard: Citus spreads tables across nodes by a distribution key, or your application routes each tenant to its own database. Concepts episode 21 covers sharding.

When one primary is not enough
  • 1. bigger box: 64+ cores,
  • TBs of RAM go a long way
  • 2. read replicas + cache
  • 3. partition by time or key
  • (drop old months instantly)
  • 4. shard: Citus, or app-level
  • routing by tenant_id
  • writes scale only by
  • splitting the data

Great at

What it is great at. Anything where correctness matters: payments, orders, inventory, bookings, where a transaction and a constraint prevent a whole class of bugs. Queries you did not plan for, because SQL and joins are flexible. And breadth: with extensions, one Postgres can handle documents, geo search, vector similarity and basic full-text search, so small teams run fewer systems.

Where Postgres shines
  • • money, orders, inventory:
  • transactions + constraints
  • • joins and ad-hoc queries
  • you did not plan for
  • • serializable isolation
  • when you need it
  • • JSONB, geo, vectors, full-
  • text in the same database
  • • huge ecosystem, every cloud

Where it hurts

Where it hurts. There is one writer, so write throughput has a ceiling, and sharding is something you add, not something it does. Tables updated constantly create dead rows, and if vacuum falls behind they bloat. Too many connections hurt. Replicas lag, so a user may not see their own write. Altering a billion-row table needs care. And active-active writes across regions are not what it was built for.

Pain points
  • • one primary: write ceiling
  • • no native auto-sharding
  • • vacuum + bloat on hot,
  • update-heavy tables
  • • process-per-connection
  • • replica lag: stale reads
  • • schema change on a billion
  • rows needs care
  • • multi-region writes: hard

Use it when

So, use it when your data has relationships, you need transactions, you will run reports, or you cannot yet predict the queries. That covers most products. Look elsewhere when you need millions of writes a second, writes accepted in several regions at once, or only simple key lookups at enormous scale, where DynamoDB or Cassandra fit better. See the Which database guide for the full table.

Use it when / avoid it when
  • USE when
  • relationships, transactions,
  • reporting, unknown queries
  • data fits a few TB per node
  • AVOID when
  • millions of writes per second
  • multi-region active writes
  • pure key lookups at huge scale
  • (DynamoDB, Cassandra)

Numbers

Numbers to have ready, assuming a modern server with NVMe disks. Simple indexed reads run from ten thousand to over a hundred thousand a second on one node, and simple writes from roughly five thousand to thirty thousand a second, more with batching. A few terabytes per node is comfortable. Pages are eight kilobytes, the default connection limit is one hundred, and the default isolation level is read committed.

Numbers worth knowing
page size            8 KB
default max_connections  100
simple indexed reads  10k-100k+/s
simple writes         5k-30k+/s
  (one node, NVMe, batched commits)
comfortable size      1-10 TB/node
replica lag (same AZ) ms to seconds
row update = new row version
default isolation     READ COMMITTED

Say it like this

Here is how to say it. Orders need transactions and joins, so PostgreSQL. A primary for writes, two asynchronous replicas for reads, a connection pooler in front, and Patroni or the managed service for failover. Index (user_id, created_at) for the order history page. At ten times the writes, partition by month and then shard by user id. That answer shows you know both the strengths and the ceiling.

In the interview
  • "Orders need transactions and
  • joins, so PostgreSQL.
  • Primary for writes, two async
  • replicas for reads, PgBouncer
  • in front, Patroni for failover.
  • Index (user_id, created_at).
  • At 10x writes: partition by
  • month, then shard by user_id
  • with Citus."

Interview questions

Why is PostgreSQL a good default choice?

It gives full ACID transactions, constraints and flexible SQL queries, handles a few terabytes and tens of thousands of writes per second on one node, and is available as a managed service on every major cloud. Most products never outgrow it, and when they do the scaling path is well known.

What is the write-ahead log and why does it matter?

Every change is first appended to the WAL and the WAL is flushed to disk on commit, before the data pages are written. After a crash the database replays the WAL to recover, and the same stream feeds replicas and change data capture.

How does MVCC work in PostgreSQL?

An update writes a new version of the row and marks the old version dead for later transactions, so a query keeps reading the snapshot it started with. Readers never block writers, and VACUUM later reclaims the dead versions.

Why do you need PgBouncer?

PostgreSQL runs one operating system process per connection, so thousands of direct connections waste memory and CPU. A pooler lets many clients share a small set of server connections, usually borrowing one per transaction.

How does PostgreSQL replication work, and what is the risk of async replicas?

Replicas stream and replay the primary WAL. With asynchronous replication a replica can lag, so reads may be stale and a failover can lose the most recent commits; synchronous replication avoids loss at the cost of commit latency.

Does PostgreSQL fail over automatically?

Not by itself. Tools such as Patroni with etcd, or managed services like RDS and Aurora, detect the failure, promote a replica and repoint clients.

How would you scale PostgreSQL writes beyond one machine?

First scale vertically and move reads to replicas and caches, then partition large tables, and finally shard by a key such as tenant or user, either with Citus or with routing in the application.

Which index would you use for JSONB queries or full-text search?

A GIN index, which indexes the individual keys, array elements or words inside each value. B-tree is for equality, ranges and sorting on scalar columns.

When would you not choose PostgreSQL?

When you need millions of writes per second, active writes in several regions, or simple key lookups at a scale where a partitioned store like DynamoDB or Cassandra is cheaper to run.