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 are the three join algorithms in PostgreSQL and when does the planner choose each?
Nested Loop probes the inner side once per outer row and suits small inputs or indexed lookups. Hash Join builds a hash table of one side and suits large unsorted equality joins. Merge Join walks two sorted inputs and suits large sorted ones.
IntermediateQuick answer1 min read - What does EXPLAIN (ANALYZE, BUFFERS) add and what do shared hit and read mean?
BUFFERS reports how many 8 kB pages each plan node touched. shared hit means the page was already in PostgreSQL's cache, shared read means it came from the operating system or disk. It shows I/O cost that timing alone hides.
IntermediateQuick answer1 min read - What does it mean when estimated rows and actual rows differ a lot in EXPLAIN ANALYZE?
The planner chose its plan from a wrong guess about row counts. Usual causes are stale statistics, correlated columns, or expressions it cannot estimate. Run ANALYZE, raise the statistics target, or create extended statistics.
IntermediateQuick answer1 min read