How Read Replica Lag Let Us Sell 340 Units of a Product We Had 60 Of

12:00:04 UTC. A flash sale for a limited restock goes live, 60 units, and the order count in Datadog is already past 60 before the page has been up for ten seconds. Nobody in the incident channel believes the number at first.

By the time someone thought to actually distrust the dashboard instead of the traffic, we'd taken 340 confirmed orders against 60 units of stock. The fix took less code than the postmortem took to write, but the mechanism is the kind of thing that only shows up under real concurrent load, which is exactly why it survived three months of testing on staging.

the setup

We run a mid-size commerce backend on Postgres 15: one primary, two streaming read replicas. Standard split for read-heavy traffic. Writes go to the primary, product listing pages and inventory checks go to whichever replica the load balancer picks. It's a pattern that works fine for browsing traffic, which is 95% of what the site does on a normal day.

Checkout was the exception, or was supposed to be. The order-placement path checks available_quantity before confirming, decrements it inside the same transaction as the order insert, and that transaction runs on the primary. That part was correct. The bug was one query earlier, in the availability check that ran before the transaction even opened.

async function checkAvailability(productId: string, qty: number) {
  // readPool routes to whichever replica has the fewest active connections
  const { rows } = await readPool.query(
    'SELECT available_quantity FROM inventory WHERE product_id = $1',
    [productId]
  );
  return rows[0].available_quantity >= qty;
}

async function placeOrder(productId: string, qty: number, userId: string) {
  const ok = await checkAvailability(productId, qty);
  if (!ok) throw new Error('OUT_OF_STOCK');

  // Only the decrement itself was transactional on the primary
  return writePool.query(
    `UPDATE inventory SET available_quantity = available_quantity - $1
     WHERE product_id = $2 AND available_quantity >= $1
     RETURNING available_quantity`,
    [qty, productId]
  );
}

The UPDATE ... WHERE available_quantity >= $1 guard is a correct optimistic check on the primary. If two requests race for the last unit, only one UPDATE succeeds; the other returns zero rows and the order fails cleanly. That part of the system worked exactly as designed the whole time. The problem was that checkAvailability ran first, on a replica, and its result decided whether we ever reached the safe part.

the scramble

First theory in the incident channel, ninety seconds in: bot traffic. A flash sale for a known-scarce item draws scalping scripts, and 340 orders in under a minute looked like exactly that pattern. We pulled request logs expecting to find a cluster of identical user agents hitting the endpoint in a tight loop.

We didn't find that. The 340 orders came from 340 distinct, mostly-legitimate-looking sessions, spread across normal browser user agents, arriving over about 40 seconds. Whatever was happening, it wasn't a script hammering the endpoint. It was ordinary traffic getting an ordinary "yes" from the availability check, over and over, well past the point where the answer should have flipped to "no."

Second theory: the UPDATE ... WHERE available_quantity >= $1 guard itself was broken, maybe a type coercion issue letting negative comparisons through. We checked the actual row on the primary directly.

SELECT product_id, available_quantity FROM inventory WHERE product_id = 'flash-60';
 product_id | available_quantity
------------+---------------------
 flash-60   |                -280

Negative 280. So the guard on the primary had done its job: it kept letting the decrement through because each individual UPDATE was evaluated against whatever the count was at that instant, and by the time we looked, 400 near-simultaneous decrements had each independently passed a check that was comparing against a number that was already wrong before the first one landed. The guard prevents the count from being misread by concurrent writers on the primary. It does nothing about a read that happened somewhere else entirely.

the hunt

That reframed the question: not "why did the guard fail" but "why did checkAvailability keep saying yes." We pulled the query logs for the read pool and found every single availability check during the incident window had gone to the same replica, replica-b. The load balancer's least-connections routing had funneled the flash-sale traffic there because it happened to be the quieter of the two replicas when the sale started.

We checked replication lag on replica-b for that window.

SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

       replica_lag
