DEV Community: subashthiruppathy

Cache Stampede (Thundering Herd) in Node.js & Redis: Preventing Database Meltdowns on Cache Misses

subashthiruppathy — Mon, 26 Oct 2026 15:26:08 +0000

You added Redis in front of an expensive query, and it worked beautifully. The dashboard that took 800 ms now returns in 3 ms. The database CPU dropped from 70% to 10%. Everyone is happy.

Then, one afternoon, the database CPU spikes to 100%, latency goes vertical, and the incident channel lights up. The graphs show a sharp, periodic pattern: calm, spike, calm, spike. Nothing deployed. Traffic didn't change.

What happened is a cache stampede (also called the thundering herd or dogpile problem). A hot key expired, and for the next few hundred milliseconds, every request that wanted it missed the cache at the same moment and ran the expensive query itself.

This post explains exactly how that happens and builds up a set of defenses in Node.js with ioredis, from a ten-line fix to a production-grade helper.

Anatomy of a stampede

Here's the classic cache-aside code:

async function getLeaderboard() {
  const cached = await redis.get('leaderboard');
  if (cached) return JSON.parse(cached);

  const data = await db.query(EXPENSIVE_SQL);         // takes 800 ms
  await redis.set('leaderboard', JSON.stringify(data), 'EX', 60);
  return data;
}

Perfectly reasonable. Now watch what happens at the moment the key expires, with 500 requests per second hitting this endpoint:

t=0.000  key expires
t=0.001  request 1: cache miss → starts the 800 ms query
t=0.003  request 2: cache miss → starts the 800 ms query
t=0.004  request 3: cache miss → starts the 800 ms query
  ...
t=0.800  request 1 finishes, writes the cache
         (by now ~400 requests have missed and started the same query)

The query takes 800 ms, so for 800 ms every request is a miss. At 500 req/s that's ~400 identical expensive queries launched against the database in under a second, instead of one. The database slows down under that load, which makes the queries take longer, which widens the miss window, which launches more queries. It's a feedback loop.

Problem	What happens	Typical cause
Stampede / thundering herd	Many concurrent misses on one hot key	A popular key expires
Avalanche	Many different keys expire together, or the cache is gone	Same TTL set in a batch; Redis restart or failover
Penetration	Requests for data that doesn't exist always miss and hit the database	Missing IDs, scanners, bad input

Defense 1: Single-flight (collapse concurrent misses in one process)

The cheapest fix: if a load for a key is already in progress in this process, wait for it instead of starting another. Store the in-flight promise in a map.

const inflight = new Map<string, Promise<unknown>>();

async function singleFlight<T>(key: string, fn: () => Promise<T>): Promise<T> {
  const existing = inflight.get(key);
  if (existing) return existing as Promise<T>;

  const promise = fn().finally(() => inflight.delete(key));
  inflight.set(key, promise);
  return promise;
}

async function getLeaderboard() {
  const cached = await redis.get('leaderboard');
  if (cached) return JSON.parse(cached);

  return singleFlight('leaderboard', async () => {
    const data = await db.query(EXPENSIVE_SQL);
    await redis.set('leaderboard', JSON.stringify(data), 'EX', 60);
    return data;
  });
}

Now 400 concurrent misses in one process become one database query, and all 400 callers share its result. Because Node.js is single-threaded, the check-then-set on the map has no race.

The limitation is in the name: it's per process. With 20 instances you get up to 20 concurrent loads per expiry, not 400, a big improvement but not a guarantee. For that, you need coordination through Redis.

Defense 2: A distributed lock so only one process rebuilds the value

Use Redis itself to elect one loader across the whole fleet. SET key value NX PX ttl sets the key only if it doesn't exist, atomically:

import { randomUUID } from 'node:crypto';

async function tryLock(key: string, ttlMs: number): Promise<string | null> {
  const token = randomUUID();
  const ok = await redis.set(`lock:${key}`, token, 'PX', ttlMs, 'NX');
  return ok === 'OK' ? token : null;
}

// Release only if we still own the lock. Compare-and-delete must be atomic, so use Lua.
const RELEASE = `
  if redis.call('get', KEYS[1]) == ARGV[1] then
    return redis.call('del', KEYS[1])
  else
    return 0
  end`;

async function unlock(key: string, token: string) {
  await redis.eval(RELEASE, 1, `lock:${key}`, token);
}

Two details matter:

The random token + Lua release ensures you never delete someone else's lock. Without it, if your work outlasts the lock's TTL, another process acquires the lock, and your delayed DEL releases their lock.
The lock TTL is a safety net, not a timer. It must exceed your worst-case load time, so a crashed process can't hold the lock forever.

Then the miss path becomes:

async function getWithLock<T>(key: string, loader: () => Promise<T>, ttlSec: number): Promise<T> {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const token = await tryLock(key, 10_000);
  if (token) {
    try {
      const value = await loader();
      await redis.set(key, JSON.stringify(value), 'EX', ttlSec);
      return value;
    } finally {
      await unlock(key, token);
    }
  }

  // Someone else is loading. Wait briefly for their result instead of piling onto the database.
  for (let i = 0; i < 40; i++) {                 // ~2s max
    await sleep(50);
    const filled = await redis.get(key);
    if (filled) return JSON.parse(filled);
  }
  return loader();                                // gave up waiting: load it ourselves
}

Is this a "correct" distributed lock? Not in the strict sense. A paused process can outlive its lease, and a single Redis node can fail over mid-lock. But for cache filling that doesn't matter: the worst case is that two processes load the same value and write the same result. You're using the lock to reduce duplicate work, not to guard a critical section, so a simple SET NX PX is enough. (If you're protecting something where duplicates are harmful, that's a different problem and needs fencing tokens or a database constraint, not a cache lock.)

The downside: waiters are blocked. Everyone who missed is stuck polling for up to the load time. The next defense removes that wait entirely.

Defense 3: Stale-while-revalidate (never make users wait)

The insight: for most cached data, slightly old data is better than a slow response. So don't delete the value when it "expires". Keep it around, mark it stale, and refresh it in the background while still serving the old copy.

Store two expirations: a soft TTL (when it becomes stale) and a hard TTL (when Redis actually deletes it), longer by a grace window.

interface Entry<T> {
  v: T;
  softExp: number;   // epoch ms: after this, the value is stale and should be refreshed
}

async function set<T>(key: string, value: T, softTtlMs: number, graceMs: number) {
  const entry: Entry<T> = { v: value, softExp: Date.now() + softTtlMs };
  await redis.set(key, JSON.stringify(entry), 'PX', softTtlMs + graceMs);
}

On read:

Fresh (now < softExp): return it.
Stale (past softExp but still in Redis): return it immediately, and if you win the lock, refresh in the background.
Missing (past the hard TTL): load synchronously. This is the only path where someone waits.

Now the expensive load happens off the request path. Users see 3 ms responses all the time, and the database sees one refresh per interval. As a bonus, if the refresh fails (the database is struggling), you keep serving the stale copy, a "stale-if-error" safety net that turns a database outage into slightly old data rather than errors.

Defense 4: Probabilistic early refresh (no locks needed)

Another approach avoids coordination entirely: have requests randomly refresh the value shortly before it expires, with probability that rises as expiry approaches. By the time the key would expire, someone has almost certainly already refreshed it.

This is the XFetch algorithm from the paper Optimal Probabilistic Cache Stampede Prevention (Vattani et al.). Store how long the value took to compute (delta), and on each read decide:

// Refresh early if:   now - delta * beta * ln(random()) >= expiry
function shouldRefreshEarly(entry: { softExp: number; delta: number }, beta = 1): boolean {
  return Date.now() - entry.delta * beta * Math.log(Math.random()) >= entry.softExp;
}

Math.log(Math.random()) is negative, so the subtraction pushes "now" forward by a random amount. The expression is scaled by delta:

Expensive values (large delta) start refreshing earlier, because they need more lead time.
Cheap values refresh almost right at expiry.
beta (default 1) tunes aggressiveness. Higher means earlier refreshes and more cache churn.

Used alone, XFetch can still let a few concurrent requests decide to refresh simultaneously, but instead of hundreds it's a handful. Combined with single-flight or a lock, it's very effective.

Defense 5: Prevent synchronized expiry with TTL jitter

If you warm 10,000 keys at deploy time with EX 3600, they all expire in the same second an hour later. That's the avalanche pattern. The fix is one line: add randomness.

const jitter = (ms: number, spread = 0.1) =>
  Math.round(ms * (1 - spread + Math.random() * spread * 2));   // ±10%

await redis.set(key, value, 'PX', jitter(60 * 60 * 1000));

Keys written together now expire spread across a window instead of all at once. It's trivial, it costs nothing, and it should be your default for every cached key.

Defense 6: Negative caching (stop penetration)

