Split The code is public; the app answers only to the crew.

The first paint groups every match ever played: `standings` costs 42 ms at 50k rows and 171 ms at 200k #184

shipped merged opened this has it
opened this

Make the board's cost flat, so / keeps answering in milliseconds however many matches the app has stored.

standings in src/db.ts is a GROUP BY identity over the whole match table with no index that can serve it, and it is asked for on the hot paths:

  • gamePage on every GET / (the first paint),
  • statsHtml in the POST /api/match response — so a live match pays it once a round,
  • readData in src/tty.ts, at 20 rows, on every terminal frame.

Measured here, seeding through saveMatch itself (5,000 identities, 24 rounds a row), best of 20 calls:

rowsstandings(db, 10)recentMatches(db, 10)
50,00041.6 ms0.07 ms
200,000171.1 ms0.13 ms

recentMatches is flat because match_by_time serves it. standings is a full scan plus a sort, on one thread, so at 200k rows every other request waits behind it too. Production is small today — this is a measured cliff, not a present outage, and the point of the issue is that nothing in the repository would notice it arriving.

Done looks like

  • [ ] A test in src/db.test.ts seeds a stated number of rows (enough to be honest — 20k is plenty) through saveMatch and asserts standings returns inside a stated budget, with the budget and the row count named in the test's own words. It fails against today's db.ts.
  • [ ] The board's cost no longer grows with the table: either an aggregate kept per identity and maintained inside saveMatch, or a memoised result invalidated on write — the builder's call, argued in the pull.
  • [ ] The answer is unchanged: one row per identity, the name from that identity's most recent match, the same ORDER BY kills DESC, damage DESC, the same WHERE rounds > 0, the same shape for /api/leaderboard, the page and the terminal. An existing db.test.ts case proves it.
  • [ ] A database written before this change works on first open, migrated in openDatabase beside the identity, walked_out and map migrations, in the same style.
  • [ ] POST /api/match still answers with the poster's own numbers including the result it just stored — no stale board in the response that a player reads as their match not counting.
  • [ ] SQL stays in src/db.ts; no caller learns anything new.

Risks

  • saveMatch raises a row's columns with MAX(...) on conflict, so an aggregate cannot be maintained by adding the incoming row — it has to move by the delta between stored and stored-after, or be recomputed for that one identity. A test with two posts for the same match id (the periodic post then the final one, and then the periodic one arriving late) is the one that catches this.
  • A cache shared between the page and the terminal is two processes over one file: the terminal reads the database directly, so an in-process cache in the server must not be the only place the truth lives.
  • deleteMatch (the terminal's x) has to move the aggregate too.

Files I expect to change

src/db.ts, src/db.test.ts. Possibly src/server.test.ts if a budget assertion belongs at the boundary too. Nothing in src/client/.

Suggested: scout — this is the server's own floor, and the measurement is the work.

Sign in to join this conversation.

↑↓ navigate ↵ open ⌘↵ its app esc close

Keyboard shortcuts

Docs