Backend engineer databases interview questions

As asked

A page loads in 4 seconds. The database shows 200 queries per page load. You suspect an ORM N+1. Walk me through how you would fix it.

Sample answer outline

Confirm the diagnosis: turn on query logging, look at the call site that generates the chatty queries. Usually a loop that accesses a lazy-loaded relation. Fix with the ORM's eager loading primitive (include, with, populate, prefetch_related). Alternatively, batch the lookup with a single IN query. After the fix, verify the page is one or two queries. Watch for the second-order N+1 where the eagerly loaded set itself has a lazy relation. For React/Node: use a dataloader to batch requests inside a request scope.

Reference implementation (typescript)

// Bad: N+1 (one query per post for its author)
const posts = await prisma.post.findMany({ where: { feed: feedId } });
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
  // ...
}

// Good: eager load in a single query
const posts = await prisma.post.findMany({
  where: { feed: feedId },
  include: { author: true },
});

// Alternative: batch with DataLoader inside a request scope
const userLoader = new DataLoader<string, User>(async (ids) => {
  const users = await prisma.user.findMany({ where: { id: { in: [...ids] } } });
  const byId = new Map(users.map((u) => [u.id, u]));
  return ids.map((id) => byId.get(id)!);
});

Expect these follow-ups

• Why does an ORM lazy-load by default?
• When is a JOIN slower than two queries?
• How would you catch an N+1 in CI before it ships?

As asked

A Postgres query on a 200 million row orders table takes 40 seconds. How do you read the query plan and decide what index to add?

Sample answer outline

Use EXPLAIN ANALYZE to compare estimated rows with actual rows, identify sequential scans, expensive sorts, nested loops over large inputs, and whether time is spent reading buffers or computing. Design the index around the predicate, join, and ordering pattern, not around every column in the table. For common queries, a composite or partial index may beat separate single-column indexes, but it must match selectivity and column order. Check write overhead, bloat, and whether the index supports index-only scans through included columns. Candidates often add indexes blindly without validating the plan before and after.

Expect these follow-ups

• When would a sequential scan be the right plan?
• How do stale statistics affect the optimiser?
• What is the tradeoff between a composite index and two single-column indexes?

As asked

You notice your API response time degrades linearly with the number of results returned. You suspect an N+1 query. How do you confirm the diagnosis, and what are the two or three concrete fix strategies you would apply in a Node.js or Go service backed by Postgres?

Sample answer outline

Confirm by logging all SQL queries with durations; a pattern of many identical queries with varying IDs is the signature. Fix strategies: (1) eager loading with a JOIN or a separate IN query to fetch related records in bulk; (2) a DataLoader-style batching layer that collects IDs within a tick and issues one query per batch; (3) denormalization or precomputed fields if the relationship is always queried together. A strong answer also mentions that ORM magic can hide N+1 problems and that query-level logging in development is non-negotiable.

Expect these follow-ups

• How would you implement a DataLoader-style batcher in Node.js without a library?
• When is a JOIN worse than two separate queries?

As asked

Name the four standard SQL isolation levels and tell me which anomaly each one prevents. Then tell me what isolation level you would use for a bank transfer and why.

Sample answer outline

Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable reads. Repeatable Read prevents non-repeatable reads but allows phantom reads. Serializable prevents all three anomalies. For a bank transfer, Repeatable Read or Serializable is appropriate because you need to read balances and ensure they do not change between your read and your write. A strong answer notes that Postgres's Repeatable Read actually prevents most phantom reads due to MVCC, and that Serializable Snapshot Isolation in Postgres is highly concurrent compared to traditional locking-based serializable.

Expect these follow-ups

• What is a write skew anomaly and which isolation level prevents it?
• How does Postgres implement Serializable Snapshot Isolation without full table locks?

As asked

You are designing a fiat-to-crypto purchase flow at Coinbase. The flow debits a bank account, credits a custodial crypto wallet, and records a transaction ledger entry. Walk me through which ACID properties matter most at each step and how you would use Postgres transactions to guarantee consistency.

Sample answer outline

A strong answer clearly maps each ACID property: Atomicity ensures all three writes succeed or none do; Isolation prevents another transaction from reading a partial state (candidate should mention isolation levels, ideally READ COMMITTED vs SERIALIZABLE and their tradeoffs); Durability ensures a committed purchase survives a crash. The candidate should note that the bank debit is an external system and cannot be part of the same DB transaction, so they need a saga or two-phase commit pattern for that boundary.

Expect these follow-ups

• When would you choose SERIALIZABLE isolation in Postgres versus READ COMMITTED, and what is the performance cost?
• How do you handle a scenario where the bank debit succeeds but your DB write fails?

