Founding engineer databases interview questions

As asked

You are building the data layer for a new SaaS product before you have a single paying user. How do you choose between PostgreSQL, a document store like MongoDB, and a hosted service like PlanetScale or Supabase? What signals make you revisit that choice six months later?

Sample answer outline

Strong answers prioritize operational simplicity and schema flexibility in early stages, default to Postgres for relational integrity, and treat hosted services as DX wins until team has ops maturity. Candidate should name concrete revisit triggers: read replica need, JSONB abuse, join query pain, or multi-region latency.

Expect these follow-ups

• You chose Postgres and six months in every feature is adding nullable columns to avoid migrations. What do you do?
• How do you handle database migrations safely when you are deploying multiple times a day with one engineer?

As asked

A customer complains that loading their dashboard takes twelve seconds. You trace it to a single Postgres query joining four tables with a WHERE clause on three columns. Walk me through exactly how you diagnose and fix this, using the tools Postgres gives you.

Sample answer outline

Candidate should immediately reach for EXPLAIN ANALYZE, read the query plan to find sequential scans and high row estimates, identify missing indexes or a bad join order, and apply targeted indexes. Strong answers also mention pg_stat_statements for catching patterns, partial indexes for low-cardinality filtered columns, and the risk of index bloat on write-heavy tables.

Expect these follow-ups

• The query is already indexed but Postgres still does a sequential scan. Why might that happen?
• How do you add an index on a 50 million row table without locking writes?

As asked

You are using Prisma in a Node.js API. A GET /projects endpoint returns projects with their associated users and tasks. Your logs show 47 queries executing for a response that returns 5 projects. What is happening and how do you fix it?

Sample answer outline

This is the classic N+1: one query fetches projects, then N queries fetch users for each project, then N more for tasks. Fix with Prisma's include to generate a JOIN or with select to batch. Candidate should also mention using a query logger to surface the problem in development and using a tool like pg_stat_statements or Prisma's queryRawUnsafe to inspect the actual SQL.

Expect these follow-ups

• Prisma's include generates a single query with JOINs. When would that single query be slower than multiple queries?
• How do you set up a test that catches an N+1 regression before it ships?

As asked

You are the engineer who chose Postgres for your startup and now must defend that choice to a technical co-founder who wants to switch to a NoSQL store for simplicity. Explain each ACID property with a concrete example of what breaks in a real application if that property is violated. Which one do most developers underestimate, and why does losing it matter more at a startup where you cannot afford a data integrity incident?

Sample answer outline

Atomicity: half-completed order placement leaves inventory decremented but no order record. Consistency: a constraint violation that allows a negative account balance. Isolation: two concurrent requests both reading and then updating the same row, resulting in lost updates (most underestimated, because default READ COMMITTED allows this). Durability: a committed transaction lost on crash because write-ahead log was not flushed.

Expect these follow-ups

• What is the default isolation level in Postgres and what anomalies does it allow?
• How do you protect against a lost update in Postgres without using SERIALIZABLE isolation?

As asked

You are building a B2B product where each customer is an organization. Walk me through the three common multi-tenancy models at the database level and tell me which one you would pick for a zero-to-one product expecting fifty customers in year one.

Sample answer outline

The three models are shared table with tenant_id column, separate schema per tenant (Postgres schemas), and separate database per tenant. Strong answer recommends shared table for early stage due to migration simplicity and operational cost, covers row-level security in Postgres as a safety net, and flags when to graduate to schema-per-tenant (compliance requirements, noisy-neighbor risk, customer contractual isolation demands).

Expect these follow-ups

• A customer's security audit asks for proof that their data cannot be read by another tenant. How do you demonstrate that with a shared-table model?
• How do you run a database migration that adds a column to a 10 million row shared tenants table without downtime?

As asked

You need to rename a column in a high-traffic Postgres table. The old name is in production code and the new name will be in the next deploy. Walk me through the exact migration strategy you use to rename it without downtime or data loss.

Sample answer outline

The canonical expand-contract pattern: add the new column, dual-write to both, backfill old rows, deploy code that reads the new column, drop the old column. Candidate should note that ALTER TABLE RENAME COLUMN takes an ACCESS EXCLUSIVE lock in Postgres and blocks reads, which is why you avoid it on large tables. Strong answers also mention using a generated column or view as an intermediate shim.

Expect these follow-ups

• What lock does adding a NOT NULL column with a DEFAULT take in Postgres 10 versus Postgres 11?
• How do you validate that the backfill completed correctly before switching reads to the new column?

As asked

Your Next.js app is deployed on Vercel with serverless functions. Each invocation opens a Postgres connection. At 500 concurrent requests you hit Postgres max_connections. Walk me through how connection pooling works, what PgBouncer does, and how you fix this specific problem.

Sample answer outline

Serverless functions do not share a process-level connection pool, so each cold start opens a new connection. PgBouncer in transaction mode sits between the app and Postgres and multiplexes many short-lived connections onto a smaller pool, but it breaks prepared statements and advisory locks. For Vercel specifically the answer is using a connection-pooled endpoint from Neon, Supabase, or Prisma Accelerate rather than running PgBouncer yourself.

Expect these follow-ups

• What is the difference between PgBouncer in session mode and transaction mode, and why does transaction mode break prepared statements?
• How do you set max_connections in Postgres and what is the cost of setting it too high?

As asked

You added a B-tree index to a column last week but a slow query is still doing a sequential scan in production and customers are complaining. Explain how a B-tree index works internally in Postgres and name three specific query patterns where a B-tree index exists but Postgres will choose a sequential scan instead, so you know what to check.

Sample answer outline

B-tree is a balanced tree where leaf nodes hold (key, ctid) pairs, allowing O(log n) lookups and range scans. Postgres ignores it when: the predicate is on the non-leading column of a composite index; the planner estimates the selectivity is low enough that a seq scan is cheaper (low cardinality column like boolean); the LIKE pattern starts with a wildcard ('%foo'); or the column is cast in the WHERE clause (WHERE id::text = '123').

Expect these follow-ups

• When would you choose a BRIN index over a B-tree index?
• How do you force Postgres to use an index for testing purposes when it has chosen a seq scan?