How do you design a flash sale system that does not oversell stock?

Make the stock check and the stock decrement a single atomic operation, such as an UPDATE with a WHERE clause that requires enough stock and a RETURNING clause, and treat zero affected rows as sold out. Add a CHECK constraint so the quantity can never go negative. Then layer on reservations with a TTL, a waiting room, and idempotent orders as traffic demands.

Why does reading stock and then updating it cause overselling?

Because other requests run between your read and your write, so several buyers can each see the same stock number and each pass the check. With stock 1 and two buyers, both read 1, both write 0, and two orders are created for one unit. Wrapping the two steps in a transaction does not help under Read Committed.

Should I use Redis or Postgres to decrement flash sale stock?

Use Postgres as the source of truth, because it has a CHECK constraint and durable reservation rows. Add Redis in front, with a Lua script that checks and decrements in one atomic step, when you need cheap sold-out rejections and more throughput than one hot row allows. A successful Redis result should only mean you may try Postgres.

How long should a flash sale stock reservation last?

Base it on how long your real payment flow takes, then add a margin. The cost is easy to compute: with 500 units and one unit per order, a 10-minute hold can freeze all 500 units for 600 seconds if every buyer abandons. A sweeper job must expire lapsed holds and return the units to both Postgres and the Redis counter.

How do you keep Redis stock and the database consistent?

Declare Postgres the winner and compare the two on a schedule. If Redis shows more stock than Postgres, correct it immediately and alert, because it will let through requests the database must reject. If Redis shows less, it only undersells, so correct it when the same gap persists on the next run.

Design a Flash Sale System That Never Oversells Stock

How to design a flash sale system that does not oversell stock: atomic decrements in Postgres and Redis Lua, TTL reservations, a waiting room, sharded counters and reconciliation.

Key Takeaway

To stop a flash sale overselling, never read stock and then write it. Decrement atomically in one step, either a Postgres UPDATE guarded by a stock check or a Redis Lua script, and treat zero affected rows as sold out. Then add TTL reservations, a waiting room, sharded counters, idempotent orders and reconciliation.

Every flash sale design starts from the same bad moment: 500 units go on sale, the traffic arrives all at once, and the order table ends the night with 503 rows. Nothing crashed and no query failed. Every request read a stock number that was true when it was read.

This is a worked design, not a war story, so each number below is either derived from stated assumptions with the arithmetic shown or taken from the PostgreSQL and Redis documentation. The stack is the one I reach for on a single VPS: Postgres, Redis and Node or NestJS. The ledger side of inventory, where stock is a sum of movements, is covered in my posts on stock movements and the ERP inventory module. This post is about the next hour: a counter that thousands of buyers hit at once.

Why does read-then-write oversell stock?

Because the check and the write are two separate steps, and other requests run between them. This is the classic time-of-check to time-of-use race. Take stock of 1 and two buyers. Both run the SELECT and both see 1. Both pass the if statement. Both write 0 and both create an order. One unit, two orders.

It gets worse when the application computes the new value. Say stock is 5, buyer A takes 2 and buyer B takes 1, and both read 5. A writes 5 minus 2, which is 3. B writes 5 minus 1, which is 4. The last write wins, so the table says 4 although 3 units were sold and the true answer is 2. Two units appeared from nowhere. A transaction does not fix this on its own: under the default isolation level each SELECT simply sees committed data, and nothing stops two transactions holding the same stale number.

// Wrong: the check and the write are two round trips. Anything can happen between them.
const { rows } = await pool.query("SELECT available FROM sku_stock WHERE sku = $1", [sku]);
if (rows[0].available >= qty) {
  // Two buyers both got here holding the same stale number.
  await pool.query("UPDATE sku_stock SET available = $2 WHERE sku = $1", [sku, rows[0].available - qty]);
  await createOrder(sku, buyerId, qty);
}

// Right: one statement. The database does the check and the write under the row lock.
const res = await pool.query(
  "UPDATE sku_stock SET available = available - $2 WHERE sku = $1 AND available >= $2 RETURNING available",
  [sku, qty],
);
if (res.rowCount === 0) throw new SoldOutError(sku);   // zero rows = sold out, not an error in the database

