Skip to content
Tutorials Center
My learning

Topics

Lesson 89 of 90 ยท Practice and Review

PostgreSQL Interview Questions

Common PostgreSQL interview questions with concise answers on MVCC, vacuum, indexes, isolation levels, replication, query tuning and schema design.

PostgreSQL interviews rarely stop at "write a join". Interviewers want to hear that you understand what happens under the hood: why an update leaves a dead row behind, why an index is ignored, why a replica can fill the primary's disk. This page collects questions that come up again and again for backend developers, data engineers and DBAs, each with a short answer you can put into your own words.

Try to answer each question out loud before you open it. If you cannot explain an idea in three or four sentences, follow the link to the lesson and review it. Two questions come with live editors, which run SQLite in your browser; everything specific to PostgreSQL servers is shown as read-only text, as in the rest of this track.

What interviewers probe

Internals MVCC, vacuum, WAL.
Performance Indexes, plans, statistics.
Concurrency Isolation, locks, retries.
Operations Replication, backups, upgrades.
1 / 2

Tip: when you do not know an answer, reason aloud from first principles ("PostgreSQL keeps old row versions, so..."). Interviewers value structured reasoning more than memorized trivia, and a wrong guess that you correct yourself is fine.

Fundamentals

What is MVCC, and why does PostgreSQL use it?

Multi-Version Concurrency Control keeps several versions of a row so that readers never block writers and writers never block readers. An UPDATE does not overwrite a row: it writes a new row version and marks the old one as ended. Each transaction sees a consistent snapshot, so a long SELECT is not disturbed by concurrent changes. The price is that dead versions pile up and must be cleaned by VACUUM. See MVCC.

What are xmin and xmax?

Hidden system columns on every row version. xmin is the ID of the transaction that created the version, and xmax is the ID of the transaction that deleted or replaced it (zero while the version is live). A snapshot uses them to decide whether a version is visible to the current transaction.

What does VACUUM do, and how does it differ from VACUUM FULL?

Plain VACUUM finds dead row versions that no transaction can see any more and marks their space reusable inside the table. It runs alongside normal reads and writes and usually does not shrink the file. It also updates the visibility map and freezes old rows. VACUUM FULL rewrites the entire table into a new file, which returns space to the operating system, but it takes an ACCESS EXCLUSIVE lock that blocks all access while it runs. Use it rarely; tools such as pg_repack do the rewrite with much less locking. See VACUUM and autovacuum.

What is autovacuum, and when would you tune it?

A background launcher that starts workers to vacuum and analyze tables automatically once enough rows have changed. A table is vacuumed after roughly 50 rows plus 20% of its size have died (the defaults), which is far too lazy for a huge, hot table: 20% of a billion rows is 200 million dead tuples. You tune it per table with storage parameters such as autovacuum_vacuum_scale_factor, and you give it more resources when it cannot keep up. Never turn it off.

What is transaction ID wraparound?

Transaction IDs are 32-bit numbers compared in a circle, so only about two billion transactions in either direction are "past" or "future". If very old rows were never frozen, their xmin would eventually look like it lies in the future and the rows would vanish. Vacuum freezes old rows to prevent this; if it falls far behind, PostgreSQL first forces aggressive autovacuums and finally stops accepting writes to protect the data. Monitor the age of datfrozenxid.

timestamp or timestamptz?

Almost always timestamptz. It stores an absolute instant (internally UTC) and converts to the session's time zone on output, so the same value is correct for every viewer. Plain timestamp stores whatever wall-clock reading you gave it, with no zone, which is only right for things like "the store opens at 09:00 local" that really are local clock times. See dates and times.

Is varchar(n) faster than text?

No. In PostgreSQL text, varchar and varchar(n) are stored the same way, and the only difference is the length check. Use text unless a real business rule limits the length, and prefer a CHECK constraint for rules you may want to change later. char(n) pads with spaces and is almost never what you want. See text types.

serial or identity columns?

Prefer GENERATED ALWAYS AS IDENTITY (or BY DEFAULT). It is standard SQL, is owned by the column so permissions and dumps behave better, and cannot be overwritten by accident with ALWAYS. serial is a shorthand that creates a sequence with a default; it still works but its ownership and permission rules are looser. In both cases, sequences are not transactional: rolled-back inserts leave gaps. See UUIDs, serials and identity.

Why does NOT IN sometimes return no rows at all?

Because of NULL. x NOT IN (1, 2, NULL) is unknown for every x not in the list, and WHERE keeps only true rows, so nothing matches. NOT EXISTS does not have this trap. Run it, then change the subquery to see the difference:

