Database engineer coding interview questions

As asked

You find application code that runs SELECT * FROM orders WHERE user_id = $1 in a loop for each user. The loop runs 5,000 times per request. Rewrite this as a single query and explain the join strategy the database should choose.

Sample answer outline

Replace the loop with SELECT o.* FROM orders o WHERE o.user_id = ANY($1) where $1 is an array of all user IDs, or join directly to a users query. The planner should choose a hash join on user_id if the user list is large, using the user_id array as the build side. The candidate should mention adding an index on orders.user_id and ensuring statistics are fresh so the planner estimates join cardinality correctly.

Reference implementation (sql)

-- Before: N+1 pattern
FOR user_id IN (SELECT id FROM users WHERE active = true) LOOP
  SELECT * FROM orders WHERE user_id = user_id;
END LOOP;

-- After: single query
SELECT o.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = true;

Expect these follow-ups

• When would you use a lateral join instead of a plain join here?

As asked

Given a ledger table with (account_id, txn_date, amount), write a query that returns each row plus a running balance per account ordered by txn_date without using a self-join or subquery per row.

Sample answer outline

Use SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The candidate should note that using the default RANGE frame instead of ROWS can produce incorrect results when multiple rows share the same txn_date. They should also mention indexing (account_id, txn_date) to allow an index scan that avoids a sort.

Reference implementation (sql)

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM ledger
ORDER BY account_id, txn_date;

Expect these follow-ups

• How would you make this query idempotent if there are ties on txn_date?

As asked

You receive a stream of events with (event_id, user_id, payload, created_at) and must insert them exactly once, updating payload if the event_id was already seen. Write the SQL and explain how it behaves under concurrent inserts of the same event_id.

Sample answer outline

Use INSERT ... ON CONFLICT (event_id) DO UPDATE SET payload = EXCLUDED.payload. Under concurrent inserts with the same event_id, one transaction will succeed and the other will hit the conflict branch and update instead. The candidate should note that a unique index on event_id is required, and that ON CONFLICT DO NOTHING is appropriate when updates are not needed (pure idempotent insert). They should also mention that in high-concurrency scenarios, the upsert still serializes on the row lock.

Reference implementation (sql)

INSERT INTO events (event_id, user_id, payload, created_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (event_id)
DO UPDATE SET
  payload = EXCLUDED.payload,
  updated_at = now()
WHERE events.payload IS DISTINCT FROM EXCLUDED.payload;

Expect these follow-ups

• How does this differ from MySQL's INSERT ... ON DUPLICATE KEY UPDATE?

As asked

A deploy script is stuck waiting for an ALTER TABLE lock. Write a PostgreSQL query against system catalogs that shows you the blocking PID, query text, and how long it has been running.

Sample answer outline

Join pg_stat_activity to pg_locks and self-join pg_locks to find the blocking PID. The query filters for wait_event_type='Lock' in pg_stat_activity and matches the granted=true lock on the same relation. The candidate should know that pg_blocking_pids(pid) is a built-in convenience function in PostgreSQL 9.6+ that returns the array of PIDs blocking a given PID, simplifying the query considerably.

Reference implementation (sql)

SELECT
  blocked.pid            AS blocked_pid,
  blocked.query          AS blocked_query,
  blocker.pid            AS blocker_pid,
  blocker.query          AS blocker_query,
  now() - blocker.query_start AS blocker_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Expect these follow-ups

• When would you use pg_cancel_backend() versus pg_terminate_backend()?

As asked

A categories table has (id, name, parent_id). Write a query that returns every descendant of category id=5 at any depth, along with the depth level and full path as a string.

Sample answer outline

Use a recursive CTE: the anchor selects the root row and the recursive term joins back to the CTE on parent_id = id. Accumulate depth as an integer incremented per recursion and path as a text concatenation. The candidate should mention adding a CYCLE detection clause (PostgreSQL 14+) or a MAX depth guard to handle accidental circular references in the data.

Reference implementation (sql)

WITH RECURSIVE descendants AS (
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories
  WHERE id = 5
  UNION ALL
  SELECT c.id, c.name, c.parent_id, d.depth + 1, d.path || ' > ' || c.name
  FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT id, name, depth, path
FROM descendants
ORDER BY path;

Expect these follow-ups

• How would you optimize this for a table with millions of categories?

As asked

A payments table has a payment_id serial column. Payments can be voided but never deleted. Write a query to find all missing payment_ids (gaps in the sequence) between the minimum and maximum existing IDs.

Sample answer outline

Generate the full series with generate_series(min_id, max_id) and LEFT JOIN to the payments table, returning rows where the join produces NULL. An alternative is a LAG/LEAD window approach: LAG(payment_id) OVER (ORDER BY payment_id) gives the previous ID, and a gap exists where the difference is greater than 1. The generate_series approach is simpler but may be slow on very large ranges; the window function approach reads only existing rows.

Reference implementation (sql)

SELECT gs.id AS missing_id
FROM generate_series(
  (SELECT MIN(payment_id) FROM payments),
  (SELECT MAX(payment_id) FROM payments)
) AS gs(id)
LEFT JOIN payments p ON p.payment_id = gs.id
WHERE p.payment_id IS NULL;

Expect these follow-ups

• How would you adapt this to find gaps in a date series where some dates are expected to be absent (weekends)?

As asked

A sales table has (region, month, revenue). Write a query that outputs one row per region with separate columns for each of the 12 months. You may use either the crosstab extension or conditional aggregation.

Sample answer outline

Conditional aggregation uses SUM(CASE WHEN month=1 THEN revenue END) AS jan repeated for each month in a GROUP BY region. The tablefunc crosstab() function is more concise but requires the tablefunc extension and the input query to be pre-sorted. The candidate should note that conditional aggregation is more portable and easier to read but verbose for many columns, while crosstab is compact but harder to debug.

Reference implementation (sql)

SELECT
  region,
  SUM(CASE WHEN month = 1  THEN revenue ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2  THEN revenue ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 3  THEN revenue ELSE 0 END) AS mar
  -- ... repeat for months 4-12
FROM sales
GROUP BY region
ORDER BY region;

Expect these follow-ups

• How would you generate this query dynamically in Python if the number of months is not known ahead of time?

As asked

A query_log table stores (query_id, duration_ms, logged_at). Write a single query that returns the p50, p95, and p99 of duration_ms per day using ordered-set aggregate functions.

Sample answer outline

Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration_ms), PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms), and PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) in a GROUP BY DATE(logged_at). The candidate should mention that PERCENTILE_CONT interpolates between values (continuous), while PERCENTILE_DISC returns the nearest actual value. For large tables, approximate percentiles via pg_tdigest are faster.

