Interview Q&A / PostgreSQL
PostgreSQL interview questions
Indexes, EXPLAIN, transactions and isolation, JSONB and the Postgres specifics backend interviews probe.
Prefer rapid revision? Practise PostgreSQL short interview answers →
- What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read a query plan?
EXPLAIN shows the plan PostgreSQL intends to use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually runs the query and adds real timings and real row counts, so you can compare estimates with reality.
BeginnerIn-depth4 min read - Is it safe to run EXPLAIN ANALYZE on an UPDATE or DELETE?
No, not without care. EXPLAIN ANALYZE really executes the statement, so the rows are changed. Wrap it in BEGIN and ROLLBACK to inspect the plan and timings without keeping the change.
BeginnerQuick answer1 min read - What do the cost numbers in an EXPLAIN plan actually mean?
cost=A..B gives the estimated startup cost (before the first row) and total cost, in arbitrary planner units based on page fetches and CPU work. They are for comparing plans, not seconds or milliseconds.
BeginnerQuick answer1 min read - What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan in a PostgreSQL plan?
Seq Scan reads the whole table, Index Scan follows the index row by row, and Bitmap Heap Scan collects matching row locations from the index first, then reads table pages in physical order. The planner picks by estimated selectivity and cost.
BeginnerQuick answer1 min read