## Reason About Indexes, Kafka, and Database Locking Answer the three backend fundamentals sections below. For each one, connect the mechanism to a concrete access or failure pattern rather than listing terms. ### Part 1 — Database Indexes An `orders` table is queried by exact `order_id`, by `customer_id` over a recent date range, and by `status` for a background worker. Explain how a B-tree-style index locates rows, propose useful indexes for these queries, and discuss write, storage, and selectivity trade-offs. #### What This Part Should Cover - The difference between an index and the table rows it points to or contains. - Composite-key order for `customer_id` plus time-range access. - Why a low-cardinality `status` index may or may not help. - Maintenance cost on insert, update, delete, and page split. ```hint Start from predicates and ordering Write the leading equality columns, then the range or sort column, before choosing a composite index order. ``` ### Part 2 — Kafka as a Distributed Log and Queue A producer publishes order events, several instances of one consumer service share the work, and a separate analytics service needs its own complete copy. Explain topics, partitions, offsets, consumer groups, ordering, redelivery, and failure recovery. #### What This Part Should Cover - Ordering within a partition rather than across the whole topic. - Same-group load sharing versus different-group independent consumption. - Key selection for per-order ordering. - Offset commit timing, duplicate processing, and idempotent effects. ```hint Follow one keyed order Track which partition receives all events for one order and how two different consumer groups advance independently. ``` ### Part 3 — Database Concurrency Control and Locks Two transactions attempt to reserve the last unit of inventory while another report scans inventory rows. Compare pessimistic locking, optimistic version checks, and isolation-level behavior. Explain lock granularity, deadlocks, and retry. #### What This Part Should Cover - A transactionally safe check-and-decrement operation. - Row locks versus broader locks and their effect on concurrency. - Lost updates and the role of a version predicate. - Consistent lock order, deadlock detection, and bounded transaction retry. ```hint Make the invariant one atomic decision The stock check and decrement cannot be two unrelated operations that both transactions pass independently. ``` ### What a Strong Answer Covers - Chooses indexes from actual predicates and explains their maintenance cost. - Treats Kafka as a partitioned replayable log with group-specific progress, not as a magical exactly-once queue. - Connects isolation and locking choices to the inventory invariant. - Explains failure and retry behavior for both message processing and database transactions. ### Follow-up Questions 1. When can an index-only scan avoid reading the base table? 2. What happens when a consumer group has more active consumers than topic partitions? 3. Why can committing an offset before a database write lose an event? 4. How would optimistic inventory updates report and retry a version conflict?