How to Stop Overselling in Inventory Management Software Development (Node.js and PostgreSQL) published: false

Two checkout requests arrive 4 ms apart for the last unit of a SKU. Both read on_hand = 1, both pass the check in application code, and both write. Stock ends at -1 and one customer gets an apology email. This race appears wherever a web store, a marketplace sync, and a warehouse scanner write to the same row, and it grows worse with every added channel or worker process. It is the first bug we design against in inventory management software development for concurrent, multi-writer systems. The fix is small: let PostgreSQL decide the outcome, not your Node.js process.

Context and Setup

The scenario is a Node.js 20 API with pg talking to PostgreSQL 15, serving several channels that reserve stock before payment clears. PostgreSQL is a safe base for this: the 2025 Stack Overflow Developer Survey reports it as the most-used database among professional developers (55.6%).

You need two tables. stock_levels holds sku, location, on_hand, and reserved. reservations holds a unique idempotency_key, qty, status, and expires_at. Available stock is on_hand - reserved, and it must never go below zero.

Reserving Stock Atomically in Inventory Management Software Development

The short answer is one conditional UPDATE guarded by a constraint. Three steps get you there.

Step 1: Make the database refuse negative stock

In Inventory Management Software Development a constraint protects the invariant from every writer, including services that do not exist yet.

1. Fix any existing rows that violate the rule.
2. Add the constraint in a migration with a short lock timeout.

-- Why: even buggy code or a manual script cannot oversell past this line
ALTER TABLE stock_levels
  ADD CONSTRAINT available_not_negative CHECK (on_hand - reserved >= 0);

Step 2: Reserve with One Conditional UPDATE in Inventory Management Software Development

Check and write must be one statement. PostgreSQL locks the row during UPDATE, and under READ COMMITTED a waiting transaction re-evaluates the WHERE clause after the lock clears (see the PostgreSQL transaction isolation docs). The second request then matches zero rows.

import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function reserve({ key, sku, location, qty, ttlSeconds = 900 }) {
  const db = await pool.connect();
  try {
    await db.query("BEGIN");
    // Why: the unique key turns a client retry into a no-op instead of a double hold
    const hold = await db.query(
      `INSERT INTO reservations (idempotency_key, sku, location, qty, status, expires_at)
       VALUES ($1, $2, $3, $4, 'held', now() + make_interval(secs => $5))
       ON CONFLICT (idempotency_key) DO NOTHING RETURNING id`,
      [key, sku, location, qty, ttlSeconds]
    );
    if (hold.rowCount === 0) { await db.query("ROLLBACK"); return { status: "duplicate" }; }

    // Why: check and write in one statement avoids the read-then-write race
    const upd = await db.query(
      `UPDATE stock_levels SET reserved = reserved + $3
       WHERE sku = $1 AND location = $2 AND on_hand - reserved >= $3`,
      [sku, location, qty]
    );
    if (upd.rowCount === 0) { await db.query("ROLLBACK"); return { status: "insufficient" }; }

    await db.query("COMMIT");
    return { status: "reserved", id: hold.rows[0].id };
  } catch (err) {
    await db.query("ROLLBACK");
    throw err;
  } finally {
    db.release();
  }
}

Step 3: Expire holds and weigh the alternatives

Inventory Management Software Development Abandoned carts must release stock, or reserved drifts upward. Run this worker query every minute.

-- Why: SKIP LOCKED lets several workers run without blocking each other
WITH expired AS (
  SELECT id, sku, location, qty FROM reservations
  WHERE status = 'held' AND expires_at < now()
  FOR UPDATE SKIP LOCKED LIMIT 100
), flipped AS (
  UPDATE reservations r SET status = 'expired'
  FROM expired e WHERE r.id = e.id
  RETURNING e.sku, e.location, e.qty
), totals AS (
  -- Why: UPDATE ... FROM applies only one source row per target row, so sum first
  SELECT sku, location, sum(qty) AS qty FROM flipped GROUP BY sku, location
)
UPDATE stock_levels s SET reserved = s.reserved - t.qty
FROM totals t WHERE s.sku = t.sku AND s.location = t.location;

The trade-offs against other designs:

• SELECT ... FOR UPDATE first: correct, but it adds a round trip and holds the lock longer.
• Optimistic version column: cheap for cold SKUs, yet hot SKUs burn retries.
• Redis counters: fast, but they create a second source of truth that needs reconciliation.

The conditional update serializes writers per row. For a flash-sale SKU, split stock into bucket rows.

Real-World Application

In one of our inventory management software development projects at Oodles

Key Takeaways

• In inventory Management Software Development put the invariant in the database with a CHECK constraint, because application checks cannot see concurrent writers.
• Collapse check and write into one conditional UPDATE; READ COMMITTED re-evaluates the WHERE clause after a lock wait.
• Use an idempotency key so client and queue retries never double-reserve.
• Expire holds with FOR UPDATE SKIP LOCKED workers, and sum quantities before updating the parent rows.
• Choose Redis or optimistic locking only after measuring contention on hot SKUs.

Questions about reservation design or hot-SKU contention? Drop them in the comments, or reach us about inventory management software development.

What is inventory management software development?
It is the engineering of systems that track stock across locations and channels, reserve units for orders, and keep counts consistent under concurrent writes. Core work covers data models, reservation logic, integrations, and audit trails.

How do you prevent overselling in inventory software?
Combine a database CHECK constraint with a single conditional UPDATE that tests available stock in its WHERE clause. The database serializes competing writers per row, so only requests that fit the remaining quantity succeed.

Should I use Redis or PostgreSQL for stock counts?
In Inventory Management Software Development Use PostgreSQL as the source of truth because it gives transactions, constraints, and audit history. Add Redis only as a read cache or rate limiter, and reconcile it against the database to avoid drift.

Which isolation level do stock updates need?
READ COMMITTED is enough for a conditional UPDATE, because PostgreSQL re-checks the WHERE clause after a lock wait. Use SERIALIZABLE only for multi-row invariants, and be ready to retry on serialization failures.

Why do reservations need idempotency keys?
Clients, queues, and gateways retry failed calls. A unique idempotency key lets the second attempt hit ON CONFLICT DO NOTHING, so one order can never hold stock twice.