68 PostgreSQL interview questions with answers

All PostgreSQL interview questions

• What is PostgreSQL and when should you choose it?

  PostgreSQL is an open-source relational database — data lives in tables with clear columns and strict constraints. Key strengths: standard SQL, ACID transactions, many index types for fast queries, and the JSONB type that lets you store flexible NoSQL-style data inside a relational DB. ACID…

• How are primary keys, unique keys and foreign keys different?

  All three are constraints but serve different purposes: - Primary key: uniquely identifies each row (one id per user). It is the column other tables usually point to. - Unique key: ensures one (or several) columns are not duplicated, e.g. email cannot repeat — but…

• How should you understand `NULL` in PostgreSQL?

  NULL means "unknown / missing", not an empty string, zero or false. Because it is "unknown", comparing with = NULL always gives an undefined result (NULL, not true) — use IS NULL / IS NOT NULL instead. WHERE keeps only rows whose condition is true,…

• What problem do transactions solve in PostgreSQL?

  A transaction groups several statements into one all-or-nothing block: COMMIT saves every change durably, ROLLBACK discards everything as if it never ran. Other transactions never see your uncommitted data, even at the Read Uncommitted isolation level (PostgreSQL treats it as Read Committed). Money transfer example:…

• How are constraints different from indexes?

  They are easy to mix up because some constraints create an index, but their roles differ: a constraint is a data rule (primary key, foreign key, unique, check, not null), while an index is a structure that makes finding/reading faster. A unique constraint usually creates…

• What do indexes help with in PostgreSQL and what are the trade-offs?

  An index is like the index at the back of a book: instead of flipping through every page to find a word, the database jumps straight to the right row. This makes filtering (WHERE), joins and sorting (ORDER BY) much faster, and unique lookups almost…

• When should you use B-tree, GIN, GiST and BRIN indexes?

  PostgreSQL has several index types, each suited to a kind of data/query: - B-tree (default): great for equality, ranges (<, , BETWEEN) and sorting on ordinary values (numbers, text, dates). - GIN: for "many values in one cell" data such as JSONB, arrays and full-text…

• How important is column order in a composite index?

  In a B-tree composite index (multi-column index), the columns are sorted in the order you declared them, like a phone book sorted by "last name then first name". Looking up by last name is fast. Looking up by first name alone gets little help from…

• When should you use a partial index?

  A partial index indexes only rows matching a condition instead of the whole table. It fits well when queries usually care about a small subset: active records, unprocessed jobs, or rows not yet soft-deleted. Example — enforce unique email, but only for non-soft-deleted users: Being…

• How are `EXPLAIN` and `EXPLAIN ANALYZE` different?

• How are PostgreSQL isolation levels different?

  An isolation level decides how much of another transaction's changes a transaction can see while they run in parallel. PostgreSQL has three real levels; Read Uncommitted is treated as Read Committed, so dirty reads never happen. Level When the snapshot is taken Non-repeatable read Phantom…

• When should you use `SELECT FOR UPDATE`?

  SELECT ... FOR UPDATE locks the rows you selected: another transaction that wants to UPDATE, DELETE or SELECT ... FOR UPDATE those same rows has to wait until you COMMIT or ROLLBACK. Use it when the app reads a value, checks a condition in code,…

• How is `SKIP LOCKED` used to build a job queue?

• How should you choose between UUID, serial and identity columns?

  Three ways to make an auto-generated primary key: - serial: an old shorthand that implicitly creates a sequence and attaches it as the default value. Still works but is no longer the recommended way. - GENERATED ... AS IDENTITY: the SQL-standard way, preferred for integer…

