Hard Database Engineering interview problem
Catalog Pagination Skips and Repeats Products
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
A product catalog uses LIMIT/OFFSET and appears correct for the first few pages. Under concurrent inserts and deep navigation, users see repeated products, miss others entirely, and trigger increasingly expensive scans.
This is a hard production-debugging exercise because correctness and performance depend on the same total ordering, including a deterministic tie-breaker and a matching index. 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 catalog exposes a stable sort order and a continuation token so a caller can walk products in bounded pages. The database access path should resume after the final row from the previous page rather than recounting all earlier rows.
Offset identifies a position in a changing result set, not a durable boundary. Rows inserted ahead of that position shift subsequent pages, while large offsets force PostgreSQL to visit and discard growing prefixes.
What you’ll practice
- Designing a stable composite sort order
- Encoding and validating keyset continuation state
- Handling rows with equal primary sort values
- Building an index that serves filter and order together
- Testing inserts between consecutive page requests
How to approach it
Identify the exact row that marks a page boundary and express the next-page predicate in terms of that row’s ordered fields. Include a unique tie-breaker so no pair of products is ambiguous.
Test the access path at a depth where OFFSET would be expensive, then insert a product before the prior boundary. A correct keyset query remains bounded and neither repeats nor skips the existing sequence.
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 Catalog Pagination Skips and Repeats Products?
Begin with page boundaries, duplicate and missing IDs, concurrent inserts, deep-page query plans, and index ordering. 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 correctness and performance depend on the same total ordering, including a deterministic tie-breaker and a matching index. 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.