Wrapping the SELECT and the UPDATE in a transaction does not make the pair atomic under Read Committed. Either collapse them into a single statement, as below, or take an explicit row lock with SELECT FOR UPDATE and accept the queueing that comes with it.

How do you decrement stock atomically in Postgres?

Put the check inside the UPDATE. The WHERE clause carries the condition, RETURNING tells you whether you won, and a CHECK constraint makes a negative quantity impossible even if some future code path forgets the rule.

CREATE TABLE sku_stock (
  sku       text    PRIMARY KEY,
  initial   integer NOT NULL,                       -- what we loaded for the sale, never changes
  available integer NOT NULL CHECK (available >= 0) -- the backstop: Postgres refuses to go negative
);
INSERT INTO sku_stock VALUES ('SKU-FLASH-01', 500, 500);

-- The whole "check stock and take it" step. No SELECT first.
UPDATE sku_stock
SET    available = available - 2
WHERE  sku = 'SKU-FLASH-01'
  AND  available >= 2          -- re-evaluated after any concurrent writer commits
RETURNING available;           -- 1 row back = you got the units. 0 rows = you did not.

This is safe because of how Read Committed treats a contended row. If a second UPDATE meets a row that a concurrent transaction is changing, it waits for that transaction to commit or roll back, and then the PostgreSQL documentation says the search condition is re-evaluated against the updated version of the row. Walk it through with stock 1 and two buyers who each want 1. Buyer A takes the row lock, sets available to 0 and commits. Buyer B was waiting, re-checks 0 is at least 1, finds it false and touches zero rows. Your code reads rowCount 0 as sold out.

The RETURNING clause matters too: it hands back the new value from the same statement, so you never need a follow-up SELECT, which would be a second read of a number that is already stale. For many sales this single statement is the whole design. Everything after this section exists because one hot row has a throughput ceiling, not because the statement is wrong.

Should a flash sale use a Redis counter or Lua script instead?

Redis is a fine front gate, as long as the decrement is one atomic step. DECRBY alone is atomic, but it will happily take stock below zero, and it initialises a missing key to 0 before it runs, so an unloaded counter goes to a negative number rather than failing. To check and decrement together you need a Lua script. The Redis documentation guarantees atomic execution of a script, and says all other server activity is blocked while it runs, so no other client can slip between the GET and the DECRBY.

-- reserve.lua: take qty units and record a hold, as ONE atomic step.
-- KEYS[1] = stock:SKU-FLASH-01     (integer counter, loaded before the sale)
-- KEYS[2] = hold:SKU-FLASH-01:<orderKey>
-- ARGV[1] = qty   ARGV[2] = hold TTL in seconds
-- Returns: units remaining (0 or more) | -1 sold out | -2 this order key already holds stock

if redis.call('EXISTS', KEYS[2]) == 1 then
  return -2                                    -- a retry of the same order: do not take stock twice
end

-- GET on a missing key gives Lua false, so "or '0'" makes an unloaded counter read as sold out
local stock = tonumber(redis.call('GET', KEYS[1]) or '0')
local qty   = tonumber(ARGV[1])

if stock < qty then
  return -1
end

redis.call('DECRBY', KEYS[1], qty)
redis.call('SET', KEYS[2], qty, 'EX', ARGV[2])  -- the hold marker expires on its own
return stock - qty

import Redis from "ioredis";
import { readFileSync } from "node:fs";

const redis = new Redis();
const script = readFileSync("reserve.lua", "utf8");

// eval(script, numberOfKeys, ...keys, ...args). Both keys are passed in KEYS, never built inside the script.
const result = Number(
  await redis.eval(script, 2, "stock:SKU-FLASH-01", "hold:SKU-FLASH-01:" + orderKey, 1, 600),
);

if (result === -1) return { status: "sold_out" };    // cheap rejection, Postgres never hears about it
if (result === -2) return { status: "already_held" };
// result >= 0: Redis let you through. Postgres still has the final say.

