Senior interview scenarioDatabase performance

A Database Query Takes 50ms in Dev and 4 Seconds in Production

A senior engineering interview scenario about diagnosing production-only database latency through execution plans, cardinality, locks, connection pools, resources, and network time.

Try the free scenarios →Explore the full library

Interview prompt

The same query takes 50ms in development and four seconds in production. How do you separate query execution time from waiting and identify the real cause?

The first strong observation

Do not assume the SQL text is the whole system. Production differs in data volume, distributions, concurrency, statistics, hardware, network placement, cache warmth, and lock contention. Measure where the four seconds are spent.

Evidence to inspect first

  • â—†Database execution time versus pool wait and network time.
  • â—†EXPLAIN (ANALYZE, BUFFERS) with representative parameters.
  • â—†Table cardinality, statistics freshness, index selectivity, and plan changes.
  • â—†Locks, concurrent sessions, I/O, memory pressure, and cache warmth.

A strong step-by-step approach

1

Break down the latency

Trace acquisition of a database connection, server execution, row transfer, ORM hydration, and serialization separately. A four-second endpoint does not prove a four-second query.

2

Reproduce the production shape

Use the real parameter distribution and production-scale cardinality in a safe replica or explain plan. Small development data often hides scans, poor joins, and unstable plans.

3

Read the execution plan

Inspect estimated versus actual rows, scan type, join order, loops, sort or spill behaviour, and buffer reads. Fix the observed access path rather than adding indexes by reflex.

4

Check concurrency and waiting

Look for lock waits, exhausted connection pools, long transactions, I/O saturation, noisy neighbours, and queueing. These can dominate wall time while the query itself remains fast.

5

Verify application behaviour

Detect N+1 calls, fetching unused columns, unbounded result sets, repeated queries, and ORM-generated SQL. Measure the whole request after the database fix.

6

Roll out with a guardrail

Validate read and write impact, deploy gradually, compare plan and latency metrics, and keep a rollback path. An index that accelerates reads can still hurt writes or storage.

Structure your answer in this order

  1. 01Separate execution from waiting
  2. 02Use representative parameters and scale
  3. 03Explain the important plan evidence
  4. 04Check locks and pool pressure
  5. 05Describe a measured, reversible rollout

Weak-answer signals

  • ×Assuming a missing index is always the answer.
  • ×Comparing environments with different parameters or data shape.
  • ×Ignoring pool wait, locks, ORM work, and network latency.

Likely follow-up questions

  • ?What if EXPLAIN ANALYZE itself is unsafe in production?
  • ?How can stale statistics change the plan?
  • ?When should you use a read replica?

Quick FAQ

What is the interviewer evaluating in this scenario?

The interviewer is not looking for one technology name. They are evaluating how you gather evidence, limit risk, choose a safe action, and verify the outcome under uncertainty.

How long should the answer be?

Use the first two minutes to state your priority and initial hypothesis, then move through signals, diagnosis, mitigation, and verification. Go deeper when the interviewer asks follow-up questions.