Top 17 Postgresql Interview Questions

Browse 17 real-world technical and behavioral interview questions about Postgresql. Review scenarios, edge cases, and architectural best practices.

Related Topics:Indexing Query optimisation Sql Joins Locking

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.

hardconcept

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.

hardscenario

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.

hardconcept

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.

mediumcoding

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.

hardscenario

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.

mediumconcept

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.

hardscenario

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.

mediumconcept

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.

hardconcept

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.

harddesign

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.

hardscenario

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.

hardscenario

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.

hardscenario

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.

hardconcept

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.

mediumdesign

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.

hardconcept

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.

hardscenario