Two details in that script are deliberate. Every key it touches is passed through KEYS, because the documentation says a script should only access keys whose names are given as input arguments. And the hold marker is keyed by order key, so a retry of the same order returns minus 2 instead of taking stock a second time. Redis answers in memory, so it can say sold out to most of the crowd without Postgres ever seeing them. But it only ever advises: a successful result means you may try Postgres, and Postgres still has the final say.

A TTL on the Redis hold marker does not give the stock back. When the marker expires the counter stays decremented. Returning units is the job of the sweeper in the next section, which also pushes the corrected number back to Redis. Also remember that the script cache is volatile: after a restart or failover, load the script again before calling EVALSHA.

How do reservations with a TTL and idempotent orders work?

Taking stock when the buyer clicks is not the same as selling it. Payment takes time and some buyers never finish. So the click creates a reservation that holds the units for a fixed window, payment confirms it, and a sweeper releases any hold that lapsed. A reservation has three states: held, confirmed, expired. The reservations table also carries a unique order_key, which is the idempotency key the client generates once per checkout attempt.

CREATE TABLE reservations (
  id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  sku        text        NOT NULL REFERENCES sku_stock(sku),
  buyer_id   text        NOT NULL,
  qty        integer     NOT NULL CHECK (qty > 0),
  order_key  text        NOT NULL UNIQUE,            -- client-generated idempotency key
  status     text        NOT NULL DEFAULT 'held',    -- held | confirmed | expired
  expires_at timestamptz NOT NULL
);

-- Confirm after payment. Fails (0 rows) if the hold already expired or was confirmed.
UPDATE reservations
SET    status = 'confirmed'
WHERE  id = $1 AND status = 'held' AND expires_at > now()
RETURNING sku, qty;

-- Release sweeper, every 30 s. Expire holds and give the units back in one statement.
WITH expired AS (
  UPDATE reservations
  SET    status = 'expired'
  WHERE  status = 'held' AND expires_at <= now()
  RETURNING sku, qty
), totals AS (
  SELECT sku, sum(qty) AS qty FROM expired GROUP BY sku
)
UPDATE sku_stock s
SET    available = s.available + t.qty
FROM   totals t
WHERE  s.sku = t.sku
RETURNING s.sku, s.available;   -- push these numbers back to the Redis counter

The order of operations inside the transaction is the point. Insert the reservation first with ON CONFLICT DO NOTHING, then decrement. If the insert touched no row, this order key was already processed, so you return the original result and take no stock. If the decrement touched no row, you roll back, which also removes the reservation row. A buyer who double-clicks or a client that retries after a timeout therefore costs one unit, not two.

// Idempotent reserve: insert the order key FIRST, decrement second, one transaction.
async function reserve(sku: string, buyerId: string, qty: number, orderKey: string) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const ins = await client.query(
      `INSERT INTO reservations (sku, buyer_id, qty, order_key, expires_at)
       VALUES ($1, $2, $3, $4, now() + interval '10 minutes')
       ON CONFLICT (order_key) DO NOTHING
       RETURNING id`,
      [sku, buyerId, qty, orderKey],
    );
    if (ins.rowCount === 0) {                       // same key seen before: a retry, not a new order
      await client.query("ROLLBACK");
      return findReservationByKey(orderKey);        // hand back the original answer
    }

    const dec = await client.query(
      "UPDATE sku_stock SET available = available - $2 WHERE sku = $1 AND available >= $2 RETURNING available",
      [sku, qty],
    );
    if (dec.rowCount === 0) {                       // sold out: rolling back also removes the insert
      await client.query("ROLLBACK");
      return { status: "sold_out" as const };
    }

    await client.query("COMMIT");
    return { status: "held" as const, id: ins.rows[0].id };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Pick the hold length from how long payment really takes, then add margin. The 10 minutes here is a choice for illustration, and it has a price you can compute: with 500 units and one unit per order, at most 500 holds can be open, so in the worst case, where every buyer walks away, stock stays frozen for 600 seconds. Show held units separately from sold units in the product page copy so a buyer who sees sold out knows a release may still come.

