Why PostgreSQL
You'll likely discuss PostgreSQL in a system design interview. Two traps: diving into WAL/MVCC when the interviewer only needs to know if it can model your relationships, or claiming "NoSQL scales better" without nuance. This deep dive focuses on when Postgres wins, practical limits, and the trade-offs that matter on a whiteboard.
Start here · justify alternatives.
Indexes · GIN · covering.
WAL · ~5k/sec/core · batch.
Replicas · partition · shard.
Hands-on lab
Spin up Postgres locally, create the social schema, add indexes, and EXPLAIN a profile query — same loop you'd demo for MongoDB or DynamoDB access patterns.
# Docker Postgres 16
docker run -d --name pg-lab \
-e POSTGRES_PASSWORD=postgres \
-p 5432:5432 \
-v pg-lab-data:/var/lib/postgresql/data \
postgres:16
docker exec -it pg-lab psql -U postgres-- Schema + seed
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
status TEXT DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
title TEXT,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE likes (
user_id INTEGER REFERENCES users(id),
post_id INTEGER REFERENCES posts(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, post_id)
);
INSERT INTO users (username, email) VALUES
('evan', 'evan@example.com'),
('stefan', 'stefan@example.com');
INSERT INTO posts (user_id, title, content) VALUES
(1, 'Hello', 'Hello, world!'),
(1, 'Postgres', 'Indexes matter'),
(2, 'Feed', 'Another post');
-- Hot path index
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);
-- Before vs after: should show Index Scan
EXPLAIN ANALYZE
SELECT title, created_at FROM posts
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;A motivating example
Design a growing social platform — not mega-scale, but real relationships and mixed consistency needs:
- Users create posts; comment; follow; like posts and comments; send DMs.
- DM thread creation must be atomic (thread + participants + first message).
- Comments need referential integrity — no orphan comments.
- Like counts can be eventually consistent.
- Profiles need efficient recent-post + follower metadata reads.
- Search across posts and users.
That mix — relationships, integrity, mixed consistency, search, room to grow — is why Postgres shows up constantly in interviews.
Read performance
Reads usually dominate. Profile views fetch posts by user_id — without an index, that's a full table scan that gets worse as data grows. Index the columns you filter and sort on. See Database indexing.
Basic B-tree indexes
Default index type. Great for exact matches, ranges, and sorting when ORDER BY matches index column order. Postgres auto-indexes primary keys; add secondary indexes for hot filters.
-- Bread-and-butter
CREATE INDEX idx_users_email ON users(email);
-- Multi-column for common patterns
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);
Full-text search (GIN)
Built-in stemming, ranking, AND/OR/NOT — often enough to skip Elasticsearch until you need faceting, fuzzy typeahead, or search across huge distributed corpora.
ALTER TABLE posts ADD COLUMN search_vector tsvector;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('postgresql & database');JSONB + GIN
Flexible metadata (hashtags, mentions, media type) without a schema migration for every attribute — document flexibility inside a relational core.
ALTER TABLE posts ADD COLUMN metadata JSONB;
CREATE INDEX idx_posts_metadata ON posts USING GIN(metadata);
SELECT * FROM posts
WHERE metadata @> '{"type": "video"}'
AND metadata @> '{"hashtags": ["coding"]}';PostGIS (GiST)
Spatial extension for points, polygons, distance, containment. Reach for PostGIS before a specialized geo DB. See Proximity search.
CREATE EXTENSION postgis;
ALTER TABLE posts ADD COLUMN location geography(Point, 4326);
CREATE INDEX idx_posts_location ON posts USING GIST(location);
SELECT * FROM posts
WHERE ST_DWithin(
location,
ST_MakePoint(-122.4194, 37.7749)::geography,
5000 -- 5km
);Combine capabilities — video posts near SF mentioning food with a restaurant hashtag, one query:
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('food')
AND metadata @> '{"type": "video", "hashtags": ["restaurant"]}'
AND ST_DWithin(
location,
ST_MakePoint(-122.4194, 37.7749)::geography,
5000
);Also name in interviews when relevant: pgvector for embeddings (Vector databases) and TimescaleDB for metrics (Time-series databases) — stretch vanilla Postgres first.
Covering indexes
Index lookups normally find a row pointer then fetch the heap. INCLUDE stores extra columns in the index so the query never touches the table.
-- Common profile query
SELECT title, created_at FROM posts
WHERE user_id = 123 ORDER BY created_at DESC;
CREATE INDEX idx_posts_user_include
ON posts(user_id) INCLUDE (title, created_at);Partial indexes
Index only the rows you query — smaller, faster, less write overhead.
-- Only active users
CREATE INDEX idx_active_users
ON users(email) WHERE status = 'active';Write performance
Nobody wants to wait after hitting Post. Understanding the write path tells you what actually bounds throughput.
- Buffer cache + WAL record (memory) — dirty pages + WAL entry during the transaction.
- WAL flush (disk) — at COMMIT, sequential WAL write makes the tx durable. This is the sync gate on commit latency.
- Background writer — dirty pages land on data files later (checkpoints / pressure).
- Index updates — each index needs WAL + memory work; many indexes = slower writes.
- Simple inserts: ~5,000/sec per core (well-tuned hardware).
- Updates with indexes: ~1,000–2,000/sec per core.
- Complex multi-table transactions: hundreds/sec.
- Bulk load: tens of thousands of rows/sec.
Scaling writes beyond one node
Hitting >~5k writes/sec/core doesn't eliminate Postgres — it means optimize, then shard. Ladder: Scaling writes.
- Vertical scaling — NVMe for WAL, more RAM for buffer cache, more cores.
- Batch writes — many rows in one transaction (crash loses the in-flight batch).
- Write offloading — send analytics / last-seen / metrics to Kafka, batch into Postgres async.
- Table partitioning — time-based splits; concurrent writers hit different partitions; prune old data.
- Sharding — multiple Postgres instances; shard on your hottest query key (e.g.
user_id). Manual or Citus — no DynamoDB-style auto-shard built in.
-- Batch insert
INSERT INTO likes (post_id, user_id) VALUES
(1, 101), (1, 102), (1, 103);
-- Time-based partitioning
CREATE TABLE posts (
id SERIAL, user_id INT, content TEXT, created_at TIMESTAMP
) PARTITION BY RANGE (created_at);
CREATE TABLE posts_2024_01 PARTITION OF posts
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Replication
Replication serves two jobs: scale reads and high availability.
- Async (default) — primary acks after local WAL; replicas catch up in background. Best write latency; small loss window on primary failure.
- Synchronous — primary waits for replica ack. Stronger durability; higher write latency.
- Hybrid — one sync replica for durability + async replicas for read scale is common.
HA failover: detect primary down → promote replica → re-point apps. Managed RDS/Cloud SQL usually handles this. In interviews: mention HA + lag, not failover config knobs.
Data consistency
Don't stop at "Postgres is ACID." Show how you'll use it for your consistency NFRs. See Consistency models and CAP theorem (ACID consistency ≠ CAP consistency).
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Concurrency: the auction trap
Default isolation is Read Committed. A transaction that reads max bid then inserts can race — both bidders read $90; both think they're winning; you get an inconsistent bid history.
-- Fix 1: row-level lock
BEGIN;
SELECT max_bid FROM auctions WHERE id = 123 FOR UPDATE;
INSERT INTO bids (item_id, user_id, amount) VALUES (123, 456, 100);
UPDATE auctions SET max_bid = 100 WHERE id = 123;
COMMIT;
-- Fix 2: Serializable (retry on conflict)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- same logic...
COMMIT;
-- Fix 3: optimistic concurrency (version column)
UPDATE auctions SET max_bid = 100, version = 6
WHERE id = 123 AND version = 5; -- 0 rows → retry| Aspect | Serializable | Row locking (FOR UPDATE) |
|---|---|---|
| Concurrency | Retries on conflict | Wait only when same rows |
| Best for | Complex multi-row logic hard to lock | Known hot rows (bids, inventory) |
| App work | Retry serialization failures | Handle deadlocks |
| Prefer when | Hard to name locks | You know exactly which rows |
| Isolation | Dirty read | Nonrepeatable | Phantom |
|---|---|---|---|
| Read Committed (default) | No | Possible | Possible |
| Repeatable Read | No | No | No in PG* |
| Serializable | No | No | No |
*Postgres Repeatable Read is stronger than the SQL standard — it also prevents phantoms. You may not need Serializable as often as on other databases. Read Uncommitted is treated as Read Committed.
OCC fits rare conflicts (avoid holding locks). Bad fit when conflicts are frequent — wasted retries. Prefer FOR UPDATE when you know the hot row.
When to use PostgreSQL
Start with Postgres, then justify alternatives. That's stronger than leading with a niche store.
Default yes
- Complex relationships and foreign keys.
- ACID transactions and strong consistency paths.
- Rich queries, JSONB, built-in full-text, PostGIS.
- Read scaling via replicas; write scaling via partition + shard.
Perfect fits: e-commerce, fintech, CMS, analytics up to reasonable scale.
Consider alternatives
- Extreme writes (millions/sec) — Cassandra, Redis counters, Kafka pipeline.
- Global active-active writes — CockroachDB, DynamoDB Global Tables; Postgres is single-primary per cluster.
- Pure key-value — Redis, DynamoDB; MVCC + planner overhead you don't need.
- Unbounded document nesting — MongoDB if schema churn is the core bet.
Scalability alone is not a reason to abandon Postgres — design first, then prove you need something else.
Appendix: Basic SQL & ACID
Tables are relations (rows + typed columns). Primary keys uniquely identify rows; foreign keys enforce relationships. Normalization avoids duplication; intentional denormalization (e.g. cached like_count) trades consistency for read speed — say so in the interview.
- One-to-one — user ↔ profile settings
- One-to-many — user → posts
- Many-to-many — users ↔ liked posts (join table)
ACID — Atomicity (all or nothing), Consistency (constraints hold — different from CAP consistency), Isolation (concurrent txs), Durability (WAL flushed at commit). Use ACID for money and auth; relax for likes and analytics.
-- DDL
CREATE TABLE accounts (
account_id TEXT PRIMARY KEY,
balance DECIMAL CHECK (balance >= 0)
);
-- DML
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days';
UPDATE users SET email = 'new@email.com' WHERE id = 123;
-- TCL
BEGIN;
-- operations...
COMMIT;SQL command families: DDL (CREATE/ALTER), DML (SELECT/INSERT/UPDATE/DELETE), DCL (GRANT/REVOKE), TCL (BEGIN/COMMIT). Interviews usually ask access patterns and indexes — not syntax quizzes.
Cost and performance levers
Postgres interview scenarios
Failure mode card
| Symptom | Likely cause | Fix |
|---|---|---|
| High read latency | Missing index / seq scan | EXPLAIN, add index |
| Replication lag | Heavy writes / slow replica | Tune, split reads, upgrade |
| Connection exhaustion | No pooler | PgBouncer |
| Bloat / vacuum debt | Update-heavy rows | Autovacuum, fillfactor |
Interview Q&A by level
Match depth to the bar: define → trade off → operate.
Wrapping up
PostgreSQL should be your interview default. ACID, indexes, JSONB, full-text, PostGIS, and replication often replace extra systems until scale or global write patterns force a switch. Analyze concrete NFRs — consistency per feature, index plans, read vs write bottlenecks — not trends.
Compare with MongoDB, DynamoDB, Cassandra, Sharding, Contention, and Key technologies.