Inventory Reservation Database Design: Prevent Overselling

Two shoppers add the last pair of headphones to their carts. Both checkouts read available = 1 before either writes anything. If both accept the order based on that read, one customer gets a confirmation for stock you don’t have.

Inventory reservation database design starts with a rule: checking availability and claiming stock must happen together. This PostgreSQL example shows how to reserve the last unit, handle a retry, and return stock when a checkout expires.

Model stock and reservations separately

Store a balance for each sellable SKU at each fulfillment location. Keep a separate row for every checkout hold, with the quantity, expiration time, and status. One balance can have many reservations over time. A reservation belongs to exactly one balance.

In this example, on_hand is the stock balance used for checkout. Confirmation deducts units immediately, even if the parcel hasn’t shipped yet. If your warehouse counts physical stock until dispatch, model the committed quantity separately and subtract both holds and commitments from what you can sell.

The last two rows are alternative outcomes from the same hold. Available stock is always on_hand - reserved; there’s no third count to keep in sync.

Define the keys and stock constraints

The balance uses (sku_id, location_id) as its primary key. The reservation references that pair so a hold at one warehouse can’t be applied to a balance at another. The example assumes SKU, location, and checkout IDs come from the application; add foreign keys to their parent tables in your full schema.

CREATE TABLE inventory_balances (
  sku_id bigint NOT NULL,
  location_id bigint NOT NULL,
  on_hand integer NOT NULL CHECK (on_hand >= 0),
  reserved integer NOT NULL DEFAULT 0,
  PRIMARY KEY (sku_id, location_id),
  CHECK (reserved BETWEEN 0 AND on_hand)
);

CREATE TABLE inventory_reservations (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  checkout_id uuid NOT NULL,
  request_key uuid NOT NULL,
  sku_id bigint NOT NULL,
  location_id bigint NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  status text NOT NULL DEFAULT 'held'
    CHECK (status IN ('held', 'confirmed', 'released', 'expired')),
  expires_at timestamptz NOT NULL,
  UNIQUE (checkout_id, request_key),
  FOREIGN KEY (sku_id, location_id)
    REFERENCES inventory_balances (sku_id, location_id)
);

CREATE INDEX inventory_reservations_due_idx
  ON inventory_reservations (expires_at, id)
  WHERE status = 'held';

The PostgreSQL constraints keep each balance between sensible limits and require a positive quantity. A row check can’t enforce that reserved equals the sum of all held quantities. Keep those values in sync by changing the reservation and its balance together, and make receiving, adjustments, and checkout use the same balance. If a count correction would reduce stock below existing holds, resolve those commitments explicitly.

Claim stock in one atomic statement

Run the following statement for a single SKU and location. The conditional update increments reserved only when enough stock is available. The insert uses the row returned by that update, so no updated balance means no new hold.

-- Bind checkout ID, request key, SKU, location, and quantity.
WITH claimed AS (
  UPDATE inventory_balances
  SET reserved = reserved + $5
  WHERE sku_id = $3 AND location_id = $4
    AND $5 > 0 AND on_hand - reserved >= $5
  RETURNING sku_id, location_id
)
INSERT INTO inventory_reservations
  (checkout_id, request_key, sku_id, location_id, quantity, expires_at)
SELECT $1, $2, sku_id, location_id, $5,
       clock_timestamp() + interval '15 minutes'
FROM claimed
RETURNING id, status, expires_at;

Bind the parameters through your driver and validate the requested quantity before executing. Under PostgreSQL’s default Read Committed isolation, two updates to the same balance can’t both claim the final unit. The second waits for the first transaction, then PostgreSQL checks its condition again against the updated balance. If the first commits its claim, the second gets zero rows. If the first rolls back, the second can still succeed.

Keep any cart availability read for display. The result of this write decides what the checkout may promise. For an all-or-nothing basket, run the statement for every line in one transaction, in a consistent order by SKU and location. Roll back the entire basket if any new line cannot be reserved. Follow a consistent locking order across bulk writes and retry transactions that PostgreSQL aborts for deadlocks.

Make checkout retries safe

A browser can time out just after the hold commits. Give each logical reservation request a stable key scoped to its checkout. On retry, return the existing reservation when its SKU, location, and quantity match. Reject a key reused with different inputs. Authorize access to the checkout before looking up the key.

Two requests may both miss an initial lookup. If the second insert hits the request-key unique constraint, roll back and read the original reservation in a fresh transaction. Also look up the key after a zero-row result: a prior success may have reserved the last unit. Avoid adding ON CONFLICT DO NOTHING to the insert above. That would let the balance update succeed without a new reservation when there’s still stock and the key already exists.

This schema remembers successful holds, including their terminal states. A fresh attempt after expiry needs a fresh key. If the API must replay failed outcomes as well, store request outcomes in a dedicated table. The same idea of stable retry identity appears in our guide to webhook events, deliveries, and retries.

Confirm, release, or expire a hold exactly once

Make held the only state that can transition. A confirmation reduces both on_hand and reserved. An expiry or explicit release reduces only reserved. Keep the terminal reservation row so a late retry can see the outcome.

Start a transaction and lock the specific reservation using SELECT id FROM inventory_reservations WHERE id = $1 FOR UPDATE. Then run the statement below in the same transaction. The lock serializes competing transitions on that hold. The deadline check uses clock_timestamp() after acquiring the lock, so a request that waited past the deadline can’t confirm an expired hold.

-- After locking the reservation row in this transaction:
-- Bind reservation ID and target status.
WITH changed AS (
  UPDATE inventory_reservations
  SET status = $2
  WHERE id = $1 AND status = 'held'
    AND (
      ($2 = 'confirmed' AND expires_at > clock_timestamp())
      OR ($2 = 'expired' AND expires_at <= clock_timestamp())
      OR $2 = 'released'
    )
  RETURNING sku_id, location_id, quantity, status
)
UPDATE inventory_balances AS b
SET reserved = b.reserved - c.quantity,
    on_hand = b.on_hand - CASE
      WHEN c.status = 'confirmed' THEN c.quantity ELSE 0
    END
FROM changed AS c
WHERE b.sku_id = c.sku_id AND b.location_id = c.location_id
RETURNING c.status, b.on_hand, b.reserved;

Commit the transaction after processing the result. If no row comes back, read the reservation state to distinguish a repeat of a completed action from a disallowed transition or a missing reservation. Run the expiry worker in small batches: find held rows with expires_at <= statement_timestamp(), then lock and recheck each before releasing it. Until that release commits, the balance still counts the hold even if its deadline has passed. Monitor how long due holds stay in that state.

Keep the payment provider call outside the database transaction. If payment succeeds after a hold expires, resolve that through a checkout policy such as trying a fresh reservation or reversing the authorization. Never bypass the stock check to make a late callback succeed.

Test the cases that break checkout

• Last unit: Use two database sessions to claim one available unit. Only one can commit a new hold.
• Lost response: Retry a successful request with the same key, both when there is spare stock and when there is none. The quantity stays reserved once.
• Expiry race: Confirm while the expiry worker tries the same hold. Verify that only one transition changes the balance.
• Reconciliation: Compare reserved with the total quantity of rows whose status is held, including overdue holds. Investigate mismatches before correcting them.

Confirmed orders have their own history. If an order must preserve the price, description, or other details the buyer saw, use snapshot fields on the order line instead of reading mutable catalog values later.

Start by sketching SKU, location, balance, and reservation in DB Designer. Mark the composite balance key and the reservation’s request key on the diagram, then walk the last-unit example through each state with your team.