Top 19 Postgresql Interview Questions
Browse 19 real-world technical and behavioral interview questions about Postgresql. Review scenarios, edge cases, and architectural best practices.
You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?
The dequeue has to pick the highest-priority ready row, lock it, and mark it claimed in one statement, using FOR UPDATE SKIP LOCKED so ten workers each take a different row instead of queueing behind the same one. The lock only lasts as long as the transaction, so the claim must be written or the transaction held.
You are asked to design something distributed and your honest answer is one Postgres box. When is that the strongest answer in the room, and when does it end the interview?
One PostgreSQL primary with a replica and tested backups is the strongest answer when a capacity estimate built from the prompt fits it with headroom and the core invariants need transactions. It ends the interview when the prompt rules it out, or when you use it to decline the distributed design you were asked for.
A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?
A partial index only contains rows matching its predicate, so indexing WHERE deleted_at IS NULL stores five million entries instead of forty million - smaller, shallower, and far likelier to stay cached. The catch is the planner only uses it when it can prove the query predicate implies the index predicate, and it does nothing about dead rows still in the heap.
Your team says the database is backed up nightly. How would you establish whether you could really restore it?
A backup job that exits zero is a belief, not a control. Establishing recoverability means agreeing an RPO and RTO, then rehearsing a restore onto clean infrastructure, measuring how long it took and verifying the result — including everything the dump does not contain.
Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?
Order by how the predicate uses the column, not by cardinality: equality columns first, then one range column, then columns needed only for sorting. A range predicate stops the index seeking on everything after it, and the leftmost prefix decides which other queries the index can serve.
Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.
Use NOT EXISTS for customers with no recent order because it expresses an anti-join and avoids NULL traps. NOT IN can return no rows if the subquery contains NULL, and LEFT JOIN becomes wrong if filters are placed in WHERE. Use this SQL answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects null semantics to the point an interviewer is testing.
You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?
A deadlock is a cycle in the wait-for graph, so read the engine's own report to get both statements and the locks each held. Nearly all of them come from two code paths taking the same rows in different orders; impose one order, shorten transactions, and retry, because a deadlock is always safe to retry.
Before you tune anything, how do you decide which queries are worth your time?
Rank by the total time a statement shape consumes across a measured window rather than by the slowest single execution, then check what that time was spent waiting on before touching the SQL. The cheap statement running ten thousand times a minute usually outranks the ten-second report.
This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?
Decide from evidence, not intuition: per-index scan counts read against the date the statistics were reset, redundancy judged by leading columns, and the constraints and foreign keys that need an index whether or not anything ever scans them. Then make the drop reversible.
Walk me through how you read a PostgreSQL execution plan.
Read the plan tree innermost-node-first, remember that EXPLAIN only estimates while EXPLAIN ANALYZE executes, multiply each node's actual rows by its loops, and treat a large gap between estimated and actual rows as the primary finding.
Walk me through the transaction isolation levels and which anomalies each one permits.
Each level is defined by the anomalies it allows: READ COMMITTED permits non-repeatable reads and phantoms, PostgreSQL's REPEATABLE READ is snapshot isolation so it prevents both yet still permits write skew, and only SERIALIZABLE rules out all four.
Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?
Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.
The same statement takes 20ms in psql and four seconds from the application. Where do you look?
When the same query is fast in psql but slow from the application, first separate database time from connection wait, network transfer and ORM hydration. Then compare the real application plan, bind parameters, session settings, fetch size and result volume.
You need to migrate the schema of a 400-million-row table on a live system. Talk me through the plan, the backup you would want first, and how the connection pool affects it.
Every step is chosen to hold weak locks briefly - add the constraint invalid and validate separately, index concurrently, backfill in committed batches - behind a restore you have rehearsed and timed, and a pool small enough that one blocked DDL statement does not exhaust it.
A user submits the form twice and you get two identical orders. Why did the check-then-insert not prevent it, and how do you fix it in the database?
Two transactions can each run the SELECT before either commits, so neither sees the other and both inserts succeed. The fix is a serialisation point the database owns - a unique index on a natural or client-supplied key - plus a defined answer for whoever loses the race. It also connects idempotency to the point an interviewer is testing.
Monitoring says the age of the oldest transaction ID on this PostgreSQL database keeps climbing. What is it measuring, and what happens if it is ignored?
PostgreSQL compares 32-bit transaction IDs, so old rows must be frozen before the counter laps them. A rising age means vacuum is not freezing fast enough, and if it keeps rising the server eventually refuses to assign new transaction IDs and stops accepting writes.
Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?
Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember. Model the lifecycle explicitly instead.
In PostgreSQL, what does an UPDATE do to a row under MVCC, and why does a long-running transaction make that expensive?
An UPDATE writes a new row version and leaves the old one visible to older snapshots; vacuum reclaims it only once no snapshot can see it. A long transaction pins that horizon, so dead tuples accumulate as bloat and freezing stalls, which is what leads to transaction-id wraparound.
A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.
Work outward from the plan: confirm the index is actually usable for the predicate as written, then check whether the optimiser is choosing to ignore it because of selectivity, stale statistics, or a type or collation mismatch that makes the predicate non-sargable.