Courses › System Design › Key Technologies

PostgreSQL

Transactions, isolation levels, indexes, replication and scaling limits.

PostgreSQL is the best default database for a system design interview. It's mature, strongly consistent, supports rich queries and transactions, and scales much further than many candidates assume. If you can't articulate why you need something else, choose Postgres.

What it gives you

  • ACID transactions — multi-row, multi-table updates that succeed or fail together
  • Rich querying — joins, aggregations, window functions, CTEs
  • Constraints — unique, foreign key and check constraints enforce integrity in the database itself
  • Extensions — PostGIS (geospatial), full-text search, pgvector (embeddings), JSONB for semi-structured data

Transactions and isolation levels

Isolation levels determine which anomalies concurrent transactions can see:

For contention problems (booking a seat, decrementing inventory), you usually don't need Serializable. Use one of:

BEGIN;
SELECT * FROM seats WHERE id = 'A12' AND event_id = 77 FOR UPDATE;
-- check status, then:
UPDATE seats SET status = 'reserved', reserved_by = 42 WHERE id = 'A12' AND event_id = 77;
COMMIT;
UPDATE seats
SET status = 'reserved', reserved_by = 42, version = version + 1
WHERE id = 'A12' AND event_id = 77 AND status = 'available';
-- 1 row updated = success; 0 rows = someone else got it

The conditional update is often the simplest correct answer. See Dealing with Contention.

Indexes

B-tree indexes by default; GIN for full-text and JSONB; GiST for geospatial (PostGIS). Partial indexes (WHERE status = 'active') and covering indexes (INCLUDE (...)) are worth mentioning. See Database Indexing.

Scaling Postgres

  • Vertical scaling — a single large instance handles tens of thousands of transactions per second and many terabytes.
  • Read replicas — stream changes to replicas for read scaling. Replication is typically asynchronous, so replicas lag slightly; send reads that must see a user's own write to the primary.
  • Connection pooling (PgBouncer) — Postgres connections are expensive; pool them.
  • Partitioning — split large tables by time or key within one database, to keep indexes small and make archiving easy.
  • Sharding — split data across multiple Postgres clusters by a shard key, either in the application or with tools like Citus. This is the last resort, because it gives up cross-shard joins and transactions.

Durability and replication

Writes are recorded in the write-ahead log (WAL) before data pages change, so committed transactions survive a crash. The WAL also drives replication and point-in-time recovery. Synchronous replication to at least one standby gives zero data loss on failover, at a latency cost.

[!INTERVIEW] When to choose something else - Extremely high write throughput of simple, append-heavy data (messages, events, metrics) → Cassandra or a time-series store - Simple key-based access at massive scale with minimal ops → DynamoDB - Full-text search over large text with relevance ranking → Elasticsearch, synced from Postgres Even then, Postgres often remains the system of record for the transactional core.