SQL for E-commerce Inventory: Flash Sales, Low Stock Alerts, & Stock Reconciliation

Flash Sale Inventory Management

During a flash sale, thousands of orders hit your database in minutes. Overselling can ruin customer trust and revenue. These SQL patterns keep inventory accurate under extreme concurrency.

1. Prevent Overselling with Advisory Locks

Use PostgreSQL advisory locks to serialize access to limited-stock items — only one transaction can decrement stock at a time.

-- Acquire advisory lock before updating stock
SELECT pg_advisory_xact_lock(12345); -- Lock persists until transaction commits

UPDATE products
SET stock = stock - 1
WHERE product_id = 123
  AND stock > 0  -- Prevent going negative
RETURNING *;

2. Flash Sale: Sell Remaining Stock All at Once

When a flash sale ends, you need to sell whatever stock is left at a discounted price — atomically.

WITH remaining AS (
    SELECT product_id, stock
    FROM products
    WHERE flash_sale_active = true
    FOR UPDATE
)
UPDATE products p
SET stock = 0,
    price = price * 0.5  -- 50% off at end
FROM remaining r
WHERE p.product_id = r.product_id
  AND r.stock > 0;

3. Low Stock Alert Across Warehouses

Aggregate inventory across all warehouses and flag products that fall below the reorder threshold.

SELECT
    product_id,
    SUM(warehouse_stock) AS total_stock,
    MIN(reorder_threshold) AS threshold
FROM inventory
GROUP BY product_id
HAVING SUM(warehouse_stock) < MIN(reorder_threshold)
ORDER BY total_stock ASC;

4. Reconciling Discrepancies Between Systems

Compare orders shipped vs. orders invoiced to find unshipped or unbilled orders.

SELECT
    o.product_id,
    COUNT(DISTINCT o.order_id) AS orders_shipped,
    COUNT(DISTINCT i.invoice_id) AS invoiced,
    COUNT(DISTINCT o.order_id) - COUNT(DISTINCT i.invoice_id) AS uninvoiced
FROM order_items o
LEFT JOIN invoices i ON o.product_id = i.product_id
 AND o.order_id = i.order_id
GROUP BY o.product_id
HAVING COUNT(DISTINCT o.order_id) > COUNT(DISTINCT i.invoice_id)
ORDER BY uninvoiced DESC;

Key Takeaways for Production

• Advisory locks (pg_advisory_xact_lock) are a simple way to serialize access to limited resources without full row locking.
• LEFT JOIN … IS NULL is the classic pattern for finding records in one table with no match in another — great for reconciliation.
• Always use FOR UPDATE or SELECT ... FOR SHARE in transactions that modify shared state to avoid race conditions.
• Materialized views can pre-compute low-stock alerts so your checkout flow doesn't have to scan the entire inventory table.