Interviews test portable SQL plus Postgres specifics: MVCC, JSONB, indexes, EXPLAIN, isolation, and operational basics.
Common question patterns
- Latest row per group (DISTINCT ON or ROW_NUMBER)
- JSONB filtering and indexing
- Dead tuples and vacuum
- Difference vs MySQL (RETURNING, types, extensions)
Sample: duplicates
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;Practice: Review prior lessons in psql, then attempt interview-style queries without peeking.
Sample: explain plan literacy
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 1;Talk through whether an index on customer_id would appear and why.
Important interview questions and answers
- Q: MVCC benefit?
A: Readers do not block writers; vacuum reclaims old row versions. - Q: Why JSONB over JSON?
A: Binary storage and GIN indexing for containment queries.
Self-check
- How do you find duplicate emails?
- What does n_dead_tup indicate in pg_stat_user_tables?
Tip: Explain MVCC and JSONB when asked why teams pick Postgres.
Interview prep
- MVCC interview line?
Readers don't block writers; vacuum reclaims old versions.
- Duplicate emails?
GROUP BY email HAVING COUNT(*) > 1.