Forward-deployed engineer databases interview questions

As asked

A customer says their weekly summary report takes 4 minutes to load. It used to be 30 seconds. The report runs a single SQL query across 3 joined tables. Walk me through how you find and fix the regression.

Sample answer outline

Start with EXPLAIN ANALYZE to identify sequential scans or hash joins on large tables. Check whether indexes exist on the join columns and the WHERE clause predicates. Look for data volume growth (did the customer's dataset grow 10x since the query was written?). Fixes might include adding a composite index, rewriting a correlated subquery as a join, or adding a materialized view for the aggregation. Candidates should also mention query plan caching and statistics freshness (ANALYZE in Postgres).

Expect these follow-ups

• What if adding the index makes writes noticeably slower for this customer? How do you present that tradeoff?
• How do you prevent this from happening to other customers whose data will eventually reach the same size?

As asked

A customer dashboard endpoint that shows a list of 50 orders with their line items is making 51 database queries per request. You are the engineer who needs to fix it. How do you identify it and what do you do?

Sample answer outline

Identify with query logging (Postgres pg_stat_statements or ORMs' debug mode, seeing the same query pattern 50 times). The fix is eager loading: one query for orders, one query for all line items WHERE order_id IN (...), then join in memory. In an ORM this is typically an include/preload option. Candidates should also mention adding a test that asserts the query count stays low.

Expect these follow-ups

• How do you prevent N+1 regressions from being introduced by other engineers in the future?

As asked

Your integration receives the same event multiple times due to retries at the source. The event needs to be stored exactly once in Postgres. How do you implement this, and what are the edge cases you need to handle?

Sample answer outline

The canonical answer is INSERT ... ON CONFLICT (unique_key) DO UPDATE with a deterministic conflict key derived from the event's natural ID. Edge cases include: what fields to update vs. ignore on conflict (e.g., never overwrite a final status with a retry's in-progress status), handling the case where the event ID is missing, and clock skew if you use timestamps as part of the key. Strong candidates also note that ON CONFLICT requires that the conflict target column has a unique constraint or unique index, and that in Postgres a unique constraint is backed by a unique index, so both achieve the same result.

Expect these follow-ups

• How does this interact with Postgres transaction isolation if two workers process the same event concurrently?

As asked

Your API returns a list of records. A customer wants to export all 500,000 of them. Your current implementation uses OFFSET/LIMIT. What is wrong with that at scale, and how do you fix it?

Sample answer outline

OFFSET forces the database to scan and discard rows, so page 5000 requires reading 5000 * page_size rows. The fix is cursor-based pagination: encode the last-seen row's unique, ordered key (e.g., created_at + id) as an opaque cursor, and use WHERE (created_at, id) > (cursor_created_at, cursor_id) with ORDER BY in subsequent queries. This is O(log n) via index scan at each page. Candidates should also address cursor stability (what if rows are inserted mid-export).

Expect these follow-ups

• How do you expose this to a customer whose integration already depends on the page-number interface?

As asked

Your SaaS product stores all customers' data in shared Postgres tables, using a tenant_id column to isolate them. What are the ways a bug could accidentally leak one customer's data to another, and how do you defend against each?

Sample answer outline

Risks include: forgetting the WHERE tenant_id = ? clause in a query, the ORM defaulting to a global scope that a developer disables temporarily, a JOIN that crosses tenant boundaries, and caching responses that don't include tenant_id in the cache key. Defenses: application-level middleware that injects tenant_id into every query via ORM scoping, database-level row security policies (Postgres RLS), automated tests that create two tenants and verify each can only see their own data, and integration tests for every new endpoint.

Expect these follow-ups

• How does Postgres Row Level Security work, and what are its performance implications for a high-throughput multi-tenant system?