------------------------
 00:00:41.283412

Forty-one seconds of lag, right at the moment the sale opened. Not a permanently broken replica, not something our standard lag alerting would have caught. Our alert threshold was 60 seconds, tuned for steady-state traffic where a 41-second blip on a browsing query is invisible to a user. Nobody had considered what 41 seconds of lag does to a query that gates a write.

The lag itself had a mundane cause: the flash sale also triggered a burst of unrelated writes to a page_views table for analytics, and WAL replay on replica-b briefly fell behind under that combined write volume. Nothing exotic, just enough concurrent write pressure to push replay a few dozen seconds behind the primary for about a minute.

For that minute, every read against replica-b was answering a question about inventory that was already stale by the time the response left Postgres, and stayed stale for as long as the client took to act on it. 400-odd checkout attempts asked "is there stock" during that window. All 400 got the pre-sale answer, effectively "yes, 60 available," because the replica hadn't yet replayed the WAL records for the orders that had already decremented the count on the primary.

the find

Root cause: the availability check that gated whether we even attempted an order was reading from a replica with no bound on how stale that replica was allowed to be. The transactional decrement on the primary was correct and never let stock go negative from its own perspective. Each write correctly saw the count after all prior writes. But the gate in front of it, the thing deciding whether to try in the first place, was reading a copy of the data that could lag the source of truth by however long WAL replay happened to take under load, with no circuit breaker if that lag grew.

340 orders confirmed against 60 units. 280 of those orders should never have reached the point where they were attempted, because at the moment each one asked "is this in stock," the honest answer, on the primary, right then, was already no.

the fix

The fix was narrow: any read that gates a write with real-world consequences (inventory, balances, seat counts) has to either read from the primary or refuse to answer if the replica it would otherwise use is lagging past a tight threshold. We didn't want to route all read traffic to the primary, that defeats the point of having replicas. We only needed this for the specific query class that decides whether to let a write proceed.

const MAX_ACCEPTABLE_LAG_MS = 250;

async function checkAvailability(productId: string, qty: number) {
  const replica = await pickReplicaWithFreshness(MAX_ACCEPTABLE_LAG_MS);

  // No replica was fresh enough, read from the primary instead of guessing
  const pool = replica ?? primaryPool;

  const { rows } = await pool.query(
    'SELECT available_quantity FROM inventory WHERE product_id = $1',
    [productId]
  );
  return rows[0].available_quantity >= qty;
}

async function pickReplicaWithFreshness(maxLagMs: number) {
  for (const replica of readPools) {
    const { rows } = await replica.query(
      'SELECT EXTRACT(MILLISECONDS FROM now() - pg_last_xact_replay_timestamp()) AS lag_ms'
    );
    if (rows[0].lag_ms !== null && rows[0].lag_ms < maxLagMs) {
      return replica;
    }
  }
  return null;
}

250ms was chosen deliberately conservative: our replicas typically report under 15ms of lag under normal load, so 250ms is already an order of magnitude past steady state and still catches the failure mode without the check itself adding meaningful latency. The final UPDATE ... WHERE available_quantity >= $1 decrement stayed exactly as it was, that guard was never the problem.

We also lowered the replication lag alert threshold from 60 seconds to 5, and split it into two separate alerts: a low-severity one at 5 seconds for anything writer-side to investigate, and a page at 20 seconds, since we now know 41 seconds of lag during a traffic spike is enough to cause real damage, not just stale page content.

the aftermath

Cancelling 280 orders and issuing refunds cost us a support queue backlog for two days and a round of "why was my order cancelled" emails, none of it dangerous, all of it avoidable. We shipped store credit to everyone affected as an apology, which cost more than the 280 units would have.

We've run two more flash sales since, both routed to a replica we now confirm as fresh before every gating read. Neither oversold. The 250ms check adds a query and a few milliseconds to the hot path. It's the cheapest insurance we've bought all year.