If a client requests /users/999999999 (an ID that doesn't exist), the cache never has it, so every request goes to the database, forever. Scanners and bugs generate these constantly.

Cache the absence too, with a short TTL:

// Wrap values so that "null" is a legitimate cached result, distinct from "key not in cache"
const entry = { v: user ?? null, softExp: Date.now() + (user ? 60_000 : 10_000) };

Use a shorter TTL for "not found" (seconds, not minutes) so a newly created record shows up quickly, or invalidate that key on create. This is why the entry wrapper above stores v inside an object. A raw null would be indistinguishable from a miss.

For very large, hostile key spaces, a Bloom filter in front of the cache can reject IDs that definitely don't exist, but start with negative caching.

Putting it together: a reusable helper

Here's a compact implementation combining single-flight, Redis lock, stale-while-revalidate, XFetch, jitter, and a safe fallback if Redis is unhealthy:

// cache.ts
import { randomUUID } from 'node:crypto';
import type Redis from 'ioredis';

interface Entry<T> { v: T; softExp: number; delta: number }

export interface CacheOpts {
  ttlMs: number;          // how long the value is considered fresh
  staleMs?: number;       // extra time it may be served stale while refreshing (default: 5x ttlMs)
  lockMs?: number;        // lock safety TTL: must exceed worst-case load time
  waitMs?: number;        // how long a cold-miss waiter polls for another loader's result
}

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
const jitter = (ms: number) => Math.round(ms * (0.9 + Math.random() * 0.2));

const RELEASE = `
  if redis.call('get', KEYS[1]) == ARGV[1] then return redis.call('del', KEYS[1]) else return 0 end`;

export function createCache(redis: Redis, log = console) {
  const inflight = new Map<string, Promise<unknown>>();

  async function tryLock(key: string, ms: number) {
    const token = randomUUID();
    const ok = await redis.set(`lock:${key}`, token, 'PX', ms, 'NX');
    return ok === 'OK' ? token : null;
  }
  const unlock = (key: string, token: string) =>
    redis.eval(RELEASE, 1, `lock:${key}`, token).catch(() => {});

  async function load<T>(key: string, loader: () => Promise<T>, o: Required<CacheOpts>): Promise<T> {
    const started = Date.now();
    const v = await loader();
    const delta = Date.now() - started;                    // how long the load took (for XFetch)
    const softTtl = jitter(o.ttlMs);
    const entry: Entry<T> = { v, softExp: Date.now() + softTtl, delta };
    await redis.set(key, JSON.stringify(entry), 'PX', softTtl + o.staleMs).catch((err) =>
      log.error('cache set failed', err),
    );
    return v;
  }

  async function read<T>(key: string, loader: () => Promise<T>, o: Required<CacheOpts>): Promise<T> {
    let raw: string | null;
    try {
      raw = await redis.get(key);
    } catch (err) {
      log.error('redis get failed; loading directly', err);
      return loader();                                      // fail open (see "When Redis is down")
    }

    const entry: Entry<T> | null = raw ? JSON.parse(raw) : null;
    const now = Date.now();

    // 1. Fresh and not selected for early refresh: serve it
    if (entry && now < entry.softExp && now - entry.delta * Math.log(Math.random()) < entry.softExp) {
      return entry.v;
    }

    // 2. Stale or about to expire: serve the old value, one refresher updates it in the background
    if (entry) {
      const token = await tryLock(key, o.lockMs).catch(() => null);
      if (token) {
        load(key, loader, o)
          .catch((err) => log.error('background refresh failed; serving stale', err))
          .finally(() => unlock(key, token));
      }
      return entry.v;
    }

    // 3. True miss: one caller loads; the others wait briefly for the result
    const token = await tryLock(key, o.lockMs).catch(() => null);
    if (token) {
      try { return await load(key, loader, o); } finally { await unlock(key, token); }
    }

    const deadline = Date.now() + o.waitMs;
    while (Date.now() < deadline) {
      await sleep(50 + Math.random() * 50);
      const filled = await redis.get(key).catch(() => null);
      if (filled) return (JSON.parse(filled) as Entry<T>).v;
    }
    return load(key, loader, o);                            // waited long enough: load it ourselves
  }

  return function cached<T>(key: string, loader: () => Promise<T>, opts: CacheOpts): Promise<T> {
    const o: Required<CacheOpts> = {
      staleMs: opts.ttlMs * 5,
      lockMs: 15_000,
      waitMs: 3_000,
      ...opts,
    };

    // Single-flight inside this process
    const existing = inflight.get(key);
    if (existing) return existing as Promise<T>;
    const p = read<T>(key, loader, o).finally(() => inflight.delete(key));
    inflight.set(key, p);
    return p;
  };
}

Usage:

const cached = createCache(redis);

app.get('/leaderboard', async (_req, res) => {
  const data = await cached('leaderboard:v1', () => db.getLeaderboard(), {
    ttlMs: 60_000,        // fresh for ~1 minute
    staleMs: 5 * 60_000,  // may be served stale for up to 5 more minutes while refreshing
  });
  res.json(data);
});

A walkthrough of what the helper does:

Fresh hit: returns immediately (the common path, one Redis GET).
Stale or nearing expiry: returns the old value, and the one process that wins the lock refreshes in the background. XFetch randomly pulls some refreshes forward, so expiry rarely arrives "cold".
Cold miss: one caller across the fleet loads; the others poll briefly. If they time out, they load themselves rather than failing.
TTL jitter is applied to every write.
Single-flight collapses concurrent in-process callers before Redis is even consulted.
Lock released safely via the Lua compare-and-delete.

Because the value is wrapped in an entry object, caching null (negative caching) just works. Use a shorter ttlMs when the loader returns "not found."

Notes on the choices:

lockMs must exceed your worst-case loader time, or a slow load will let a second loader in. It's still only a duplicate load, not corruption.
Version your keys (leaderboard:v1) so you can invalidate by bumping the version when the shape changes. Old entries age out on their own.
Don't cache errors. The helper only writes on success, so a failing loader leaves the previous (stale) value in place.

When Redis itself is down

The try/catch around redis.get makes the helper fail open: if Redis is unreachable, it calls the loader directly. That keeps the app working, but it's also a recipe for a stampede, because now every request goes to the database. Single-flight limits it to one load per key per process, but with many distinct keys the database still takes the full force.

Add backstops at the database edge:

A concurrency limit on loaders (a semaphore or p-limit) so the database never sees more than N concurrent cache-fill queries
A circuit breaker around the loader: when it's failing or slow, fail fast or serve stale rather than piling on
Aggressive timeouts on Redis calls (commandTimeout, connectTimeout in ioredis) so a hung Redis doesn't hang every request
Load shedding (503 with Retry-After) on non-critical endpoints during the incident

Treat Redis as an optimization that may vanish at any moment, and make sure the database survives that moment.

Warming and deploys

Cold caches cause stampedes too, right after a Redis restart, failover, flush, or a deploy that bumps a key version.

Warm critical keys before taking traffic (a startup script that loads the top N keys), or populate them from a background job
Roll out gradually. Deploying all instances at once with a new key prefix means all of them miss simultaneously
Refresh-ahead for known-hot keys: a scheduled job that recomputes the leaderboard every 30 seconds means requests never trigger the load at all

For a few well-known hot keys, refresh-ahead via a scheduler is often simpler and more predictable than any request-driven scheme.

Test it: prove the stampede is gone

Don't trust the cache until you've watched it under concurrent load. Count how many times the loader actually runs:

import { it, expect } from 'vitest';

it('runs the loader once for 200 concurrent cold misses across 2 "processes"', async () => {
  const cachedA = createCache(redis);   // separate in-process maps simulate two instances
  const cachedB = createCache(redis);
  await redis.flushdb();

  let loads = 0;
  const loader = async () => { loads++; await sleep(300); return { top: 'alice' }; };

  const calls = Array.from({ length: 200 }, (_, i) =>
    (i % 2 ? cachedA : cachedB)('leaderboard:test', loader, { ttlMs: 60_000 }),
  );
  const results = await Promise.all(calls);

  expect(results.every((r) => r.top === 'alice')).toBe(true);
  expect(loads).toBe(1);                                  // not 200
});

it('serves stale and refreshes once after the soft TTL', async () => {
  // set ttlMs small, wait past it, fire N requests, assert they return instantly
  // with the old value and that the loader ran exactly once in the background
});

Also run a load test (autocannon or k6) against a staging environment with a short TTL (say 5 seconds) so that you hit expiries constantly, and watch the database's query rate and connection count. A healthy setup shows a flat line with small, steady refresh queries rather than periodic spikes.

What to monitor

Hit rate, split into fresh hits, stale hits, and misses
Loader concurrency per key. Spikes mean a stampede is forming
Loader duration (it's also your delta, and shows when queries are slowing)
Lock contention: how often callers fail to get the lock, and how often waiters time out and load anyway
Database queries per second and its correlation with cache expiry times
Redis latency and errors, since the fail-open path is your riskiest moment

Choosing defenses

Technique	Prevents	Cost	Notes
Single-flight (in-process)	Stampede within one process	Tiny	Always do it, but it's per-process
Redis lock (`SET NX PX`)	Stampede across the fleet	A Redis round trip; waiters block	Duplicate work is tolerable, so a simple lock suffices
Stale-while-revalidate	User-visible latency on refresh	Serves slightly old data; more memory	Best user experience; adds stale-if-error
XFetch (early refresh)	Synchronized expiry on hot keys	Slightly more refreshes	No locking; combine with single-flight
TTL jitter	Avalanche (mass expiry)	None	Use on every key
Negative caching	Penetration	Small memory	Short TTL for "not found"
Refresh-ahead job	Everything for known hot keys	A scheduler	Simple and predictable
Loader concurrency limit + breaker	Database overload if everything else fails	Small	The last line of defense

Checklist

✅ Collapse concurrent misses with single-flight
✅ Use a lock (SET NX PX + Lua release) so one process per key rebuilds
✅ Serve stale while revalidating, so users never wait on a refresh
✅ Add TTL jitter to every key
✅ Cache "not found" with a short TTL
✅ Make the lock TTL longer than the worst-case load time
✅ Bound database concurrency, and use timeouts and a circuit breaker for when Redis fails
✅ Warm caches before cutover, and roll deploys gradually
✅ Test that N concurrent misses cause exactly 1 load
✅ Monitor loader concurrency and the stale/fresh/miss split

Key takeaways

A cache stampede is a feedback loop. When a hot key expires, every concurrent request misses and runs the expensive query, which slows the database and widens the miss window.
Single-flight is the cheapest fix, but it only works within one process. Use a Redis lock for fleet-wide coordination.
Stale-while-revalidate is the best user experience. Serve the old value and refresh in the background, and you also get stale-if-error for free.
Probabilistic early refresh (XFetch) avoids expiry cliffs without coordination.
Add TTL jitter everywhere and cache misses for nonexistent keys to stop avalanches and penetration.
Plan for Redis being down. Fail open only if the database has its own concurrency limits and breakers.
Prove it with a test that fires hundreds of concurrent cold requests and asserts the loader ran once.

Have you been hit by a stampede, or found a pattern I didn't cover? Share it in the comments.

Further reading: the paper "Optimal Probabilistic Cache Stampede Prevention" (Vattani, Chierichetti, Lowenstein), the Redis docs on SET and distributed locks, and the ioredis README.

The Dual-Write Problem in Distributed Systems: Implementing the Transactional Outbox Pattern in Node.js & PostgreSQL

subashthiruppathy — Fri, 23 Oct 2026 17:17:45 +0000

You've written this code. Everyone has:

async function placeOrder(input: OrderInput) {
  const order = await db.orders.insert(input);          // 1. write to the database
  await broker.publish('order.placed', { id: order.id }); // 2. tell the rest of the system
  return order;
}

It looks correct, and it works for months. Then a deploy lands at the wrong moment, the broker has a hiccup, or the process runs out of memory, and the system quietly ends up in a state that makes no sense:

The order exists, but no email was sent, no inventory reserved, no invoice created
Or the event was published for an order that was rolled back and doesn't exist

There is no exception in your logs, just two systems that disagree. This is the dual-write problem, and this post explains why you can't fix it with better error handling, then implements the standard solution, the transactional outbox, in Node.js and PostgreSQL.

Why two writes can't be made atomic

A database transaction gives you atomicity within one database. Your broker (Kafka, RabbitMQ, SQS, a webhook) is a different system with its own failure modes, and there's no transaction that spans both. Whichever order you choose, there's a window where one write succeeded and the other didn't.

Order A: write the database first, then publish

BEGIN; INSERT order; COMMIT;     ✅ committed
publish event                    💥 process crashes / broker down / network timeout

Result: the order exists, but the event is lost forever. Downstream services never find out.

Order B: publish first, then write the database

publish event                    ✅ delivered; consumers start working
BEGIN; INSERT order; COMMIT;     💥 fails (constraint violation, crash, deadlock)

Result: a phantom event for an order that doesn't exist.

Order C: publish inside the transaction, before the commit

BEGIN
  INSERT order
  publish event                  ✅ delivered
COMMIT                           💥 fails, or the process dies first

Same phantom-event problem as B, plus you've held a transaction (and a pooled connection) open across a network call.

"Just retry" doesn't solve it

Retrying the publish helps only if the process is still alive. If it crashed after the commit, there's nothing left to retry, because the intent to publish lived only in memory.
A retry after an ambiguous failure (a timeout where the broker did receive the message) produces duplicates.
Wrapping it in try/catch and "compensating" with a delete creates another failing write.

The fundamental issue is that the intent to publish isn't stored durably alongside the data change. The outbox pattern fixes exactly that.

The outbox idea

Instead of publishing directly, write the event into a table in the same database, in the same transaction as the business change. Then a separate process (the relay) reads that table and publishes to the broker.

┌────────────── one local transaction ──────────────┐
│  INSERT INTO orders ...                           │
│  INSERT INTO outbox (type, payload) ...           │   ← event is durable and atomic with the order
└───────────────────────────────────────────────────┘
                         │
                         ▼
        Relay: read unpublished rows ──▶ broker ──▶ consumers
                         │
                         ▼
               mark rows as published

Now the two writes that must succeed or fail together are in one database transaction, which databases are excellent at:

Transaction commits → the order and its event both exist. The relay will eventually publish it, even across crashes and restarts.
Transaction rolls back → neither exists. No phantom events.

What you give up: delivery becomes at-least-once. The relay can crash after publishing but before marking the row as published, so the same event may be published twice. That's the price, and it's manageable: consumers must be idempotent (covered below).

Step 1: The schema

CREATE TABLE outbox (
  id            BIGSERIAL    PRIMARY KEY,
  event_id      UUID         NOT NULL DEFAULT gen_random_uuid(),  -- stable id for consumer-side dedupe
  aggregate_type TEXT        NOT NULL,       -- e.g. 'order'
  aggregate_id  TEXT         NOT NULL,       -- e.g. the order id; used as the partition/ordering key
  type          TEXT         NOT NULL,       -- e.g. 'order.placed'
  payload       JSONB        NOT NULL,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ,                 -- NULL = not yet published
  attempts      INTEGER      NOT NULL DEFAULT 0,
  locked_until  TIMESTAMPTZ                  -- lease so multiple relays don't double-claim
);

-- The relay only ever looks at unpublished rows, so index just those
CREATE INDEX outbox_unpublished_idx ON outbox (id) WHERE published_at IS NULL;

Notes:

gen_random_uuid() is built in from PostgreSQL 13 (earlier versions need the pgcrypto extension).
event_id is separate from id. id is for the relay's ordering; event_id travels with the message so consumers can recognize duplicates.
The partial index keeps the relay's lookups fast no matter how many published rows have piled up.

Step 2: Write the event in the same transaction

async function placeOrder(input: OrderInput) {
  return withTransaction(async (tx) => {
    const { rows: [order] } = await tx.query(
      `INSERT INTO orders (customer_id, total_cents, status)
       VALUES ($1, $2, 'placed') RETURNING id, customer_id, total_cents`,
      [input.customerId, input.totalCents],
    );

    await tx.query(
      `INSERT INTO outbox (aggregate_type, aggregate_id, type, payload)
       VALUES ('order', $1, 'order.placed', $2)`,
      [String(order.id), JSON.stringify({
        orderId: order.id,
        customerId: order.customer_id,
        totalCents: order.total_cents,
      })],
    );

    return order;
  });
}

Notice what's not here: no broker call, no network I/O inside the transaction. Writing a row is fast, local, and atomic with the order. (withTransaction is the helper from the earlier posts in this series.)

A few payload design tips:

Include what consumers need (a snapshot) so they don't have to call back for it, but keep payloads small. Don't put whole entities with sensitive fields in an event log.
Version your event types (order.placed.v1) or include a schema version, since consumers will outlive your current code.

Step 3: The relay

The relay is a small worker that repeatedly: claims a batch of unpublished events, publishes them, and marks them done.

Don't hold a transaction open while publishing

The tempting implementation is BEGIN; SELECT ... FOR UPDATE SKIP LOCKED; publish; UPDATE; COMMIT. It's correct, but it holds a transaction and a pooled connection open across broker calls, the pattern we warned against in the pool starvation post. A slow broker would pin connections and row locks.

A better design is a lease: claim rows with a short, quick UPDATE, publish outside any transaction, then mark them published. If the relay dies mid-way, the lease expires and another pass picks the rows up.

// relay.ts
const BATCH = 100;
const LEASE_SECONDS = 30;
const MAX_ATTEMPTS = 10;

const CLAIM_SQL = `
  UPDATE outbox
     SET locked_until = now() + make_interval(secs => $2),
         attempts     = attempts + 1
   WHERE id IN (
           SELECT id FROM outbox
            WHERE published_at IS NULL
              AND attempts < $3
              AND (locked_until IS NULL OR locked_until < now())
            ORDER BY id
            LIMIT $1
            FOR UPDATE SKIP LOCKED
         )
  RETURNING id, event_id, aggregate_type, aggregate_id, type, payload, created_at`;

export async function relayOnce(publisher: Publisher): Promise<number> {
  const { rows } = await pool.query(CLAIM_SQL, [BATCH, LEASE_SECONDS, MAX_ATTEMPTS]);
  if (rows.length === 0) return 0;

  rows.sort((a, b) => Number(BigInt(a.id) - BigInt(b.id)));   // RETURNING order isn't guaranteed

  const published: string[] = [];
  let failed: { id: string; err: unknown } | undefined;

  for (const ev of rows) {
    try {
      await publisher.publish(ev);
      published.push(ev.id);
    } catch (err) {
      failed = { id: ev.id, err };
      break;                       // stop at first failure to preserve order within the batch
    }
  }

  if (published.length) {
    await pool.query(
      'UPDATE outbox SET published_at = now() WHERE id = ANY($1::bigint[])',
      [published],
    );
  }

  if (failed) {
    log.warn({ err: failed.err, outboxId: failed.id }, 'outbox publish failed');
    // Exponential backoff via the lease: retry in 2^attempts seconds, capped at 5 minutes
    await pool.query(
      `UPDATE outbox SET locked_until = now() + make_interval(secs => least(300, 2 ^ attempts))
        WHERE id = $1`,
      [failed.id],
    );
    // Release the rest of the batch so they can be retried immediately
    const rest = rows.map((r) => r.id).filter((id) => !published.includes(id) && id !== failed!.id);
    if (rest.length) {
      await pool.query('UPDATE outbox SET locked_until = NULL WHERE id = ANY($1::bigint[])', [rest]);
    }
  }

  return published.length;
}

The FOR UPDATE SKIP LOCKED inside the claim subquery lets several relay instances run concurrently without blocking each other or claiming the same rows. And because the claim is a single quick statement, no transaction is held during publishing.

The publisher is a thin adapter over your broker. Here's one for Kafka using kafkajs:

interface Publisher { publish(ev: OutboxRow): Promise<void> }

export class KafkaPublisher implements Publisher {
  constructor(private producer: Producer) {}

  async publish(ev: OutboxRow) {
    await this.producer.send({
      topic: `${ev.aggregate_type}-events`,
      messages: [{
        key: ev.aggregate_id,                       // same key => same partition => per-aggregate ordering
        value: JSON.stringify(ev.payload),
        headers: { 'event-id': ev.event_id, 'event-type': ev.type },
      }],
    });
  }
}

And the loop that drives it:

let running = true;

export async function runRelay(publisher: Publisher) {
  while (running) {
    try {
      const n = await relayOnce(publisher);
      if (n === 0) await sleep(500);              // idle: poll gently
    } catch (err) {
      log.error({ err }, 'relay iteration failed');
      await sleep(2_000);                         // database trouble: back off
    }
  }
}

// graceful shutdown: stop claiming; the current batch finishes before the loop exits
export const stopRelay = () => { running = false; };

The trap: ids are not commit order

Here's a bug that sinks many first implementations. It's tempting to poll like a log reader: "give me rows with id > last_seen_id."

-- ❌ Can permanently skip events
SELECT * FROM outbox WHERE id > $1 ORDER BY id LIMIT 100;

The problem: sequence values are assigned when the row is inserted, not when the transaction commits. Two concurrent transactions:

Tx A: INSERT outbox → id 101 ... (slow work) ... COMMIT   ← commits LATE
Tx B: INSERT outbox → id 102 ... COMMIT                   ← commits first

If the relay polls between those commits, it sees 102 but not 101 (uncommitted, invisible). It advances last_seen_id to 102. When 101 commits, it's below the cursor, and it's never published.

The design above avoids this by tracking state per row (published_at IS NULL) rather than with a high-water mark. A late-committing row simply shows up as unpublished the next time the relay looks. If you ever build something that reads by position, such as a cursor on id, you need a different technique (for example, only advancing past ids older than your longest transaction, or reading the WAL directly, which is what CDC does).

Ordering: what you do and don't get

Within a batch from one relay, events publish in id order.
Across multiple relays, two events for the same aggregate could be claimed by different relays and published out of order.
Also, id order isn't strictly commit order (see above), so "order by id" is a close approximation, not a guarantee.

How much this matters depends on your consumers. Options:

Make consumers order-tolerant. Include a per-aggregate version number or timestamp in the payload and have consumers ignore anything older than what they've applied. This is the most robust approach.
Shard the relay. Run one relay per shard, with events routed by hash(aggregate_id) % N, so a given aggregate is always handled by one relay.
Run a single relay instance if throughput allows. It's the simplest and often sufficient.

Using the aggregate id as the Kafka message key gives you per-aggregate ordering inside the broker once messages arrive, but it can't fix order lost before publishing.

Step 4: Idempotent consumers (the other half of the pattern)

Because delivery is at-least-once, the same event can arrive twice: the relay crashed between publishing and marking, or the broker redelivered. If your consumer sends an email or charges a card per event, duplicates are real bugs.

The standard fix is an inbox / processed-events table with a unique key, checked in the same transaction as the consumer's side effects:

CREATE TABLE processed_events (
  event_id     UUID PRIMARY KEY,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

async function handleOrderPlaced(eventId: string, payload: OrderPlaced) {
  await withTransaction(async (tx) => {
    const res = await tx.query(
      'INSERT INTO processed_events (event_id) VALUES ($1) ON CONFLICT DO NOTHING',
      [eventId],
    );
    if (res.rowCount === 0) return;               // already handled: safe no-op

    await tx.query(
      'INSERT INTO invoices (order_id, amount_cents) VALUES ($1, $2)',
      [payload.orderId, payload.totalCents],
    );
  });
}

The dedupe insert and the business write commit together, so either both happen or neither does. If the handler crashes after the commit but before acking the message, the redelivery hits the unique key and does nothing.

When the consumer's side effect is external (an email, a payment API), a local table can't make it atomic, so pass the event_id as an idempotency key to the external service where it supports one, or accept and design for the rare duplicate.

Alternatives to polling

Polling every few hundred milliseconds is simple and works well for most systems. Two ways to improve on it:

LISTEN/NOTIFY as a wake-up signal. Have the writer run NOTIFY outbox_new (delivered only when the transaction commits) and have the relay LISTEN, so it can poll immediately instead of waiting. Treat it purely as a hint: notifications are lost if no listener is connected, so keep the periodic poll as a fallback. Don't put the event itself in the notification.
Change data capture (CDC). Tools like Debezium read PostgreSQL's write-ahead log via logical decoding and publish outbox rows as they commit. It removes polling load and gives you true commit ordering, at the cost of more infrastructure (a replication slot to monitor, connectors to operate). It's a good fit when volume or latency requirements outgrow a poller.

Both patterns still write the outbox row in the same transaction. Only the relay changes.

Operating an outbox

Clean up, or it grows forever

Published rows are dead weight. Delete them on a schedule:

DELETE FROM outbox
 WHERE published_at IS NOT NULL
   AND published_at < now() - interval '7 days';

Delete in batches (a LIMIT via a subquery on id) so you don't create one giant transaction. A table that is constantly inserted, updated, and deleted generates bloat, so make sure autovacuum keeps up. For very high volume, consider time-partitioning the table and dropping old partitions, which is far cheaper than deleting rows.

Handle poison messages

An event that can never be published (a payload the broker rejects, say) would be retried forever. The attempts < MAX_ATTEMPTS guard in the claim query parks it instead of looping. Alert on rows that hit the limit, and fix or replay them deliberately rather than letting them silently stall.

Monitor lag and backlog

The metric that matters is how stale the oldest unpublished event is:

SELECT count(*)                          AS unpublished,
       max(now() - created_at)           AS oldest_age,
       count(*) FILTER (WHERE attempts >= 10) AS stuck
  FROM outbox
 WHERE published_at IS NULL;

Export these and alert when oldest_age exceeds your tolerance (for example, 30 seconds), or when stuck > 0. A growing backlog means the relay is down, slow, or blocked on a bad broker.

Test the failure windows

The whole point of the pattern is surviving specific failures, so test those exact failures:

it('creates no event when the business transaction rolls back', async () => {
  await expect(placeOrder({ ...validInput, totalCents: -1 })).rejects.toThrow(); // violates a CHECK
  const { rows } = await pool.query('SELECT count(*)::int AS n FROM outbox');
  expect(rows[0].n).toBe(0);
});

it('redelivers after a crash between publish and mark, and the consumer dedupes', async () => {
  await placeOrder(validInput);

  // Publisher succeeds, but "crash" before the row is marked published
  const published: OutboxRow[] = [];
  await relayOnceWithCrashAfterPublish((ev) => published.push(ev));

  // After the lease expires, the relay runs again and republishes
  await expireLeases();
  await relayOnce({ publish: async (ev) => { published.push(ev); } });
  expect(published).toHaveLength(2);                       // at-least-once: duplicate delivered

  // The consumer applies it exactly once
  for (const ev of published) await handleOrderPlaced(ev.event_id, ev.payload);
  const { rows } = await pool.query('SELECT count(*)::int AS n FROM invoices');
  expect(rows[0].n).toBe(1);
});

When not to use it

The outbox adds a table, a worker, and consumer-side dedupe. It's worth it when losing or inventing an event causes real harm (billing, inventory, notifications that matter). It's overkill when:

There's a single system and no downstream consumers
Losing an occasional event is genuinely acceptable (some analytics or cache invalidation)
You can derive state from the database itself (consumers read the data directly)

Pattern summary

Piece	Responsibility
Business transaction	Writes the data and the outbox row atomically
Outbox table	Durable record of "events that must be published"
Relay	Claims, publishes, marks; retries with backoff; safe to run multiple instances
Broker	Carries events, with a key for per-aggregate ordering
Consumer	Idempotent, deduping on `event_id` in the same transaction as its effects
Monitoring	Oldest-unpublished age, stuck rows, cleanup

Checklist

✅ Never call the broker inside (or beside) the business transaction. Insert an outbox row instead
✅ Write the outbox row in the same transaction as the data change
✅ Track publish state per row, not with an id high-water mark
✅ Claim with a lease (FOR UPDATE SKIP LOCKED) and publish outside any transaction
✅ Retry with exponential backoff; cap attempts; alert on stuck rows
✅ Give every event a stable event_id, and make consumers idempotent
✅ Use the aggregate id as the ordering key, and decide how you handle ordering across relays
✅ Clean up published rows and watch table bloat
✅ Test the crash windows explicitly

Key takeaways

Dual writes can't be made atomic. Any order of "write database, publish event" has a failure window that loses events or invents them.
The outbox turns two writes into one. The event is saved in the same database transaction as the data, and a relay publishes it later.
Delivery is at-least-once, so consumers must be idempotent. Dedupe on a stable event_id in the same transaction as the side effects.
Don't poll by id high-water mark. Sequence order isn't commit order, and late-committing rows get skipped. Track published state per row.
Claim with leases, not long transactions, so a slow broker can't pin your connection pool.
Decide your ordering story (version numbers, sharded relays, or a single relay) rather than assuming it.
Operate it: monitor the age of the oldest unpublished event, park poison messages, and clean up old rows.

Have you run an outbox in production, with polling or CDC? I'd like to hear what bit you, so share it in the comments.

Further reading: Chris Richardson's write-up of the transactional outbox pattern, the Debezium outbox event router docs, and the PostgreSQL docs on SELECT ... FOR UPDATE SKIP LOCKED and NOTIFY.

Why OFFSET Pagination Is Killing Your PostgreSQL Queries (And How to Implement Keyset Pagination in Node.js)

subashthiruppathy — Thu, 22 Oct 2026 15:18:20 +0000

Almost every API starts with the same pagination code:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

It's simple, it works in development, and it ships. Months later, the table has millions of rows and you're seeing:

Page 1 loads in 5 ms. Page 5,000 takes 3 seconds.
A crawler or an export job walking through pages brings the database to its knees.
Users report seeing the same item twice, or missing items entirely, as they page through a live feed.

All three have the same root cause: OFFSET pagination doesn't scale and isn't stable. This post explains why, then builds the alternative, keyset pagination (also called cursor or seek pagination), step by step in Node.js and PostgreSQL, including a subtle timestamp bug that trips up most first implementations.

Why OFFSET gets slower with every page

OFFSET 100000 doesn't mean "jump to row 100,000." PostgreSQL has no way to teleport to the Nth row of a sorted result. It has to produce the first 100,020 rows in order and throw away the first 100,000.

So the cost of a page is proportional to offset + limit, not to limit. The shape of the plan tells the story:

EXPLAIN (ANALYZE) SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 2000000;

Limit  (actual time=640.2..640.3 rows=20)
  ->  Index Scan Backward using orders_created_idx on orders  (actual time=0.03..598.4 rows=2000020)

(Illustrative numbers. Yours will differ, but the pattern is the same.) The Limit node returns 20 rows, but the scan underneath it produced 2,000,020 and discarded 2,000,000. Even with a perfect index, the database walks past every skipped row, and it also has to visit the table for each of them to fetch the columns you selected.

The result is a cost curve that climbs linearly with page depth:

Page	`OFFSET`	Rows the database walks
1	0	20
100	1,980	~2,000
10,000	199,980	~200,000
100,000	1,999,980	~2,000,000

Real users rarely go to page 100,000, but bots, exports, and "load everything" jobs do, and they're exactly the traffic that hurts you. An attacker can also request ?page=999999 on purpose.

Problem 2: unstable results

OFFSET pagination is positional ("skip N rows"), so if the data changes between requests, positions shift:

Page 1 (OFFSET 0):  rows A B C D E   ← user reads this
                    ── a new row Z is inserted at the top ──
Page 2 (OFFSET 5):  rows E F G H I   ← E appears AGAIN (duplicate)

Delete a row from an earlier page instead, and everything shifts the other way: a row is skipped and the user never sees it. On a busy feed, this happens constantly.

Problem 3: the "total pages" count

Classic pagination UIs need SELECT count(*) to render "Page 3 of 4,812". In PostgreSQL, counting a large table is slow, because MVCC means the database has to check row visibility rather than reading a stored counter. Running that on every request is an expensive habit. (More on alternatives later.)

The idea behind keyset pagination

Instead of saying "skip 40 rows", say "give me the rows that come after the last one I saw."

If the previous page ended with the row (created_at = 2026-10-22 10:00:00, id = 8841), the next page is:

SELECT * FROM orders
WHERE (created_at, id) < ('2026-10-22 10:00:00', 8841)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With a matching index, PostgreSQL seeks directly to that position in the B-tree and reads 20 rows from there. The cost is the same on page 1 and page 100,000, because nothing is skipped. It also fixes stability: your position is anchored to a value, not a row count, so inserts and deletes elsewhere don't shift it.

Step 1: The index

The index must match your ORDER BY columns, in the same order:

CREATE INDEX orders_tenant_created_id_idx
  ON orders (tenant_id, created_at DESC, id DESC);

Equality filters first (tenant_id), then the sort columns. This lets Postgres jump straight to one tenant's slice and read it in order.
id is the tiebreaker. Many rows can share the same created_at (bulk imports, batch jobs). Without a unique tiebreaker, rows with identical timestamps can be skipped or repeated across pages. The sort key must be unique as a whole.
The DESC markers aren't strictly required when every column sorts in the same direction (Postgres can scan a B-tree backwards), but they make the intent clear and are required for mixed directions.

Step 2: The query

The tuple comparison (created_at, id) < ($1, $2) is a row-value comparison, and it's the cleanest way to express "after this position" for a multi-column sort. It compares created_at first, and id only when the timestamps are equal. PostgreSQL can use a matching B-tree index to evaluate it efficiently.

const { rows } = await pool.query(
  `SELECT id, status, total_cents, created_at
     FROM orders
    WHERE tenant_id = $1
      AND (created_at, id) < ($2::timestamptz, $3::bigint)
    ORDER BY created_at DESC, id DESC
    LIMIT $4`,
  [tenantId, cursor.t, cursor.id, limit + 1],
);

Notice limit + 1. That's the trick for knowing whether there's a next page without a count query: fetch one extra row. If you got limit + 1 rows, there's more; return only limit of them.

The trap: JavaScript dates lose precision

Here's the bug in most first implementations. PostgreSQL timestamptz has microsecond precision. A JavaScript Date has millisecond precision. When node-postgres parses a timestamp into a Date, the last three digits are silently dropped:

Postgres value:   2026-10-22 10:00:00.123456
After JS Date:    2026-10-22 10:00:00.123       ← .000456 lost

If you build your cursor from that Date, the cursor points to a position slightly earlier than the real last row, and the effect depends on the sort direction:

-- Newest first (DESC). The real last row was ...123456, but the cursor says ...123000
WHERE (created_at, id) < ('2026-10-22 10:00:00.123', 8841)

Any rows with timestamps between ...123000 and ...123456 should come next, but they're greater than the truncated cursor, so < excludes them. Those rows are silently skipped. In ascending order the mirror image happens: with > and a truncated cursor, the real last row itself is greater than the cursor, so it's returned again as a duplicate.

Either way, it only happens when timestamps have sub-millisecond digits and several rows land close together, which is why it passes casual testing.

Fix: build the cursor from Postgres's own text

Have Postgres give you the timestamp as text, and pass that exact string back:

SELECT id, status, total_cents, created_at,
       created_at::text AS created_at_raw     -- full microsecond precision, as text
  FROM orders ...

created_at_raw = '2026-10-22 10:00:00.123456+00'

Put that string in the cursor and cast it back with $2::timestamptz. No precision is lost, because the value never passes through a JS Date. (An alternative that avoids the issue entirely is sorting by a monotonically increasing bigint id, but time-based feeds usually need timestamps.)

Step 3: The cursor

Make the cursor opaque: encode it so clients treat it as a token rather than building their own. That lets you change the internals later without breaking clients.

import { z } from 'zod';

const TS_RE = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}(\.\d{1,6})?[+-]\d{2}(:\d{2})?$/;

const cursorSchema = z.object({
  t: z.string().regex(TS_RE),           // created_at as Postgres text
  id: z.string().regex(/^\d{1,19}$/),   // bigint as string (pg returns bigint as string)
});
export type Cursor = z.infer<typeof cursorSchema>;

export const encodeCursor = (c: Cursor) =>
  Buffer.from(JSON.stringify(c)).toString('base64url');

export function decodeCursor(raw: string): Cursor {
  try {
    const json = JSON.parse(Buffer.from(raw, 'base64url').toString('utf8'));
    return cursorSchema.parse(json);
  } catch {
    throw new BadRequestError('Invalid cursor');
  }
}

Always validate the decoded cursor. It comes from the client, so treat it as untrusted input. Parameterized queries already protect against SQL injection, but validation turns a malformed token into a clean 400 instead of a database error (an invalid timestamp cast would otherwise fail with a 22007 error).

Cursor tampering is also not a security boundary. A user can forge any cursor, which only changes where in their own results they start. Authorization must come from the query's WHERE clause (tenant_id = $1 above), never from anything inside the cursor.

Step 4: The complete function

export async function listOrders(opts: { tenantId: string; limit: number; cursor?: string }) {
  const limit = Math.min(Math.max(opts.limit, 1), 100);   // always clamp client-supplied limits
  const cursor = opts.cursor ? decodeCursor(opts.cursor) : undefined;

  const params: unknown[] = [opts.tenantId];
  let where = 'tenant_id = $1';
  if (cursor) {
    params.push(cursor.t, cursor.id);
    where += ' AND (created_at, id) < ($2::timestamptz, $3::bigint)';
  }
  params.push(limit + 1);

  const { rows } = await pool.query(
    `SELECT id, status, total_cents, created_at,
            created_at::text AS created_at_raw
       FROM orders
      WHERE ${where}
      ORDER BY created_at DESC, id DESC
      LIMIT $${params.length}`,
    params,
  );

  const hasMore = rows.length > limit;
  const page = hasMore ? rows.slice(0, limit) : rows;
  const last = page[page.length - 1];

  return {
    data: page.map(({ created_at_raw, ...row }) => row),
    nextCursor: hasMore && last
      ? encodeCursor({ t: last.created_at_raw, id: String(last.id) })
      : null,
  };
}

The only string interpolated into the SQL is the where fragment, which is built from fixed literals. All values still go through parameters.

And the route:

app.get('/orders', async (req, res) => {
  const limit = Number(req.query.limit ?? 20);
  const result = await listOrders({
    tenantId: req.user.tenantId,
    limit: Number.isFinite(limit) ? limit : 20,
    cursor: typeof req.query.cursor === 'string' ? req.query.cursor : undefined,
  });
  res.json(result);   // { data: [...], nextCursor: "eyJ0Ijoi..." | null }
});

A client just follows nextCursor until it's null:

let cursor: string | null | undefined;
do {
  const page = await api.get('/orders', { params: { limit: 100, cursor } });
  handle(page.data);
  cursor = page.nextCursor;
} while (cursor);

Going backwards (previous page)

To page backwards from a cursor, flip the comparison and the sort, then reverse the rows in memory:

SELECT ... FROM orders
WHERE tenant_id = $1 AND (created_at, id) > ($2::timestamptz, $3::bigint)
ORDER BY created_at ASC, id ASC       -- flipped
LIMIT $4;

const pageRows = rows.slice(0, limit).reverse();   // restore display order

Return a prevCursor built from the first row of the page, and use the same limit + 1 trick to know whether an earlier page exists. This is also more work than a "load more" button, so many apps just support forward paging.

Gotchas

Mixed sort directions

Row-value comparison needs every column to sort the same way. For something like "highest score first, then oldest id first" (score DESC, id ASC), spell the condition out:

WHERE score < $1
   OR (score = $1 AND id > $2)
ORDER BY score DESC, id ASC

Create the index with the same directions ((score DESC, id ASC)) and check the plan with EXPLAIN. The OR form can be harder for the planner than a plain row comparison, and on large tables some people split it into two range queries joined with UNION ALL. Test on your own data.

NULLs in the sort column

Comparisons involving NULL evaluate to NULL, not true, so rows with a null sort value can silently drop out of keyset queries. Prefer NOT NULL sort columns. If you can't, normalize (for example, order by COALESCE(col, 'infinity')) and index that exact expression.

Sort columns must be stable

If a sort value changes between requests (say, sorting by updated_at, and a row is updated mid-pagination), it can jump positions and show up twice or be missed. For "never miss anything" exports, sort by something immutable like created_at or an increasing id.

Filters change the cursor's meaning

A cursor is only valid for the same filters and sort order that produced it. If you let users filter or re-sort, either bind that into the cursor (and reject mismatches) or require a fresh query without a cursor whenever the filters change.

Jumping to "page 47"

This is the real trade-off. Keyset pagination can't jump to an arbitrary page, because positions are relative to the previous page. It fits infinite scroll, "load more", APIs, and exports very well. It fits "go to page N" UIs poorly. Options when you need both:

Use OFFSET for shallow pages only (cap it, say at a few hundred rows deep) and keyset beyond that
Offer jump-to-date navigation: seed a keyset query from created_at < $date
Keep OFFSET for small, rarely-changing tables where it's genuinely fine. It still is.

What about the total count?

If you need a "total" and can't drop it:

Don't count on every request. Cache it, or compute it asynchronously.
Show an estimate: the planner statistics give an approximate row count cheaply (reltuples in pg_class, or the row estimate from EXPLAIN), which is fine for "about 4.8M results".
Cap the count: SELECT count(*) FROM (SELECT 1 FROM orders WHERE ... LIMIT 1001) t lets you show "1,000+" while bounding the work.
Skip it, and use hasMore from the limit + 1 trick, which is all infinite scroll needs.

Verify it yourself

Don't take the performance claims on faith. Build a big table and compare:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  status TEXT NOT NULL,
  total_cents INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

INSERT INTO orders (tenant_id, status, total_cents, created_at)
SELECT 't1', 'paid', (random() * 10000)::int,
       now() - (g * interval '1 second') * random()
FROM generate_series(1, 5000000) g;

CREATE INDEX orders_tenant_created_id_idx ON orders (tenant_id, created_at DESC, id DESC);
ANALYZE orders;

Then compare deep pages:

-- OFFSET: cost grows with depth
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 't1'
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 2000000;

-- Keyset: constant, using a (created_at, id) taken from a row near that position
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 't1'
  AND (created_at, id) < ('2026-09-01 00:00:00+00', 1000000)
ORDER BY created_at DESC, id DESC LIMIT 20;

Compare the rows scanned and the execution time. You should see the OFFSET query do work proportional to the offset while the keyset query touches roughly 20 index entries.

Test the correctness, not just the speed

The stability and tiebreaker claims deserve a test, especially with the bad case baked in, a pile of rows sharing the same timestamp:

it('walks every row exactly once, even with identical timestamps', async () => {
  await seedOrders({ count: 500, sameTimestampGroups: 50 });   // many rows share created_at

  const seen: string[] = [];
  let cursor: string | undefined;
  do {
    const page = await listOrders({ tenantId: 't1', limit: 37, cursor });  // odd page size on purpose
    seen.push(...page.data.map((r) => String(r.id)));
    cursor = page.nextCursor ?? undefined;
  } while (cursor);

  expect(seen).toHaveLength(500);
  expect(new Set(seen).size).toBe(500);            // no duplicates, no skips
});

Also seed rows with microsecond-precision timestamps in this test. That's what catches the Date truncation bug.

OFFSET vs keyset at a glance

	`OFFSET` / `LIMIT`	Keyset (cursor)
Cost of page N	Grows with N	Constant
Stable under inserts/deletes	No (duplicates and skips)	Yes
Jump to arbitrary page	Yes	No
Needs a unique, indexed sort key	No	Yes
Handles total counts	Needs a separate `count(*)`	Needs a separate approach
Complexity	Lowest	Moderate
Good for	Small tables, admin tables with page numbers	Feeds, APIs, exports, infinite scroll

Checklist

✅ Index matches WHERE equality columns, then the sort columns
✅ Sort key is unique (add id as a tiebreaker)
✅ Row-value comparison (a, b) < ($1, $2) for same-direction sorts
✅ Fetch limit + 1 to learn hasMore without a count
✅ Build timestamp cursors from created_at::text, never a JS Date
✅ Cursor is opaque, validated on decode, and carries no authorization
✅ Client-supplied limit is clamped
✅ Test with duplicate and microsecond-precision timestamps

Key takeaways

OFFSET makes the database read and discard every skipped row, so deep pages get progressively slower.
It's also unstable: inserts and deletes between requests cause duplicates and skipped rows.
Keyset pagination anchors to a value instead of a position: "rows after this one." With the right index, every page costs about the same.
You need a unique sort key, usually the timestamp plus id, and an index that matches it.
Beware timestamp precision. JS Date truncates Postgres microseconds. Round-trip the value as text.
Keyset can't jump to page N. Choose it for feeds and APIs, keep OFFSET for small tables, or combine them.
Test with ties and sub-millisecond timestamps, since those are what expose subtle pagination bugs.

Have you migrated an endpoint from OFFSET to keyset? I'd love to hear how it went and what the speedup looked like in the comments.

Further reading: the PostgreSQL docs on row-wise comparison and LIMIT/OFFSET, and Markus Winand's "Use The Index, Luke" on why not to use offset.

Why `await Promise.all()` Can Crash Your Database (And How to Throttle Concurrent Async Tasks in Node.js)

subashthiruppathy — Thu, 15 Oct 2026 16:10:58 +0000

It's the most natural-looking line in modern JavaScript:

await Promise.all(ids.map((id) => processOrder(id)));

Fast, concise, parallel. In development with 20 IDs it finishes in a blink. Then a backfill job runs it against 200,000 IDs, and:

The database CPU pins at 100%, or too many clients already floods the logs
Other endpoints on the same service start timing out
The third-party API starts returning 429 Too Many Requests
The process runs out of memory, or dies with EMFILE: too many open files

Promise.all didn't do anything wrong. It did exactly what you asked. The problem is that you asked for unlimited concurrency, and you may not have realized it.

This post explains what Promise.all actually does, the specific ways unbounded fan-out hurts a Postgres-backed Node.js service, and several ways to throttle concurrency, from a five-line loop to a proper queue.

The misconception: `Promise.all` doesn't run things in parallel

Look at what happens in this line, step by step:

const promises = ids.map((id) => processOrder(id)); // (1) every task STARTS here
const results = await Promise.all(promises);        // (2) this only WAITS

An async function begins executing the moment you call it. By the end of step 1, processOrder has been invoked N times, and N operations are already in flight. Promise.all at step 2 does no scheduling or throttling at all. It just waits for the promises that already exist.

So "use Promise.all to run things concurrently" is true, but incomplete. It has no concurrency limit. If the array has 200,000 items, you start 200,000 operations at once.

Two more properties that matter for failure handling:

Fail-fast, but no cancellation. If one promise rejects, Promise.all rejects immediately, but the other tasks keep running. Your code has moved on (probably into an error handler) while hundreds of queries are still executing in the background.
All results are held in memory until everything finishes.

How unbounded concurrency hurts

1. Connection pool exhaustion and queue pile-up

With pg, the pool has a fixed max (default 10). Extra pool.query() calls don't fail, they wait in a queue:

const pool = new Pool({ max: 10, connectionTimeoutMillis: 2_000 });

// 5,000 queries start simultaneously: 10 run, 4,990 wait in the pool's queue
await Promise.all(ids.map((id) => pool.query('SELECT * FROM orders WHERE id = $1', [id])));

Consequences:

Everyone queued behind this job waits too, including unrelated requests (login, health checks, your API traffic). A single background job can starve the whole service.
Waiters beyond connectionTimeoutMillis fail with timeout exceeded when trying to connect, so a job that "should be fine" fails partway.
Thousands of pending promises, closures, and queued requests consume memory and add GC pressure.

(We covered this failure family in depth in the post on connection pool starvation.)

2. Overloading the database itself

The pool protects Postgres from connection floods, but only if every task shares it. Two common ways the database still gets crushed:

Per-task connections. Code that does new Client() per task, or an ORM/serverless setup creating a pool per invocation, opens one real connection per concurrent task. Each Postgres connection is a separate backend process. Hundreds of them exhaust max_connections and make everything slower, since too many concurrent active queries mean CPU and I/O contention, not speed.
Heavy queries in lockstep. Even through a pool, if each query is expensive and your pool is large, saturating the pool means the database runs max heavy queries simultaneously. Concurrency above what the hardware can usefully execute adds queueing and lock contention without adding throughput.

Bulk writes add their own problems: lock contention on hot rows, replication lag on replicas, and WAL volume spikes.

3. Hitting downstream rate limits

Calling an external API for each item with Promise.all is a reliable way to earn 429s, temporary bans, or throttled accounts. The retries then pile on more load.

4. Memory and file descriptors

Each in-flight task holds buffers, response bodies, and promise state. Reading 10,000 files concurrently can hit EMFILE. Fetching 10,000 large responses concurrently can exhaust memory long before the work completes.

5. Retry storms and thundering herds

A brief database hiccup makes thousands of concurrent tasks fail at once, and if each retries on its own, they all retry at once. Bounded concurrency naturally limits how much work is exposed to a failure and how much re-attempts at once.

Reproduce it on purpose

Seeing the failure beats believing it:

// repro.ts
import { Pool } from 'pg';

const pool = new Pool({ max: 10, connectionTimeoutMillis: 2_000 });

const results = await Promise.allSettled(
  Array.from({ length: 5_000 }, () => pool.query('SELECT pg_sleep(0.05)')),
);

const failed = results.filter((r) => r.status === 'rejected');
console.log(`failed: ${failed.length} / ${results.length}`);
console.log((failed[0] as PromiseRejectedResult | undefined)?.reason?.message);
// Typically: many failures with "timeout exceeded when trying to connect"

Each query takes ~50 ms and the pool runs 10 at a time, so the last queries in the queue wait far longer than the 2-second connection timeout. Many fail even though every individual query is trivial. (Exact numbers depend on your machine.)

Fix 1: Process in chunks (simplest)

// ✅ At most `size` in flight at a time
async function inChunks<T, R>(items: T[], size: number, fn: (item: T) => Promise<R>): Promise<R[]> {
  const out: R[] = [];
  for (let i = 0; i < items.length; i += size) {
    const chunk = items.slice(i, i + size);
    out.push(...(await Promise.all(chunk.map(fn))));
  }
  return out;
}

const results = await inChunks(ids, 10, (id) => processOrder(id));

It's dead simple, but there's an efficiency flaw: each chunk is only as fast as its slowest task. If one task in a chunk takes 5 seconds and the other nine take 50 ms, nine "slots" sit idle for ~5 seconds. With uneven task times this wastes a lot of capacity. The next options keep slots busy continuously.

Fix 2: `p-limit` (sliding-window concurrency)

p-limit gives you a limiter: wrap each task, and at most N run at once. As soon as one finishes, the next starts.

import pLimit from 'p-limit';

const limit = pLimit(5);

const results = await Promise.all(
  ids.map((id) => limit(() => processOrder(id))),   // note: the function is deferred
);

The crucial detail is limit(() => processOrder(id)). You must pass a function so the task doesn't start until the limiter allows it. Writing limit(processOrder(id)) calls processOrder immediately and defeats the purpose.

Caveats:

Recent versions of p-limit (and p-map) are ESM-only. In a CommonJS project, use dynamic import() or pin an older version.
The ids.map(...) above still creates N closures and promises up front, which is fine for thousands of items, but for millions you want an iterator-based approach (below).

Fix 3: `p-map` (map with concurrency built in)

p-map packages the pattern as a single call and has useful error options:

import pMap from 'p-map';

const results = await pMap(ids, (id) => processOrder(id), {
  concurrency: 5,
  stopOnError: true,   // default: reject on first error and stop starting new tasks
});

Set stopOnError: false to run everything and get an AggregateError at the end. Because it accepts iterables, you can feed it a generator or lazily-produced sequence rather than materializing a giant array.

Fix 4: A tiny worker pool with no dependencies

Dependencies are optional. The core idea fits in a few lines: start limit workers, and each one repeatedly pulls the next item until none are left.

export async function mapLimit<T, R>(
  items: readonly T[],
  limit: number,
  fn: (item: T, index: number) => Promise<R>,
): Promise<R[]> {
  const results = new Array<R>(items.length);
  let next = 0;
  let failed = false;

  async function worker() {
    while (!failed) {
      const i = next++;                    // safe: JS runs this synchronously, no race
      if (i >= items.length) return;
      try {
        results[i] = await fn(items[i], i);
      } catch (err) {
        failed = true;                     // tell sibling workers to stop pulling new items
        throw err;
      }
    }
  }

  await Promise.all(Array.from({ length: Math.min(limit, items.length) }, worker));
  return results;
}

const results = await mapLimit(ids, 5, (id) => processOrder(id));

Why next++ is safe without locks: Node runs your JavaScript on a single thread, and there's no await between reading and incrementing next, so two workers can't claim the same index.

The failed flag matters. Without it, when one task throws, Promise.all rejects but the other workers keep pulling new items, continuing to do work nobody's waiting for, which is the "no cancellation" problem from earlier.

If you'd rather collect every outcome instead of failing fast, catch inside the worker and store { status, value | reason } per item, mirroring Promise.allSettled.

Fix 5: Don't load everything into memory first

Throttling the tasks doesn't help if you first load 5 million rows into an array. For big jobs, make the producer lazy so there's a bounded amount of work in memory at any time.

import { once } from 'node:events';

// Page through the source with keyset pagination and process a bounded window
async function backfill() {
  let lastId = 0;
  for (;;) {
    const { rows } = await pool.query(
      'SELECT id FROM orders WHERE id > $1 ORDER BY id LIMIT 1000',
      [lastId],
    );
    if (rows.length === 0) break;

    await mapLimit(rows, 8, (row) => processOrder(row.id));
    lastId = rows[rows.length - 1].id;
  }
}

Memory stays proportional to the page size (1,000), not the table size. Keyset pagination (WHERE id > $1 ORDER BY id LIMIT n) also stays fast on deep pages, unlike OFFSET. For CSV or file inputs, the same idea applies with streams: read a line, hand it to a bounded processor, and rely on backpressure instead of buffering the whole file.

Fix 6: Reduce the number of tasks (batch in SQL)

The cheapest concurrent query is the one you don't run. If each task is "update one row", you may not need N tasks at all:

// ❌ 10,000 tasks, even throttled: 10,000 round trips
await mapLimit(updates, 10, (u) => pool.query('UPDATE orders SET status = $1 WHERE id = $2', [u.status, u.id]));

// ✅ 1 round trip per 1,000 rows
for (const batch of chunk(updates, 1000)) {
  await pool.query(
    `UPDATE orders o
        SET status = v.status
       FROM unnest($1::bigint[], $2::text[]) AS v(id, status)
      WHERE o.id = v.id`,
    [batch.map((u) => u.id), batch.map((u) => u.status)],
  );
}

Passing arrays and expanding them with unnest lets one statement handle a whole batch. The same pattern works for inserts (INSERT ... SELECT ... FROM unnest(...)), and for very large loads, COPY is faster still. Combine the two ideas: batch to reduce round trips, then throttle the batches.

Fix 7: For big or durable work, use a queue

If the job is huge, must survive restarts, needs retries, or shouldn't block your web process, concurrency control belongs in a job queue rather than in an in-process loop. For example with BullMQ:

import { Queue, Worker } from 'bullmq';

const queue = new Queue('backfill', { connection });
await queue.addBulk(ids.map((id) => ({ name: 'process-order', data: { id } })));

new Worker('backfill', async (job) => processOrder(job.data.id), {
  connection,
  concurrency: 5,                              // per-worker cap
  limiter: { max: 100, duration: 1_000 },      // optional: at most 100 jobs per second
});

You get durable state, retries with backoff, a rate limiter, and the ability to scale by adding workers. Remember that with several workers, the total concurrency is workers × concurrency, so size the sum against your database, not each worker in isolation.

Concurrency limit vs. rate limit

These are different controls, and APIs often need the second:

Concurrency limit: at most N operations in flight at once. Protects pools, memory, and database load.
Rate limit: at most N operations per time window (for example, 50 requests per second). Protects against 429s.

A concurrency cap of 10 with 20 ms calls is still ~500 requests/second, which may break a 100/s API limit. Libraries such as bottleneck support both concurrency and rate limits in one limiter. Pick based on what the downstream system actually constrains.

How to choose the number

There's no universal value, but there's a sensible way to pick one:

Never exceed what the downstream can serve. For database work, stay below your pool size, and leave headroom for normal traffic, since the pool is shared. If the pool is 10 and the API needs to stay responsive, a background job might get 3 to 5.
Account for every instance. instances × concurrency is the real load. Ten pods each running a 10-wide job is 100 concurrent operations.
Measure. Run the job at concurrency 1, 2, 4, 8, 16 and watch throughput and p95 latency. Throughput rises, flattens, then often drops as contention takes over. Choose a value at or just before the knee.
Little's Law helps with intuition: concurrency ≈ throughput × latency. If each query takes 20 ms and you want 250 queries/second, you need about 5 in flight. More than that mostly adds queueing.

for (const concurrency of [1, 2, 4, 8, 16, 32]) {
  const start = performance.now();
  await mapLimit(sample, concurrency, (id) => processOrder(id));
  const secs = (performance.now() - start) / 1000;
  console.log(`concurrency=${concurrency}  throughput=${(sample.length / secs).toFixed(0)}/s`);
}

Error handling that doesn't leave a mess

Prefer Promise.allSettled when partial success is acceptable, so one failure doesn't hide the other results.
Add per-task timeouts (AbortSignal.timeout(ms)) so one hung task can't occupy a slot forever.
Retry transient errors with backoff and jitter, a bounded number of times, inside the limited task so retries also respect the concurrency cap.
Make tasks idempotent. Throttled jobs still get interrupted, rerun, and retried, so processing an item twice should be harmless.
Stop cleanly on shutdown. Have workers check a shutdown signal before taking the next item, so a deploy doesn't kill a job mid-task.

Choosing an approach

Approach	Slots stay busy?	Handles huge inputs?	Dependency	Best for
`Promise.all` on everything	n/a (unbounded)	No	None	Small, bounded N only
Chunked `Promise.all`	No (waits for slowest)	Yes	None	Simple scripts, uniform task times
`p-limit` / `p-map`	Yes	Moderate (`p-map` with iterables: yes)	Yes (ESM)	General-purpose throttling
Hand-rolled `mapLimit`	Yes	Moderate	None	No-dependency code, custom error rules
Lazy paging + limiter	Yes	Yes	Optional	Backfills over big tables
SQL batching (`unnest`)	n/a	Yes	None	Many small writes
Job queue	Yes	Yes	Yes	Durable, retried, scalable work

Checklist

✅ Ask of every Promise.all(xs.map(...)): how big can xs get? If unbounded, throttle it
✅ Keep a task's concurrency well below your pool size, leaving headroom for live traffic
✅ Pass a function to the limiter, not an already-started promise
✅ Use allSettled or a fail-flag so errors don't leave orphaned work running
✅ Page through big sources instead of loading them whole
✅ Batch small writes with unnest or COPY
✅ Separate concurrency limits from rate limits
✅ Measure throughput at several concurrency levels, and size for all instances combined

Key takeaways

Promise.all doesn't parallelize or throttle. Tasks start the moment you create the promises, and Promise.all just waits.
Unbounded fan-out causes pool starvation, database overload, rate-limit errors, and memory blowups, often while every individual query looks fast.
Throttle with a sliding window: p-limit, p-map, or a small worker pool. Chunking works but wastes slots on uneven tasks.
Reduce the work too. Batch in SQL, and stream or page large inputs rather than materializing them.
Concurrency isn't rate limiting. Know which one your downstream needs.
Pick the number by measurement, accounting for every instance and for the traffic sharing your pool.
Use a real queue when work is large, long-running, or must survive restarts.

Have you been burned by an innocent-looking Promise.all? Tell the story in the comments.

Further reading: p-limit, p-map, bottleneck, and the node-postgres pooling docs.

Graceful Shutdown in Node.js: Preventing Dropped Requests and Broken Database Transactions

subashthiruppathy — Wed, 14 Oct 2026 16:46:59 +0000

Every time you deploy, autoscale, or restart a node, your orchestrator does the same thing to your app: it asks it to stop.

If your app handles that request well, nobody notices. If it doesn't, you get a brief spike of 502s on every release, users who got "payment failed" on a payment that actually went through, background jobs that ran twice, and database transactions cut off mid-flight.

The frustrating part is that it's easy to miss. Locally you press Ctrl-C and the process dies instantly, with no one watching. In production, with real traffic, the same behavior drops real requests.

This post covers how to shut a Node.js service down gracefully: stop taking new work, finish the work in flight, release resources in the right order, and exit before the orchestrator loses patience.

What actually happens when your app is stopped

On Docker, Kubernetes, ECS, systemd, and most platforms, stopping a process is a two-step sequence:

The platform sends SIGTERM: "please shut down."
After a grace period, if you're still running, it sends SIGKILL, which can't be caught or handled. The process is gone immediately.

Platform	Default grace period
Docker (`docker stop`)	10 seconds (`--stop-timeout` / `stop_grace_period` to change)
Kubernetes	30 seconds (`terminationGracePeriodSeconds`)

The grace period is your time budget. Everything in this post has to fit inside it.

If you don't handle SIGTERM, Node's default behavior is to exit immediately. Every in-flight request is cut mid-response, and every open transaction loses its connection.

(Ctrl-C sends SIGINT, which behaves similarly, so handle both.)

Failure mode 1: The naive version

// ❌ No signal handling at all
const server = app.listen(3000);

On deploy, in-flight requests are severed. Clients see ECONNRESET or a 502 from the load balancer.

The first fix everyone tries:

// ⚠️ Better, but still wrong
process.on('SIGTERM', () => {
  server.close();
  process.exit(0);   // kills everything still in flight, immediately
});

server.close() stops accepting new connections, but process.exit(0) on the next line terminates the process before any in-flight request can finish. This is the most common "I handle SIGTERM" bug.

The correct shutdown sequence

Order matters. The principle is: stop work coming in, let work in progress finish, then tear down what that work depended on, and not before.

SIGTERM
  │
  1. Mark unready          → load balancer stops sending new traffic
  2. Wait briefly          → let routing updates propagate
  3. Stop accepting HTTP   → server.close(), close idle keep-alive connections
  4. Drain in-flight work  → requests finish (with a deadline)
  5. Stop background work  → job workers, queue consumers, cron
  6. Close dependencies    → DB pool, Redis, tracing/log flush
  7. Exit                  → code 0 if clean, 1 if forced

Closing the database pool at step 6 and not step 3 is the key insight for transactions: requests that are still running need their connections.

Step 1 and 2: Fail readiness first, then wait

On Kubernetes, termination is not a clean sequence. When a pod is deleted, two things happen concurrently: the pod is removed from Service endpoints, and the container receives SIGTERM. Routing updates take a moment to reach every proxy and load balancer. If you close your listener the instant SIGTERM arrives, some traffic is still being routed to you and hits a closed port.

The fix is to make your app report not ready, then pause briefly before closing the listener.

let shuttingDown = false;

app.get('/healthz', (_req, res) => res.sendStatus(200));            // liveness: process is alive
app.get('/readyz', (_req, res) => res.sendStatus(shuttingDown ? 503 : 200)); // readiness

You can do the pause in the app (await sleep(5000) before server.close()), or with a Kubernetes preStop hook:

lifecycle:
  preStop:
    exec:
      command: ["sleep", "5"]

Either works. Just make sure it's counted in your time budget (see below).

Step 3 and 4: Stop accepting, drain in-flight requests

Node's server.close() stops the server from accepting new connections and fires its callback once all existing connections have ended. Two details trip people up:

HTTP keep-alive. Idle keep-alive connections count as open connections, so close() can appear to hang until they time out. In Node 19+, server.close() closes idle connections for you. On Node 18.2+, call server.closeIdleConnections() explicitly. For connections that are busy during shutdown, tell clients not to reuse them by sending Connection: close on responses:

app.use((req, res, next) => {
  if (shuttingDown) res.set('Connection', 'close');
  next();
});

A hard deadline. One stuck request (a client that never finishes reading) would hold shutdown open until SIGKILL. Set your own deadline, a bit shorter than the platform's, and force-close the stragglers when it hits:

function closeServer(server: http.Server, deadlineMs: number): Promise<void> {
  return new Promise((resolve, reject) => {
    const force = setTimeout(() => {
      log.warn('HTTP drain deadline hit; closing remaining connections');
      server.closeAllConnections?.();             // Node 18.2+
    }, deadlineMs);

    server.close((err) => {
      clearTimeout(force);
      err ? reject(err) : resolve();
    });
    server.closeIdleConnections?.();              // Node 18.2+ (no-op need on 19+)
  });
}

Long-lived connections need special handling. server.close() doesn't close WebSockets or SSE streams for you. Track them and close them explicitly (for WebSockets, send a close frame with code 1001, "going away", so clients know to reconnect elsewhere).

Step 5: Stop background work

If the same process runs queue workers or consumers, stop them before closing the resources they use.

import { Worker } from 'bullmq';

// worker.close() waits for currently active jobs to finish by default
await worker.close();

With BullMQ, worker.close() stops fetching new jobs and waits for active ones to complete. If a job outlasts your grace period and the process is killed, BullMQ will eventually consider that job stalled and retry it, which means your job handlers must be idempotent. Graceful shutdown reduces duplicate runs. It doesn't eliminate them. The same applies to any at-least-once consumer (Kafka, SQS, RabbitMQ): stop consuming, finish or negotiate the in-flight message, and commit or ack appropriately before disconnecting.

Stop timers and cron schedulers too, so no new work starts while you're draining.

Step 6: Close dependencies, and why order protects your transactions

Now that nothing is using them, close the shared resources.

await pool.end();          // pg: ends idle clients, and checked-out clients as they're released
await redis.quit();        // ioredis: graceful (waits for pending replies); disconnect() is abrupt
await otelSdk.shutdown();  // flush traces/metrics
await logger.flush?.();    // flush buffered logs, if your logger buffers

Why this order protects database transactions: a request in the middle of BEGIN ... COMMIT holds a checked-out connection. If you close the pool (or exit) first, that transaction is cut off. Because we waited for requests to drain in step 4, those transactions have committed or rolled back by the time we reach pool.end().

What if you are killed mid-transaction anyway (SIGKILL, OOM, node failure)? PostgreSQL has your back on atomicity: when the connection drops, the server rolls back the uncommitted transaction automatically. Your data won't be half-written inside the database.

The danger is everything outside the database. If the handler already charged a card, published a message, or sent an email before the commit, killing the process leaves the world in an inconsistent state: payment taken, no order recorded. Graceful shutdown narrows that window, but it can't close it. The durable fixes are:

Idempotency keys on external calls and on the endpoints themselves, so a retry is safe
The outbox pattern: write the event in the same transaction as the data, and publish it from a separate relay
Keeping transactions short, with no slow network calls inside them (which also protects your connection pool)

Don't let a deadline become a mid-commit kill

If drain is taking too long, you'd rather abort work cleanly than be SIGKILLed at a random point. Tie request work to a shutdown signal so handlers can stop and roll back on their own terms:

const shutdownController = new AbortController();

// in shutdown(), when the drain budget is nearly spent:
shutdownController.abort(new Error('server shutting down'));

// in a handler / withTransaction callback:
shutdownController.signal.throwIfAborted();   // before starting the next step

An explicit abort leads to an explicit ROLLBACK and a clean error response, which is always better than a severed connection.

Putting it together

// server.ts
import http from 'node:http';
import express from 'express';

const GRACE_MS = Number(process.env.SHUTDOWN_GRACE_MS ?? 25_000); // < platform grace period
const READINESS_DELAY_MS = Number(process.env.READINESS_DELAY_MS ?? 5_000);
const HTTP_DRAIN_MS = Number(process.env.HTTP_DRAIN_MS ?? 15_000);

const app = express();
let shuttingDown = false;

app.use((_req, res, next) => {
  if (shuttingDown) res.set('Connection', 'close');
  next();
});
app.get('/healthz', (_req, res) => res.sendStatus(200));
app.get('/readyz', (_req, res) => res.sendStatus(shuttingDown ? 503 : 200));
// ...your routes

const server = http.createServer(app).listen(Number(process.env.PORT ?? 3000));

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

async function shutdown(signal: string) {
  if (shuttingDown) {                       // second signal: operator is impatient, stop waiting
    log.warn({ signal }, 'second signal received; forcing exit');
    process.exit(1);
  }
  shuttingDown = true;
  log.info({ signal }, 'shutdown started');

  // Absolute backstop: never outlive the platform's grace period
  setTimeout(() => {
    log.error('graceful shutdown timed out; forcing exit');
    process.exit(1);
  }, GRACE_MS).unref();

  let exitCode = 0;
  try {
    await sleep(READINESS_DELAY_MS);              // 1-2. unready, let routing catch up
    await closeServer(server, HTTP_DRAIN_MS);     // 3-4. stop accepting, drain requests
    await worker.close();                         // 5.   finish active jobs
    await pool.end();                             // 6.   DB pool (nothing is using it now)
    await redis.quit();
    await otelSdk.shutdown();
    log.info('shutdown complete');
  } catch (err) {
    log.error({ err }, 'error during shutdown');
    exitCode = 1;
  }
  process.exit(exitCode);                         // 7.
}

process.on('SIGTERM', () => void shutdown('SIGTERM'));
process.on('SIGINT', () => void shutdown('SIGINT'));

Notes on the details:

process.exit() at the end is fine once cleanup is done. The problem earlier was calling it instead of cleanup. Without it, a stray open handle (a forgotten interval, a socket) can keep the process alive until SIGKILL.
The .unref() on the backstop timer means it never keeps the process alive by itself.
The second-signal guard makes Ctrl-C twice do what developers expect, and prevents running the shutdown sequence twice.

Crashes should shut down, too

An uncaughtException or unhandledRejection means your process is in an unknown state. Don't try to carry on, but do try to exit cleanly:

process.on('unhandledRejection', (reason) => {
  log.error({ reason }, 'unhandled rejection');
  void shutdown('unhandledRejection');
});
process.on('uncaughtException', (err) => {
  log.fatal({ err }, 'uncaught exception');
  void shutdown('uncaughtException');
});

For crash paths, consider a shorter drain budget and a non-zero exit code so your orchestrator records a failure.

Budget your time

The pieces must fit inside the platform's grace period. For a Kubernetes default of 30 seconds:

Phase	Budget
Readiness delay / `preStop`	5s
HTTP drain	15s
Workers, pool, flush	5s
Safety margin	5s

Then set terminationGracePeriodSeconds to match what you actually need. If your longest legitimate request takes 60 seconds, a 30-second grace period guarantees you'll kill some of them, so either raise the grace period or shorten the work. And keep your in-app deadline (GRACE_MS) comfortably below the platform's.

spec:
  terminationGracePeriodSeconds: 45
  containers:
    - name: api
      lifecycle:
        preStop:
          exec: { command: ["sleep", "5"] }
      readinessProbe:
        httpGet: { path: /readyz, port: 3000 }

Container gotchas that silently break all of this

Signals never reach your app

This one is brutal because your shutdown code is perfect and never runs.

# ❌ npm and shell-wrapped commands often don't forward signals to node
CMD npm start

# ✅ exec form, running node directly
CMD ["node", "dist/server.js"]

If npm or a shell is PID 1, SIGTERM goes to it, and it frequently doesn't pass it on. Docker then waits out the full grace period and SIGKILLs you. Use the exec form of CMD/ENTRYPOINT and run node directly (or use --init / tini as a minimal init process).

Node as PID 1

On Linux, PID 1 gets special treatment: signals without an explicit handler are ignored, not defaulted. Once you register process.on('SIGTERM', ...) this is fine, but it's another reason the handler must actually be installed.

Dev tooling

Watchers such as nodemon or tsx watch restart your process on file change using signals as well. They're good for exercising your shutdown path locally.

Frameworks and libraries that help

You don't have to hand-roll everything:

Fastify: await app.close() runs onClose hooks for plugins, and stops the server
NestJS: call app.enableShutdownHooks() and implement OnApplicationShutdown / OnModuleDestroy
http-terminator or stoppable: utilities that track connections for you and implement drain-with-deadline for plain http servers

Whatever you use, still verify the behavior with a test. Defaults vary by version.

Test it, don't trust it

Shutdown bugs only show up during deploys, so reproduce them on purpose. Spawn the real server, start a slow request, send SIGTERM, and check that the request completes and the process exits cleanly:

// shutdown.test.ts
import { spawn } from 'node:child_process';
import { it, expect } from 'vitest';

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

it('finishes in-flight requests on SIGTERM, then exits 0', async () => {
  const child = spawn('node', ['dist/server.js'], {
    env: { ...process.env, PORT: '4123', READINESS_DELAY_MS: '0' },
    stdio: 'inherit',
  });
  const exited = new Promise<number | null>((resolve) => child.on('exit', resolve)); // attach before kill

  await waitForOk('http://localhost:4123/readyz');

  const slow = fetch('http://localhost:4123/slow?ms=2000');   // a test route that sleeps
  await sleep(200);                                           // make sure it's in flight
  child.kill('SIGTERM');

  const res = await slow;
  expect(res.status).toBe(200);                               // request was NOT dropped
  expect(await exited).toBe(0);                               // clean exit
});

it('exits non-zero if drain exceeds the deadline', async () => {
  // start with a tiny SHUTDOWN_GRACE_MS, hit /slow?ms=60000, send SIGTERM,
  // and assert the process exits with code 1 well before 60s
});

For a stronger check, run a load test (autocannon, k6) against a staging deployment while performing a rolling restart, and watch for non-2xx responses. A well-behaved service shows zero errors during the roll.

Checklist

✅ Handle SIGTERM and SIGINT; install the handlers before listening
✅ Fail readiness first and pause briefly for routing to catch up
✅ server.close() plus closing idle connections; send Connection: close while draining
✅ Track and close WebSockets/SSE explicitly
✅ Stop job workers and consumers before closing their dependencies
✅ Close the DB pool and other clients after in-flight work has finished
✅ Hard deadline shorter than the platform's grace period; force-exit beyond it
✅ Idempotency and the outbox pattern for side effects outside the database
✅ CMD ["node", ...] in exec form so signals reach your process
✅ A test that sends SIGTERM mid-request

Key takeaways

Deploys are shutdowns. Every release sends SIGTERM to your app, then SIGKILL after the grace period.
server.close() then process.exit() is a bug. Wait for the close to complete before exiting.
Order is everything: unready, stop accepting, drain requests, stop workers, close the pool, exit.
Close the database pool last, so in-flight transactions can finish. If the process dies anyway, Postgres rolls back uncommitted work, but side effects outside the database need idempotency or an outbox.
Always have a deadline, shorter than the platform's grace period, so a stuck request can't turn a clean shutdown into a SIGKILL.
Check your container. If signals never reach node, none of the above matters.
Test it by sending SIGTERM during a slow request.

What's the worst shutdown bug you've seen in production? Share it in the comments.

Further reading: the Node.js docs for http.Server.close() and process signal events, and the Kubernetes documentation on pod termination.

Optimistic vs. Pessimistic Locking in PostgreSQL & Node.js: Preventing Inventory Overselling

subashthiruppathy — Tue, 13 Oct 2026 14:51:03 +0000

You launch a sale. There are 10 units of a hot item in stock. Fifteen people click "Buy" within the same second. When the dust settles, your database says:

stock = -2
orders confirmed = 12

Two customers are getting an apology email. Support is unhappy, and finance wants to know how the stock count went negative.

This is the lost update problem, and it's one of the classic concurrency bugs in backend code. It doesn't show up in development (one user, one request at a time), it passes unit tests, and then it appears the first time real concurrent traffic hits the same row.

In this post we'll reproduce the bug, then fix it three ways with PostgreSQL and Node.js:

Atomic conditional updates (often all you need)
Pessimistic locking (SELECT ... FOR UPDATE)
Optimistic locking (version checks with retries)

We'll compare them honestly, because the right choice depends on your contention level.

The bug: read, check, write

Here's the code almost everyone writes first:

// ❌ Race condition
async function purchase(productId: number, qty: number) {
  const { rows } = await pool.query('SELECT stock FROM products WHERE id = $1', [productId]);
  const stock = rows[0].stock;

  if (stock < qty) throw new OutOfStockError();

  await pool.query('UPDATE products SET stock = $1 WHERE id = $2', [stock - qty, productId]);
  await pool.query('INSERT INTO orders (product_id, qty) VALUES ($1, $2)', [productId, qty]);
}

Now two requests run at the same time with stock = 1:

Request A                          Request B
SELECT stock  → 1
                                   SELECT stock  → 1
check 1 >= 1  ✓
                                   check 1 >= 1  ✓
UPDATE stock = 0
                                   UPDATE stock = 0     ← overwrites A; both think they got the last unit
INSERT order                       INSERT order         ← two orders, one unit

Both requests read the same value, both pass the check, and the second write silently clobbers the first. The gap between the read and the write is the race window. Every fix below is a different way of closing it.

Prove it before you fix it

Make this a test you keep forever. If you can't reproduce the bug, you can't trust the fix:

// oversell.test.ts
import { it, expect, beforeEach } from 'vitest';

beforeEach(async () => {
  await pool.query('TRUNCATE orders RESTART IDENTITY');
  await pool.query("UPDATE products SET stock = 10 WHERE id = 1");
});

it('never sells more than the available stock', async () => {
  // 50 buyers fight over 10 units
  const results = await Promise.allSettled(
    Array.from({ length: 50 }, () => purchase(1, 1)),
  );

  const sold = results.filter((r) => r.status === 'fulfilled').length;
  const { rows: [{ stock }] } = await pool.query('SELECT stock FROM products WHERE id = 1');
  const { rows: [{ count }] } = await pool.query('SELECT count(*)::int AS count FROM orders');

  expect(sold).toBe(10);
  expect(count).toBe(10);
  expect(stock).toBe(0);
});

Run it against the naive version and it will fail. Use a pool larger than 1 so the requests genuinely interleave. (Timing-dependent tests can occasionally pass by luck, so run it several times, or raise the buyer count.)

Fix 0: Let the database do it atomically

Before reaching for locks, consider the simplest fix: move the check into the write.

// ✅ Check and decrement in one atomic statement
async function purchase(productId: number, qty: number) {
  return withTransaction(async (tx) => {
    const res = await tx.query(
      `UPDATE products
          SET stock = stock - $1
        WHERE id = $2 AND stock >= $1`,
      [qty, productId],
    );

    if (res.rowCount === 0) throw new OutOfStockError(); // not enough stock (or no such product)

    await tx.query('INSERT INTO orders (product_id, qty) VALUES ($1, $2)', [productId, qty]);
  });
}

Why this works: a single UPDATE takes a row-level lock on the row it modifies. If a second transaction tries to update the same row, it waits for the first to commit or roll back. Under PostgreSQL's default READ COMMITTED isolation level, once the lock is released, the waiting statement re-checks its WHERE clause against the newly committed row. So request B re-evaluates stock >= 1 against the value A left behind (0) and updates nothing.

Request A                          Request B
UPDATE ... WHERE stock >= 1  → locks row, stock 1 → 0
                                   UPDATE ... WHERE stock >= 1  → waits on A's lock
COMMIT                             → re-checks: stock is 0, no match → rowCount = 0 → OutOfStock

Two things make this solid:

The decrement is relative (stock = stock - $1), not an absolute value computed in the app. That's what avoids the lost update.
The guard is part of the same statement, so there's no window between check and write.

Add a final safety net at the schema level so a future bug can never produce negative stock:

ALTER TABLE products ADD CONSTRAINT stock_non_negative CHECK (stock >= 0);

For many inventory systems, this is the whole answer. Locks, versions, and retries are for the cases where the logic between read and write is too complex to fit into a single UPDATE: pricing rules, multi-step validation, reservations that depend on other rows.

Fix 1: Pessimistic locking (`SELECT ... FOR UPDATE`)

Pessimistic locking assumes conflicts are likely, so it locks the row up front, before reading. Anyone else who wants the row waits.

async function purchase(productId: number, qty: number) {
  return withTransaction(async (tx) => {
    // Lock the row; concurrent transactions block here until we commit/rollback
    const { rows } = await tx.query(
      'SELECT stock, price_cents FROM products WHERE id = $1 FOR UPDATE',
      [productId],
    );
    if (rows.length === 0) throw new NotFoundError();

    const { stock, price_cents } = rows[0];

    // Arbitrary application logic is now safe: nobody can change this row under us
    if (stock < qty) throw new OutOfStockError();
    const total = computeTotalWithPromotions(price_cents, qty);

    await tx.query('UPDATE products SET stock = stock - $1 WHERE id = $2', [qty, productId]);
    await tx.query(
      'INSERT INTO orders (product_id, qty, total_cents) VALUES ($1, $2, $3)',
      [productId, qty, total],
    );
  });
}

withTransaction is the helper from earlier posts in this series: it acquires a client, issues BEGIN, runs your function, commits or rolls back, and always releases the connection.

Now the read, the check, and the write are one critical section. This is the right tool when the logic between read and write can't be expressed as a single SQL statement.

Pessimistic locking variants worth knowing

-- Fail immediately if someone else holds the lock, rather than waiting
SELECT ... FROM products WHERE id = $1 FOR UPDATE NOWAIT;      -- error 55P03 (lock_not_available)

-- Skip locked rows (great for job queues and "claim the next available item")
SELECT ... FROM tickets WHERE status = 'free' LIMIT 1 FOR UPDATE SKIP LOCKED;

You can also bound waiting with SET LOCAL lock_timeout = '2s' inside the transaction, so a stuck lock turns into a fast error instead of a pile-up.

The costs of pessimistic locking

Contention serializes your throughput. Every buyer of the same product queues behind the lock holder. For a flash-sale SKU, that's one-at-a-time processing.
Long transactions are dangerous. The lock is held until COMMIT. If you call a payment API, send an email, or do any slow work while holding it, everything else waits, and you'll also pin a connection from your pool the whole time (see our post on pool starvation). Keep locked transactions short, and never do network I/O inside them.
Deadlocks. If two transactions lock rows in opposite orders, Postgres detects the cycle and aborts one with error 40P01.

Avoiding deadlocks in multi-item orders

A cart with several products locks several rows. If request A locks product 1 then 2, while request B locks 2 then 1, they deadlock. The fix is simple and effective: always acquire locks in a consistent order.

async function purchaseCart(items: { productId: number; qty: number }[]) {
  // Sort so every transaction locks rows in the same order
  const ordered = [...items].sort((a, b) => a.productId - b.productId);

  return withTransaction(async (tx) => {
    for (const { productId, qty } of ordered) {
      const res = await tx.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1',
        [qty, productId],
      );
      if (res.rowCount === 0) throw new OutOfStockError(productId); // rolls back ALL items
    }
    await tx.query('INSERT INTO orders ...');
  });
}

Notice this uses the atomic pattern from Fix 0 plus sorted ordering, and the transaction guarantees all-or-nothing: if the third item is out of stock, the first two decrements are rolled back.

Even with consistent ordering, wrap your transactions in a retry for 40P01 (deadlock detected) and 40001 (serialization failure). They're rare, but they're expected, not exceptional.

Fix 2: Optimistic locking (version column + retry)

Optimistic locking assumes conflicts are rare, so it doesn't lock anything while you think. Instead it detects a conflict at write time and makes you try again.

Add a version column:

ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

Read the row and its version without locking. When writing, include the version you saw in the WHERE clause and bump it:

async function purchaseOptimistic(productId: number, qty: number) {
  const { rows } = await pool.query(
    'SELECT stock, version FROM products WHERE id = $1',
    [productId],
  );
  const { stock, version } = rows[0];

  if (stock < qty) throw new OutOfStockError();

  const res = await pool.query(
    `UPDATE products
        SET stock = $1, version = version + 1
      WHERE id = $2 AND version = $3`,
    [stock - qty, productId, version],
  );

  if (res.rowCount === 0) throw new ConflictError(); // someone else changed the row since we read it
  // ...create order (in the same transaction in real code)
}

If another request modified the row between our read and write, its version bump means our WHERE version = $3 matches nothing, rowCount is 0, and we know our view of the world was stale. The caller then retries from the top with fresh data.

The retry loop is not optional

async function withRetry<T>(fn: () => Promise<T>, maxAttempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await fn();
    } catch (err: any) {
      const retryable =
        err instanceof ConflictError ||
        err.code === '40001' ||   // serialization_failure
        err.code === '40P01';     // deadlock_detected
      if (!retryable || attempt >= maxAttempts) throw err;

      // Exponential backoff with full jitter, so colliding requests spread out
      const delay = Math.random() * Math.min(200, 10 * 2 ** attempt);
      await new Promise((r) => setTimeout(r, delay));
    }
  }
}

