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 do VACUUM and autovacuum do in PostgreSQL?
Because of MVCC, updates and deletes leave dead row versions behind. VACUUM marks that space as reusable, updates the visibility map and freezes old transaction IDs so they cannot wrap around. Autovacuum is a background process that runs VACUUM and ANALYZE automatically when enough rows have changed.
IntermediateIn-depth3 min read - 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 is MVCC in PostgreSQL, and why don't readers block writers?
MVCC (Multi-Version Concurrency Control) means PostgreSQL keeps several versions of a row. An UPDATE writes a new version instead of overwriting the old one, so a reader keeps seeing the version that was valid when its snapshot was taken while the writer works on the new one. Readers never wait for writers, and writers never wait for readers.
IntermediateIn-depth4 min read - Can a UNIQUE column in PostgreSQL contain more than one NULL?
Yes by default, because NULL is unknown and two unknowns are not considered equal. From PostgreSQL 15 you can add NULLS NOT DISTINCT to make NULLs count as duplicates.
IntermediateQuick answer1 min read - How do you compute a running total, and what is a window frame?
Use sum(x) OVER (ORDER BY ...). The frame decides which rows around the current one are included; with ORDER BY the default is from the start up to the current row (including peers with equal values), which gives a running total.
IntermediateQuick answer1 min read - How do you find which query is blocking another in PostgreSQL?
Look at pg_stat_activity for sessions with wait_event_type Lock, and use pg_blocking_pids(pid) to get the PIDs holding the lock. Then inspect those sessions and, if needed, cancel or terminate the blocker.
IntermediateQuick answer1 min read - How do you get the top 2 highest-paid employees in each department?
Number the rows within each department using ROW_NUMBER or DENSE_RANK with PARTITION BY dept ORDER BY salary DESC, then filter on that number in an outer query. A lateral join is an alternative.
IntermediateQuick answer1 min read - How do you index one specific key inside a JSONB column?
Create a B-tree expression index on the extracted value, such as (data ->> 'email'), and query with the same expression. This is smaller and faster than a GIN index for equality or range on one known key.
IntermediateQuick answer1 min read - How do you turn a JSONB array into rows in PostgreSQL?
Use jsonb_array_elements (or jsonb_array_elements_text for plain strings) in the FROM clause, usually with a lateral join. Each array element becomes one row you can filter, join or aggregate.
IntermediateQuick answer1 min read - How do you update a single key inside a JSONB column without replacing the whole document?
Use jsonb_set(target, path, new_value) or the || concatenation operator to produce a modified copy, and assign it in an UPDATE. PostgreSQL still rewrites the entire jsonb value, since there is no in-place edit.
IntermediateQuick answer1 min read - How does FOR UPDATE SKIP LOCKED help build a job queue in PostgreSQL?
SKIP LOCKED makes a query ignore rows another transaction has already locked instead of waiting. Several workers can each grab different pending jobs at the same time without blocking or double-processing.
IntermediateQuick answer1 min read - How would you list all reports under a manager, at any depth, using SQL?
Use a recursive CTE: start with the manager's direct reports, then repeatedly join employees whose manager_id matches the previous level. Track depth and a path array to show levels and guard against cycles.
IntermediateQuick answer1 min read - Is the hash index in PostgreSQL worth using compared to B-tree?
A hash index supports only equality lookups. Since PostgreSQL 10 it is crash-safe and can be slightly smaller than a B-tree for long keys, but B-tree also handles equality plus ranges and sorting, so B-tree stays the default.
IntermediateQuick answer1 min read - Should a recursive CTE use UNION or UNION ALL?
UNION ALL keeps every produced row and is faster. UNION removes duplicates against all rows found so far, which can stop some cycles but costs extra work. For trees, use UNION ALL; for graphs with possible loops, add explicit cycle protection.
IntermediateQuick answer1 min read - Should you use an ENUM type, a CHECK constraint, or a lookup table for a fixed set of values?
A CHECK on text is the easiest to change, an ENUM is compact and ordered but awkward to alter, and a lookup table with a foreign key is best when the values need extra data or change often.
IntermediateQuick answer1 min read - What are advisory locks in PostgreSQL and when would you use one?
Advisory locks are application-defined locks on an arbitrary number, managed by PostgreSQL but not tied to any table or row. Use them to make sure only one process runs a task, like a nightly report job, at a time.
IntermediateQuick answer1 min read - 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 are xmin and xmax on a PostgreSQL row?
They are hidden system columns recording the transaction ID that created the row version (xmin) and the one that deleted or replaced it (xmax). MVCC uses them to decide which versions a given snapshot may see.
IntermediateQuick answer1 min read - What does CREATE INDEX CONCURRENTLY do and what are its trade-offs?
It builds the index without blocking writes to the table, by scanning the table in multiple passes. It takes longer, cannot run inside a transaction block, and can leave an INVALID index behind if it fails.
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 - What does the @> operator do on JSONB and why is it preferred for filtering?
a @> b is true when jsonb a contains jsonb b, meaning every key and value in b appears in a. It can use a GIN index, whereas filtering with ->> text comparisons usually cannot.
IntermediateQuick answer1 min read - What happens when you run ALTER TABLE ... ALTER COLUMN TYPE on a large table?
In most cases PostgreSQL rewrites the whole table while holding an ACCESS EXCLUSIVE lock, so reads and writes wait. Only some changes, such as widening varchar or dropping a length limit, skip the rewrite.
IntermediateQuick answer1 min read - What is a BRIN index and when is it better than B-tree?
BRIN stores only the minimum and maximum value for each block range of the table, so it is tiny. It works well on huge tables where the column's value follows the physical row order, like an append-only timestamp.
IntermediateQuick answer1 min read - What is a covering index and what does the INCLUDE clause do?
A covering index holds every column a query needs, so PostgreSQL can answer from the index alone with an index-only scan. INCLUDE adds extra payload columns to the index leaf without making them part of the sort key or uniqueness.
IntermediateQuick answer1 min read - What is a data-modifying CTE and how can DELETE ... RETURNING feed an INSERT?
PostgreSQL lets INSERT, UPDATE and DELETE appear inside WITH, and RETURNING hands the affected rows to the next step. This moves rows between tables in one atomic statement, for example archiving deleted rows.
IntermediateQuick answer1 min read - What is a GIN index and which PostgreSQL features use it?
GIN is an inverted index: it maps each element (a word, an array item, a JSONB key) to the rows that contain it. It powers full-text search, array and JSONB containment queries, and trigram search, with slower writes than B-tree.
IntermediateQuick answer1 min read - What is a GiST index, and how does it differ from GIN?
GiST is a flexible tree framework used for ranges, geometry, nearest-neighbour search and exclusion constraints. GIN is faster for lookups of elements inside values, while GiST is smaller and cheaper to update but lossy for text.
IntermediateQuick answer1 min read - What is table bloat in PostgreSQL and how do you detect it?
Bloat is space in a table or index taken by dead row versions that vacuum has marked reusable but not returned to the operating system. It makes scans slower and files larger. Check n_dead_tup in pg_stat_user_tables or use pgstattuple.
IntermediateQuick answer1 min read - Why can a long-running or idle-in-transaction session make PostgreSQL slow?
Vacuum can only remove dead rows that no open transaction might still need. One session that sits idle in transaction holds back the cleanup horizon for the whole database, so bloat grows and queries slow down.
IntermediateQuick answer1 min read