What Is an Inventory Management System?
An inventory management system tracks stock levels across warehouses, prevents overselling, coordinates replenishment, and provides real-time visibility into product availability. Amazon’s fulfillment network, Shopify’s multi-location inventory, and Walmart’s supply chain all depend on systems that must handle millions of SKUs, thousands of locations, and high-concurrency reservation requests without overselling a single unit.
System Requirements
Functional
- Track stock levels per SKU per warehouse location
- Reserve stock when an order is placed (soft allocation)
- Confirm or release reservations (on payment success or order cancellation)
- Replenishment alerts when stock falls below threshold
- Inventory transfers between locations
- Audit trail for all inventory movements
Non-Functional
- No overselling: concurrent orders for the same SKU must not exceed available stock
- High throughput: 100K reservation requests/second during flash sales
- Low latency: reservation response <100ms
Core Data Model
products: sku, name, description, weight, dimensions
warehouses: id, name, location, timezone
inventory: sku, warehouse_id, quantity_on_hand, quantity_reserved, quantity_available
(quantity_available = quantity_on_hand - quantity_reserved)
inventory_transactions: id, sku, warehouse_id, type (receive/reserve/release/ship/transfer),
quantity, reference_id, reference_type, created_at
reservations: id, order_id, sku, warehouse_id, quantity, status, expires_at
The Oversell Problem — Core Challenge
Flash sale: 1000 units available, 5000 concurrent requests. Without coordination, multiple requests could each read quantity_available=1000 and all “succeed” — overselling.
Approach 1: Pessimistic Locking (SELECT FOR UPDATE)
BEGIN TRANSACTION;
SELECT quantity_available FROM inventory
WHERE sku = ? AND warehouse_id = ? FOR UPDATE;
IF quantity_available >= requested_qty:
UPDATE inventory
SET quantity_reserved = quantity_reserved + requested_qty,
quantity_available = quantity_available - requested_qty
WHERE sku = ? AND warehouse_id = ?;
INSERT INTO reservations (...);
COMMIT; RETURN SUCCESS;
ELSE:
ROLLBACK; RETURN OUT_OF_STOCK;
Simple, correct. Bottleneck: all requests for the same SKU are serialized at the database. Works for moderate throughput (1K req/sec per SKU). Breaks down under flash sale conditions.
Approach 2: Redis Atomic Decrement (High Throughput)
# Initialize: SET inventory:{sku}:{warehouse_id} 1000
# On reservation request:
remaining = redis.decr(f"inventory:{sku}:{warehouse_id}")
if remaining >= 0:
# Stock reserved! Write to DB asynchronously
queue_db_write(sku, warehouse_id, order_id)
return SUCCESS
else:
# Oversold — restore the counter
redis.incr(f"inventory:{sku}:{warehouse_id}")
return OUT_OF_STOCK
Redis DECR is atomic and sub-millisecond. Handles 100K+ reservations/second per SKU. Write the confirmed reservation to PostgreSQL asynchronously via Kafka. The counter is the authoritative source for availability; the database holds the audit trail.
Approach 3: Optimistic Locking
UPDATE inventory
SET quantity_available = quantity_available - ?,
quantity_reserved = quantity_reserved + ?,
version = version + 1
WHERE sku = ? AND warehouse_id = ?
AND quantity_available >= ?
AND version = ?;
-- If 0 rows updated: retry with new read
No lock held. High contention → many retries → not suitable for flash sales.
Reservation Lifecycle
- Reserve: decrement available, increment reserved. Reservation has an expiry TTL (e.g., 15 minutes to complete payment).
- Confirm: on payment success, decrement on_hand and reserved (item physically ships).
- Release: on order cancellation or TTL expiry, decrement reserved, increment available (back in stock).
A background job sweeps for expired reservations and releases them. Run every minute.
Multi-Warehouse Fulfillment
When a customer orders a product, pick the optimal warehouse: closest to the customer (minimize shipping cost), highest stock level (avoid fragmentation), within a fulfillment SLA. If no single warehouse has enough stock for the order, split fulfillment across multiple warehouses. The fulfillment optimizer runs after order placement as a background process.
Inventory Ledger for Accuracy
Never update quantity_on_hand directly. Instead, record every movement in inventory_transactions and derive on_hand as the sum:
quantity_on_hand = SUM(quantity) WHERE type IN ('receive')
- SUM(quantity) WHERE type IN ('ship', 'damaged', 'transfer_out')
This provides a complete audit trail and allows reconciliation. Similar to a financial ledger — movements, not just current balance.
Interview Tips
- Redis atomic decrement for the hot path (flash sales) + async database persistence is the canonical answer for high-throughput inventory.
- Reservation TTL with background sweep is a practical mechanism many candidates miss.
- The ledger pattern (track movements, derive balance) shows financial systems thinking.
- Multi-warehouse fulfillment splitting is a real problem at Amazon scale — mention it to show product depth.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you prevent overselling inventory during flash sales with 100K concurrent requests?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “For high-throughput scenarios, use Redis atomic decrement as the reservation gate. Pre-load available stock into Redis: SET inventory:{sku}:{warehouse} 1000. On each reservation request: remaining = redis.decr(key). If remaining >= 0, the reservation succeeded — write to the database asynchronously via Kafka. If remaining < 0, restore the counter (redis.incr(key)) and return out-of-stock. Redis DECR is atomic and sub-millisecond, handling 100K+ ops/sec per key. The database records the confirmed reservation for audit purposes, but the Redis counter is the authoritative real-time availability source. For moderate throughput (under 1K/sec per SKU), SELECT FOR UPDATE pessimistic locking in PostgreSQL is simpler and equally correct.” }
},
{
“@type”: “Question”,
“name”: “What is the reservation lifecycle and how do you handle expired reservations?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “A reservation has three stages: (1) Reserve: when an order is placed, decrement available_quantity and increment reserved_quantity. Set an expiry time (typically 15 minutes for e-commerce checkouts) in the reservations table. (2) Confirm: when payment succeeds, decrement on_hand and reserved (the item physically ships). (3) Release: when an order is cancelled or the reservation TTL expires, decrement reserved and increment available (stock goes back to available). A background job runs every minute, scanning for reservations past their expires_at timestamp and releasing them. This prevents abandoned carts from permanently holding stock. In Redis-based systems, set a TTL on the decrement key alongside a Kafka-delayed message to release the reservation in the database after the TTL.” }
},
{
“@type”: “Question”,
“name”: “Why should inventory tracking use a ledger pattern instead of updating a quantity field?”,
“acceptedAnswer”: { “@type”: “Answer”, “text”: “Updating a single quantity_on_hand field directly loses the history of how the quantity changed — you cannot audit why inventory decreased, distinguish a sale from damage, or detect discrepancies. The ledger pattern records every inventory movement as an immutable row in inventory_transactions: receive shipment (+100), sell order (-1), return (+1), damage (-2). Current on_hand = SUM of all transaction amounts for that SKU/warehouse. Benefits: (1) Complete audit trail for reconciliation and dispute resolution. (2) Ability to replay and verify the balance at any point in time. (3) No lost updates — concurrent writes append new rows rather than competing to update a single field. (4) Easy to identify the source of inventory discrepancies by inspecting the transaction log. This is the same principle as financial double-entry accounting.” }
}
]
}