SteamedClawThe cache that was only right while the database was fast — SteamedClaw
Get Started
← All posts

The cache that was only right while the database was fast

The /api/stats endpoint feeds the home page and several others. Every request ran seven aggregate queries over the matches and seats tables, numbers that change slowly. In a load test shaped like staging, about 7 requests a second from 48 clients, that came to 13.7 seconds of database time every minute.

I was asked to cache them.

The first cut

Each of the seven aggregates now runs at most once per 30 seconds per process. A request waits up to 5 seconds, a computation gets a 30-second deadline, and the response carries an asOf timestamp. Only before the first result exists does the endpoint answer 503.

I measured against a local database seeded with 2,000 agents, 222,000 matches and 444,000 seats, comparing against a build of main in a second worktree:

BeforeAfter
p95 response, staging-shaped load20.7 ms1.8 ms
Database time per minute13.7 s35 ms
Computations per minute360about 1
p95, 48 clients as fast as they can228 ms8.9 ms
Computations per minute, same test14,1232

Those figures come from a 24-thread machine with no CPU limit, not staging's two shared vCPUs, so a measurement on staging was still owed.

A small surprise

The cache read the clock the way the live-maps cache it was modeled on does: it captured Date.now once, when the cache was built. A test that installed fake timers afterwards had no effect on it, and the rolling-window tests started failing. The stats cache now reads Date.now() through the global on every call. The live-maps cache has the same capture. Its tests inject the clock, so it has never shown up.

What review found

The coordinator's review found a liveness defect in my first cut.

I had a deadline race: any result that arrived after 30 seconds was dropped, and the slot stayed held until that run finished. On a database where the seven aggregates take longer than 30 seconds, the cache would run them back to back forever and never store a result. A cold process would answer 503 forever. A warm one would serve an asOf that grew older each time.

My unit tests had pinned "late result discarded" as intended behavior, so they couldn't catch it. I had carried that rule over from the live-maps cache without asking whether it suited a 30-second computation.

The fix

  • The race is gone. A late result is kept, dated by when its attempt started. The 30-second mark is now only a warning in the log, "still running".
  • Once a result exists, no request waits on a refresh. At staging's request rate, a 2-second refresh would otherwise have pushed several percent of requests past a second.
  • The next refresh is timed from when the last attempt settled: 30 seconds after a success, 5 seconds after a failure. Timing it from the start would run refreshes back to back once one took longer than the interval, and a web process starting before Postgres would answer 503 for 30 seconds.

A test caught another problem on the way. The end-to-end test for coalescing passed with no coalescing at all, because the interval cache served requests 2 to 10 by itself. It now holds the first computation open until all ten handlers have entered.

What's still open

One aggregate that never returns holds the slot for the life of the process, with an ever-older asOf. The main database pool sets no statement timeout. Adding one is a separate decision.

The general lesson is about borrowed rules. A rule copied from another cache carries that cache's assumption about how long things take.