PostgreSQL interview questions

Indexes and query plans

• Read the plan first. `EXPLAIN ANALYZE` versus plain `EXPLAIN` - one executes, one estimates. Saying you would look before guessing is the expected opening.
• Sequential scan versus index scan versus index-only scan, and why the planner sometimes correctly chooses a sequential scan.
• B-tree versus GIN versus GiST - and what each is actually for (GIN for arrays, JSONB and full text).
• Composite index column order, and why an index on (a, b) helps a query filtering on `a` but not one filtering only on `b`.
• Partial indexes, and when a covering index earns the extra write cost.
• Why does an index stop being used? Functions on the column in `WHERE`, type mismatches, or stale statistics.

MVCC, vacuum and bloat

The topic most candidates have never thought about, and the one most likely to appear in an operational role.

• How does Postgres give each transaction a consistent view? Row versions rather than read locks - readers never block writers.
• What is a dead tuple, and why does an UPDATE create one?
• What does autovacuum do, and what happens when it cannot keep up? Table bloat, and eventually transaction ID wraparound.
• `VACUUM` versus `VACUUM FULL` - and why the second takes an exclusive lock you probably cannot afford.

Transactions and locking

• Isolation levels, and which anomalies each prevents. Postgres has no dirty reads even at read committed.
• What is a deadlock here, and how does Postgres resolve one? It detects and kills a victim.
• `SELECT FOR UPDATE` and when you need it.
• Why can a long-running transaction be a production problem even when idle? It holds back vacuum.
• What does a schema migration lock, and how do you add a column or index without downtime? `CREATE INDEX CONCURRENTLY`.

Schema and data types

JSONB versus a normalised schema, and the honest answer that JSONB is excellent for genuinely variable data and a trap for data you will query structurally. Then: when to partition, `text` versus `varchar(n)` (functionally identical in Postgres, which surprises people), and why `timestamptz` is almost always the right choice over `timestamp`. The query-writing half is in SQL interview questions.

How deep it goes by level

• Junior: the fundamentals above, explained clearly. Nobody expects production scars yet, and saying what you have not done reads better than bluffing.
• Mid-level: the same ground plus a schema you have designed and migrated. Expect follow-ups asking why you made a choice rather than whether you know the syntax.
• Senior: replication, partitioning at scale, and a production incident you diagnosed from query plans. The recall questions get shorter and the judgement questions get longer.

Whatever the level, say your answers out loud rather than reading them. Most of what candidates lose in a PostgreSQL interview is lost between knowing something and explaining it under observation - the method is in how to use AI for interview prep.

Where CoPilot Interview fits

Disclosure: we build an interview assistant, so treat this as a disclosed pitch. CoPilot Interview is a native Windows and macOS app that puts suggested answers on your own screen in about two seconds, across coding, system design and behavioral rounds, with a permanent free tier that needs no credit card.

Frequently asked questions

What are the most common PostgreSQL interview questions?

Reading query plans with `EXPLAIN ANALYZE`, index types and composite column order, MVCC and what autovacuum does, isolation levels, and how to add an index without locking the table. The operational questions about vacuum and bloat separate candidates most.

Why would Postgres ignore an index?

Commonly because a function or a type cast is applied to the column in the `WHERE` clause, because statistics are stale, or because the planner correctly estimates that a sequential scan is cheaper for the proportion of rows being returned.

What is MVCC in PostgreSQL?

Multi-version concurrency control - each transaction sees a consistent snapshot built from row versions rather than taking read locks, so readers never block writers. The cost is dead tuples left behind by updates and deletes, which vacuum must reclaim.

How do you add an index without downtime?

`CREATE INDEX CONCURRENTLY`, which avoids the exclusive lock a normal index build takes. It is slower, cannot run inside a transaction block, and can leave an invalid index if it fails - all points worth mentioning.