PostgreSQL in FAANG Interviews
An offer from Stripe or Cloudflare. The final round is system design with PostgreSQL. You have 45 minutes to explain MVCC, design a schema, and discuss scaling. The final readiness check.
- Stripe uses the MVCC and XID wraparound question as a standard filter for senior backend positions
- Cloudflare asks candidates to design a schema for DNS logs at 10M+ records/sec on PostgreSQL
- Notion has publicly described how schema design interviews help evaluate experience with high-scale PostgreSQL
Typical Interview Questions
PostgreSQL interviews at FAANG and large tech companies span several levels: core concepts (ACID, MVCC, indexes), scenario questions (design X), and deep-dives into internals (how VACUUM works, what WAL is). Saying 'use an index' without explaining when and why is a red flag.
Transaction isolation is a guaranteed question. You should be able to explain all 4 levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and the anomalies each one allows. Default in PostgreSQL: Read Committed. Read Uncommitted in PostgreSQL is implemented as Read Committed (dirty reads are not supported).
Interviewers often ask the difference between optimistic and pessimistic locking. Pessimistic: SELECT FOR UPDATE (immediate row lock). Optimistic: application-level versioning (check the version before UPDATE). PostgreSQL supports both.
Interviewer: 'How does MVCC let readers avoid blocking writers?'
Schema Design in Interviews
Schema design is the most common task type at senior+ interviews. Prompt: design a schema for Twitter, Uber, or a notification system. You are evaluated on: normalization vs denormalization, choice of data types, index strategy, and trade-off discussions.
Normalization vs denormalization: normalization removes anomalies but requires JOIN. At 1M+ QPS a single extra JOIN can cost 50ms of latency. Denormalization (duplicating data) speeds up reads at the cost of write complexity. Instagram denormalized follower counters into the users table: one SELECT instead of a COUNT.
On schema-design interviews, always state the scale explicitly: 'If reads are 1M QPS and writes 10K, I pick denormalized counters. If it is write-heavy, I pick normalization.' Showing trade-off thinking matters more than the right answer.
Task: a likes table with 10B rows. The hottest query is COUNT(*) by post_id. Best solution?
Query Optimization: EXPLAIN and Index Strategy
Query optimization in interviews shows you can read EXPLAIN ANALYZE and make index decisions. The interviewer hands you a slow query and asks: 'What is wrong? How do you speed it up?' The right algorithm: EXPLAIN ANALYZE -> find the bottleneck -> propose a fix -> discuss trade-offs.
Index types and when to use them: B-tree (default, equality + range), GiST (geometry, full-text, exclusion constraints), GIN (arrays, jsonb, full-text - many values), Hash (equality only, faster than B-tree for hash joins), BRIN (huge tables with physical ordering, min/max per block).
Covering indexes (INCLUDE) come up often in senior interviews. Index Only Scan is possible only when every required column lives in the index. INCLUDE adds columns to leaf nodes without affecting the sort key.
For a JSONB lookup attributes @> '{"color": "red"}' which index type do you need?
PostgreSQL Internals Questions
Internals questions separate senior from principal engineer. The interviewer wants to know: does the candidate understand how the system works, or only how to use it? Key topics: heap files, page structure, checkpoint, WAL, vacuum internals, MVCC visibility.
The XID wraparound question signals deep understanding. Transaction IDs in PostgreSQL are 32-bit numbers (4 billion values). On wraparound, old transactions become 'future' and their data effectively disappears. Autovacuum VACUUM FREEZE prevents this by replacing old XIDs with FrozenTransactionId. Monitoring: age(datfrozenxid) should be < 1.5 billion.
Why does PostgreSQL not overwrite a row in place on UPDATE?
Mock Interview: Trading System
Final section: a practical mock interview. Task: design a PostgreSQL schema for a trading platform handling 50K transactions per second, with ACID requirements and analytics over the last 90 days.
Typical interviewer follow-ups: 'How do you scale to 500K TPS?' (answer: connection pooling, read replicas for analytics, sharding by account_id). 'How do you make transfers idempotent?' (answer: idempotency_key UUID with a UNIQUE constraint; a retry returns the existing result). 'How do you get balances over a time range fast?' (answer: a separate ledger table with running balance, or a materialized view).
Key success signals on PostgreSQL interviews: discussing trade-offs (not 'the best answer' but weighed alternatives), command of EXPLAIN ANALYZE, understanding of MVCC and WAL, ability to design for scale within real constraints.
Interviews want one correct answer with no discussion of alternatives
Strong candidates discuss trade-offs: 'You can do X, which gives Y at the cost of Z. The alternative is A, better when B.' The interviewer evaluates thinking, not memorization.
In real systems there is no single right answer. UUID vs bigint, normalization vs denormalization, partitioning vs sharding - every choice depends on the workload. A candidate who answers without discussing context raises doubts about production experience.
How do you make a financial transfer idempotent when the client has retry logic?
PostgreSQL in Interviews
- MVCC: xmin/xmax on every row, readers see a snapshot and do not block writers
- Schema design: discuss trade-offs (UUID vs bigint, normalization vs denormalization, index strategy)
- Query optimization: EXPLAIN ANALYZE -> find the bottleneck -> partial/composite/covering index
- Internals: UPDATE creates a new tuple (HOT optimization), checkpoint flushes dirty pages, XID wraparound = freeze
- Mock interview: idempotency key, optimistic locking via version, partitioning for analytics
Related Topics
Interviews test knowledge of the entire PostgreSQL course
- Troubleshooting — Diagnosing production issues is a key skill tested in senior interviews
- Monitoring — Knowing pg_stat_* views is a baseline expectation for senior PostgreSQL roles
- PostgreSQL at Scale — Instagram, Stripe, and Notion examples back architectural decisions in interviews
Вопросы для размышления
- How do you explain MVCC to a non-technical interviewer in 2 minutes without losing accuracy?
- Which UUID vs bigint trade-offs matter most when designing a high-write system?
- How is XID wraparound tied to autovacuum, and why can't you just turn autovacuum off?