• How are `json` and `jsonb` different?

  json stores the raw JSON text and re-parses it on every use. jsonb stores a pre-parsed binary form — it loses original formatting and key order, but querying and indexing are much faster. Most applications should use jsonb. Example searching by JSON content (the "contains"…

• When should you normalize instead of using JSONB?

  Choose normalize (split into separate columns/tables) when a field needs joins, frequent filtering, constraints/foreign keys, independent updates, or reporting. Choose JSONB when the shape changes often, deep querying is rare, metadata changes constantly, or you must keep a payload from an external system as-is. Practical…

• When should you use generated columns in PostgreSQL?

  A generated column is one whose value is computed automatically from other columns, so you don't repeat that logic in the app and can query it directly. It fits fixed formulas that belong to the data: a full name, a normalized key, or a precomputed…

• When should you use window functions in PostgreSQL?

  A window function computes over a group of related rows but does not collapse them like GROUP BY — you keep every row and gain a computed column. It fits ranking, running totals, moving averages, deduplication, or comparing the current row with a previous one.…

• What is an index-only scan and why might it not happen?

• How do `ANALYZE` and statistics affect query plans?

  ANALYZE collects statistics about each column's data distribution: how many distinct values it has, which values are most common, the NULL fraction. The planner uses these to estimate how many rows each step of a query returns, then picks indexes, join types and join order.…

• Why is connection pooling important in PostgreSQL?

  Each connection to PostgreSQL is a separate, fairly heavy process (it uses RAM). If the app opens too many, the DB can run out of memory, the CPU spends time context-switching and latency worsens. A connection pool fixes this by keeping a set of connections…

• What types of replication does PostgreSQL have?

• What do backups and PITR need in PostgreSQL?

• How do you run PostgreSQL schema migrations with low downtime?

  Low-downtime migrations mean avoiding commands that hold a strong lock for long or rewrite the whole table at peak hours. ALTER TABLE needs a table lock, and if it has to wait behind a long transaction, every query arriving after it queues up behind it…

• How does soft delete affect indexes and constraints in PostgreSQL?

  Soft delete means not really deleting a row but marking it as deleted, usually with a deletedat column. As a result almost every query must add WHERE deletedat IS NULL, and this affects both indexes and unique constraints. The biggest problem is the unique constraint.…

• How do you use UPSERT in PostgreSQL?

  UPSERT means "update if it exists, insert if not". In PostgreSQL you write it as INSERT ... ON CONFLICT: when the insert hits a unique constraint/index, you choose to update the existing row instead of erroring, or to skip it. The first run inserts. Running…

• When is `RETURNING` useful in PostgreSQL?

  RETURNING lets INSERT, UPDATE, DELETE (and MERGE since PostgreSQL 17) hand back the affected rows directly, with no second query. Handy for grabbing a generated id, logging the changed rows, or updating and returning the response straight to the client. Double benefit: fewer round trips…

• When is PostgreSQL full-text search enough?

• When should you use partitioning in PostgreSQL?

• When should you use advisory locks in PostgreSQL?

• Why might PostgreSQL use a sequential scan even when an index exists?

• What is MVCC in PostgreSQL?

• When should you use CTEs in PostgreSQL?

  A CTE (the WITH clause) gives a subquery a name so you can split a complex query into readable steps, or write a recursive query. The recursive query takes employee 1, then keeps finding people whose managerid is a row it just found, until no…

• Should you use enum types in PostgreSQL?

  Enum in PostgreSQL restricts a column to a fixed set of values and makes the schema self-describing: looking at the data type tells you which states exist. An enum value takes 4 bytes and compares by declaration order. Adding a value (with BEFORE/AFTER to control…

• Do read replicas solve every read scaling problem?

• How do you usually handle N+1 queries with PostgreSQL?

• What are the choices for PostgreSQL multi-tenant design?

• How do you write a monthly revenue report where **months with no orders still show a zero row**?

  GROUP BY only emits rows for data that already exists — a month with no orders has nothing to group. The standard approach is to generate the date range yourself and LEFT JOIN the data onto it. Three details that decide correctness: - The LEFT…

• What does PostgreSQL's `DISTINCT ON` do? Compared with `ROW_NUMBER()` for latest-row-per-group, which should you pick?

• How do you query a multi-level category tree with a recursive CTE, and how do you prevent infinite loops?

• Is a CTE an "optimization fence" in PostgreSQL? What are `MATERIALIZED` / `NOT MATERIALIZED` for?

• For bulk updates from another table, should you use `UPDATE ... FROM` or `MERGE`? How do they differ from `INSERT ... ON CONFLICT`?

• What is an expression index? How do you use it for case-insensitive email lookup?

  An expression index indexes the result of an expression rather than the raw column value. It solves exactly the case where a condition must wrap the column in a function. Matching rule: the expression in WHERE must match the one used at CREATE INDEX time.…

• How do you read `EXPLAIN ANALYZE` output? What is the difference between `cost` and `actual time`, and what does a row estimate mismatch tell you?

  Each line is a node; more deeply indented nodes run first. Read inside-out. - cost=start..total is the planner's estimate in internal units (not milliseconds). The first number is the cost to return the first row, the second to return the last. Useful only for comparing…

• What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan? When does the planner pick a bitmap?

  These are three ways to fetch rows from a table, differing in their disk access pattern. Seq Scan — reads the whole table sequentially in page order. No index needed. Fastest when most of the table is needed, because sequential reads are far cheaper than…

• You need to add an index to a 200-million-row table in production. How do you avoid locking it?

  A plain CREATE INDEX takes a SHARE lock on the table — blocking all INSERT/UPDATE/DELETE until the index is built. On a large table that lasts tens of minutes, effectively a write outage. Use CONCURRENTLY: It scans the table twice and waits for older transactions…

• What is index bloat? Why does an index grow even when the row count is stable, and how do you fix it?

• How do you find redundant or unused indexes so you can drop them?

• What is a HOT update in PostgreSQL? Why can adding one index collapse write throughput on an update-heavy table?

• How does PostgreSQL store multiple versions of a row? What are `xmin` and `xmax`?

  PostgreSQL never updates a row in place. Every UPDATE writes a new version of the row (a new tuple) and marks the old version as expired. DELETE only marks; it does not free the bytes immediately. Each tuple carries two system columns: - xmin —…

• What is a dead tuple? What does table bloat cause and how do you detect it?

  A dead tuple is a row version that was replaced by an UPDATE or marked by a DELETE, and that no transaction needs to see anymore. It comes from MVCC (multiple versions of data coexist so reads and writes do not block each other): an…

• What does autovacuum do? When does it fall behind?

  Autovacuum is a background process that cleans tables automatically. UPDATE and DELETE do not remove the old row right away but leave a dead tuple, an old row version no transaction needs anymore (a consequence of MVCC). Autovacuum runs two jobs per table: 1. VACUUM…

• What is transaction ID wraparound? Why can it stop the database from accepting writes?

• How does `VACUUM FULL` differ from a plain `VACUUM`? Why avoid it on a live table?

  The two commands solve different problems. VACUUM (and autovacuum): marks dead-tuple space as reusable by that same table. The on-disk file barely shrinks. It only takes a SHARE UPDATE EXCLUSIVE lock — SELECT, INSERT, UPDATE, DELETE keep running. VACUUM FULL: rewrites the entire table into…

• What is TOAST in PostgreSQL? How does it affect performance for large values?

• Which operators let a `jsonb` query use an index? When should data not go into `jsonb`?

  A plain B-tree does not help for searching inside a document. jsonb needs a GIN index, and only certain operators can use it. - @ (containment) and ?, ?, ?& (key existence) work with the default GIN opclass (jsonbops). - jsonbpathops is smaller and faster…

• A 500M-row log table is only queried for the last 30 days — how would you partition it by time? What does partition pruning require?

  Use declarative range partitioning on the timestamp column, one partition per month (or per week under heavy write volume). The main win is not faster queries but data lifecycle: dropping old data with drop table requestlogs202601 is instant and creates no dead tuples, unlike DELETE-ing…

• Why does each PostgreSQL connection cost resources? Is setting `max_connections` high a problem?

  PostgreSQL uses a process-per-connection model: every connection is a separate OS process, not a thread. Per-connection costs: - Private backend memory: workmem per sort/hash operation, tempbuffers, catalog cache, plan cache. An idle connection still holds several MB. - Scheduler context-switching once process count far exceeds…

• How does PgBouncer transaction mode work? Why do prepared statements and session state break under it?

• How does streaming replication work? How do you measure replication lag?

  Streaming replication is physical replication: the primary writes changes to WAL, the standby opens a connection and continuously receives the WAL stream, then replays each record to keep a byte-identical copy. WAL position states on the standby, in order: - sentlsn — how far the…

• A user creates an order, then the list page does not show it because reads go to a replica. How do you handle this flow?

• How does logical replication differ from physical replication? When would you use it?

  Physical (streaming) replication copies WAL at the byte level: the standby is an exact copy of the whole cluster, same major version, read-only, no table selection. Logical replication decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and ships them to subscribers via publications and subscriptions. Core differences:…

• Production is slow but you do not know which query is responsible. How do you use `pg_stat_statements`?

  pgstatstatements is an extension that aggregates cumulative statistics per query shape — it normalizes parameters, so where id = 1 and where id = 2 are counted together. (The extension must be in sharedpreloadlibraries, so it needs a one-time restart.) Find the queries consuming the…

• An `ALTER TABLE` on production froze the whole app even though the table is small. How do you diagnose it with `pg_locks`?

• How do `statement_timeout` and `idle_in_transaction_session_timeout` differ? What values should you set?

  The two settings stop two different failure modes. statementtimeout — cancels a statement that runs too long. It protects against bad plans, accidental full scans, and heavy reports landing at peak hours. idleintransactionsessiontimeout — terminates a session that has issued BEGIN but is sitting idle,…

• How do `SELECT ... FOR UPDATE`, `FOR NO KEY UPDATE`, and `FOR SHARE` differ?

  All three take an explicit row-level lock inside a SELECT; they differ in strength and in who they block. - FOR UPDATE — the strongest. It reserves rows for your own update or delete; any other transaction wanting FOR UPDATE, FOR SHARE, UPDATE, or DELETE…

• Several workers pull jobs from one queue table — how do `SKIP LOCKED` and `NOWAIT` solve it?

  With a plain SELECT ... FOR UPDATE every worker targets the same oldest row, so only one proceeds and the rest queue behind it. Two modifiers change the waiting behavior: - SKIP LOCKED — skips rows currently locked and takes the next free one. This…

• Why does PostgreSQL `REPEATABLE READ` raise "could not serialize access", and how must the application handle it?