SQL NOT IN versus NOT EXISTS with NULLs

Press Run (or Ctrl + Enter) to see the result here.

The three results are 3 and 2, then 0, then 3. PostgreSQL behaves the same way, as do all SQL databases.

Indexes and query tuning

Which index types does PostgreSQL offer, and when do you use each?

B-tree (the default) for equality, ranges and sorting. Hash for equality only, rarely better than a B-tree. GIN for values with many parts: jsonb, arrays, full-text tsvector. GiST for ranges, geometry, nearest-neighbour and exclusion constraints. SP-GiST for partitioned search structures such as tries and points. BRIN for huge tables whose values correlate with physical order, like timestamps in an append-only log. See B-tree and GIN and GiST.

Why might PostgreSQL ignore an index?

Common reasons: the query applies a function to the column (lower(email) = ... cannot use a plain index on email); a type mismatch forces a cast; the condition is not selective, so reading most of the table sequentially is cheaper; the table is tiny; statistics are stale; or the leading column of a multicolumn index is not constrained. The fix depends on the reason: an expression index, matching types, ANALYZE, or a different column order. The live example shows the function case with SQLite's planner:

SQL A function on the column hides the index

Press Run (or Ctrl + Enter) to see the result here.

The first plan is a full scan; the second searches idx_email_lower. PostgreSQL's plan text differs (Seq Scan versus Index Scan), but the cause and cure are the same. See partial and expression indexes.

What is an index-only scan, and what is INCLUDE for?

An index-only scan answers a query from the index alone, without visiting the table. It works when every needed column is in the index and the pages involved are marked all-visible in the visibility map (which vacuum maintains). INCLUDE adds extra payload columns to a B-tree index without making them part of the search key, so you can cover a query without bloating the sort order.

What is the cost of having many indexes?

Every insert writes to every index, and an update that changes an indexed column (or cannot use a HOT update) writes new index entries too. Indexes use disk and cache, and they slow down vacuum. Index what queries actually need, and remove unused ones; pg_stat_user_indexes.idx_scan shows which indexes are never read. See index maintenance.

How do you find out why a query is slow?

Run EXPLAIN (ANALYZE, BUFFERS) and read from the innermost node outward. Compare estimated rows with actual rows (a big gap means bad statistics), look at the node that consumes most of the time, and check loops, sorts that spill to disk, and shared-buffer reads. To find which queries to look at in the first place, use pg_stat_statements ordered by total time. See EXPLAIN and pg_stat_statements.

How would you paginate a large table?

Avoid OFFSET for deep pages, because the database still reads and discards every skipped row. Use keyset pagination: remember the last row's sort key and ask for rows after it, WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 50, backed by an index on those columns. See query tuning patterns.

What is work_mem, and what is the risk of raising it?

The memory each sort or hash operation may use before spilling to temporary files. It applies per operation, not per query or per connection, so a query with several sorts and hashes, running on many connections at once, can multiply it. Raise it for specific sessions or roles that run big reports, not globally to a huge value. See configuration tuning.

Transactions and concurrency

Which isolation levels does PostgreSQL implement, and what is the default?

Three behaviours, selected with four names. Read Committed (the default) takes a new snapshot for each statement. Repeatable Read uses one snapshot for the whole transaction and also prevents phantom reads in PostgreSQL. Serializable adds Serializable Snapshot Isolation, which detects dangerous dependency patterns and aborts one transaction. READ UNCOMMITTED is accepted but behaves as Read Committed, so dirty reads never happen. See isolation levels.

What is write skew, and how do you prevent it?

Two transactions read overlapping data, each decides something based on it, and each writes a different row, so neither overwrites the other but the combined result breaks a rule (two doctors both going off call, two bookings for the last seat). Repeatable Read does not stop it. Use SERIALIZABLE and retry on SQLSTATE 40001, or lock the rows you depend on with SELECT ... FOR UPDATE, or design a constraint that makes the conflict visible.

How do you build a job queue in PostgreSQL?

Select a batch with FOR UPDATE SKIP LOCKED, so each worker locks different rows and never waits for another worker, then update the status in the same transaction:

SQL
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running'
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

Add retries, a visibility timeout and a partial index on status = 'queued'. See locking and the job queue project.

How does PostgreSQL detect deadlocks?

When a session waits on a lock for longer than deadlock_timeout (1 second by default), PostgreSQL checks the wait graph for a cycle. If it finds one, it aborts one transaction with an error and the others continue. Applications should retry. Prevent deadlocks by always locking rows and tables in a consistent order.