await withRetry(() => purchaseOptimistic(1, 1));

Cap the attempts, and add jitter. Without jitter, colliding requests retry in lockstep and collide again.

When optimistic locking shines (and when it doesn't)

Good fit:

Low contention: collisions are occasional (many different products, each rarely bought concurrently)
Long "think time" between read and write, where holding a lock would be unacceptable. For example, a user editing a record in a UI: send the version with the form and reject a stale save (the HTTP version of this is ETag / If-Match)
You don't want to hold database locks or connections while waiting on slow things

Poor fit:

Hot rows. If 500 people want the same last 10 units, most attempts fail and retry, burning CPU and database round trips on work that gets thrown away. Throughput can collapse under heavy contention.
Anything where retrying isn't safe unless you've made the surrounding side effects idempotent (don't charge a card inside a retried block)

Comparing the approaches

	Atomic conditional `UPDATE`	Pessimistic (`FOR UPDATE`)	Optimistic (version + retry)
Locks held	Only during the statement	From the read until commit	None while reading
Handles complex logic between read and write	No (must fit in SQL)	Yes	Yes
Behavior under contention	Queues briefly, stays correct	Queues (serialized)	Retries (can thrash)
Failure mode	Out-of-stock (`rowCount 0`)	Waiting, timeouts, deadlocks	Conflicts, retry storms
Retry logic needed	No	Only for deadlock/timeout	Yes
Best for	Counters, stock decrements	High contention, multi-step logic	Low contention, long think time
Complexity	Lowest	Medium	Highest

A practical decision guide:

Can the rule be expressed as UPDATE ... SET x = x - n WHERE x >= n? Do that. It's the simplest and handles contention well.
Is the logic complex and contention likely (flash sales, popular SKUs)? Pessimistic, with short transactions and ordered locking.
Is contention rare, or is there a long gap between read and write (user edits, multi-step approvals)? Optimistic.

A word on isolation levels

PostgreSQL defaults to READ COMMITTED, and everything above is designed to be correct there. You can also raise the isolation level:

await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');

At REPEATABLE READ and SERIALIZABLE, PostgreSQL detects conflicting concurrent updates and aborts one transaction with 40001 rather than letting it proceed on stale data. That gives you correctness without hand-placed locks, but you must catch 40001 and retry the whole transaction. It's the same retry discipline as optimistic locking, enforced by the database. The trade-off is more aborted transactions under contention, so test with realistic concurrency before adopting it broadly.

When one row is too hot: scaling past a single counter

If a single product row is the center of a stampede, even the best-behaved locking serializes on it. Options when that becomes your bottleneck:

Reservations with expiry. Decrement stock into a reserved count when a user enters checkout, with a reserved_until timestamp. Release expired reservations with a background job. Also keeps the lock window short and avoids selling stock to people who never finish paying.
An append-only inventory ledger. Record +N / -N movements and derive stock by summing, with periodic roll-ups. Inserts don't contend on one row, and you get an audit trail for free.
Sharded counters. Split stock for a hot SKU across several rows (for example, 10 buckets) and have each purchase pick one bucket. It's more complex to read the total and to handle "bucket empty, try another."
Serialize in a queue. Route all purchases for a hot SKU through a single consumer (for example, a queue partitioned by product ID). The queue gives you ordering and backpressure, and the database sees no contention.
Advisory locks. pg_advisory_xact_lock(key) lets you lock a logical resource that isn't a single row. Useful, but it's still a lock, with the same hold-time and deadlock considerations.

Reach for these only when measurements say the simple approaches aren't enough.

Don't forget idempotency

Network retries and double-clicks mean the same purchase may arrive twice. Locking prevents overselling, but not double-ordering by one customer. Add an idempotency key with a unique constraint:

ALTER TABLE orders ADD COLUMN idempotency_key TEXT;
CREATE UNIQUE INDEX orders_idem_idx ON orders (idempotency_key);

const res = await tx.query(
  `INSERT INTO orders (product_id, qty, idempotency_key)
   VALUES ($1, $2, $3)
   ON CONFLICT (idempotency_key) DO NOTHING`,
  [productId, qty, key],
);
if (res.rowCount === 0) {
  // Already processed. Return the existing order instead of decrementing stock again.
}

Insert the order (and check the conflict) before decrementing stock, inside the same transaction, so a duplicate request rolls back cleanly without touching inventory.

Checklist

✅ Use relative updates (stock = stock - n), never write back a value computed from an earlier read
✅ Put the guard in the WHERE clause (AND stock >= n) and check rowCount
✅ Add CHECK (stock >= 0) as a last line of defense
✅ Keep locking transactions short, with no HTTP calls or slow work inside
✅ Lock multiple rows in a consistent order
✅ Retry on 40001 and 40P01, with a cap, backoff, and jitter
✅ Make order creation idempotent
✅ Keep a concurrency test (N buyers, M units) in CI

Key takeaways

Read-then-write races oversell inventory. The gap between the check and the update is the bug.
The simplest fix is usually an atomic conditional UPDATE, with the check and the decrement in one statement.
Pessimistic locking (FOR UPDATE) serializes access up front. Use it for complex logic under high contention, but keep transactions short and lock in a consistent order.
Optimistic locking (version column) detects conflicts at write time. It's great under low contention, and it needs a bounded retry loop with jitter.
Pick based on contention and logic complexity, not preference. Hot rows punish optimistic locking, and rare conflicts make pessimistic locking needless overhead.
Test with real concurrency. A test that fires 50 buyers at 10 units will catch what code review won't.

How do you handle hot SKUs in your system? Share your approach in the comments.

Further reading: the PostgreSQL docs on explicit locking and transaction isolation.

The N+1 Query Problem in Node.js & PostgreSQL: How to Detect and Eliminate It

subashthiruppathy — Mon, 12 Oct 2026 16:37:09 +0000

Here's a performance bug that never shows up in a slow-query log.

Every individual query is fast: 1 to 2 ms, perfectly indexed, textbook plans. Your database CPU is bored. And yet the endpoint takes 400 ms, gets slower as data grows, and under load it eats your connection pool alive.

That's the N+1 query problem: you run 1 query to fetch a list, then N more queries, one per item, to fetch something related. 100 items means 101 round trips to the database.

It's one of the most common performance problems in backend code, and it's easy to write by accident, especially with ORMs, GraphQL, and async/await, which make a loop of queries look completely innocent.

In this post we'll cover how to recognize it, how to prove it's happening, and the fixes, from plain SQL to DataLoader to ORM eager loading.

What N+1 looks like

Say we have a blog with authors, posts, and comments:

CREATE TABLE authors  (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE posts    (id BIGSERIAL PRIMARY KEY, author_id BIGINT NOT NULL REFERENCES authors(id),
                       title TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE comments (id BIGSERIAL PRIMARY KEY, post_id BIGINT NOT NULL REFERENCES posts(id),
                       body TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now());

CREATE INDEX posts_author_idx    ON posts (author_id);
CREATE INDEX comments_post_idx   ON comments (post_id, created_at DESC);

The endpoint: "list the 50 latest posts with their author's name."

// ❌ N+1: 1 query for posts + 50 queries for authors
app.get('/posts', async (req, res) => {
  const { rows: posts } = await pool.query(
    'SELECT id, author_id, title FROM posts ORDER BY created_at DESC LIMIT 50',
  );

  for (const post of posts) {
    const { rows } = await pool.query('SELECT name FROM authors WHERE id = $1', [post.author_id]);
    post.author = rows[0];
  }

  res.json(posts);
});

That's 51 queries. It reads naturally, it works in development with 5 rows, and it passes code review because no single line looks wrong.

Why it hurts more than you'd expect

Round-trip latency dominates. Each query pays network latency to the database, even if execution takes microseconds. If a round trip costs ~1 to 2 ms, 51 sequential queries cost 50 to 100 ms of pure waiting, versus one or two trips for the right approach. (Those numbers are illustrative. Measure your own environment, since the gap is much bigger when the database is in a different availability zone.)
It scales with data, not with traffic. The cost grows with N. A page that returns 20 items today returns 200 next quarter.
It's invisible to EXPLAIN. Each query's plan is perfect. The problem is the count, which per-query tools can't show.
It hammers the connection pool. A single request holding a connection through 50 sequential queries (or fanning out 50 in parallel with Promise.all) can monopolize your pool. N+1 is a frequent root cause of the pool starvation we've covered in earlier posts.

The hidden variants

The loop-with-a-query version is the easy one to spot. In real codebases N+1 hides in several disguises.

1. `Promise.all` over a map

// ❌ Still N+1. It's just concurrent N+1
const withAuthors = await Promise.all(
  posts.map(async (p) => ({
    ...p,
    author: (await pool.query('SELECT name FROM authors WHERE id = $1', [p.author_id])).rows[0],
  })),
);

This is faster in wall-clock terms (the queries overlap), but it still issues N queries, and now they all hit the pool at once. It trades latency for pool pressure.

2. GraphQL resolvers

const resolvers = {
  Query: { posts: () => db.query('SELECT * FROM posts ORDER BY created_at DESC LIMIT 50') },
  Post: {
    // ❌ Called once per post in the result
    author: (post) => db.query('SELECT * FROM authors WHERE id = $1', [post.author_id]),
  },
};

Field resolvers run per parent object, so a nested field becomes an N+1 automatically. This is the textbook GraphQL performance trap.

3. ORM lazy loading

Many ORMs let you access a relation as a property and load it on demand. Looping over a collection and touching post.author fires a query per iteration without a single explicit query() call in your code. If your ORM supports lazy relations, they are a common source of N+1.

4. Helper functions that hide the query

// Looks like a pure function call. Is actually a database round trip.
for (const order of orders) {
  order.total = await calculateTotal(order.id);   // runs SELECT ... inside
}

Any await of a data-access function inside a loop deserves suspicion.

Step 1: Detect it

You can't fix what you can't see. Here are four increasingly rigorous ways to catch N+1s.

A. Read the query log for repeating patterns

Temporarily log every statement in a dev or staging database:

-- Log all statements (very noisy; use on dev/staging, briefly)
ALTER SYSTEM SET log_min_duration_statement = 0;
SELECT pg_reload_conf();

Hit the endpoint once and look at the log. N+1 is unmistakable:

SELECT id, author_id, title FROM posts ORDER BY created_at DESC LIMIT 50
SELECT name FROM authors WHERE id = 7
SELECT name FROM authors WHERE id = 12
SELECT name FROM authors WHERE id = 7
SELECT name FROM authors WHERE id = 31
... (46 more)

Same statement shape, different parameter, repeated. (Remember to turn logging back down afterward: ALTER SYSTEM RESET log_min_duration_statement; and reload.)

B. `pg_stat_statements`: find it in production

The pg_stat_statements extension aggregates normalized queries across all traffic. N+1 shows up as a cheap query with an enormous call count:

SELECT
  calls,
  round(mean_exec_time::numeric, 3)  AS mean_ms,
  round(total_exec_time::numeric, 0) AS total_ms,
  left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

Look for a statement like SELECT name FROM authors WHERE id = $1 with millions of calls and a tiny mean time. That profile (low mean, huge calls, large total) is the N+1 fingerprint. (The extension must be in shared_preload_libraries and created with CREATE EXTENSION pg_stat_statements. Column names like total_exec_time apply to PostgreSQL 13+.)

C. Count queries per request in your app

Use AsyncLocalStorage to attribute queries to the request that issued them, then log or alert when the count is suspicious:

// query-counter.ts
import { AsyncLocalStorage } from 'node:async_hooks';
import type { Pool } from 'pg';

interface Counter { count: number; queries: string[] }
export const als = new AsyncLocalStorage<Counter>();

export function instrument(pool: Pool): Pool {
  const original = pool.query.bind(pool) as (...args: any[]) => Promise<any>;
  (pool as any).query = (...args: any[]) => {
    const store = als.getStore();
    if (store) {
      store.count++;
      const text = typeof args[0] === 'string' ? args[0] : args[0]?.text;
      store.queries.push(text);
    }
    return original(...args);
  };
  return pool;
}

// middleware
app.use((req, res, next) => {
  const counter: Counter = { count: 0, queries: [] };
  als.run(counter, () => {
    res.on('finish', () => {
      metrics.histogram('http.db_queries', counter.count, { route: req.route?.path ?? req.path });
      if (counter.count > 30) {
        logger.warn({ route: req.path, count: counter.count, sample: counter.queries.slice(0, 5) },
          'suspiciously many queries in one request');
      }
    });
    next();
  });
});

Note that this wraps pool.query. Queries run on a client from pool.connect() (for example, inside transactions) need the same wrapper on client.query. Once you graph "DB queries per request" per route, N+1 routes stand out immediately: they're the ones where the number tracks the size of the response.

D. Assert query counts in tests (the best long-term defense)

Detection tells you about today's bugs. A test prevents tomorrow's:

// posts.test.ts
import { it, expect } from 'vitest';
import { als } from './query-counter';

async function countQueries<T>(fn: () => Promise<T>) {
  const counter = { count: 0, queries: [] as string[] };
  const result = await als.run(counter, fn);
  return { result, ...counter };
}

it('lists posts with authors without N+1', async () => {
  await seedPosts(50); // 50 posts, many authors

  const { count } = await countQueries(() => getLatestPosts(50));

  expect(count).toBeLessThanOrEqual(2); // fixed number, independent of N
});

The crucial design choice is asserting a constant bound, and testing with enough rows that an N+1 would blow it. A test with 2 posts would pass an N+1 implementation with 3 queries.

Step 2: Fix it

The principle behind every fix is the same: replace N queries with a constant number of queries, regardless of how many rows you're processing.

Fix 1: Use a JOIN

When you need related data for every row, let the database join it:

// ✅ 1 query, regardless of N
const { rows } = await pool.query(`
  SELECT p.id, p.title, p.created_at,
         a.id AS author_id, a.name AS author_name
  FROM posts p
  JOIN authors a ON a.id = p.author_id
  ORDER BY p.created_at DESC
  LIMIT 50
`);

const posts = rows.map((r) => ({
  id: r.id,
  title: r.title,
  createdAt: r.created_at,
  author: { id: r.author_id, name: r.author_name },
}));

For many-to-one relations (post → author), a join is almost always the best answer. Make sure the join columns are indexed (posts.author_id above).

Fix 2: Batch with `= ANY($1)` (the "WHERE IN" pattern)

When a join is awkward (data lives in another service, or you want to keep queries separate), fetch the parents, collect their IDs, and load all children in one query:

// ✅ 2 queries total
const { rows: posts } = await pool.query(
  'SELECT id, author_id, title FROM posts ORDER BY created_at DESC LIMIT 50',
);

const authorIds = [...new Set(posts.map((p) => p.author_id))];

const { rows: authors } = await pool.query(
  'SELECT id, name FROM authors WHERE id = ANY($1::bigint[])',
  [authorIds],
);

const authorById = new Map(authors.map((a) => [a.id, a]));
const result = posts.map((p) => ({ ...p, author: authorById.get(p.author_id) }));

Details that matter:

Pass the array as a single parameter and use = ANY($1::bigint[]). node-postgres converts a JS array into a Postgres array, so you never build IN (...) strings by hand, and it stays safe from SQL injection.
Deduplicate IDs with a Set so you don't fetch the same author repeatedly.
Index the lookup column.
For truly huge ID lists (tens of thousands), process them in chunks.

This "fetch parents, then fetch children by ID list, then stitch in memory" approach is exactly what many ORMs do under the hood for eager loading.

Fix 3: One-to-many with `json_agg`

One-to-many relations (post → comments) are trickier, because a naive JOIN repeats the parent columns on every child row. You can aggregate children into JSON in the same query:

const { rows } = await pool.query(`
  SELECT p.id, p.title,
         COALESCE(
           json_agg(json_build_object('id', c.id, 'body', c.body) ORDER BY c.created_at DESC)
             FILTER (WHERE c.id IS NOT NULL),
           '[]'
         ) AS comments
  FROM (
    SELECT * FROM posts ORDER BY created_at DESC LIMIT 50
  ) p
  LEFT JOIN comments c ON c.post_id = p.id
  GROUP BY p.id, p.title, p.created_at
  ORDER BY p.created_at DESC
`);
// rows[i].comments is already a JS array (pg parses json columns)

Notice the subquery with LIMIT 50 on posts first, then the join. If you join first and LIMIT afterward, you're limiting rows, not posts, and you'll get half-loaded posts or wasted work.

Fix 4: "Latest 3 comments per post" with `LATERAL`

A very common requirement is a limited number of children per parent. json_agg can't limit per group on its own. LATERAL handles it cleanly:

SELECT p.id, p.title, lc.comments
FROM (SELECT * FROM posts ORDER BY created_at DESC LIMIT 50) p
LEFT JOIN LATERAL (
  SELECT COALESCE(json_agg(t), '[]') AS comments
  FROM (
    SELECT c.id, c.body
    FROM comments c
    WHERE c.post_id = p.id
    ORDER BY c.created_at DESC
    LIMIT 3
  ) t
) lc ON true
ORDER BY p.created_at DESC;

The LATERAL subquery runs per post inside the database (fast, using the comments (post_id, created_at DESC) index), with zero extra application round trips. Alternatively, you can use row_number() OVER (PARTITION BY post_id ORDER BY created_at DESC) in a batched child query and filter rn <= 3.

Fix 5: DataLoader for GraphQL (and other resolver-style code)

In GraphQL, you can't easily restructure around joins because resolvers are independent. DataLoader solves this by batching all .load(key) calls made in the same tick of the event loop into one function call:

import DataLoader from 'dataloader';

function createLoaders() {
  return {
    authorById: new DataLoader<number, Author>(async (ids) => {
      const { rows } = await pool.query(
        'SELECT id, name FROM authors WHERE id = ANY($1::bigint[])',
        [ids as number[]],
      );
      const byId = new Map(rows.map((r) => [Number(r.id), r]));
      // MUST return results in the same order and length as `ids`
      return ids.map((id) => byId.get(id) ?? new Error(`Author ${id} not found`));
    }),
  };
}

// Create per request, so the cache never leaks across users
const server = new ApolloServer({ typeDefs, resolvers });
// context: () => ({ loaders: createLoaders(), user })

const resolvers = {
  Post: {
    author: (post, _args, { loaders }) => loaders.authorById.load(post.author_id),
  },
};

Fifty resolvers each call loaders.authorById.load(...); DataLoader collects them and invokes your batch function once with all the IDs. That turns 50 queries into 1.

Rules for DataLoader:

One instance per request, never a global. It caches by key, and a global cache would serve stale or cross-user data.
The batch function must return an array the same length and order as the keys. Missing keys should map to null or an Error, not be skipped. A mismatch is the most common DataLoader bug.
DataLoader batches calls made within the same tick, so the load calls must happen before an intervening await pushes them into different ticks. In practice, resolver trees satisfy this naturally.

Fix 6: ORM eager loading

If you use an ORM, the fix is usually one option away. The exact API varies, so check your ORM's docs for the version you use:

// Prisma: load relations up front
const posts = await prisma.post.findMany({
  take: 50,
  orderBy: { createdAt: 'desc' },
  include: { author: true },
});

// Sequelize: include => JOIN
const posts = await Post.findAll({ limit: 50, order: [['createdAt', 'DESC']], include: [Author] });

// TypeORM: join and select the relation
const posts = await dataSource
  .getRepository(Post)
  .createQueryBuilder('post')
  .leftJoinAndSelect('post.author', 'author')
  .orderBy('post.createdAt', 'DESC')
  .take(50)
  .getMany();

Different ORMs use different strategies (a SQL join versus a second batched IN query), and some let you choose. Whichever it is, verify with the query log or your query counter rather than assuming. "I used include" has produced plenty of surprise N+1s, especially with nested relations or relations accessed through a different path than the one you eager-loaded.

Pitfalls when you fix N+1

Eliminating N+1 can introduce its own problems if you're not careful.

Row explosion from multiple one-to-many joins

-- ❌ 50 posts × 20 comments × 10 tags = 10,000 rows for 50 posts
SELECT ... FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN post_tags t ON t.post_id = p.id

Joining two independent one-to-many relations produces a Cartesian product per parent. Use json_agg in separate lateral subqueries, or load each relation with its own batched ANY($1) query and stitch in memory. Several well-shaped queries beat one giant join.

Pagination with joins

Apply LIMIT/OFFSET (or, better, keyset pagination) to the parent set in a subquery, as in Fix 3. Never paginate after a one-to-many join.

Over-fetching

Batching is not a license to SELECT * everything. If you only need authors.name, select just that. Wide rows multiplied by many results add up over the wire.

Don't batch what shouldn't be batched

One query with a 50,000-element ANY array, or a join that drags back millions of rows, is not an improvement. Cap batch sizes and chunk large inputs.

Premature "optimization"

N+1 with N = 3 and a sub-millisecond query is not worth rewriting for. The problem matters when N is unbounded or large, when queries are slow, or when latency to the database is high. Prioritize with data: use the per-route query counts and pg_stat_statements rather than hunting by code review.

Putting guardrails in place

Graph "DB queries per request" by route. It should be flat as response size grows.
Assert constant query counts in integration tests, using enough seed rows to expose a loop.
Review pg_stat_statements periodically for cheap queries with huge call counts.
Make DataLoader (or an equivalent batching pattern) the default in GraphQL APIs, so the batched path is the easy path.
Treat await inside loops as a code review smell, especially await db.something() inside for or .map.
Limit pool damage while you fix things: set connectionTimeoutMillis, and cap concurrency if you must fan out.

Before and after

	Naive loop	JOIN / batched `ANY`
Queries for 50 posts	51	1 or 2
Queries for 500 posts	501	1 or 2
Scales with	Number of rows (N)	Roughly constant
Visible in slow query log?	No (each query is fast)	n/a
Pool pressure	High	Low
Visible in `pg_stat_statements`?	Yes: huge `calls`	Normal

Key takeaways

N+1 means 1 query for a list plus N queries for related data. The pattern repeats because the loop looks innocent.
Each query is fast, so slow-query tools miss it. Detect it by counting queries per request, scanning logs for repeated shapes, and checking pg_stat_statements for high-calls queries.
Promise.all doesn't fix it. It just makes the N queries concurrent and puts pressure on the connection pool.
Replace N queries with a constant number: JOINs for many-to-one, = ANY($1) batching, json_agg or LATERAL for one-to-many, DataLoader for GraphQL, eager loading for ORMs.
Beware row explosion and pagination. Limit parents in a subquery first, and don't join multiple independent one-to-many relations into one result.
Lock it in with tests that assert a fixed query count against realistic data sizes.

Where have you found the sneakiest N+1 in your own codebase? Let me know in the comments.

Further reading: the PostgreSQL docs on pg_stat_statements, the DataLoader repository, and your ORM's documentation on eager loading and query logging.

Diagnosing and Fixing PostgreSQL Connection Pool Starvation in Node.js

subashthiruppathy — Sat, 10 Oct 2026 17:28:57 +0000

Here's a production incident that looks like a mystery until you've seen it once.

Requests start timing out. Latency graphs go vertical.
Node CPU is low. Memory is fine.
Postgres CPU is low. Queries that do run are fast.
Restarting the app "fixes" it... for a few hours.

Nothing is slow, yet everything is stuck. The culprit is usually connection pool starvation: all connections in your pool are checked out, and every new request is waiting in line for one that never comes back.

This post walks through how to confirm starvation, find the cause, and fix it, using node-postgres (pg), though the ideas apply to any pooled client.

What a pool actually does

Opening a Postgres connection is expensive (TCP, TLS, auth, backend process startup), so apps keep a small pool of open connections and lend them out.

 request ──▶ pool.connect() ──▶ [ conn1 conn2 conn3 ... connN ]  ──▶ Postgres
                  │                    (max = N)
                  └── if all N are busy: WAIT in queue

When every connection is checked out, new callers join a wait queue. If connections come back quickly, you barely notice. If they come back slowly, or never, the queue grows without bound and every request that needs the database hangs behind it.

Key point: starvation isn't about the database being slow. It's about connections being held too long or never returned.

Defaults worth knowing in pg:

Option	Default	Meaning
`max`	`10`	Maximum connections in the pool
`connectionTimeoutMillis`	`0`	No timeout waiting for a connection (waits forever!)
`idleTimeoutMillis`	`10000`	Close idle connections after 10s

That default of 0 is why starvation presents as a hang rather than an error. Fix that first (see below).

Step 1: Confirm it's starvation

Look at the pool's own counters

pg.Pool exposes three numbers:

pool.totalCount: connections currently open
pool.idleCount: open and available
pool.waitingCount: callers queued waiting for a connection

import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
});

setInterval(() => {
  console.log(JSON.stringify({
    msg: 'pg.pool',
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  }));
}, 5_000).unref();

The signature of starvation:

{"total":10,"idle":0,"waiting":47}
{"total":10,"idle":0,"waiting":112}
{"total":10,"idle":0,"waiting":240}

idle: 0 with a growing waiting means demand exceeds supply, and it's not draining. A healthy busy pool shows waiting bouncing near zero.

Look at what Postgres thinks those connections are doing

Now ask the database what the pool's connections are up to:

SELECT
  pid,
  state,
  now() - xact_start    AS xact_age,
  now() - state_change  AS in_state_for,
  wait_event_type,
  wait_event,
  left(query, 100)      AS last_query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

How to read the state column:

`state`	Meaning	What it implies
`active`	Running a query right now	Slow queries or lock contention
`idle`	Connected, nothing in progress	Healthy (or an app-side leak, if the pool says it's checked out)
`idle in transaction`	`BEGIN` was sent, no `COMMIT`/`ROLLBACK` yet	Prime suspect. The app is holding a transaction open while doing something else

The smoking gun is a pile of connections that are idle in transaction with large xact_age. Postgres isn't busy; your app is sitting on connections and not using them.

Compare the two views:

Pool says 10 checked out, Postgres shows most as idle (not in a transaction) → leaked clients (checked out, never released).
Pool says 10 checked out, Postgres shows idle in transaction → transactions held open across non-DB work or never closed.
Pool says 10 checked out, Postgres shows active with long runtimes → slow queries or lock waits; check wait_event_type = 'Lock'.
Pool isn't maxed out but you still see errors → look at Postgres-side max_connections (see "Sizing" below).

Cause 1: Leaked clients (forgot to `release()`)

The classic. You check out a client manually and some code path never releases it.

// ❌ Leaks the client if the query throws
async function getUser(id: string) {
  const client = await pool.connect();
  const { rows } = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  client.release();
  return rows[0];
}

If query throws (bad SQL, timeout, constraint error), release() is never reached. Do that ten times and your pool of ten is permanently empty. Nothing crashes. The app just slowly seizes up.

Fix: `try / finally`, always

// ✅ Always released
async function getUser(id: string) {
  const client = await pool.connect();
  try {
    const { rows } = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return rows[0];
  } finally {
    client.release();
  }
}

Better fix: don't check out manually for single statements

pool.query() checks out a client, runs the query, and releases it for you, even on errors:

// ✅ Simplest and safest for a single statement
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Only use pool.connect() when you genuinely need multiple statements on the same connection (transactions, session settings, advisory locks, cursors).

💡 If a client is in a bad state (for example, after a connection error), call client.release(true). Passing a truthy argument tells the pool to destroy that client instead of returning it to the pool.

Cause 2: Transactions that leak or linger

Transactions are leaks with extra steps. If BEGIN succeeds and the code then throws before COMMIT/ROLLBACK, the client goes back to the pool (or never does) while still inside a transaction, holding locks.

Don't hand-write transaction plumbing at every call site. Write one helper and use it everywhere. Note the release(err) detail: if ROLLBACK itself fails, the connection is likely broken, so we destroy it instead of returning it to the pool.

// db.ts
import type { PoolClient } from 'pg';

export async function withTransaction<T>(
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  let destroy: Error | undefined;
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    try {
      await client.query('ROLLBACK');
    } catch (rollbackErr) {
      destroy = rollbackErr as Error; // broken connection; don't return it to the pool
    }
    throw err;
  } finally {
    client.release(destroy);
  }
}

Usage:

await withTransaction(async (tx) => {
  await tx.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amt, from]);
  await tx.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amt, to]);
});