How does a waiting room shed load before it reaches the database?

The database should never see the crowd, only the number of buyers it can serve. Suppose 20,000 buyers arrive for 500 units, and assume, as a number to replace with your own measurement, that one decrement holds the row lock for 5 ms. That is 1,000 divided by 5, or 200 decrements per second on one row. Let all 20,000 through and the serial work is 20,000 times 5 ms, which is 100 seconds, with a connection pool full of waiting requests. A waiting room turns that into a short, bounded stream.

Put a sold-out flag in Redis next to the counter. Once the Lua script returns minus 1, every later request is rejected with a cheap response and never reaches Postgres.
Before the sale opens, send buyers to a queue page holding a position, not to the checkout. A random draw at the opening second is fairer than first come first served, which rewards the fastest connection.
Admit buyers at a rate your database can serve, for example 200 per second under the assumption above, by handing each admitted buyer a short-lived signed token that checkout requires.
Cap quantity per buyer, and rate limit per account and per IP, so one person cannot spend the 500 units in one click.

With the sold-out flag in front, the count that reaches Postgres is roughly the 500 successful holds plus whatever was already in flight, not 20,000. At 5 ms each that is about 2.5 seconds of lock time instead of 100. This is the same shape as any load-shedding design: be cheap and early about saying no, and spend expensive resources only on requests that can still succeed.

What if one hot row becomes the bottleneck?

When a single SKU is the whole sale, every decrement queues on one row lock, and the ceiling is fixed by how long the lock is held. The fix is to split the counter. Instead of one row with 500, keep 5 rows with 100 each. Under the same 5 ms assumption each row serves 200 per second, so five rows give a ceiling of 1,000 per second. A request starts at a random shard and walks forward when it misses, and only after five misses in a row does it report sold out.

-- 500 units split across 5 rows of 100. Five rows = five independent locks.
CREATE TABLE sku_stock_shard (
  sku       text     NOT NULL,
  shard     smallint NOT NULL,
  available integer  NOT NULL CHECK (available >= 0),
  PRIMARY KEY (sku, shard)
);
INSERT INTO sku_stock_shard
SELECT 'SKU-FLASH-01', g, 100 FROM generate_series(0, 4) AS g;

-- Each request starts at a random shard (0-4) and walks forward on a miss.
UPDATE sku_stock_shard
SET    available = available - $3
WHERE  sku = $1 AND shard = $2 AND available >= $3
RETURNING shard;
-- 0 rows: try shard (n+1) mod 5. Five misses in a row = the SKU is sold out.

-- Real remaining stock is the sum, and only the sum:
SELECT sum(available) FROM sku_stock_shard WHERE sku = 'SKU-FLASH-01';

Sharding has a cost, and it is arithmetic rather than opinion. Remaining stock is only meaningful as the sum across shards. And the tail gets awkward: if all five shards have 1 unit left, there are 5 units free, but a buyer who wants 2 fails on every shard. Cap the quantity at 1 for the final stretch, or let the sweeper rebalance units between shards. A worst-case buyer also pays up to five sequential attempts, about 25 ms of lock time under the assumption, which is still better than queueing behind thousands.

Do not shard until you have measured one row. A single UPDATE with a CHECK constraint is far easier to reason about, and the waiting room usually removes the need for shards. Add them when lock wait time on that one row is the thing your monitoring points at.

How do you reconcile Redis stock with the database?

Two stores will drift, so decide in advance which one wins. Postgres is the truth because it has the CHECK constraint and the reservation rows, and Redis is a cache in front of it. The first query shows Postgres can audit itself: available must equal what you loaded minus live claims, and any row it returns means a bug somewhere in the write paths. The second compares the Redis counter with Postgres, and the direction of the gap decides the response.

-- What Postgres says should be available: what we loaded, minus live claims on it.
SELECT s.sku,
       s.available AS db_available,
       s.initial - COALESCE(sum(r.qty) FILTER (WHERE r.status IN ('held', 'confirmed')), 0) AS expected