As asked

You have a Postgres table with 500 million rows of crypto transactions. A query that filters by user_id, asset, and a date range is taking over 3 seconds. EXPLAIN ANALYZE shows a sequential scan. Walk me through your diagnostic process and the steps you would take to fix it.

Sample answer outline

A strong answer starts by reading EXPLAIN ANALYZE output: sequential scan suggests the planner is not using an index, which means either there is no index or the planner thinks it is cheaper than a scan. The candidate should propose a composite index on (user_id, asset, created_at), explain index column ordering (equality columns first, range column last), and mention partial indexes if asset or status has low cardinality. They should also discuss VACUUM/ANALYZE keeping stats fresh and consider table partitioning by date if the table keeps growing.

Expect these follow-ups

• The query is now fast but inserts have slowed down. What tradeoff did you introduce?
• How do you add that index to a production table without locking writes for minutes?

As asked

Delta Lake sits on top of cloud object storage like S3 or GCS and still claims ACID transaction guarantees. Walk me through exactly how that works: what the transaction log is, how concurrent writers are handled, and where the atomicity guarantee actually comes from.

Sample answer outline

A strong answer explains the _delta_log directory of JSON and Parquet checkpoint files that form an ordered, append-only transaction log. Atomicity comes from the atomic put-if-absent semantics of object stores: writers append a new JSON commit file and the first writer to successfully create that file wins. Isolation is provided by optimistic concurrency control: writers read the current log version, write their changes to staged files, then attempt to commit. If a conflict is detected (another writer committed first), the transaction is retried or aborted. Checkpoints are written periodically in Parquet for fast log replay.

Expect these follow-ups

• What happens when two writers try to append to the same partition at the same time?
• How does VACUUM interact with the transaction log and what data loss risk does it introduce?

As asked

Given a DoorDash orders table with columns consumer_id, order_id, restaurant_id, order_total, and created_at, write a single SQL query that returns each consumer_id along with their first order date, their most recent order date, their total number of orders, and their average order value. Then add a column showing how many days have passed since their last order.

Sample answer outline

A clean answer uses GROUP BY with MIN, MAX, COUNT, and AVG aggregates, and calculates days since last order as CURRENT_DATE minus MAX(created_at). A stronger answer mentions that if you also need individual order details alongside aggregates, window functions like FIRST_VALUE and LAST_VALUE with PARTITION BY consumer_id are the right tool. They should know when to use GROUP BY vs. window functions.

Reference implementation (sql)

SELECT
  consumer_id,
  MIN(created_at)                                    AS first_order_date,
  MAX(created_at)                                    AS last_order_date,
  COUNT(*)                                           AS total_orders,
  ROUND(AVG(order_total), 2)                         AS avg_order_value,
  CURRENT_DATE - MAX(created_at)::date               AS days_since_last_order
FROM orders
GROUP BY consumer_id;

Expect these follow-ups

• How would you add a column showing the order total of the consumer's most recent order without a subquery?
• The orders table has 2 billion rows. How do you make this query run in under 5 seconds?

As asked

GitLab has strict rules about database migrations because they run on a live production database with hundreds of millions of rows. You need to add a non-nullable column with a default value to a 200 million row table. Walk me through how you'd do this with zero downtime, following GitLab's migration guidelines.

Sample answer outline

Strong answers describe the three-step GitLab pattern: add the column as nullable first, backfill it in batches using add_column_with_default or a background migration, then add the NOT NULL constraint once all rows are filled. They should mention that adding a NOT NULL constraint with a default causes a full table lock in older Postgres and how GitLab's migration helpers handle this, plus testing via db:check_migrations.

Expect these follow-ups

• How do you handle the window between deploying the migration and the background migration finishing?
• What is the risk if you just use add_column with a default value on a large table in standard Rails?

As asked

You have an Employees table with columns employee_id, name, department_id, and salary. Write a SQL query that returns each employee along with their rank within their department by salary (highest first), and also the difference between their salary and the top salary in their department. Use Oracle SQL syntax.

Sample answer outline

The solution uses RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) for the rank and FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) minus salary for the gap. The candidate should explain the difference between RANK and DENSE_RANK, mention that Oracle SQL uses these ANSI window functions natively, and note that window functions compute per-row without collapsing the result set the way GROUP BY does.

Reference implementation (sql)

SELECT
  employee_id,
  name,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) - salary AS gap_from_top
FROM employees
ORDER BY department_id, dept_rank;

Expect these follow-ups

• How would you rewrite this without window functions, and why is that worse?
• What index would you add to make this query faster on a table with 100 million rows?

