yoklainterview sim
ProgressHistory

PostgreSQL Database Interview Questions

450 verified PostgreSQL Database interview questions — solve with answers, learn from explanations, test yourself in a real simulation.

Try the real simulation →

Sample questions

Pg IndexesDifficulty 1
A plain B-tree index handles equality, ranges, BETWEEN, and ORDER BY well. Which predicate does a plain B-tree index NOT serve, so PostgreSQL falls back to a sequential scan even when the column is indexed?
  • aprice = 100
  • bprice BETWEEN 50 AND 150
  • cprice > 100
  • dtags @> '{"role": "admin"}'::jsonb✓
Explanation:A B-tree stores sortable scalar values, so it directly supports =, <, >, BETWEEN and range comparisons. The @> containment operator on jsonb needs an index type that understands nested structure — GIN — not a B-tree. Options (a)-(c) are exactly the comparisons a B-tree is built for.
Pg IndexesDifficulty 2
CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_id int
);

Right after this statement runs — before any explicit CREATE INDEX — what does pg_indexes show for orders?
  • aNothing yet, because PRIMARY KEY is only a constraint and index creation is deferred until the first insert
  • bOne unique B-tree index named orders_pkey on id, created automatically to back the primary key✓
  • cA non-unique index on id, since uniqueness for a primary key is enforced by a trigger, not an index
  • dOne index covering both id and customer_id, since PostgreSQL indexes every declared column by default
Explanation:Declaring PRIMARY KEY makes PostgreSQL create a unique B-tree index immediately, named <table>_pkey by default; pg_indexes shows it as soon as the CREATE TABLE finishes. There is no trigger involved and no deferral, and customer_id gets no index unless you ask for one — confirmed against a live orders table in this run.
Pg IndexesDifficulty 2
ALTER TABLE users ADD CONSTRAINT users_email_uniq UNIQUE (email);

What does this statement actually do at the storage level?
  • aIt creates a unique B-tree index named users_email_uniq on email, which then enforces the constraint✓
  • bIt records the rule in the catalog only; uniqueness is checked row by row on each write without any index
  • cIt requires an index on email to already exist, otherwise the statement is rejected outright
  • dIt creates a non-unique index and relies on a separate check constraint to reject duplicate values
Explanation:A UNIQUE constraint in PostgreSQL is implemented by creating a unique index behind the scenes — here named users_email_uniq, visible in pg_indexes as USING btree (email). It doesn't require a pre-existing index, and it's the index itself doing the enforcement, not a separate trigger or check constraint — confirmed against pg_indexes in this run.
Pg IndexesDifficulty 2
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

A report runs SELECT * FROM orders WHERE order_date > '2024-06-01' — filtering only on the second column — and EXPLAIN shows a sequential scan. Why doesn't the composite index help here?
  • aComposite indexes in PostgreSQL only ever accelerate ORDER BY, never a plain WHERE filter
  • border_date is a date column, and composite B-tree indexes cannot store dates as a non-leading key
  • cIt's sorted by customer_id first; without that filter it can't jump to the right order_date range✓
  • dThe index only activates once the table exceeds a fixed row-count threshold that hasn't been reached yet
Explanation:A composite B-tree on (customer_id, order_date) sorts rows by customer_id, and within each customer_id by order_date. Skipping the leading column means matching order_date values are scattered across the whole index, so the planner can't use it efficiently and falls back to a scan — the classic 'leftmost prefix' rule. There's no row-count threshold (d) and no restriction tied to the date type (b).
Pg IndexesDifficulty 2
Same index, idx_orders_cust_date ON orders (customer_id, order_date). Running EXPLAIN (ANALYZE, BUFFERS) on:
SELECT * FROM orders
WHERE customer_id = 42 AND order_date > '2024-06-01';

produces:
Bitmap Heap Scan on orders
  Recheck Cond: (customer_id = 42) AND (order_date > '2024-06-01')
  ->  Bitmap Index Scan on idx_orders_cust_date
        Index Cond: (customer_id = 42) AND (order_date > '2024-06-01')

What does this confirm about the index?
  • aThe index is being used only for customer_id, and order_date is filtered afterward by scanning the whole table
  • bBoth predicates are applied inside the index scan itself, not just customer_id✓
  • cPostgreSQL silently created a second, temporary index to cover order_date for this one query
  • dThe Bitmap Heap Scan step means the index was actually not used, and the table was scanned sequentially instead
Explanation:Index Cond lists both predicates, meaning the bitmap index scan itself locates matching rows using customer_id = 42 and the order_date range together — the leftmost-prefix condition on customer_id is what unlocks efficient use of the second key. Bitmap Heap Scan is the step that fetches the actual rows the bitmap pointed to; it's driven by the index, not a substitute for one, and no temporary index is created.
Pg IndexesDifficulty 2
A developer writes a migration:
BEGIN;
CREATE INDEX CONCURRENTLY idx_orders_amount ON orders (amount);
COMMIT;

and it fails immediately with an error. What happens, and why?
  • aThe migration succeeds but silently falls back to a regular blocking CREATE INDEX, ignoring CONCURRENTLY
  • bIt fails because amount is a numeric column, and CONCURRENTLY only supports indexing integer and text columns
  • cIt fails with CREATE INDEX CONCURRENTLY cannot run inside a transaction block✓
  • dIt fails because a table can only ever have one CONCURRENTLY-built index at a time across the whole database
Explanation:CREATE INDEX CONCURRENTLY performs several separate internal scans and commits between them so it can avoid holding a long write-blocking lock; that's incompatible with running inside a BEGIN/COMMIT block, which PostgreSQL rejects with exactly this error message. The fix is to run the statement on its own, outside any explicit transaction. There's no such per-database limit (d) or type restriction (b), and it does not silently downgrade (a) — confirmed by running it live and reading the error text.

By seniority

Database JuniorDatabase MidDatabase Senior

Other fields

BackendFrontendFullstackDevOps / CloudAI EngineerMobileSecurityQA / Test AutomationData EngineerGame DevelopmentML Engineer

Test yourself against the 2475-question Database bank.

Start interview