71 PostgreSQL interview questions with answers
71 PostgreSQL interview questions with detailed answers, sorted from basic to advanced. Free to practice, with progress saved automatically.
All PostgreSQL interview questions
What is PostgreSQL and when should you choose it?
How are primary keys, unique keys and foreign keys different?
How should you understand `NULL` in PostgreSQL?
What problem do transactions solve in PostgreSQL?
How are constraints different from indexes?
What do indexes help with in PostgreSQL and what are the trade-offs?
When should you use B-tree, GIN, GiST and BRIN indexes?
How important is column order in a composite index?
When should you use a partial index?
How are `EXPLAIN` and `EXPLAIN ANALYZE` different?
How are PostgreSQL isolation levels different?
When should you use `SELECT FOR UPDATE`?
How is `SKIP LOCKED` used to build a job queue?
When do deadlocks happen in PostgreSQL and how do you handle them?
How should you choose between UUID, serial and identity columns?
How are `json` and `jsonb` different?
When should you normalize instead of using JSONB?
When should you use generated columns in PostgreSQL?
How are PostgreSQL join types different?
When should you use window functions in PostgreSQL?
What is an index-only scan and why might it not happen?
How do `ANALYZE` and statistics affect query plans?
Why is connection pooling important in PostgreSQL?
What types of replication does PostgreSQL have?
What do backups and PITR need in PostgreSQL?
How do you run PostgreSQL schema migrations with low downtime?
How does soft delete affect indexes and constraints in PostgreSQL?
How do you use UPSERT in PostgreSQL?
When is `RETURNING` useful in PostgreSQL?
When is PostgreSQL full-text search enough?
When should you use partitioning in PostgreSQL?
When should you use advisory locks in PostgreSQL?
Why might PostgreSQL use a sequential scan even when an index exists?
What is MVCC in PostgreSQL?
When should you use CTEs in PostgreSQL?
Should you use enum types in PostgreSQL?
Do read replicas solve every read scaling problem?
How do you usually handle N+1 queries with PostgreSQL?
What are the choices for PostgreSQL multi-tenant design?
How do you write a monthly revenue report where **months with no orders still show a zero row**?
What does PostgreSQL's `DISTINCT ON` do? Compared with `ROW_NUMBER()` for latest-row-per-group, which should you pick?
How do you query a multi-level category tree with a recursive CTE, and how do you prevent infinite loops?
Is a CTE an "optimization fence" in PostgreSQL? What are `MATERIALIZED` / `NOT MATERIALIZED` for?
For bulk updates from another table, should you use `UPDATE ... FROM` or `MERGE`? How do they differ from `INSERT ... ON CONFLICT`?
What is an expression index? How do you use it for case-insensitive email lookup?
How do you read `EXPLAIN ANALYZE` output? What is the difference between `cost` and `actual time`, and what does a row estimate mismatch tell you?
What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan? When does the planner pick a bitmap?
You need to add an index to a 200-million-row table in production. How do you avoid locking it?
What is index bloat? Why does an index grow even when the row count is stable, and how do you fix it?
How do you find redundant or unused indexes so you can drop them?
What is a HOT update in PostgreSQL? Why can adding one index collapse write throughput on an update-heavy table?
How does PostgreSQL store multiple versions of a row? What are `xmin` and `xmax`?
What is a dead tuple? What does table bloat cause and how do you detect it?
What does autovacuum do? When does it fall behind?
What is transaction ID wraparound? Why can it stop the database from accepting writes?
How does `VACUUM FULL` differ from a plain `VACUUM`? Why avoid it on a live table?
What is TOAST in PostgreSQL? How does it affect performance for large values?
Which operators let a `jsonb` query use an index? When should data not go into `jsonb`?
A 500M-row log table is only queried for the last 30 days — how would you partition it by time? What does partition pruning require?
Why does each PostgreSQL connection cost resources? Is setting `max_connections` high a problem?
How does PgBouncer transaction mode work? Why do prepared statements and session state break under it?
How does streaming replication work? How do you measure replication lag?
A user creates an order, then the list page does not show it because reads go to a replica. How do you handle this flow?
How does logical replication differ from physical replication? When would you use it?
Production is slow but you do not know which query is responsible. How do you use `pg_stat_statements`?
An `ALTER TABLE` on production froze the whole app even though the table is small. How do you diagnose it with `pg_locks`?
How do `statement_timeout` and `idle_in_transaction_session_timeout` differ? What values should you set?
How do `SELECT ... FOR UPDATE`, `FOR NO KEY UPDATE`, and `FOR SHARE` differ?
Several workers pull jobs from one queue table — how do `SKIP LOCKED` and `NOWAIT` solve it?
How do `lock_timeout`, `statement_timeout`, and `idle_in_transaction_session_timeout` differ, and how should you set them?
Why does PostgreSQL `REPEATABLE READ` raise "could not serialize access", and how must the application handle it?
