Reason About Indexes, Kafka, and Database Locking

Scenario

You are designing the backend persistence and event processing layer for an e-commerce platform handling flash sales. The system consumes order placement events from Apache Kafka and updates both customer order records and item inventory counts in a relational database (PostgreSQL-compatible).

Database Schema

CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    status VARCHAR(32) NOT NULL, -- 'PENDING', 'CONFIRMED', 'CANCELLED'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

CREATE TABLE order_items (
    order_id UUID REFERENCES orders(order_id),
    item_id UUID NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, item_id)
);

CREATE TABLE inventory (
    item_id UUID PRIMARY KEY,
    available_stock INT NOT NULL CHECK (available_stock >= 0),
    reserved_stock INT NOT NULL DEFAULT 0
);

Workload & System Requirements

1. Query Workload:

   • High-frequency query: Fetch the 20 most recent confirmed orders for a given user: SELECT * FROM orders WHERE user_id = ? AND status = 'CONFIRMED' ORDER BY created_at DESC LIMIT 20;
   • Periodic analytics: Find orders created within a timestamp range across all users.

2. Event Consumption:

   • An upstream service publishes OrderCreatedEvent(order_id, user_id, items: [(item_id, quantity)]) to a partitioned Kafka topic orders.created.
   • Multiple consumer instances run in a single consumer group.
   • Delivery is at-least-once: network retries or consumer rebalances can deliver the same message more than once.

3. Stock Reservation & Concurrency:

   • For each valid event, the consumer must verify sufficient stock and reserve it (available_stock -= qty, reserved_stock += qty) atomically with marking the order confirmed.
   • Multiple concurrent orders can request the same items or overlapping item sets simultaneously.

Questions to Address

1. Index Design:

   • Propose the exact composite index definition for orders to serve the user order query without table scans or separate sorting steps. Explain column ordering.
   • Evaluate whether a partial index would be beneficial here.

2. Kafka Partitioning & Idempotency:

   • What partition key should the producer use on orders.created? Explain how partition assignment affects concurrent processing across items vs. users.
   • How should the consumer handle duplicate deliveries to guarantee that inventory is decremented exactly once per order?

3. Concurrency Control & Deadlock Prevention:

   • Write out the transactional SQL logic (or pseudo-code) for reserving stock across multiple items in an order.
   • Explain how concurrent transactions updating overlapping sets of items (e.g., Order A reserves items [1, 2] while Order B reserves items [2, 1]) can deadlock, and describe your strategy to guarantee deadlock-free execution with bounded retries.