Now there is exactly one place where BEGIN, COMMIT, ROLLBACK, and release live, and it's correct.

Cause 3: Holding a connection while doing non-database work

This one is subtle and extremely common. The code is "correct" (everything is released) but connections are held far too long:

// ❌ The connection is held for the entire duration of the HTTP call
await withTransaction(async (tx) => {
  const { rows } = await tx.query('SELECT * FROM orders WHERE id = $1 FOR UPDATE', [id]);
  const quote = await fetch('https://shipping-partner.example/quote', { /* ... */ }); // 200ms-30s!
  await tx.query('UPDATE orders SET shipping_cost = $1 WHERE id = $2', [quote.cost, id]);
});

If the partner API gets slow, each in-flight request now pins a connection (and a row lock) for the length of that call. With a pool of 10 and a partner that takes 5 seconds, your maximum throughput is 2 requests per second, and Postgres shows idle in transaction everywhere.

Fix: do slow external work outside the transaction

// ✅ Fetch first, then open a short transaction
const quote = await fetchShippingQuote(id);               // no connection held

await withTransaction(async (tx) => {
  await tx.query('SELECT 1 FROM orders WHERE id = $1 FOR UPDATE', [id]);
  await tx.query('UPDATE orders SET shipping_cost = $1 WHERE id = $2', [quote.cost, id]);
});

