← Back to the library
System designApplied · 7 min

The database spikes when a hot cache key expires

“Load is flat for minutes, then a popular key expires and hundreds of requests miss at once, all run the same expensive query, and latency or DB CPU spikes on a regular rhythm.”

ERROR CODES YOU MAY SEE

A code is a clue. Use its meaning and surrounding evidence to narrow the cause.

57014PostgreSQL SQLSTATE · platform

The query was canceled (query_canceled).

Read the accompanying message to identify the cancellation cause. Check timeouts and query traces; cancellation alone does not establish N+1 access.

Official reference for 57014 (opens in new tab)Code definition reviewed

THE PRINCIPLE

Expiry should trigger one refresh, not one query per concurrent reader.

FIRST MOVES

  1. Confirm the pattern: DB spikes that line up with TTL boundaries or deploys and repeat the same query fingerprint.
  2. Coalesce misses per key in-process (singleflight / a shared in-flight promise).
  3. Across instances, take a short-TTL lock (e.g. Redis SET key token NX PX) for the rebuild; others serve stale or wait briefly. It only saves work, so correctness must not depend on it.
  4. Store a soft expiry inside the value and keep a longer hard TTL, so stale data can be served while one refresh runs.
  5. Refresh hot keys before they expire: probabilistic early expiration (XFetch) or a background refresher.
  6. Add jitter to TTLs so keys written together don't expire together.

TOOLS: THEN → NOW

Fixed TTL plus cache-asideStill fine for cold keys; add coalescing, soft TTLs, and jitter for hot ones
Each instance recomputes on misssingleflight (Go), shared promises, or proxy_cache_lock in NGINX
Purge then refill at the CDNstale-while-revalidate / stale-if-error (RFC 5861)

PATTERN SNAPSHOT

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

async function getProduct(id: string): Promise<Product> {
  const hit = await cache.get(id);               // { value, softExpiresAt }
  if (hit && Date.now() < hit.softExpiresAt) return hit.value;
  let refresh = inflight.get(id);
  if (!refresh) {
    refresh = loadAndStore(id).finally(() => inflight.delete(id));
    inflight.set(id, refresh);                   // one DB query per key per process
  }
  if (hit) { refresh.catch(() => {}); return hit.value; } // serve stale meanwhile
  return refresh;
}

CLOSE THE AI. EXPLAIN THIS.

In-process coalescing works on each server. What still happens with 200 servers, and what fixes it?

SOURCES

Guide reviewed