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 index types does PostgreSQL support, and why is my index sometimes not used?
B-tree is the default and handles equality, ranges and sorting. GIN indexes values that contain many items, like JSONB, arrays and full-text search. BRIN is a tiny index for huge tables whose rows are stored in a natural order, like time-series data. The planner skips an index when a full scan looks cheaper, or when the query is written in a way the index cannot match.
IntermediateIn-depth4 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