PLAYGRND has a common sports-product problem: the data is naturally relational, but the public pages often need aggregates.

The raw layer should stay normalized. Matches, player appearances, goals, cards, and events make sense as separate records. That shape is good for ingestion, verification, corrections, and auditability. It is not always good for the hot path.

If every season page, player page, or featured player list repeatedly rebuilds the same result from matches, match_players, and match_events, the system pays that cost on every request. That matters even more in an SSR product: the frontend calls the Go API, Go reads Postgres, and the user waits for work that could often have been prepared earlier.

So we recently optimized the PLAYGRND Go API and Postgres layer for several repeated paths:

  • season standings
  • scorer lists
  • player season stats
  • featured players
  • global upcoming and finished match lists

The goal was not to cache everything. The goal was to move expensive repeated aggregation away from request paths where it does not need to live.

Principle

Raw data remains the source of truth.

That boundary matters. Derived data should not become a second version of the truth that nobody knows how to rebuild. If a raw record changes, derived state needs a clear recompute mechanism. If derived state does not exist yet, the endpoint should not fail.

For PLAYGRND, the split is straightforward:

  • active seasons still change
  • finished seasons are mostly stable
  • aggregates for finished seasons can be computed and stored
  • cache is useful for frequent snapshot responses, but it should not be the only source

That led to a two-layer approach.

The first layer is persisted derived tables in Postgres for data that is deterministic and stable. The second layer is Redis snapshots for season responses that are read frequently and can be rebuilt quickly.

Behind that, there is still a fallback: when derived rows are missing, the endpoint returns to the raw match_players and match_events path. That is slower, but the product remains available and correct.

Implementation

The change touched several parts of the backend layer.

We added and tightened Postgres indexes for hot paths: global match lists, season standings, scorers, and player-heavy queries. That is the foundation. If the query planner does not have good indexes, cache only hides the problem until the first miss.

For finished seasons, we introduced persisted tables for standings, scorers, and player season stats. These tables do not replace raw data. They are a derived view of state that rarely changes and can be recomputed.

Season endpoints also received Redis snapshot caching. That removes repeated work from the Go API and Postgres for responses that are requested often in the same shape.

Recompute is deliberately controlled. An internal maintenance path can trigger refresh and recompute when raw data has changed or when a backfill is being run. For player season stats, recompute is transactional: existing derived rows are deleted and new rows are inserted in the same transaction. That avoids a half-refreshed state where part of a season reads an old picture and another part reads a new one.

We also added a read-only EXPLAIN ANALYZE helper for the hot paths. That is not something to run permanently in production, but it is useful as an operations tool when you need to inspect the real query plan in production-like conditions.

Backfill and measurements

Before the production backfill, we took a database backup. It is the boring step until it is skipped, and then it becomes the most important step that did not happen.

The backfill then populated derived state for finished seasons:

  • 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 remained without derived player rows because they do not have raw player rows

After the backfill, we checked the hot paths with the read-only EXPLAIN ANALYZE helper:

  • 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

These numbers do not mean the whole product is now universally faster. That would be the wrong interpretation.

The largest effect is on aggregate-heavy and player-heavy surfaces. Featured players was around 77 ms before the backfill when the fallback had to read raw data for most seasons. After the backfill it was around 35 ms, because it no longer had to rebuild the same aggregate from raw tables for every finished season.

Most list hot paths are now sub-millisecond. That is a good result, but the more important result is stability: endpoints no longer depend on expensive raw fallback when they do not need to, while still keeping that fallback when derived state is missing.

Lessons

Cache is not architecture. Cache is a tool.

If cache is placed over an unclear model, the result is faster confusion. If the system first separates source of truth from derived state, cache becomes much safer.

For PLAYGRND, the practical rules were:

  • do not cache blindly
  • cache what is stable or expensive
  • treat finished seasons as good candidates for derived state
  • keep active seasons shorter-lived and more conservative
  • every derived state needs recompute
  • fallback should exist, even if it is slower
  • use EXPLAIN ANALYZE as a measurement tool, not permanent runtime overhead
  • take a backup before a larger production backfill

This kind of optimization is not flashy. It does not create a large new UI surface. But it changes the character of the product. Frequently opened pages become more predictable, aggregates stop being a constant runtime cost, and the backend gets a clearer distinction between raw records, derived state, and cache.

That matters for a product like PLAYGRND, where the public sports record needs to be fast while staying verifiable. Speed only helps if the system knows where the data came from and how it can be computed again.