Rule of thumb: a connection should be checked out only for as long as you're actually talking to the database. No HTTP calls, no file I/O, no await sleep, no CPU-heavy work, and no message-queue publishes inside a transaction. If you need to publish an event atomically with a DB change, use the outbox pattern rather than doing both in one long transaction.

Cause 4: Nested acquisition (self-inflicted deadlock)

This is the nastiest one because it works fine in dev and deadlocks under load.

// ❌ Holds connection A while waiting for connection B
async function createOrder(input) {
  return withTransaction(async (tx) => {
    const order = await tx.query('INSERT INTO orders ... RETURNING *', [...]);
    const customer = await getCustomer(input.customerId); // calls pool.query() => needs ANOTHER connection
    // ...
  });
}

With a pool of 10: if 10 requests enter createOrder simultaneously, each holds one connection and each now waits for a second connection that doesn't exist. Every connection is held by a request waiting on the pool. Nobody can make progress, ever. It's a textbook resource deadlock, and the pool's wait queue never drains.

Fix options

Pass the client down so inner code reuses the same connection:

async function getCustomer(id: string, db: Pick<Pool, 'query'> = pool) {
  const { rows } = await db.query('SELECT * FROM customers WHERE id = $1', [id]);
  return rows[0];
}

await withTransaction(async (tx) => {
  const customer = await getCustomer(input.customerId, tx); // same connection
});

