one_d4 first-load latency: the levers the first-page cache doesn't pull

[aaylward]

Follow-ups from the review panel on #1312 (first-page cache), extended by the holistic altitude pass over #1312 + #1314. The cache removes server think-time for the default request, but the panel's altitude/resources lenses found bigger, unmeasured terms in the same first-load chain. Worth taking in roughly this order — and step 0 first, so the rest is aimed by data rather than plausibility.

• 0. Measure before optimizing further. The http_server_* per-route histograms landed just before one_d4: serve the first-load query from a warmed in-memory cache #1312; read the POST /v1/query latency split (and browser-side timing against api.1d4.net) to see which of the terms below actually dominates first paint. Include client-side bundle parse/execute time here — see the demotion note on item 4.
• 1. Response compression on api.1d4.net. The Caddyfile has encode only in the r3dr.net block; the API block has none, and one_d4 has no Micronaut-side compression. Every first load ships ~50–150KB of JSON — including 25 full PGNs — uncompressed. Likely a one-liner: encode zstd gzip.
• 2. Drop pgn from the list response (or add a detail endpoint). The games list never renders pgn; only GameDetailPanel does, on click. It's the bulk of the payload bytes — and the altitude pass adds a second, server-side reason: every 30s warmer refresh detoasts 25 full PGNs on a 0.25-CPU Postgres, so dropping pgn from the list projection cuts the warmer's steady-state DB load ~10× too, not just the wire bytes. Note the detail panel currently renders from the list payload, so this needs a fetch-on-click or a slimmer list projection plus detail lookup. Once landed, the warmer tick can also relax (e.g. 2min tick / 5min MAX_AGE) if a few minutes of first-page staleness is acceptable.
• 3. Index behind the browse ordering. Done in one_d4: serve the first-load query from a warmed in-memory cache #1312 (commit 77c1abd): idx_game_features_played_at ON game_features(played_at DESC, game_url ASC) in Migration, mirroring SqlCompiler's exact ORDER BY, with a migration test asserting column order and directions rather than mere existence.
• 4. Split the frontend bundle. Demoted by the altitude pass: the 394KB chunk ships as ~110–120KB brotli via Cloudflare, so wire size is not the lever it looked like — the real question is parse/execute time on low-end devices, which item 0 should measure before any React.lazy work. (chess.js and react-chessboard remain the obvious lazy candidates if the measurement says so.)
• 5. Observability for the bounded loops. Re-scoped by the altitude pass: now that one_d4: bound read-query execution with a statement timeout #1314 bounds every loop, the failure mode shifted from "wedged forever" to "silently truncated/skipped" — plain hit/miss counters would miss that. What's wanted: retention_sweeps_total{outcome} plus an age-of-last-successful-sweep gauge, db_statement_timeouts_total{path} for cancelled statements, and a warmer-failure counter (a wedged warmer still degrades silently to pre-one_d4: serve the first-load query from a warmed in-memory cache #1312 behavior). The service already injects CustomMetrics.
• 6. Statement timeout on the query path. Done in one_d4: bound read-query execution with a statement timeout #1314: 10s JDBC queryTimeout on serving reads via StatementTimeouts.withStatementTimeout, with H2 session-scope leak containment, H2 + real-Postgres behavioral cancellation tests, and a pinned tick-budget relationship (fixedDelay + 2×timeout < MAX_AGE).
• 7. Bound IndexingRequestDao.claimNext. Done in one_d4: bound read-query execution with a statement timeout #1314: the candidate scan carries the serving-read bound (its claim UPDATEs stay unbounded, pinned by test) — previously one wedged scan stopped the instance claiming any work until a restart.
• 8. Bound the retention sweep's deleteOlderThan. Done in one_d4: bound read-query execution with a statement timeout #1314: all three sweeps bounded at a sweep-sized 120s — idempotent hourly cleanup, re-run in an hour if truncated.
• 9. socketTimeout in DataSourceFactory, in code. Done in one_d4: bound read-query execution with a statement timeout #1314: Postgres URLs default to socketTimeout=150 unless the URL already carries one; the value must exceed the sweep bound (a long DELETE is silent on the socket), pinned by DataSourceFactoryTest.

New items from the holistic altitude pass (2026-08-06):

• 10. Index white_username / black_username on game_features. Done in one_d4: expression indexes for username search + the retention-delete index (#1313 items 10–11) #1317: Postgres expression indexes on LOWER(white_username) / LOWER(black_username) (the predicate is case-folded on both sides, so plain column indexes could never serve it — the review panel caught that the browse UI's search reaches the same predicate through the STRING_COLUMNS branch, now pinned too), H2 plain-column stand-ins for migration parity, EXPLAIN-level reachability pins on real Postgres for both compiler paths, mutation-verified.
• 11. Index indexed_at for the retention delete. Done in one_d4: expression indexes for username search + the retention-delete index (#1313 items 10–11) #1317 too: idx_game_features_indexed_at (plain b-tree, dialect-neutral), so a sweep truncated at its 120s bound leaves fewer expired rows for the next pass instead of ratcheting on the same scan forever. Column pinned on H2 (MigrationTest), plan reachability for the sweep's exact DELETE shape pinned on real Postgres (PostgresRetentionIndexTest), both wrong-column and creation-dropped mutations killed.
• 12. Serve-stale-on-error in FirstPageCache. Today a DB outage empties the cache at MAX_AGE and first loads fail with the DB. Caffeine can serve the last good snapshot past expiry when the refresh fails (bounded staleness, e.g. a few minutes) — the first-page experience would then survive brief DB blips. Needs a deliberate staleness cap and a metric (item 5) so it can't mask a real outage.
• 13. Reanalysis reads vs the 150s socket cap. fetchForReanalysis is deliberately unbounded by statement timeout, but the one_d4: bound read-query execution with a statement timeout #1314 socket default means any single statement silent for >150s now dies by connection teardown — a cliff, not a cancel. Fine at today's data volume; before reanalysis grows (or if it ever streams), either page the reads or set a per-connection override.

From the #1317 review panel (2026-08-07):

• 14. The unguarded-aggregate footgun on /v1/aggregate. A player with no perspective field in play adds no participation guard — documented contract (the guard repairs perspective resolution, it doesn't scope), now pinned in both directions by SqlCompilerTest. On the select path a human sees other players' names in the rows; on the aggregate path nothing in the output reveals it: player: hikaru, query: num.moves >= 0, group_by: [opening_family] — the natural MCP call for "hikaru's most common openings" — returns the whole corpus's openings presented as hikaru's. Wanted: docs on the MCP tool + either a validation error or a warning when player is set on /v1/aggregate without any perspective field or explicit username filter. (AggregateGamesTool's own worked example sidesteps it with an explicit white.username = ..., which separately only counts one color.)
• 15. Stale README claim about deployed storage. one_d4/README.md (~line 382) still says the deployed indexer uses H2 file storage; compose.yaml contradicts it (Postgres). One-line doc fix, noted by the one_d4: expression indexes for username search + the retention-delete index (#1313 items 10–11) #1317 panel. Related: the MCP server does run boot-scoped in-memory H2 (McpModule defaults to jdbc:h2:mem:indexer, no /etc/ fallback, and compose sets no INDEXER_DB_URL) — worth a sentence in its README, and worth checking that's intentional rather than a missing env var.