The Insights board was slow, so we traced it, indexed it, cached it, and stopped blocking first paint on it
The Statpro Insights board had a cold-start problem. First visitor of the hour waited on a chain of database queries that included a window function scanning a player's whole career. And it was not just the board: every public page waited on a session read before painting a single pixel. This week (mostly last night) we took the whole path apart. If you run a Postgres-backed dashboard behind auth, this is the playbook we wish we had last week.
Measure before you dig
The first commit was pure instrumentation. The trends pool query ran its parts serially, so we fanned them out in parallel and cached the pooled result. That alone told us where the rest of the night would go.
The window function was the tax
The canonical game-logs query ranked rows with row_number() over a player's entire career. Postgres cannot start streaming until it materializes every log row, ranks them, then filters. The fix was to shrink the input before the window runs, with a per-league recency floor that still covers the 120-game cap:
-- Simplified. Bound the join before the window function,
-- so Postgres never materializes a whole career of rows.
SELECT *
FROM game_logs g
JOIN games gm
ON gm.id = g.game_id
AND gm.game_date >= recency_floor(g.league_slug) -- MLB/NBA 3y, NFL 9y
WHERE g.player_id = Second find of the same pass: an NFL-week subquery ran for every MLB and NBA row, where it always resolved to null. Wrapping it in CASE WHEN league = nfl (and skipping the column entirely when the request filters NFL out) cost nothing and paid on every non-football read.
Two indexes, chosen by reading EXPLAIN
Both hot queries were doing parallel sequential scans of the games table because no index matched their filters. One composite index could not serve both, since they filter on different leading columns, so migration 0140 added two: one on (status, game_date) and one on (league, start_time). Post-merge EXPLAIN shows a bitmap index scan on each. The obvious lesson keeps being the right one: run EXPLAIN with your real production predicates, not the ones in your head.
Warm the cache, but verify the warmer actually runs
With the cold path fixed, we pre-warmed the default cache keys from the web server itself. It silently did nothing in production, and that failure taught the subtlest lesson of the week. The warmer's guard read NEXT_RUNTIME off process.env at runtime, but the standalone server.js that runs in production never sets it. Only the next CLI does. Next inlines literal process.env.X reads at build time, so a dynamic lookup returns undefined in the bundled file. The fix was to read the guard variables as literals in a boot module and pass them along explicitly. One boot log line now proves the warmer resolved a runtime, so the next silent no-op is diagnosable in seconds.
Stop blocking first paint on auth
Meanwhile the session read was stalling every public page. The root layout awaited the session (and, for signed-in visitors, a subscription-tier lookup) before returning any markup, which gated every route underneath it. The fix: start the read, never await it in the layout, and let the two small UI slots that actually need the viewer resolve it inside their own Suspense boundaries with anonymous-viewer fallbacks. A signed-in visitor never sees their name flip in from an anonymous state, because the fallback renders the same neutral shell the real value replaces. Nothing paints slower for the gated areas; everything else paints immediately.
Net effect across the night: stale-while-revalidate on the cold path, two purpose-built indexes, cache warming that provably runs, and first paint that no longer waits on a session read. Deploy time got its own pass too: the two image builds now run concurrently and the deploy fans the stack out in parallel, cutting the measured 28-minute merge-to-live down by about 2.5 minutes. Small numbers, but they compound on a lane you merge to a dozen times a day.