Do the read before the transaction, if it doesn't need to be transactional.
Never "fix" it by just raising max. You've only raised the number of concurrent requests needed to trigger it.

To catch this in testing, set max: 1 or max: 2 in your test config. Any nested acquisition then deadlocks immediately instead of in production at peak.

Cause 5: Fan-out with `Promise.all`

// ❌ 500 concurrent queries against a pool of 10
const users = await Promise.all(ids.map((id) => pool.query('SELECT ... WHERE id = $1', [id])));

This doesn't leak anything, but one request can monopolize the entire pool, starving every other request while it works through the queue. It's also an N+1 query pattern in disguise.

Fixes

Batch it into one query:

const { rows } = await pool.query('SELECT * FROM users WHERE id = ANY($1::uuid[])', [ids]);

If you truly need many queries, limit concurrency (for example with p-limit) to a small slice of the pool:

import pLimit from 'p-limit';
const limit = pLimit(3); // leave headroom for other requests
const results = await Promise.all(ids.map((id) => limit(() => fetchOne(id))));

Cause 6: Slow queries and lock waits

Sometimes the connections really are busy. If pg_stat_activity shows active queries running for seconds, or wait_event_type = 'Lock', then connections are being held by work that's legitimately slow.

Find the long runners and what blocks them:

-- Longest-running active queries
SELECT pid, now() - query_start AS runtime, wait_event_type, wait_event, left(query, 120)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY runtime DESC
LIMIT 10;

-- Who is blocking whom
SELECT blocked.pid  AS blocked_pid,
       blocking.pid AS blocking_pid,
       left(blocked.query, 80)  AS blocked_query,
       left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Then do the usual database work: add the missing index, fix the query plan (EXPLAIN (ANALYZE, BUFFERS)), shorten lock-holding transactions, and avoid long SELECT ... FOR UPDATE sessions. A single forgotten idle in transaction connection holding a row lock can block dozens of others, which then pile up as active and further drain your pool.

Set safety nets so the next bug can't take you down

You'll never prevent every leak by code review alone. Add guardrails that turn hangs into fast, loud errors.

Client-side: fail instead of waiting forever

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,  // error if no connection within 5s (default is "wait forever")
  idleTimeoutMillis: 30_000,
  query_timeout: 15_000,           // client-side per-query timeout
  statement_timeout: 15_000,       // server-side: Postgres cancels statements over 15s
  idle_in_transaction_session_timeout: 10_000, // server-side: kills sessions stuck idle in a transaction
});

pool.on('error', (err) => {
  // Emitted when an idle client errors (e.g. server restarted). Without a handler, this crashes Node.
  logger.error({ err }, 'pg idle client error');
});

What each does:

connectionTimeoutMillis converts a silent hang into a timeout exceeded when trying to connect error you can alert on and return a 503 for, so load sheds instead of piling up.
statement_timeout stops a single runaway query from pinning a connection indefinitely.
idle_in_transaction_session_timeout is the strongest protection against Causes 2 and 3: Postgres terminates any session that sits idle in transaction too long. The connection is torn down, the transaction rolled back, and locks released.
Consider lock_timeout too, so statements give up rather than queue indefinitely behind a lock.