As asked

A customer is complaining that their Snowflake queries run slowly and cost too much. Walk me through how Snowflake virtual warehouses, micro-partitioning, and query pruning actually work, and what you would check first to diagnose the problem.

Sample answer outline

A strong answer explains that Snowflake separates compute (virtual warehouses, MPP clusters of cloud compute nodes on AWS, Azure, or GCP) from storage (object-storage-backed columnar micro-partitions of 50 to 500 MB uncompressed each), that each micro-partition stores min/max and null-count metadata, and that the query optimizer uses that metadata to skip irrelevant partitions without reading them. The candidate should then mention checking QUERY_HISTORY for partition scans, looking at whether the clustering key matches the filter predicates, and considering warehouse size relative to concurrency.

Expect these follow-ups

• When would you recommend adding a clustering key and when would you leave the table unclustered?
• How does automatic clustering differ from manual CLUSTER BY, and what does it cost?

As asked

Given an events table with columns (user_id, event_type, created_at), write a SQL query that returns, for each user, only their most recent event of each event_type. If there is a tie on created_at, keep the row with the smaller event_id.

Sample answer outline

A strong answer uses ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY created_at DESC, event_id ASC) and then filters WHERE rn = 1 in a CTE or subquery. The candidate should explain the PARTITION BY and ORDER BY semantics clearly, mention that RANK() would not deduplicate ties and DENSE_RANK() would have the same issue, and discuss whether the CTE or a derived table is preferred for readability in Snowflake.

Reference implementation (sql)

-- events table schema
-- user_id     INT
-- event_id    INT
-- event_type  VARCHAR
-- created_at  TIMESTAMP

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type
      ORDER BY created_at DESC, event_id ASC
    ) AS rn
  FROM events
)
SELECT user_id, event_type, created_at, event_id
FROM ranked
WHERE rn = 1;

Expect these follow-ups

• How would you rewrite this using QUALIFY instead of a subquery, and why might that be cleaner in Snowflake?
• If this events table has billions of rows, what performance considerations matter for this query?

As asked

A query that normally finishes in 30 seconds is now taking 15 minutes. You open the Snowflake Query Profile. Walk me through what you look at, in what order, and what each bottleneck indicator tells you.

Sample answer outline

Start with the most expensive node by execution time in the operator tree. Check for a large Bytes Spilled to Local Storage or Bytes Spilled to Remote Storage value, which means the warehouse ran out of memory and spilled to disk or S3. Look at Partitions Scanned vs Total to see whether pruning is effective. A high percentage of time in Remote Disk IO usually means spilling or reading many micro-partitions. Cartesian join explosion shows up as a very high output-to-input row ratio. Skewed execution shows as a wide gap between max and average time per worker node.

Expect these follow-ups

• You see 90% of execution time is in a single join node with a high spill. What are your options?
• How do you tell the difference between a query that is slow because the warehouse is too small and one that is slow because the SQL is poorly written?

As asked

You have a Postgres table with a JSONB column storing user preferences, a text column you need full-text search on, and a timestamp column for range queries. Which index type do you pick for each and why?

Sample answer outline

B-tree is the default and handles equality and range queries on the timestamp column efficiently. GIN indexes are optimal for JSONB and full-text search because they index individual element values inside composite types, making containment and keyword lookups fast. GiST is more appropriate for geometric data or when you need a lossy index that trades some accuracy for smaller size. A strong answer mentions that GIN indexes are larger and slower to update but much faster to query for these use cases.

Expect these follow-ups

• What is a partial index and when would you add one to this table?
• How does an expression index differ from a regular index?

As asked

Two transactions update the same row at the same time in Postgres. Walk me through exactly what happens at the storage level, how MVCC prevents dirty reads, and what VACUUM has to do with any of this.

Sample answer outline

Postgres uses MVCC by storing multiple versions of a row with xmin and xmax transaction IDs. Each transaction sees a snapshot of the database as of its start time. A write creates a new row version with the writing transaction's xmin; the old version's xmax is set to that transaction ID. Reads follow visibility rules based on the snapshot. VACUUM reclaims dead row versions whose xmax is older than the oldest active transaction, preventing table bloat. A strong answer also covers transaction ID wraparound and why autovacuum matters.

Expect these follow-ups

• What isolation level prevents phantom reads in Postgres, and how does it work?
• How does FOR UPDATE interact with MVCC?

As asked

You are running a Node.js API with 20 pods each using pg-pool. How do you calculate the right pool size per pod, and what symptoms tell you the pool is too large versus too small?

Sample answer outline

