Hard Database Engineering interview problem
Database Overload Under A Traffic Spike
Written and reviewed by Sahil Srivastav
This problem is available as the same repository challenge in Python, Java, Node.js, and C++. Open the full problem and choose a language
What this interview round tests
An order-history endpoint is fast in a quiet development environment but saturates PostgreSQL during a promotion. Database work grows with a customer’s complete history even when the caller requests only a small page.
This is a hard production-debugging exercise because several individually familiar mistakes compound, so fixing only the index, only pagination, or only the N+1 loop leaves the load invariant broken. It tests whether you can move from operational evidence to a narrow invariant, then make a repair that survives concurrency, retries, failures, or workload growth instead of merely passing one happy-path example.
The scenario
The endpoint joins customers, orders, and order items to return recent orders with item counts and totals. The service contract asks for exactly N newest orders and should perform a bounded amount of work for that page.
The starter combines a missing access path with repeated per-order queries and an unbounded fetch. Sequential scans and statement counts multiply under concurrent traffic until database CPU, rather than application logic, becomes the bottleneck.
What you’ll practice
- Reading EXPLAIN plans for the endpoint’s primary access path
- Designing a composite index around filtering and ordering
- Replacing per-row lookups with bounded set-based aggregation
- Enforcing page size at the database boundary
- Measuring database work independently of wall-clock timing
How to approach it
Translate the endpoint contract into measurable limits: result count, statements executed, and rows visited. Use those limits to distinguish a real repair from a change that happens to run quickly on a small seed.
Inspect the query shape and index together. A useful plan should locate one customer’s newest orders directly, aggregate their items without a query per order, and never load the rest of the history into application memory.
The starter repository ships with a failing test suite and a bundled verify.sh. Reviewed reference solutions are part of Gronex Pro — this page stays spoiler-free on purpose.
Try it in a real repository
LeetCode teaches algorithms. Gronex teaches backend coding rounds with real repositories, failing tests, service logic, and production-style constraints. Read the diagnostic brief on this page, then open the challenge workspace when you are ready to investigate the repository.
FAQ
What should I inspect first in Database Overload Under A Traffic Spike?
Begin with query plans, pg_stat_statements counters, rows scanned, statement counts per request, and page-size behaviour. The supplied evidence narrows the failure mode before you touch the implementation, which is the same evidence-first habit expected during a production incident or senior backend interview.
What makes this a hard database engineering problem?
It is hard because several individually familiar mistakes compound, so fixing only the index, only pagination, or only the N+1 loop leaves the load invariant broken. The test suite checks the underlying invariant and adverse execution paths, so a local patch or a larger resource limit will not satisfy the challenge.
Which languages can I use for this repository challenge?
The same scenario is implemented in Python, Java, Node.js, and C++. Each language directory includes its own source, evidence or database setup, tests, and verify.sh entry point while preserving the same production invariant.