Operations and scaling

Streaming replication versus logical replication?

Streaming (physical) replication ships the WAL byte for byte, so the replica is an identical copy of the whole cluster, read-only, on the same major version. Logical replication decodes changes into row-level events, so you can replicate chosen tables, between different major versions, into a writable database. Use streaming for high availability and read replicas, and logical for upgrades, selective sync and integrations. See replication.

What is the danger of an unused replication slot?

A slot makes the primary keep every WAL segment the consumer has not yet received. If the replica or subscriber is down or abandoned, WAL accumulates until the disk is full and the primary stops. Monitor pg_replication_slots and drop slots nobody uses, or set max_slot_wal_keep_size as a safety limit.

How do synchronous and asynchronous replication differ?

With asynchronous replication, the primary commits without waiting for replicas, so it is fast but a crash can lose the latest commits. With synchronous replication, commit waits until at least one named standby has confirmed the WAL, which prevents data loss but adds latency and can stall writes if the standby is unavailable.

How do you back up PostgreSQL, and what is point-in-time recovery?

Logical backups with pg_dump are portable and per database, but slow to restore at scale. Physical backups copy the data directory (pg_basebackup or tools such as pgBackRest). Add continuous WAL archiving and you can restore the base backup and replay WAL up to any moment, for example one minute before someone ran DELETE without a WHERE. A backup you have never restored is only a hope: test restores regularly. See backup and recovery.

Why use a connection pooler such as PgBouncer?

Each PostgreSQL connection is a separate server process with its own memory, so thousands of connections waste memory and slow scheduling. A pooler holds a small number of real connections and multiplexes many clients over them. In transaction pooling mode, session features such as session-level prepared statements, advisory locks and SET do not carry across transactions. See connection pooling.

Design

When would you use JSONB instead of columns?

For data whose shape really varies, is mostly stored and retrieved as a whole, or arrives from outside systems. Keep values you filter, join, constrain or aggregate on regularly in real columns, where the planner has statistics and you can add constraints and foreign keys. A hybrid design (columns for the stable core, jsonb for extras, a GIN index only if needed) is common. See JSONB in practice.

When does partitioning help?

When a table is so large that you want to drop old data cheaply (detach or drop a partition instead of a huge DELETE), keep indexes small, or let queries filtering on the partition key skip irrelevant partitions (pruning). It does not make every query faster, and queries that do not filter on the key touch every partition. See declarative partitioning.

How would you isolate tenants in a shared schema?

Put tenant_id on every table, include it in keys and indexes, and enforce isolation with row-level security policies that compare it with a per-session setting, so a missing WHERE in application code cannot leak data. Remember that table owners and superusers bypass policies unless you force them. See row-level security.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does an UPDATE in PostgreSQL leave work for VACUUM?

    MVCC keeps the old version so concurrent snapshots stay consistent. Vacuum later reclaims the dead versions.
  2. 2. A query uses WHERE lower(email) = '[email protected]' and a plain index on email exists. What happens?

    The index stores email, not lower(email). Create an index on the expression, or compare using a case-insensitive type or collation.
  3. 3. What is the main risk of an abandoned replication slot?

    The primary must retain all WAL the slot has not yet confirmed. Monitor slots and drop unused ones.
  4. 4. Which approach stops write skew?

    Write skew is a serialization anomaly. Only Serializable detects it automatically; otherwise lock the data the decision depends on.
  5. 5. Why is OFFSET a poor choice for deep pagination?

    Cost grows with the offset. Keyset pagination seeks straight to the next page using an index.

Summary

  • MVCC gives readers and writers independence by keeping row versions; vacuum reclaims dead ones and freezes old ones to avoid transaction ID wraparound.
  • Prefer timestamptz, text and identity columns; remember that NOT IN with a NULL matches nothing.
  • Pick the index type for the data (B-tree, GIN, GiST, BRIN), index only what queries need, and read plans with EXPLAIN (ANALYZE, BUFFERS).
  • Default isolation is Read Committed; Serializable detects write skew and needs retries; SKIP LOCKED builds simple queues.
  • Streaming replication copies the cluster, logical replication copies chosen tables; unused slots can fill the disk.
  • Back up with base backups plus archived WAL for point-in-time recovery, and test restores.
  • Use poolers for many clients, partitions for lifecycle and pruning, and RLS for multi-tenant safety.

Next: PostgreSQL Cheat Sheet, the whole track on one page.