Postgres has a hard limit on max_connections (default 100). Total connections = pods x pool size must stay well below max_connections, leaving room for admin and replication connections. A common formula is (2 x CPU cores + effective_spindle_count) for the total Postgres server connections, divided by pod count. Symptoms of too small a pool: high wait time for connections, timeout errors, slow p99. Too large: Postgres context-switching overhead, memory exhaustion on the DB server, and potentially starving other services. A strong answer also mentions PgBouncer as a connection pooler that reduces the need for large per-pod pools.

Expect these follow-ups

• What pooling mode does PgBouncer use by default, and what are the trade-offs of transaction mode vs session mode?
• How do prepared statements interact with PgBouncer in transaction mode?

As asked

Your MongoDB aggregation pipeline runs a $lookup followed by a $group on a collection with 10 million documents and it is taking 30 seconds. Walk me through how you would diagnose and optimize it.

Sample answer outline

Start with explain('executionStats') to see if $match stages early in the pipeline are using indexes. The key optimization is to push $match and $project stages before $lookup to reduce the number of documents being joined. Ensure the foreign collection in $lookup has an index on the join field. For very large result sets, add a $limit before $group where possible. Consider whether the aggregation can be partially pre-computed and stored. A strong answer also mentions the allowDiskUse option for pipelines exceeding 100MB of memory and that pipeline stages have a 100MB working memory limit per stage by default.

Expect these follow-ups

• When would you denormalize data into a single collection to avoid $lookup entirely?
• How does MongoDB handle transactions across multiple collections compared to Postgres?

As asked

I paste you an EXPLAIN ANALYZE output where you see a Seq Scan on a 5-million-row table even though there is an index on the filtered column. What are the possible reasons Postgres chose the sequential scan and how do you investigate each one?

Sample answer outline

Postgres uses a cost model. A sequential scan wins when the planner estimates that the index scan plus random I/O is more expensive, often because: (1) the selectivity is low (the column has few distinct values and the predicate matches many rows); (2) the table statistics are stale, leading to a bad row count estimate (run ANALYZE); (3) the table fits in shared_buffers making seq scan fast; (4) the index has high correlation to disk order, which can cause many random reads. Check pg_stats for null_frac, n_distinct, and correlation. Also consider enabling enable_seqscan = off temporarily in a session to see what the index plan costs.

Expect these follow-ups

• How do you force a query to use a specific index in Postgres for testing purposes?
• What does a high 'Rows Removed by Filter' value in EXPLAIN ANALYZE tell you?

As asked

Your Postgres logs show frequent deadlock errors between two API endpoints that both update user and account rows. Walk me through how deadlocks happen and what you would change in the application code to prevent them.

Sample answer outline

Deadlocks occur when transaction A holds a lock on row 1 and waits for row 2, while transaction B holds row 2 and waits for row 1. Postgres detects this cycle and kills one transaction with an error. The fix is consistent lock ordering: always acquire locks in the same order (for example, always lock the user row before the account row). You can also use SELECT ... FOR UPDATE in the correct order. A strong answer also mentions using shorter transactions and reducing the time locks are held, as well as checking pg_locks and pg_stat_activity to identify the conflicting queries during an incident.

Expect these follow-ups

• How does SKIP LOCKED help in a job queue implementation in Postgres?
• When would you use advisory locks instead of row-level locks?

As asked

You need to add a NOT NULL column to a Postgres table with 50 million rows that is getting heavy write traffic. How do you do this without downtime or locking the table?

Sample answer outline

Adding a NOT NULL column with a volatile DEFAULT rewrites the entire table in all Postgres versions. Since Postgres 11, adding a column with a constant (immutable) DEFAULT is metadata-only and does not rewrite the table, but for computed or function-based defaults a full rewrite still occurs. The safe multi-step approach for a column that must not be null: (1) add the column as nullable with no default; (2) backfill in batches with UPDATE ... WHERE id BETWEEN x AND y to avoid long locks; (3) add a CHECK (col IS NOT NULL) constraint with NOT VALID so Postgres records it without scanning existing rows; (4) validate the constraint separately (VALIDATE CONSTRAINT takes a weaker ShareUpdateExclusiveLock); (5) promote to NOT NULL once validation completes. A strong answer also mentions using a migration tool like Flyway or Liquibase, deploying migrations backward-compatibly alongside old code versions, and the expand/contract pattern for column renames.

Expect these follow-ups

• What does adding an index CONCURRENTLY do differently from a regular CREATE INDEX?
• How does the expand/contract pattern apply to removing a column?

As asked

