PLAYGRND has a problem that almost every sports product eventually runs into: the data wants to be relational, but the pages people open most often want aggregates.
The raw model is the right place for truth. Matches, player appearances, goals, cards, and match events should stay normalized because that makes ingestion, corrections, and auditability much easier. But that same model is not always the right shape for every hot read path.
If a season page, player profile, or featured player list rebuilds the same answer from matches, match_players, and match_events on every request, I am paying the same cost again and again. PLAYGRND is server-rendered, so the public web app calls a Go API, the Go API reads Postgres, and the user waits for work that often could have been prepared ahead of time.
That was the motivation for a recent backend pass on PLAYGRND. I focused on the repeated paths: season standings, scorer lists, player season stats, featured players, and the global upcoming/finished match lists.
The line I did not want to cross
I did not want to turn cache into architecture.
Cache is useful, but it is dangerous when it hides the real model. If nobody knows where the cached value came from, how to rebuild it, or what happens when it is missing, then the cache becomes a second truth. That is exactly the kind of system that feels fast until the first confusing production issue.
So the rule was simple: raw data stays the source of truth. Derived data is allowed only when it can be recomputed. Missing derived data should not break the endpoint. It should fall back to the raw path, even if that path is slower.
That rule fits PLAYGRND well because finished seasons are mostly stable. Active seasons still move. Finished seasons are a good candidate for derived state.
What changed
The optimization had two layers.
The first layer was Postgres. I added and tightened indexes for the hot paths, then persisted deterministic derived tables for finished-season standings, scorers, and player season stats. These tables are not a replacement for raw data. They are a prepared view of something that can be recalculated.
The second layer was Redis snapshot caching for season responses. That removes repeated work from the Go API and Postgres when the same season response is requested in the same shape.
The important part is the maintenance path behind it. Recompute is explicit. For player season stats, recompute is transactional: delete the old derived rows and insert the new ones inside the same transaction. That avoids a half-refreshed season where some rows describe the old picture and some rows describe the new one.
I also added a read-only EXPLAIN ANALYZE helper for the hot paths. I do not want that as permanent runtime overhead, but I do want a safe way to inspect actual query behavior when I am measuring production-like data.
The backfill
Before the production backfill, I took a database backup. That is not interesting writing, but it is the kind of boring operational step that matters.
The backfill produced:
- 33 fully scraped seasons
- 8,479 derived player stat rows
- 5,331 scorer rows
- 0 missing seasons that have raw player data
- 3 empty 2019-2020 seasons without derived player rows because they have no raw player rows
After that, the read-only measurements looked like this:
- global upcoming league matches:
0.165 ms - global finished league matches:
0.530 ms - persisted season table:
0.401 ms - player season stats:
0.945 ms - featured players by goals:
35.483 ms
The featured players path is the best example of the difference. Before the backfill, it was around 77 ms when fallback had to read raw data for most seasons. After the backfill, it was around 35 ms. That is not a universal claim that every page got dramatically faster. It is a specific improvement on aggregate-heavy, player-heavy paths.
The lesson
The best cache work does not start with Redis. It starts with deciding what the source of truth is.
For PLAYGRND, the pattern that feels right is:
- keep raw data normalized and auditable
- persist stable, deterministic aggregates for finished seasons
- snapshot common season responses when they are repeatedly read
- keep a recompute path for every derived value
- fall back to raw queries when derived state is missing
- measure query plans with tools, not vibes
This is the kind of backend work that rarely looks impressive from the outside. It does not add a new screen. It does make the product feel more predictable. The public sports record stays fast, but I still know where the numbers came from and how to rebuild them.