Reference implementation (sql)

SELECT
  DATE(logged_at) AS day,
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY duration_ms) AS p50,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration_ms) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99
FROM query_log
GROUP BY DATE(logged_at)
ORDER BY day;

Expect these follow-ups

• How does the time complexity of an exact percentile calculation compare to an approximate one?

As asked

Write a Python function that executes a block of database operations under SERIALIZABLE isolation and retries automatically on serialization failure (error code 40001), with exponential backoff and a maximum retry count.

Sample answer outline

The function should set the isolation level to SERIALIZABLE, catch psycopg2.errors.SerializationFailure (SQLSTATE 40001), roll back the transaction, wait for an exponentially increasing delay with jitter, and retry up to a configurable max_retries. The candidate should note that the entire transaction must be re-executed, not just the failing statement, and that the connection must be in a clean state (no open transaction) before each retry.

Reference implementation (python)

import time, random, psycopg2

def run_serializable(conn, fn, max_retries=5):
    for attempt in range(max_retries):
        try:
            with conn:
                conn.set_isolation_level(
                    psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
                )
                return fn(conn)
        except psycopg2.errors.SerializationFailure:
            if attempt == max_retries - 1:
                raise
            delay = (2 ** attempt) * 0.05 + random.uniform(0, 0.01)
            time.sleep(delay)

Expect these follow-ups

• What is the difference between a serialization failure and a deadlock in PostgreSQL?

As asked

You need to load 100 million rows from CSV files into a PostgreSQL table as quickly as possible. Walk through the techniques you would use and the order in which you apply them.

Sample answer outline

Use COPY rather than INSERT; COPY is 10x+ faster because it bypasses the SQL parser and uses a binary or text protocol. Drop indexes and foreign key constraints before the load, then rebuild them afterward (CREATE INDEX is faster on existing data than maintaining it per-row during load). Set maintenance_work_mem high for the index build. Consider unlogged tables if crash safety is acceptable during the load window. Use parallel COPY streams if the file can be split. After loading, run ANALYZE to refresh statistics.

Reference implementation (sql)

-- Drop indexes before load
DROP INDEX IF EXISTS idx_events_user_id;
DROP INDEX IF EXISTS idx_events_created_at;

-- Load via COPY
COPY events (user_id, event_type, payload, created_at)
FROM '/data/events_2024.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Rebuild indexes in parallel
SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY idx_events_user_id ON events (user_id);
ANALYZE events;

Expect these follow-ups

• How does pg_bulkload differ from COPY and when would you use it?