You have an Adobe Analytics database with a campaigns table (campaign_id, region, impressions, conversions, date). Write a SQL query that, for each region, returns the top 3 campaigns by conversion rate in the last 30 days. Include the campaign rank within its region.

Sample answer outline

Use a CTE to filter the last 30 days and compute conversion_rate = conversions / NULLIF(impressions, 0), then apply RANK() OVER (PARTITION BY region ORDER BY conversion_rate DESC). Filter the outer query WHERE rank <= 3. A strong answer handles the NULLIF for division by zero, considers whether RANK or DENSE_RANK is more appropriate (DENSE_RANK if you want exactly 3 results per region when there are ties), and mentions index hints for the date filter.

Reference implementation (sql)

WITH recent AS (
  SELECT
    campaign_id,
    region,
    conversions * 1.0 / NULLIF(impressions, 0) AS conv_rate
  FROM campaigns
  WHERE date >= CURRENT_DATE - INTERVAL '30 days'
),
ranked AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY region ORDER BY conv_rate DESC) AS rnk
  FROM recent
)
SELECT campaign_id, region, conv_rate, rnk
FROM ranked
WHERE rnk <= 3;

Expect these follow-ups

• Your query runs in 45 seconds on a 200 million row table. What indexes would you add and how do you verify they are being used?
• How do you change this query to use a rolling 30-day window per row rather than a fixed date cutoff?

As asked

Airbnb stores millions of guest reviews in Postgres. A query that searches review text for keywords and filters by listing city and minimum star rating is running at 4 seconds. Walk me through how you diagnose and fix it.

Sample answer outline

The candidate should start with EXPLAIN ANALYZE to find the bottleneck, then consider: adding a GIN index on the tsvector column for full-text search (replacing LIKE '%keyword%'), a composite index on (city, star_rating), partial indexes if low-star reviews are rarely queried, and moving to Elasticsearch if the full-text requirements grow beyond what Postgres handles well. They should also ask about the read/write ratio and whether eventual consistency is acceptable.

Expect these follow-ups

• When would you migrate this to Elasticsearch instead of tuning Postgres?
• How do you test that your index actually improves performance without affecting the production query planner?

As asked

You are storing Amazon order records in DynamoDB. Orders arrive at about 100,000 per second during peak. If you use customer_id as the partition key, what problem do you hit, and what strategies do you consider to fix it?

Sample answer outline

The core problem is hot partitions when a small number of customer IDs (or even one, in a test/load scenario) receive a disproportionate share of traffic. Strategies include: adding a suffix to the partition key (key sharding / write sharding), using a composite key like order_date + customer_id to spread writes by time, or using a write-sharding pattern where you append a random number 1-N to the key and fan out reads across all N suffixes. The candidate should weigh each approach against the read pattern. DynamoDB's adaptive capacity helps but does not fully solve the problem for truly hot keys.

Expect these follow-ups

• How does your sharding strategy affect your query pattern for fetching all orders for a customer?
• When would you choose DynamoDB over Aurora for this workload?

As asked

A customer reports that their Jira board takes 30 seconds to load. The board runs a JQL query: 'project = BIGPROJ AND issuetype in (Bug, Task) AND status != Done ORDER BY updated DESC'. The project has 2 million issues. Walk me through how you would diagnose and fix this.

Sample answer outline

Diagnose: run EXPLAIN ANALYZE on the generated SQL. A query on 2 million issues with an inequality on status and ORDER BY updated DESC is likely doing a full table scan or a slow sort. Fix: add a composite index on (project_id, status, updated_at DESC) or (project_id, updated_at DESC) and filter status in the index range scan. With multi-tenancy, confirm the project_id predicate is the leading index column. Also check if the query is paginated at the application layer or fetching all 2 million rows. The candidate should consider a materialized index or a separate search service for such queries.

Expect these follow-ups

• The customer says the query was fast last month and nothing changed. What might have happened?
• How do you roll out the new index without locking the table for 30 minutes?

As asked

You have a table of user events with columns (user_id, event_name, event_time). The funnel is: app_open -> video_play -> like -> share. Write a SQL query that returns the number of unique users who reached each step of the funnel, in order, within a 7-day window starting from their first app_open.

Sample answer outline

A strong answer uses self-joins or a series of CTEs where each step filters users who completed the previous step within the time window. Using window functions (LAG/LEAD) or conditional aggregation with FILTER is more elegant. The candidate should handle the window constraint correctly (7 days from the user's first app_open, not a fixed calendar window) and explain that the funnel is ordered (a like must come after a video_play for the same user).

Expect these follow-ups

• How would you compute the median time between each funnel step?
• How does your query change if you want the funnel per acquisition channel?