Hard Database Engineering interview problem
CDC Search Index Synchronization
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 document service treats PostgreSQL as its source of truth and maintains a separate search projection from an append-only change stream. The projection looks plausible during normal traffic, yet diverges after rollback, deletion, and retry scenarios.
This is a hard production-debugging exercise because the bug crosses transaction visibility, ordering, delete semantics, and idempotent projection state rather than living in one malformed query. 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
Every document write records a corresponding change, and a polling projector consumes those changes in order into a search_index table. A durable cursor is supposed to make each bounded poll restartable without losing or applying work twice.
The starter implementation observes changes with the wrong transaction visibility, does not model deletion as first-class state, and cannot safely replay a partially processed batch. Search can therefore return rolled-back documents, retain deleted documents, or duplicate a document after retry.
What you’ll practice
- Reading an append-only CDC stream in deterministic order
- Keeping source writes and change records transactionally aligned
- Applying create, update, and delete events idempotently
- Advancing a durable projection cursor only with committed work
- Proving that batch retries converge on one search document per source row
How to approach it
Start with the database states that should be impossible: a visible rolled-back change, a deleted source row still searchable, or more than one projection row for a document. Trace each state back through the projector transaction and cursor update.
Treat replay as a normal execution path, not an exceptional one. Define the invariant at the database boundary, then check whether every change application and cursor movement can be repeated without changing the final projection.
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 CDC Search Index Synchronization?
Begin with the append-only change log, projection cursor, transaction boundaries, and search-index rows before and after a replay. 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 the bug crosses transaction visibility, ordering, delete semantics, and idempotent projection state rather than living in one malformed query. 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.