CAP, DB Indexing, Concurrency and Logging: Backend Fundamentals Oral Round

Requirements

Come prepared to handle an integrated oral exam covering interrelated backend production subjects:

• Describe a project’s data model and defend the indexes you chose based on real query access paths.
• Clarify how composite indexes behave with leftmost prefix rules, covering indexes, and whether certain SQL conditions can use an index.
• Discuss cache-aside consistency and the failure modes of cache breakdown and avalanche, along with how to mitigate them.
• Contrast Kafka against generic message queues regarding delivery semantics and consumer-side trade-offs.
• Articulate RESTful API norms and weigh WebSocket against Server-Sent Events for specific workload scenarios.
• Compare MySQL and MongoDB, then make a case for migration based on workload suitability and quantifiable performance results.
• Explain CAP theorem, concurrency control, locking, deadlock avoidance, and how to implement production-safe logging.
• A follow-up for backend interns: why are database indexes essential, and why shouldn’t every column have one?

Notes

• Begin schema and index discussions from the workload’s access patterns, selectivity, ordering needs, update frequency, and latency goals. Each new index increases write overhead and storage consumption.
• When dealing with composite indexes, tie column ordering to equality, range, and sorting conditions. Practice altering a SQL query to introduce the index and then analyze whether example queries will leverage it.
• Describe CAP in terms of behavior during a network partition; avoid portraying it as a fixed two-of-three trade-off under normal operation.
• Cache responses must link each failure scenario to traffic surge, expiration windows, and recovery dynamics—not just recite definitions.
• Match protocol decisions to the interaction model. For instance, clarify why a request-response AI-agent runtime might stick with standard HTTP calls despite the availability of bidirectional sockets.
• A database migration justification requires a clear before-and-after workload description, operational trade-offs, and measurable outcomes; merely listing SQL vs. NoSQL characteristics won’t suffice.

Preparation

• Sketch a project’s schema, identify its three highest-priority queries, and justify each index by analyzing those queries against the write expense.
• Rehearse cache-aside inconsistency, breakdown, and avalanche scenarios by walking through a failure timeline for each.
• Create a comparison chart for Kafka, standard message queues, WebSocket, SSE, MySQL, and MongoDB that anchors in workload considerations rather than feature inventories.
• Work through a concrete lost-update or deadlock example and show how locking, isolation levels, ordering, or optimistic retries safeguard the data invariant.