You can also set these at the database or role level (ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10s') so they apply to every client, not just this app.

⚠️ These timeouts are a safety net, not a fix. If idle_in_transaction_session_timeout is firing, you have a bug to find, so log and alert when it happens.

Shed load deliberately

Return a fast 503 with Retry-After when the pool is saturated, rather than letting requests queue until they time out at the load balancer:

app.use((req, res, next) => {
  if (pool.waitingCount > 50) {
    return res.status(503).set('Retry-After', '2').json({ error: 'busy' });
  }
  next();
});

Sizing the pool (and why "just increase max" is usually wrong)

Raising max feels like the obvious fix, and it's tempting because it makes the symptom disappear briefly. But:

It doesn't fix leaks. A leak drains any pool size. A bigger pool only delays the outage.
Postgres has a hard cap. Check SHOW max_connections;. Every connection is a separate backend process with memory overhead. Total connections across all app instances must fit:

instances × pool.max   +   other clients (migrations, cron, BI tools, admin)   ≤   max_connections

Ten pods with max: 20 is 200 connections before anything else connects.

More connections can mean lower throughput. Once concurrent active queries exceed what the database's CPU and I/O can actually run in parallel, extra connections just add contention and context switching. For many workloads, a pool in the tens (not hundreds) per database performs better than a huge one.

Practical approach: start modest, measure waitingCount and query times under realistic load, and increase only if you see waiting without leaks or long holds, and only while Postgres still has headroom.

When you do need many app connections: PgBouncer

If you have many app instances or serverless functions, put a pooler like PgBouncer between them and Postgres. In transaction pooling mode, a server connection is assigned only for the duration of each transaction, so thousands of client connections can share a few dozen real ones.

Trade-offs to know about:

Session-level state (SET, session advisory locks, LISTEN/NOTIFY, temp tables) doesn't work reliably across transactions in transaction mode.
Named prepared statements can break depending on your PgBouncer version and config. Check its docs and your client's settings.
It doesn't fix leaks or long transactions. It just changes where the scarce resource lives.

Serverless note

Each function instance often creates its own pool. Use max: 1 (or a very small number), reuse the pool across invocations by declaring it at module scope, and front the database with a pooler. Otherwise a traffic spike creates hundreds of instances × max connections and you'll hit too many clients already.

Instrumentation: make starvation visible before it's an outage

Export pool stats as metrics and alert on them:

import client from 'prom-client';

new client.Gauge({
  name: 'pg_pool_total', help: 'Open connections',
  collect() { this.set(pool.totalCount); },
});
new client.Gauge({
  name: 'pg_pool_idle', help: 'Idle connections',
  collect() { this.set(pool.idleCount); },
});
new client.Gauge({
  name: 'pg_pool_waiting', help: 'Callers waiting for a connection',
  collect() { this.set(pool.waitingCount); },
});

// Time spent waiting to acquire (your best early-warning signal)
const acquireTime = new client.Histogram({
  name: 'pg_pool_acquire_seconds', help: 'Time to acquire a connection',
  buckets: [0.001, 0.005, 0.02, 0.1, 0.5, 1, 5],
});

export async function timedConnect() {
  const end = acquireTime.startTimer();
  try { return await pool.connect(); } finally { end(); }
}

Alert on:

pg_pool_waiting > 0 sustained for more than a few seconds
p95 of pg_pool_acquire_seconds rising (it climbs before requests start failing)
Connection timeout errors from connectionTimeoutMillis
Postgres-side: count of idle in transaction sessions older than N seconds, and total connections as a % of max_connections

Reproduce it on purpose (so you trust your fix)

Don't wait for production to prove a fix works. Cause starvation in a test:

// starvation.test.ts
import { Pool } from 'pg';
import { it, expect } from 'vitest';

it('surfaces a leak as a fast error, not a hang', async () => {
  const pool = new Pool({ max: 2, connectionTimeoutMillis: 500 });

  // Leak two clients on purpose
  await pool.connect();
  await pool.connect();

  await expect(pool.query('SELECT 1')).rejects.toThrow(/timeout/i);
  expect(pool.waitingCount).toBeGreaterThanOrEqual(0);
  await pool.end().catch(() => {});
});

Run your integration tests with a tiny pool (max: 2) to flush out nested acquisition. And load test with a tool like autocannon or k6 while watching pg_stat_activity and your pool metrics to see how the system behaves at saturation, not just below it.

Diagnosis cheat sheet

What you see	Likely cause	Fix
Pool `idle=0, waiting` growing; PG connections mostly `idle`	Leaked clients	`try/finally`, prefer `pool.query`
PG connections `idle in transaction` with big `xact_age`	Missing commit/rollback, or slow non-DB work inside a transaction	`withTransaction` helper; move I/O outside; `idle_in_transaction_session_timeout`
Deadlock only under concurrency; works with low traffic	Nested acquisition	Pass the client down; test with `max: 1`
One endpoint tanks everything	`Promise.all` fan-out / N+1	Batch queries; limit concurrency
PG connections `active` for seconds; `wait_event_type = Lock`	Slow queries or lock contention	Indexes, query tuning, shorter transactions
`too many clients already`	`instances × max` exceeds `max_connections`	Smaller pools, PgBouncer
Requests hang forever with no error	`connectionTimeoutMillis` is `0`	Set it; shed load with 503s

Key takeaways

Starvation looks like a hang with idle CPUs. Check pool.waitingCount and pg_stat_activity before touching anything.
Compare the pool's view with Postgres's view. idle vs idle in transaction vs active tells you which class of bug you have.
Always release. Use try/finally, prefer pool.query, and centralize transactions in one helper.
Hold connections only while talking to the database. No HTTP calls or other slow work inside transactions.
Never acquire a second connection while holding one. Pass the client down.
Set timeouts (connectionTimeoutMillis, statement_timeout, idle_in_transaction_session_timeout) so failures are fast and loud.
Don't fix leaks by growing the pool. Size it against max_connections, and use PgBouncer when you need many clients.
Instrument the pool, so you see the queue growing long before users do.

Have you been bitten by a pool problem I didn't cover? Share your war story in the comments.

Further reading: the node-postgres pooling docs, the PostgreSQL docs on pg_stat_activity and client connection timeouts, and the PgBouncer docs.

Building an In-Memory Semantic Cache for LLM Endpoints with Node.js and pgvector

subashthiruppathy — Fri, 09 Oct 2026 11:25:31 +0000

If you run an LLM-backed endpoint (a support bot, a docs assistant, a "chat with your data" feature), you've probably noticed something in your logs: people ask the same questions over and over, just worded differently.

"How do I reset my password?"
"I forgot my password, what do I do?"
"password reset steps"

A traditional cache keyed on the exact prompt string treats these as three unrelated requests, so you pay for three LLM calls, three times the latency, and three times the tokens.

A semantic cache fixes this. Instead of matching strings, it matches meaning: embed the incoming prompt, find the nearest previously answered prompt, and if it's close enough, return the stored answer without ever calling the LLM.

In this post we'll build one in Node.js and TypeScript with a two-tier design:

L1: in-process memory. Microsecond lookups for exact repeats.
L2: Postgres + pgvector. Shared across instances, durable, with similarity search via an HNSW index.

We'll also cover the part most tutorials skip: when a cache hit is actually a wrong answer, and how to design around it.

How a semantic cache works

Request ─▶ normalize ─▶ L1 exact lookup ──hit──▶ return
                              │ miss
                              ▼
                        embed(prompt)            ◀── ~50-300 ms, small cost
                              │
                              ▼
                  L2 nearest neighbor (pgvector)
                              │
              similarity ≥ threshold? ──yes──▶ return cached answer (+ promote to L1)
                              │ no
                              ▼
                        call the LLM
                              │
                              ▼
               store in L1 + L2 (embedding + answer) ─▶ return

The economics work because embedding a prompt is much cheaper and faster than generating a completion. A miss costs you one extra embedding call plus a DB query. A hit saves a full generation. If your hit rate is decent, you come out well ahead on both cost and p50 latency.

Real numbers depend entirely on your traffic, so measure them (we'll add metrics later) rather than trusting anyone's blog post, including this one.

Step 1: Set up pgvector

Enable the extension and create the cache table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE llm_cache (
  id            BIGSERIAL PRIMARY KEY,
  scope         TEXT        NOT NULL,   -- tenant + model + prompt version (see below)
  prompt_hash   TEXT        NOT NULL,   -- hash of normalized prompt, for exact match
  prompt        TEXT        NOT NULL,   -- original text, useful for debugging and audits
  embedding     vector(1536) NOT NULL,
  response      JSONB       NOT NULL,
  hit_count     INTEGER     NOT NULL DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at    TIMESTAMPTZ NOT NULL
);

-- Exact-match lookups
CREATE UNIQUE INDEX llm_cache_scope_hash_idx ON llm_cache (scope, prompt_hash);

-- Approximate nearest neighbor search using cosine distance
CREATE INDEX llm_cache_embedding_idx
  ON llm_cache USING hnsw (embedding vector_cosine_ops);

-- For cleanup jobs
CREATE INDEX llm_cache_expires_idx ON llm_cache (expires_at);

Notes:

vector(1536) must match your embedding model's output dimension. Check your model's docs, and keep it consistent. Mixing models in one column breaks similarity.
HNSW gives fast approximate search and doesn't require training data first (unlike IVFFlat). It needs pgvector 0.5.0 or newer.
<=> is pgvector's cosine distance operator. Cosine similarity is 1 - distance.

The `scope` column is the most important design decision

A cached answer is only valid for the context it was generated in. Put everything that changes the correct answer into scope:

function buildScope(opts: {
  tenantId: string;
  model: string;
  promptVersion: string;     // bump when you change your system prompt
  toolsetVersion?: string;
}) {
  return [opts.tenantId, opts.model, opts.promptVersion, opts.toolsetVersion ?? '-'].join('|');
}

Without this, tenant A's answer can be served to tenant B (a data leak), or a stale answer generated by an old system prompt keeps being served after you "fixed" the bot. Bumping promptVersion is your cheap, instant "invalidate everything" button.

Step 2: The cache class

Install dependencies:

npm i pg pgvector lru-cache

Here's the core implementation:

// semantic-cache.ts
import crypto from 'node:crypto';
import { Pool } from 'pg';
import pgvector from 'pgvector/pg';
import { LRUCache } from 'lru-cache';

export interface CachedResponse {
  text: string;
  model: string;
  usage?: { inputTokens: number; outputTokens: number };
}

export interface CacheLookup {
  hit: boolean;
  tier?: 'l1' | 'l2';
  similarity?: number;
  response?: CachedResponse;
}

export class SemanticCache {
  // L1: exact-match, in-process. Key = scope + prompt hash.
  private l1 = new LRUCache<string, CachedResponse>({
    max: 5_000,
    ttl: 10 * 60 * 1000, // 10 minutes
  });

  // Single-flight: if two identical requests arrive at once, only one calls the LLM
  private inflight = new Map<string, Promise<CachedResponse>>();

  constructor(
    private pool: Pool,
    private embed: (text: string) => Promise<number[]>,
    private opts = {
      similarityThreshold: 0.92,
      ttlSeconds: 24 * 60 * 60,
    },
  ) {}

  static normalize(prompt: string): string {
    return prompt.trim().toLowerCase().replace(/\s+/g, ' ');
  }

  private hash(text: string) {
    return crypto.createHash('sha256').update(text).digest('hex');
  }

  async lookup(scope: string, prompt: string): Promise<CacheLookup & { embedding?: number[] }> {
    const normalized = SemanticCache.normalize(prompt);
    const promptHash = this.hash(normalized);
    const l1Key = `${scope}:${promptHash}`;

    // --- L1: exact match in memory ---
    const l1Hit = this.l1.get(l1Key);
    if (l1Hit) return { hit: true, tier: 'l1', similarity: 1, response: l1Hit };

    // --- L2a: exact match in Postgres (no embedding needed) ---
    const exact = await this.pool.query(
      `SELECT response FROM llm_cache
       WHERE scope = $1 AND prompt_hash = $2 AND expires_at > now()`,
      [scope, promptHash],
    );
    if (exact.rows[0]) {
      this.l1.set(l1Key, exact.rows[0].response);
      return { hit: true, tier: 'l2', similarity: 1, response: exact.rows[0].response };
    }

    // --- L2b: semantic match via pgvector ---
    const embedding = await this.embed(normalized);
    const { rows } = await this.pool.query(
      `SELECT id, response, 1 - (embedding <=> $2) AS similarity
       FROM llm_cache
       WHERE scope = $1 AND expires_at > now()
       ORDER BY embedding <=> $2
       LIMIT 1`,
      [scope, pgvector.toSql(embedding)],
    );

    const best = rows[0];
    if (best && best.similarity >= this.opts.similarityThreshold) {
      // Fire-and-forget stats update; don't block the response
      this.pool
        .query('UPDATE llm_cache SET hit_count = hit_count + 1 WHERE id = $1', [best.id])
        .catch(() => {});
      this.l1.set(l1Key, best.response); // promote so the exact phrasing is fast next time
      return { hit: true, tier: 'l2', similarity: best.similarity, response: best.response, embedding };
    }

    return { hit: false, embedding };
  }

  async store(
    scope: string,
    prompt: string,
    response: CachedResponse,
    embedding?: number[],
  ): Promise<void> {
    const normalized = SemanticCache.normalize(prompt);
    const promptHash = this.hash(normalized);
    this.l1.set(`${scope}:${promptHash}`, response);

    const vec = embedding ?? (await this.embed(normalized));
    await this.pool.query(
      `INSERT INTO llm_cache (scope, prompt_hash, prompt, embedding, response, expires_at)
       VALUES ($1, $2, $3, $4, $5, now() + ($6 || ' seconds')::interval)
       ON CONFLICT (scope, prompt_hash)
       DO UPDATE SET response = EXCLUDED.response,
                     embedding = EXCLUDED.embedding,
                     expires_at = EXCLUDED.expires_at`,
      [scope, promptHash, prompt, pgvector.toSql(vec), response, String(this.opts.ttlSeconds)],
    );
  }

  /** Wrap an LLM call with cache lookup, single-flight, and write-back. */
  async getOrCompute(
    scope: string,
    prompt: string,
    compute: () => Promise<CachedResponse>,
  ): Promise<CacheLookup & { response: CachedResponse }> {
    const found = await this.lookup(scope, prompt);
    if (found.hit) return found as CacheLookup & { response: CachedResponse };

    const key = `${scope}:${this.hash(SemanticCache.normalize(prompt))}`;
    let pending = this.inflight.get(key);
    if (!pending) {
      pending = compute()
        .then(async (res) => {
          // Don't let a cache write failure fail the user's request
          await this.store(scope, prompt, res, found.embedding).catch((e) =>
            console.error('cache store failed', e),
          );
          return res;
        })
        .finally(() => this.inflight.delete(key));
      this.inflight.set(key, pending);
    }
    return { hit: false, response: await pending };
  }
}

A few things worth calling out:

Exact match before embedding. Many repeats are literally identical. Skipping the embedding call for those saves both time and money.
Single-flight. If 50 users ask the same new question at once (say, right after an outage), you want one LLM call, not 50. The inflight map handles that within a process.
The cache must never break the request. Failures on store are logged and swallowed. You should also wrap lookup so a database outage degrades to "just call the LLM" (see the Express example below).
Parameterized queries throughout. pgvector.toSql() serializes the array safely for the vector type.

Step 3: Wire it into an endpoint

// server.ts
import express from 'express';
import { Pool } from 'pg';
import pgvector from 'pgvector/pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on('connect', (client) => pgvector.registerTypes(client));

const cache = new SemanticCache(pool, embedText);
const app = express();
app.use(express.json());

app.post('/api/ask', async (req, res) => {
  const { question } = req.body;
  const tenantId = req.user.tenantId; // from your auth middleware

  const scope = buildScope({
    tenantId,
    model: process.env.LLM_MODEL!,
    promptVersion: 'support-bot-v7',
  });

  const started = performance.now();
  try {
    const result = await cache.getOrCompute(scope, question, () =>
      callLlm({ system: SYSTEM_PROMPT, user: question }),
    );

    metrics.timing('ask.latency_ms', performance.now() - started, {
      cache: result.hit ? result.tier! : 'miss',
    });
    metrics.increment('ask.cache', { result: result.hit ? 'hit' : 'miss' });

    res.json({ answer: result.response.text, cached: result.hit });
  } catch (err) {
    // If the cache layer itself blows up, fall back to the LLM directly
    console.error('cache path failed, calling LLM directly', err);
    const direct = await callLlm({ system: SYSTEM_PROMPT, user: question });
    res.json({ answer: direct.text, cached: false });
  }
});

One caveat on that catch: it also catches errors from the LLM call itself, so you may double-call the LLM if the first call failed. In production, separate "cache infrastructure errors" from "compute errors" (for example by having lookup return a miss on DB failure internally).

The hard part: choosing a similarity threshold

The threshold is the knob that trades hit rate against correctness, and there's no universal right value. It depends on your embedding model, your domain, and how costly a wrong answer is.

Too low: you serve wrong answers confidently.
Too high: you rarely hit, and the cache is expensive overhead.

Embedding similarity measures topical closeness, not intent equivalence. These pairs are all highly similar to an embedding model but need different answers:

Prompt A	Prompt B	Same answer OK?
"How do I cancel my subscription?"	"How do I not cancel my subscription?"	❌
"What's the refund policy for annual plans?"	"What's the refund policy for monthly plans?"	❌
"Is feature X available in Python?"	"Is feature X available in Go?"	❌
"How do I reset my password?"	"I forgot my password"	✅

Calibrate with your own data

Don't guess. Build a small labeled set from real logs: pairs of prompts marked "same intent" or "different intent". Then sweep thresholds:

// calibrate.ts
interface Pair { a: string; b: string; sameIntent: boolean }

function cosine(a: number[], b: number[]) {
  let dot = 0, na = 0, nb = 0;
  for (let i = 0; i < a.length; i++) {
    dot += a[i] * b[i]; na += a[i] ** 2; nb += b[i] ** 2;
  }
  return dot / (Math.sqrt(na) * Math.sqrt(nb));
}

async function sweep(pairs: Pair[]) {
  const scored = [];
  for (const p of pairs) {
    const [ea, eb] = await Promise.all([embedText(p.a), embedText(p.b)]);
    scored.push({ sim: cosine(ea, eb), sameIntent: p.sameIntent });
  }

  for (let t = 0.80; t <= 0.99; t += 0.01) {
    const hits = scored.filter((s) => s.sim >= t);
    const falsePositives = hits.filter((s) => !s.sameIntent).length;
    const truePositives = hits.length - falsePositives;
    console.log(
      `threshold=${t.toFixed(2)}  hits=${hits.length}  ` +
      `correct=${truePositives}  WRONG=${falsePositives}`,
    );
  }
}

Choose the lowest threshold where WRONG is acceptably close to zero for your risk tolerance. For a customer-facing bot that quotes prices or policies, be very conservative. For a casual "explain this concept" endpoint, you can be looser.

Defense in depth beyond the threshold

Segment by scope. Different products, plans, or languages shouldn't share cache entries.
Keep entities out of the fuzzy match. If prompts contain order IDs, names, or numbers, either extract them into the scope/key or skip semantic matching when they're present, because the nearest neighbor of "status of ORD-123456" is "status of ORD-654321".
Per-category thresholds. Stricter for transactional or factual queries, looser for general explanations.
Log near-misses. Record hits between 0.88 and 0.95 along with the matched prompt for periodic human review. It's the best source of calibration data you'll get.

What you should not cache

A cache hit means "the answer from last time is still correct". That's false for many requests:

Personalized answers ("what's my balance?"). Different users, different truths.
Time-sensitive answers ("what's the status of my order?", "what's today's date?").
Responses that used tools with live data. Caching the final text freezes that data in place.
Non-deterministic or creative requests where the user expects variety ("give me another idea").
Multi-turn context. If the answer depends on earlier messages, the last user message alone is not the cache key. Either only cache stateless first turns, or embed a summary of the conversation context.
Anything with sensitive data, unless you've thought through retention, access control, and your compliance obligations. Cached prompts and answers are stored data.

Implement this as an explicit policy function rather than scattered ifs:

function isCacheable(req: AskRequest): boolean {
  if (req.conversation.length > 1) return false;       // multi-turn
  if (req.usedTools) return false;                      // live data involved
  if (req.user.requiresPersonalization) return false;
  if (/\b(today|now|current|latest|my)\b/i.test(req.question)) return false; // crude, tune it
  return true;
}

Performance and operations

HNSW tuning

At query time, hnsw.ef_search controls the recall/speed tradeoff (the default is 40; higher means better recall but slower):

SET hnsw.ef_search = 100;

For a cache you can usually tolerate slightly imperfect recall, since a missed neighbor just means one extra LLM call.

Filtering with HNSW

Our query filters by scope. With HNSW, filtering is applied after the index scan, so a highly selective filter can return fewer rows than you asked for (or none, even though matches exist). Options:

Use pgvector's iterative index scans (available in newer versions, via hnsw.iterative_scan)
Use partial indexes or table partitioning per large tenant
Keep scopes coarse enough that most scans find candidates

Check the pgvector README for the options available in your installed version.

Expiry and cleanup

expires_at filtering in queries keeps stale entries from being served, but they still occupy space and index memory. Run a periodic cleanup:

DELETE FROM llm_cache WHERE expires_at < now() - interval '1 day';

Run it in batches, or use partitioning by time if the table is large. Consider also evicting entries with zero hits after N days. They're not earning their keep.

Embedding latency is on your critical path

Every cache miss pays for an embedding call and the LLM call. Mitigations:

Skip semantic lookup for very short or very long prompts (tune the bounds)
Set a tight timeout on the embedding call. If it's slow, treat it as a miss and go straight to the LLM
Reuse the embedding computed during lookup when storing (we do this via the returned embedding)

Streaming responses

If you stream tokens to users, you can't cache until the stream completes. Buffer the full text as it streams, then store it afterwards. On a hit, you can either return the whole answer instantly or simulate streaming by chunking the cached text, which keeps your frontend code identical for both paths.

A note on "in-memory" scaling

The L1 cache is per-process. With many instances, each warms up independently, and invalidation (via promptVersion in the scope) works because new scopes simply never match old keys. If you need cross-instance L1 coherence, that's a sign to add Redis as a shared tier, but start without it. L1 + pgvector covers most workloads, and every extra tier is another thing to keep consistent.

Metrics that tell you if it's working

Instrument from day one:

Hit rate by tier (l1, l2, miss) and by scope or category
Latency by outcome (hit vs miss). Compare p50 and p95.
Embedding latency and error rate
Similarity distribution of hits. If most hits cluster right at your threshold, you're likely over-matching.
Estimated tokens saved, from the usage stored on each cached response
User feedback signals (thumbs down, re-asks) segmented by cached: true/false. If cached answers get disproportionately downvoted, raise the threshold.

That last one is the real test. A cache with a great hit rate and worse user satisfaction is a regression.

Putting it together

              ┌───────────────────────────────┐
  Request ──▶ │  isCacheable?  ── no ─────────┼──▶ LLM
              └──────────────┬────────────────┘
                             │ yes
                  ┌──────────▼──────────┐
                  │ L1: in-process LRU  │── hit ──▶ response (µs)
                  └──────────┬──────────┘
                             │ miss
                  ┌──────────▼──────────┐
                  │ L2: exact (Postgres)│── hit ──▶ response (ms)
                  └──────────┬──────────┘
                             │ miss
                  ┌──────────▼──────────┐
                  │ embed + pgvector kNN│── ≥ threshold ──▶ response
                  └──────────┬──────────┘
                             │ miss
                  ┌──────────▼──────────┐
                  │ single-flight → LLM │──▶ store L1 + L2 ──▶ response
                  └─────────────────────┘

Key takeaways

A semantic cache matches meaning, not strings. Embed the prompt, find the nearest cached prompt, and reuse the answer above a similarity threshold.
Two tiers are enough. An in-process LRU for exact repeats and Postgres with pgvector (HNSW) for shared, durable semantic matches.
Put everything that affects correctness into the scope: tenant, model, prompt version. This prevents data leaks and gives you instant invalidation.
Calibrate the threshold with labeled data from your own traffic. Similar embeddings don't guarantee equivalent intent.
Be deliberate about what's cacheable. Personalized, time-sensitive, tool-backed, and multi-turn requests usually aren't.
Make the cache fail-open. It should only ever make your endpoint faster, never less available.
Measure user-facing quality, not just hit rate.

Have you built a semantic cache, or tried one and abandoned it? I'd love to hear what threshold and embedding model worked for you in the comments.

Further reading: the pgvector README (HNSW options, iterative scans, distance operators), pgvector-node, and lru-cache.

Building Resilient LLM Function Calling in Node.js: Circuit Breakers, Fallbacks, and Schema Validation

subashthiruppathy — Thu, 08 Oct 2026 15:42:27 +0000

Function calling (a.k.a. tool use) is what turns an LLM from a text generator into something that can do things: look up an order, create a ticket, charge a card. It's also where most production LLM features quietly fall apart.

The demo works. Then real traffic arrives and you meet:

The model returns arguments that don't match your schema ("quantity": "two")
The model calls a tool that doesn't exist
The provider returns 429 or 529 during a traffic spike
A request hangs for 90 seconds and ties up your workers
The model gets stuck in a loop calling the same tool forever
A retry causes the same refund to be issued twice

In this post we'll build a layered resilience strategy in Node.js and TypeScript, where each layer handles a different failure class:

Schema validation for what the model sends you
Timeouts and retries for transient failures
Circuit breakers for sustained provider failures
Fallbacks so users get something useful when all else fails
Loop guards and idempotency for the agent loop itself

The mental model: two kinds of failure

Before writing code, separate failures into two buckets, because they need opposite treatment.

	Model-side failures	Infrastructure failures
Examples	Bad JSON, wrong types, unknown tool, missing required field	429, 5xx, timeouts, network resets
Cause	The model made a mistake	The provider or network is unhealthy
Right response	Tell the model what went wrong and let it retry	Back off, retry, or fail over
Counts toward circuit breaker?	No	Yes

The most common mistake is treating them the same: retrying bad arguments with the identical prompt (it'll fail again), or tripping your circuit breaker because the model sent one malformed payload.

Layer 1: Validate every tool call with Zod

Never trust tool_use input. Even with strict schemas in the prompt, models occasionally produce invalid values. Define each tool once, with a Zod schema as the source of truth, and derive the JSON Schema you send to the model from it.

// tools.ts
import { z } from 'zod';

export interface ToolDef<TInput extends z.ZodTypeAny = z.ZodTypeAny> {
  name: string;
  description: string;
  schema: TInput;
  /** Does this tool change state? Controls retry and idempotency behavior. */
  sideEffects: boolean;
  timeoutMs: number;
  run: (input: z.infer<TInput>, ctx: { idempotencyKey: string }) => Promise<unknown>;
}

export const getOrderStatus: ToolDef = {
  name: 'get_order_status',
  description: 'Look up the current status of a customer order by its ID.',
  schema: z.object({
    orderId: z.string().regex(/^ORD-\d{6}$/, 'Order IDs look like ORD-123456'),
  }),
  sideEffects: false,
  timeoutMs: 3_000,
  run: async ({ orderId }) => db.orders.findStatus(orderId),
};

export const issueRefund: ToolDef = {
  name: 'issue_refund',
  description: 'Refund an order. Amount is in the smallest currency unit (cents).',
  schema: z.object({
    orderId: z.string().regex(/^ORD-\d{6}$/),
    amountCents: z.number().int().positive().max(50_000),
    reason: z.enum(['damaged', 'late', 'wrong_item', 'other']),
  }),
  sideEffects: true,
  timeoutMs: 8_000,
  run: async (input, { idempotencyKey }) =>
    payments.refund({ ...input, idempotencyKey }),
};

export const registry = new Map(
  [getOrderStatus, issueRefund].map((t) => [t.name, t]),
);

Note the details: tight constraints (regex, max, enum) catch nonsense early, and the descriptions in the schema double as documentation for the model.

For the JSON Schema you send to the provider, use z.toJSONSchema() (Zod 4) or the zod-to-json-schema package (Zod 3):

import { z } from 'zod';

export const toolSpecs = [...registry.values()].map((t) => ({
  name: t.name,
  description: t.description,
  input_schema: z.toJSONSchema(t.schema), // Zod 4
}));

Turn validation failures into feedback, not exceptions

When validation fails, don't throw. Return a structured error to the model as the tool result, so it can self-correct on the next turn:

// execute-tool.ts
import { z } from 'zod';

export type ToolOutcome =
  | { ok: true; content: unknown }
  | { ok: false; kind: 'unknown_tool' | 'invalid_args' | 'tool_error'; message: string };

export async function executeToolCall(
  name: string,
  rawInput: unknown,
  idempotencyKey: string,
): Promise<ToolOutcome> {
  const tool = registry.get(name);
  if (!tool) {
    return {
      ok: false,
      kind: 'unknown_tool',
      message: `Unknown tool "${name}". Available tools: ${[...registry.keys()].join(', ')}`,
    };
  }

  const parsed = tool.schema.safeParse(rawInput);
  if (!parsed.success) {
    return {
      ok: false,
      kind: 'invalid_args',
      message: `Invalid arguments: ${z.prettifyError(parsed.error)}`, // Zod 4; use error.issues in v3
    };
  }

  try {
    const content = await withTimeout(
      tool.run(parsed.data, { idempotencyKey }),
      tool.timeoutMs,
      `Tool ${name} timed out`,
    );
    return { ok: true, content };
  } catch (err) {
    return { ok: false, kind: 'tool_error', message: (err as Error).message };
  }
}

function withTimeout<T>(p: Promise<T>, ms: number, msg: string): Promise<T> {
  let t: NodeJS.Timeout;
  const timeout = new Promise<never>((_, rej) => {
    t = setTimeout(() => rej(new Error(msg)), ms);
  });
  return Promise.race([p, timeout]).finally(() => clearTimeout(t));
}

The error message is deliberately specific and actionable. "Order IDs look like ORD-123456" lets the model fix its own mistake in one round trip. This is called a repair loop, and it resolves the large majority of malformed-call failures without any human or fallback involvement.

🔒 Security note: Schema validation is not authorization. If the model asks to refund order ORD-000042, verify that the current user owns that order inside the tool. Treat model output as untrusted user input.

Layer 2: Timeouts and retries (for infrastructure failures only)

Now the provider call itself. Two rules:

Always set a timeout. Without one, a hung connection ties up a request slot indefinitely.
Retry only retryable errors, with exponential backoff and jitter.

// retry.ts
export interface RetryOptions {
  retries: number;
  baseMs: number;
  maxMs: number;
  isRetryable: (err: unknown) => boolean;
}

export async function retry<T>(fn: () => Promise<T>, o: RetryOptions): Promise<T> {
  let attempt = 0;
  for (;;) {
    try {
      return await fn();
    } catch (err) {
      if (attempt >= o.retries || !o.isRetryable(err)) throw err;
      const exp = Math.min(o.maxMs, o.baseMs * 2 ** attempt);
      const delay = Math.random() * exp; // "full jitter"
      await new Promise((r) => setTimeout(r, delay));
      attempt++;
    }
  }
}

export function isRetryableProviderError(err: any): boolean {
  const status = err?.status ?? err?.statusCode;
  if (status === 429 || status === 408 || status === 409) return true;
  if (typeof status === 'number' && status >= 500) return true;
  // Network-level failures
  return ['ECONNRESET', 'ETIMEDOUT', 'ENOTFOUND', 'EAI_AGAIN'].includes(err?.code);
}

Things worth knowing:

Honor Retry-After on 429s when the provider sends it, instead of guessing.
Jitter matters. Without it, every client retries at the same instant and re-creates the spike.
Most official SDKs already retry a couple of times by default. Know what yours does, and don't stack your retries on top blindly. Retries multiply: 3 SDK retries × 3 of yours = 9 attempts.
Never auto-retry non-idempotent tool executions (see Layer 5).

Layer 3: Circuit breakers

Retries help with blips. But when a provider is down or badly degraded, retrying every request makes things worse: you pile up slow requests, burn through rate limits, and make users wait for a failure that was predictable.

A circuit breaker watches recent failures and, past a threshold, fails fast for a cooldown period instead of calling the provider at all. After the cooldown, it lets a trial request through to test recovery.

        failures exceed threshold
CLOSED ───────────────────────────▶ OPEN
  ▲                                  │
  │ trial succeeds                   │ after resetTimeout
  │                                  ▼
  └─────────────────────────── HALF-OPEN
        trial fails → back to OPEN

opossum is a solid, widely used breaker for Node:

// breaker.ts
import CircuitBreaker from 'opossum';
import { retry, isRetryableProviderError } from './retry';

async function callPrimary(params: ModelRequest): Promise<ModelResponse> {
  return retry(() => anthropic.messages.create(params, { timeout: 30_000 }), {
    retries: 2,
    baseMs: 300,
    maxMs: 3_000,
    isRetryable: isRetryableProviderError,
  });
}

export const primaryBreaker = new CircuitBreaker(callPrimary, {
  timeout: 35_000,               // hard ceiling for one guarded call
  errorThresholdPercentage: 50,  // open if >=50% of calls fail...
  volumeThreshold: 10,           // ...once we've seen at least 10 calls in the window
  rollingCountTimeout: 30_000,   // 30s statistics window
  resetTimeout: 20_000,          // try again after 20s
  // 4xx client errors (except 408/429) are OUR bug, not provider health.
  // Returning true means "don't count this as a failure".
  errorFilter: (err: any) => {
    const s = err?.status;
    return typeof s === 'number' && s >= 400 && s < 500 && s !== 408 && s !== 429;
  },
});

primaryBreaker.on('open', () => metrics.increment('llm.breaker.open', { provider: 'primary' }));
primaryBreaker.on('halfOpen', () => log.warn('LLM breaker half-open, probing'));
primaryBreaker.on('close', () => log.info('LLM breaker closed, provider recovered'));

Key decisions:

What counts as a failure? Timeouts, 5xx, 429s, network errors: yes. Schema validation failures and 400-class errors from your own bad requests: no. Otherwise one buggy prompt can take your whole feature offline. That's what errorFilter is for.
Volume threshold. Without a minimum sample size, 1 failure out of 2 requests (50%) opens the circuit at 3 AM when traffic is low.
Per-provider (or even per-model) breakers. Don't share one breaker across providers; you want to fail over, not fail everything.
Per-process state. opossum keeps state in memory per Node process. That's usually fine (each instance learns quickly), but it means a fleet of 50 pods each independently discovers the outage.

Layer 4: Fallbacks

When the breaker is open or retries are exhausted, what does the user see? Decide this deliberately. A good fallback chain degrades gracefully, from best to worst:

Secondary model or provider, with the same tools
Cheaper or smaller model, possibly with fewer tools
Cached or deterministic answer for common queries
Graceful "can't do that right now" message, with a way to retry or reach a human

// llm.ts
import CircuitBreaker from 'opossum';

const secondaryBreaker = new CircuitBreaker(callSecondary, {
  timeout: 35_000,
  errorThresholdPercentage: 50,
  volumeThreshold: 10,
  resetTimeout: 20_000,
});

export async function callModelResilient(req: ModelRequest): Promise<ModelResponse> {
  const chain = [
    { name: 'primary', fire: () => primaryBreaker.fire(req) },
    { name: 'secondary', fire: () => secondaryBreaker.fire(adaptForSecondary(req)) },
  ];

  let lastErr: unknown;
  for (const step of chain) {
    try {
      const res = await step.fire();
      metrics.increment('llm.call.ok', { via: step.name });
      return res;
    } catch (err) {
      lastErr = err;
      metrics.increment('llm.call.failed', { via: step.name });
      // Breaker open => fail fast and move to the next link in the chain
    }
  }
  throw new LlmUnavailableError('All model providers failed', { cause: lastErr });
}

A few cautions about fallbacks:

Tool-call formats differ between providers. Message shapes, tool schema keys, and how results are returned are not identical. Write an adapter layer so the rest of your code speaks one internal format. Don't let provider-specific types leak through.
Behavior differs between models. A fallback model might be worse at your tool schemas. Test your tool suite against every model in the chain, not just the primary.
Consider restricting capabilities in fallback mode. For example, a weaker model might be allowed to read data but not to issue refunds.
Don't fall back on model-side failures by default. If the model sent bad arguments, repair-loop first. Only escalate to a different model after the repair budget is spent.

And for the final fallback, make it honest and useful:

try {
  return await runAgent(userMessage);
} catch (err) {
  if (err instanceof LlmUnavailableError) {
    return {
      type: 'degraded',
      message:
        "I can't process that automatically right now. I've saved your request, " +
        "and you can also check your order status at /orders.",
    };
  }
  throw err;
}

Layer 5: The agent loop (loop guards and idempotency)

Tool use is a loop: the model asks for a tool, you run it, you send the result back, and repeat until the model produces a final answer. Without guardrails, that loop is a liability.

// agent.ts
import { randomUUID } from 'node:crypto';

const MAX_STEPS = 8;
const MAX_REPAIR_ATTEMPTS = 2;

export async function runAgent(userMessage: string, ctx: { requestId: string }) {
  const messages: Message[] = [{ role: 'user', content: userMessage }];
  const seenCalls = new Map<string, number>(); // detects repeated identical calls
  let repairAttempts = 0;

  for (let step = 0; step < MAX_STEPS; step++) {
    const res = await callModelResilient({
      model: process.env.LLM_MODEL!,
      max_tokens: 1024,
      tools: toolSpecs,
      messages,
    });

    messages.push({ role: 'assistant', content: res.content });

    if (res.stop_reason !== 'tool_use') {
      return extractText(res); // final answer
    }

    const toolResults = [];
    for (const block of res.content.filter((b) => b.type === 'tool_use')) {
      // Loop guard: same tool + same args repeated => the model is stuck
      const fingerprint = `${block.name}:${JSON.stringify(block.input)}`;
      const count = (seenCalls.get(fingerprint) ?? 0) + 1;
      seenCalls.set(fingerprint, count);
      if (count > 2) {
        throw new AgentLoopError(`Repeated identical call to ${block.name}`);
      }

      // Deterministic idempotency key: same request + same tool_use id => same key
      const idempotencyKey = `${ctx.requestId}:${block.id}`;
      const outcome = await executeToolCall(block.name, block.input, idempotencyKey);

      if (!outcome.ok && outcome.kind !== 'tool_error') {
        if (++repairAttempts > MAX_REPAIR_ATTEMPTS) {
          throw new AgentRepairExhaustedError(outcome.message);
        }
      }

      toolResults.push({
        type: 'tool_result',
        tool_use_id: block.id,
        content: JSON.stringify(outcome.ok ? outcome.content : { error: outcome.message }),
        is_error: !outcome.ok,
      });
    }

    messages.push({ role: 'user', content: toolResults });
  }

  throw new AgentLoopError(`Exceeded ${MAX_STEPS} steps without a final answer`);
}

What this loop gives you:

A hard step cap. An agent that can't finish within N steps shouldn't run forever or burn your token budget.
A repair budget. The model gets two chances to fix its own mistakes, then you stop and fall back.
Repeat detection. Identical calls three times in a row means the model is stuck.
is_error: true on failed tool results tells the model the call failed, so it can adjust rather than treat the error text as real data.

Idempotency for side-effecting tools

Retries, timeouts, and double-submits mean a tool will occasionally run twice. For read-only tools, who cares. For issue_refund, it's a real-money bug.

The fix is an idempotency key passed to the downstream system (most payment APIs support this natively). Because we derive the key from requestId + tool_use id, a retry of the same logical call carries the same key, so the downstream service can safely deduplicate.

Also: don't auto-retry a side-effecting tool that timed out unless you know the operation is idempotent. A timeout doesn't mean it didn't run. Instead, check the state ("was the refund created?") or return the ambiguity to the user.

For high-risk tools, consider a confirmation step: the model proposes the action, your app shows it to the user, and only a human click executes it.

Testing resilience (the part everyone skips)

Resilience code that has never been exercised is just hopeful code. Build fault injection into your tests:

// agent.test.ts
import { describe, it, expect, vi } from 'vitest';

describe('agent resilience', () => {
  it('repairs invalid tool arguments in one round trip', async () => {
    mockModel
      .respondWithToolUse('get_order_status', { orderId: '123' })        // invalid
      .respondWithToolUse('get_order_status', { orderId: 'ORD-123456' }) // corrected
      .respondWithText('Your order has shipped.');

    const answer = await runAgent('Where is order 123456?', { requestId: 'r1' });
    expect(answer).toContain('shipped');
  });

  it('fails over to the secondary provider when primary returns 529s', async () => {
    mockPrimary.alwaysFail({ status: 529 });
    mockSecondary.respondWithText('Hello from the backup.');
    const res = await callModelResilient(basicRequest);
    expect(extractText(res)).toContain('backup');
  });

  it('does not issue the same refund twice on retry', async () => {
    const calls = await runWithSimulatedTimeoutAfterSuccess('issue_refund');
    expect(payments.refund).toHaveBeenCalledTimes(1);
  });

  it('stops a model stuck calling the same tool', async () => {
    mockModel.alwaysRespondWithToolUse('get_order_status', { orderId: 'ORD-123456' });
    await expect(runAgent('hi', { requestId: 'r2' })).rejects.toThrow(AgentLoopError);
  });

  it('opens the breaker after sustained failures and fails fast', async () => {
    mockPrimary.alwaysFail({ status: 503 });
    for (let i = 0; i < 12; i++) await callModelResilient(basicRequest).catch(() => {});
    expect(primaryBreaker.opened).toBe(true);
  });
});

In staging, go further: use a proxy (Toxiproxy, or a simple fault-injecting middleware) to add latency, drop connections, and return 429s, and watch what your dashboards and fallbacks actually do.

What to monitor

Instrument each layer so you can tell which failure class is happening:

tool.validation_failed by tool name. A spike means a prompt or schema regression.
tool.repair_succeeded vs repair_exhausted
llm.call.latency (p50/p95/p99) and llm.call.failed by provider and status
llm.breaker.state transitions
llm.fallback.used rate. If you're on the fallback 20% of the time, your primary has a problem.
agent.steps histogram and agent.loop_guard_tripped
Token usage per request, to catch runaway loops through cost

And alert on rates and trends, not just absolute counts. A creeping validation failure rate after a prompt change is easy to miss otherwise.

Putting it all together

User request
   │
   ▼
Agent loop (step cap, repeat detection)
   │
   ├──▶ callModelResilient
   │        ├─ Primary breaker ─▶ retry (backoff + jitter) ─▶ Provider A
   │        └─ (open/failed) ───▶ Secondary breaker ───────▶ Provider B
   │
   ├──▶ tool_use returned
   │        ├─ Unknown tool / bad args ─▶ error back to model (repair loop)
   │        └─ Valid ─▶ timeout + idempotency key ─▶ run tool
   │
   └──▶ All providers down? ─▶ graceful degraded response

Each layer is small and testable on its own, and together they cover the full set of failure modes.

Key takeaways

Validate everything the model sends you. Schema-first tools with Zod, and return validation errors to the model so it can repair.
Separate model failures from infrastructure failures. Only the latter should trigger retries, breakers, and failover.
Retry with backoff and jitter, and only for retryable errors.
Use circuit breakers so a sick provider fails fast instead of dragging your app down with it.
Design the fallback chain on purpose, including what the user sees at the very end.
Cap the loop and make side effects idempotent. Agents will eventually loop or double-fire; make sure it's harmless.
Test the failure paths. If you haven't injected faults, you don't know it works.

Resilience isn't about preventing the model from ever being wrong or the provider from ever being down. Both will happen. It's about making sure that when they do, your users see a slightly slower answer or a polite degraded one instead of a stack trace, a double refund, or a hung page.

What failure modes have you hit with LLM tool calling in production? Share them in the comments.

Further reading: opossum (circuit breaker), Zod (schema validation), and your provider's tool-use documentation for the exact message formats.

Why Your BullMQ / Redis Job Queue is Silently Leaking Memory (And How to Fix It)

subashthiruppathy — Wed, 07 Oct 2026 17:36:47 +0000

It starts with a Slack alert at 3 AM: Redis memory at 85%.

You check your queue dashboard. Zero waiting jobs. Zero active jobs. Workers are healthy. Throughput looks normal. And yet used_memory has been climbing a little every day for weeks, like a slow tire leak.

If you run BullMQ in production, there's a good chance you've been here, or you're about to be. BullMQ is excellent, but its defaults favor debuggability over memory hygiene. Left unconfigured, it will happily keep every job you ever ran.

In this post we'll go through the six most common causes of "silent" memory growth in BullMQ and Redis, how to confirm which one is biting you, and how to fix each.

TL;DR

#	Cause	Fix
1	Completed/failed jobs are kept forever (default)	`removeOnComplete` / `removeOnFail` with `age` + `count`
2	Huge job payloads and return values	Store references, not blobs
3	Orphaned repeatable jobs	Use job schedulers, clean up stale keys
4	Unbounded event streams	Cap `streams.events.maxLen`
5	Creating `Queue` / `QueueEvents` instances per request	Create once, reuse, close on shutdown
6	Wrong Redis eviction policy or fragmentation	`maxmemory-policy noeviction`, monitor RSS vs used memory

1. The big one: jobs are never removed by default

This is the cause behind the majority of "my Redis is full of nothing" reports.

By default, BullMQ keeps completed and failed jobs in Redis indefinitely. Each job is stored as a hash (with its data, options, return value, stack traces, timestamps) plus entries in sorted sets for the completed or failed state.

If you process 50,000 jobs a day at ~2 KB each, that's roughly 100 MB a day of data nobody will ever read again.

Confirm it

import { Queue } from 'bullmq';

const queue = new Queue('emails', { connection });
console.log(await queue.getJobCounts('completed', 'failed', 'waiting', 'active'));
// { completed: 4_812_903, failed: 31_220, waiting: 0, active: 3 }

There's your leak. Nearly 5 million completed jobs sitting in Redis.

Fix it

Set retention at the queue level so every job inherits it:

const queue = new Queue('emails', {
  connection,
  defaultJobOptions: {
    removeOnComplete: {
      age: 60 * 60,   // keep for 1 hour (seconds)
      count: 1000,    // ...but never more than 1000
    },
    removeOnFail: {
      age: 7 * 24 * 60 * 60, // keep failures for 7 days for debugging
      count: 5000,
    },
  },
});

A few notes:

removeOnComplete: true deletes the job immediately. Great for fire-and-forget work, but you lose the ability to inspect results.
A number (removeOnComplete: 100) keeps the latest N jobs.
The object form ({ age, count }) is usually what you want: bounded by time and count.
Keep failed jobs longer than completed ones. That's the data you actually debug with. But still bound them.

Clean up the existing mess

Setting the option only affects future jobs. To purge the backlog:

// Remove completed jobs older than 1 hour, up to 10,000 at a time
let removed;
do {
  removed = await queue.clean(60 * 60 * 1000, 10_000, 'completed');
} while (removed.length > 0);

// Same for failed jobs older than 7 days
do {
  removed = await queue.clean(7 * 24 * 60 * 60 * 1000, 10_000, 'failed');
} while (removed.length > 0);

⚠️ Always pass a limit and loop. Cleaning millions of jobs in one call blocks Redis, and Redis is single-threaded. Do it in batches, ideally off-peak.

2. Fat payloads and return values

Even with retention configured, what you put in a job matters. BullMQ stores job.data and the worker's return value (returnvalue) in Redis. Developers often do this:

// ❌ Don't do this
await queue.add('generate-report', {
  userId,
  rows: hugeArrayOfTenThousandRows,
});

// and in the worker
async function processor(job) {
  const pdf = await buildPdf(job.data.rows);
  return { pdfBase64: pdf.toString('base64') }; // 5 MB return value in Redis
}

A single 5 MB return value multiplied by a few thousand retained jobs is gigabytes.

Fix it: pass references, not payloads

// ✅ Store the big thing elsewhere (S3, Postgres, disk)...
const key = await storage.put(`reports/${reportId}/input.json`, rows);

// ...and put only the pointer in the job
await queue.add('generate-report', { userId, inputKey: key });

// Worker returns a pointer too
async function processor(job) {
  const rows = await storage.get(job.data.inputKey);
  const pdfKey = await storage.put(`reports/${job.id}.pdf`, await buildPdf(rows));
  return { pdfKey };
}

Rule of thumb: if a job's data or result is bigger than a few KB, it probably belongs somewhere else.

Also watch out for:

Stack traces on failures. Every failed attempt appends a stack trace to the job hash. A job with attempts: 10 and a deep stack trace can get surprisingly big. Bound failed job retention (see #1).
job.log() abuse. Logs are stored in a Redis list per job. Logging every row you process adds up fast. Use keepLogs inside removeOnComplete / removeOnFail to cap how many log lines are retained:

removeOnComplete: { age: 3600, count: 1000, keepLogs: 20 }

3. Orphaned repeatable jobs

Repeatable jobs (cron-like) are a sneaky source of leaks. In older BullMQ patterns, a repeatable job is identified by a key derived from its name, repeat pattern, and job ID. Change any of those between deploys and BullMQ treats it as a brand-new repeatable job, while the old one keeps firing and enqueuing forever.

Typical story: you tweak a cron from */5 * * * * to */10 * * * *, redeploy, and now both schedules are active. Do that a few times and you have a pile of zombie schedulers each producing jobs.

Confirm it

const repeatables = await queue.getRepeatableJobs();
console.log(repeatables.length);
console.table(repeatables.map(r => ({ name: r.name, pattern: r.pattern, key: r.key })));

If you see multiple entries for the same logical job, that's your culprit.

Fix it

Remove the stale ones:

for (const r of await queue.getRepeatableJobs()) {
  if (isStale(r)) await queue.removeRepeatableByKey(r.key);
}

For the long term, use Job Schedulers (available in recent BullMQ v5 releases), which are keyed by a stable scheduler ID you choose, so redeploying with a new pattern updates the schedule instead of creating a second one:

await queue.upsertJobScheduler(
  'daily-digest',                    // stable ID
  { pattern: '0 8 * * *' },          // change this freely between deploys
  { name: 'send-digest', data: {} },
);

Check the BullMQ docs for your installed version, since the API is still evolving.

4. Unbounded event streams

BullMQ publishes queue events (completed, failed, progress, and so on) to a Redis Stream at bull:<queue>:events. This is how QueueEvents works. Streams don't expire on their own, so BullMQ trims them to approximately a max length, which defaults to 10,000 entries.

That default is fine for a quiet queue. For a high-throughput queue, 10k events per queue can still be a noticeable chunk of memory (event payloads include job IDs and, for completed, the return value), and if you have many queues it multiplies.

Fix it

If you don't consume events, or only need recent ones, lower the cap:

const queue = new Queue('emails', {
  connection,
  streams: {
    events: { maxLen: 1000 },
  },
});

If you don't use QueueEvents at all and only rely on workers, a small maxLen is a free win.

5. Leaks on the Node.js side: instances created per request

Not every leak is in Redis. A very common bug in Express/Fastify/Nest apps:

// ❌ A new Queue (and a new Redis connection) on EVERY request
app.post('/signup', async (req, res) => {
  const queue = new Queue('emails', { connection: { host, port } });
  await queue.add('welcome', { email: req.body.email });
  res.sendStatus(202);
});

Every new Queue() opens Redis connections and registers listeners. Do this under load and you'll see climbing Node heap, climbing Redis connection count (INFO clients), and eventually MaxListenersExceededWarning or ECONNRESET errors.

The same goes for QueueEvents and Worker instances created inside handlers or loops.

Fix it: create once, share, and close on shutdown

// queues.ts
import IORedis from 'ioredis';
import { Queue } from 'bullmq';

// maxRetriesPerRequest: null is required for Workers
export const connection = new IORedis(process.env.REDIS_URL!, {
  maxRetriesPerRequest: null,
});

export const emailQueue = new Queue('emails', { connection });

// graceful shutdown
process.on('SIGTERM', async () => {
  await worker.close();      // waits for active jobs to finish
  await emailQueue.close();
  await queueEvents.close();
  await connection.quit();
  process.exit(0);
});

Other Node-side things to check:

Attaching listeners inside the processor. queueEvents.on('completed', ...) inside a job handler adds a new listener on every job.
Closures capturing job.data. Holding references to big job objects in module-level caches or arrays prevents garbage collection.
Not closing workers in tests. Open handles pile up and hide real leaks.

6. Redis config: eviction policy and fragmentation

Two last Redis-level gotchas.

Use `noeviction`

BullMQ requires Redis to be configured with:

maxmemory-policy noeviction

If you use an eviction policy like allkeys-lru, Redis can evict BullMQ's internal keys when memory is tight, causing missing jobs and corrupted queue state. A leak that should have been loud (writes failing) instead becomes silent data loss. Better to fail loudly: with noeviction, Redis returns errors on writes when full, and your alerts fire.

Watch fragmentation

Deleting millions of jobs frees memory logically, but Redis may not return it to the OS. Compare:

redis-cli INFO memory | grep -E "used_memory_human|used_memory_rss_human|mem_fragmentation_ratio"

If mem_fragmentation_ratio is well above ~1.5 after a big cleanup, you're holding memory the allocator hasn't released. Options:

Enable active defragmentation (activedefrag yes) on supported builds
Restart Redis during a maintenance window (or fail over to a replica)

A diagnostic playbook

When Redis memory is growing and you don't know why, work through this in order.

1. Find the big keys

redis-cli --bigkeys
# or sample safely with a memory-aware scan
redis-cli --memkeys

2. Count keys by queue (use SCAN, never KEYS * in production)

redis-cli --scan --pattern 'bull:emails:*' | wc -l

3. Inspect job counts per state

console.log(await queue.getJobCounts());

4. Spot-check a single job's size

redis-cli MEMORY USAGE bull:emails:12345

5. Check the event stream size

redis-cli XLEN bull:emails:events

6. Check connection count

redis-cli INFO clients | grep connected_clients

Match the symptom to the cause:

Huge completed/failed counts → #1
A few enormous job hashes → #2
Many repeatable entries → #3
Large events stream → #4
Rising connected_clients or Node heap → #5
RSS far above used_memory → #6

Make it hard to regress

Fixing it once isn't enough. Put guardrails in place:

Set defaultJobOptions on every queue, ideally via a shared factory so nobody forgets:

export function createQueue(name: string) {
  return new Queue(name, {
    connection,
    defaultJobOptions: {
      removeOnComplete: { age: 3600, count: 1000 },
      removeOnFail: { age: 7 * 24 * 3600, count: 5000 },
      attempts: 3,
      backoff: { type: 'exponential', delay: 2000 },
    },
    streams: { events: { maxLen: 1000 } },
  });
}

Alert on Redis memory growth rate, not just the absolute threshold. A steady upward slope catches leaks weeks before a full-memory outage.
Export queue metrics (job counts by state) to Prometheus or your APM and graph completed and failed over time. They should plateau, not climb.
Add a lint/test rule that fails CI if a queue is created without retention options.

Wrapping up

BullMQ isn't broken. It's just conservative: it assumes you want to keep everything until you say otherwise. In development that's a feature. In production, at scale, it's a slow-motion incident waiting to happen.

If you take only one thing from this post, make it this: set removeOnComplete and removeOnFail with both age and count on every queue. That alone resolves the vast majority of cases.

Then audit payload sizes, repeatable jobs, and your Node-side instance lifecycle, and make sure Redis is on noeviction with fragmentation monitored.

Have you been bitten by a BullMQ memory leak that isn't on this list? Drop it in the comments. I'd love to add it.

Further reading: the BullMQ docs sections on "Auto-removal of jobs", "Job Schedulers", and "Going to production".

Opinion-Driven Architecture Piece

subashthiruppathy — Tue, 06 Oct 2026 16:06:41 +0000

Why Direct REST Endpoints Fail for Asynchronous Payment Workflows

When designing third-party payment and integration workflows, engineering teams frequently rely on synchronous REST endpoints: a client hits POST /process-payment, the server synchronously dials Stripe or Razorpay, performs multi-table database operations, and holds the HTTP socket open until the third-party gateway responds.

In low-traffic staging environments, this works cleanly. In production, tightly coupled synchronous architectures consistently trigger cascading microservice failures during traffic surges.

Here is an architectural breakdown of why synchronous REST APIs break down in payment pipelines, and why adopting an asynchronous message queue model is essential for backend resilience.

The Thread-Pool Exhaustion Problem

Synchronous HTTP connections are resource-intensive. When your Node.js or containerized microservice makes an outgoing HTTPS request to a payment processor, the incoming client connection remains blocked:

Downstream Latency Spikes: If the payment gateway's response time creeps from 250ms to 3.5 seconds during a network degradation event, your open socket pool fills up.
Cascading Denial of Service: Server connection pools, Express worker threads, and reverse-proxy file descriptors reach exhaustion. Unrelated API endpoints (like health checks or user profile lookups) start timing out simply because all available sockets are awaiting gateway responses.

The Illusion of Immediate Consistency

Engineers often defend synchronous REST for payments by claiming: "We need immediate feedback to show the user whether the card was charged."

In reality, distributed payment operations are never truly synchronous:

Even if an HTTP call succeeds, banks execute settlements asynchronously.
If a network drop severs the connection after the bank charges the card but before your server writes the database ledger, your system enters an inconsistent "charge succeeded, order unrecorded" split-brain state.

Synchronous REST does not guarantee consistency; it merely obscures where network drops occur.

The Asynchronous Alternative: Queue-First Ingestion

A resilient architecture decouples initial request acceptance from transaction settlement using durable queues (such as BullMQ, Redis Streams, or RabbitMQ):

Client -> [POST /payments] -> Validate & Push to Queue -> Return HTTP 202 (Accepted)
                                        |
                                        v
                    [Worker Pool] -> Dial Gateway -> Commit Ledger Atomically

The Structural Advantages

Deterministic Latency: The initial API endpoint responds in under 15ms because it only authenticates the request and pushes a payload to an in-memory or durable queue.
Backpressure and Rate Throttling: If incoming traffic spikes by 10x, your application server does not collapse under open sockets. The queue buffers the traffic, and background workers consume jobs at a strictly regulated rate that respects third-party API rate limits.
Built-in Circuit Breakers & Retries: Transient gateway timeouts can be retried with exponential backoff without tying up front-facing API threads.

Architectural Verdict

If your application processes mission-critical transactions or integrates with third-party banking APIs, transition away from synchronous HTTP execution models. Decouple ingestion from settlement using background job queues to protect your server thread pools from third-party provider latency.

DEV Community: subashthiruppathy

Cache Stampede (Thundering Herd) in Node.js & Redis: Preventing Database Meltdowns on Cache Misses

Anatomy of a stampede

Related problems with similar names

Defense 1: Single-flight (collapse concurrent misses in one process)

Defense 2: A distributed lock so only one process rebuilds the value

Defense 3: Stale-while-revalidate (never make users wait)

Defense 4: Probabilistic early refresh (no locks needed)

Defense 5: Prevent synchronized expiry with TTL jitter

Defense 6: Negative caching (stop penetration)

Putting it together: a reusable helper

When Redis itself is down

Warming and deploys

Test it: prove the stampede is gone

What to monitor

Choosing defenses

Checklist

Key takeaways

The Dual-Write Problem in Distributed Systems: Implementing the Transactional Outbox Pattern in Node.js & PostgreSQL

Why two writes can't be made atomic

"Just retry" doesn't solve it

The outbox idea

Step 1: The schema

Step 2: Write the event in the same transaction

Step 3: The relay

Don't hold a transaction open while publishing

The trap: ids are not commit order

Ordering: what you do and don't get

Step 4: Idempotent consumers (the other half of the pattern)

Alternatives to polling

Operating an outbox

Clean up, or it grows forever

Handle poison messages

Monitor lag and backlog

Test the failure windows

When not to use it

Pattern summary

Checklist

Key takeaways

Why OFFSET Pagination Is Killing Your PostgreSQL Queries (And How to Implement Keyset Pagination in Node.js)

Why OFFSET gets slower with every page

Problem 2: unstable results

Problem 3: the "total pages" count

The idea behind keyset pagination

Step 1: The index

Step 2: The query

The trap: JavaScript dates lose precision

Fix: build the cursor from Postgres's own text

Step 3: The cursor

Step 4: The complete function

Going backwards (previous page)

Gotchas

Mixed sort directions

NULLs in the sort column

Sort columns must be stable

Filters change the cursor's meaning

Jumping to "page 47"

What about the total count?

Verify it yourself

Test the correctness, not just the speed

OFFSET vs keyset at a glance

Checklist

Key takeaways

Why `await Promise.all()` Can Crash Your Database (And How to Throttle Concurrent Async Tasks in Node.js)

The misconception: Promise.all doesn't run things in parallel

How unbounded concurrency hurts

1. Connection pool exhaustion and queue pile-up

2. Overloading the database itself

3. Hitting downstream rate limits

4. Memory and file descriptors

5. Retry storms and thundering herds

Reproduce it on purpose

Fix 1: Process in chunks (simplest)

Fix 2: p-limit (sliding-window concurrency)

Fix 3: p-map (map with concurrency built in)

Fix 4: A tiny worker pool with no dependencies

Fix 5: Don't load everything into memory first

Fix 6: Reduce the number of tasks (batch in SQL)

Fix 7: For big or durable work, use a queue

Concurrency limit vs. rate limit

The misconception: `Promise.all` doesn't run things in parallel

Fix 2: `p-limit` (sliding-window concurrency)

Fix 3: `p-map` (map with concurrency built in)

Fix 1: Pessimistic locking (`SELECT ... FOR UPDATE`)

1. `Promise.all` over a map

B. `pg_stat_statements`: find it in production

Fix 2: Batch with `= ANY($1)` (the "WHERE IN" pattern)

Fix 3: One-to-many with `json_agg`

Fix 4: "Latest 3 comments per post" with `LATERAL`