FROM   sku_stock s
LEFT JOIN reservations r ON r.sku = s.sku
GROUP  BY s.sku, s.initial, s.available
HAVING s.available <> s.initial - COALESCE(sum(r.qty) FILTER (WHERE r.status IN ('held', 'confirmed')), 0);
-- Any row returned is a bug in Postgres itself. It should return nothing, ever.

// Redis is the cache, Postgres is the truth. Compare, then correct Redis, never the other way.
const dbAvailable = Number(
  (await pool.query("SELECT available FROM sku_stock WHERE sku = $1", [sku])).rows[0].available,
);
const cached = Number((await redis.get("stock:" + sku)) ?? "0");

if (cached > dbAvailable) {
  // Redis thinks there is more than Postgres does: it will let through requests Postgres must reject.
  await redis.set("stock:" + sku, dbAvailable);
  alertOps("redis oversold by " + (cached - dbAvailable));
} else if (cached < dbAvailable) {
  // Redis thinks there is less: it undersells. Safe, and often just a hold in flight,
  // so only correct it when the same gap is still there on the next run.
  logGap(sku, dbAvailable - cached);
}
// Run it every minute.

Redis higher than Postgres is the dangerous direction, because Redis will wave through requests Postgres must reject, so correct it at once and alert. Redis lower than Postgres only means the sale undersells, and it is often a hold in flight, so correct it only when the same gap survives a second run. Two further rules close the loop: the release sweeper writes its new available figure back to Redis, and the counter is always rebuilt from Postgres, never the reverse, if Redis is ever restored from an older state.

Which approach should you choose, and what is the checklist?

These layers stack rather than compete. Postgres alone is correct and is enough until one row becomes the ceiling. Redis in front buys throughput and cheap rejection. Reservations make payment safe. Shards raise the ceiling only when measurement shows you need them.

Approach	Oversell-safe	Throughput ceiling	Failure to plan for
Read, then write	No	Wrong under any real concurrency	Silent oversell and phantom units
Atomic Postgres UPDATE	Yes	One row lock: 1,000 divided by lock-hold in ms per second	Lock queue on a hot row, connection pool exhausted
Redis Lua counter	Yes, within Redis	In memory, one script at a time	Drift from Postgres after a restart; expiry does not return stock
Reservation with TTL	Yes, if the decrement is	Same as the counter underneath	Abandoned holds freeze stock until the sweeper runs
Sharded counters	Yes	Shard count times the single-row ceiling	Stranded units when the remainder is split thinly

Use this as a build order, and stop at the first step that meets your traffic.

Write the atomic UPDATE with RETURNING and a CHECK constraint, and treat zero rows as sold out.
Add reservations with a unique order key, a hold window based on real payment times, and a release sweeper.
Add the Redis Lua gate and sold-out flag once Postgres connections, not logic, are the limit.
Add the waiting room and per-buyer limits before the sale, not during it.
Shard the counter only if lock wait on the single row is what you measured, and run the reconciliation job from day one.

The rule to carry away: the database decides whether stock exists, in the same statement that takes it, and everything else is a way to keep the crowd away from that statement. Atomic decrement first, reservations second, Redis and queues third, and Postgres wins every disagreement.

Frequently Asked Questions

Design a Flash Sale System That Never Oversells Stock

Frequently Asked Questions

Design a Flash Sale System That Never Oversells Stock

Why does read-then-write oversell stock?

How do you decrement stock atomically in Postgres?

Should a flash sale use a Redis counter or Lua script instead?

How do reservations with a TTL and idempotent orders work?

How does a waiting room shed load before it reaches the database?

What if one hot row becomes the bottleneck?

How do you reconcile Redis stock with the database?

Which approach should you choose, and what is the checklist?

Why does read-then-write oversell stock?

How do you decrement stock atomically in Postgres?

Should a flash sale use a Redis counter or Lua script instead?

How do reservations with a TTL and idempotent orders work?

How does a waiting room shed load before it reaches the database?

What if one hot row becomes the bottleneck?

How do you reconcile Redis stock with the database?

Which approach should you choose, and what is the checklist?