Small pattern that saved me a whole pipeline.
I compute a score from an external API that is rate limited, so I cannot recompute on every request. Instead of a cache layer plus a separate leaderboard job, I write every computed result into one table: username unique, score as int, raw stats as jsonb, and a computed_at column. A request does get-or-compute. If the row is younger than 12h I serve it, otherwise I refetch and upsert.
The part I like: the leaderboard is just SELECT ... ORDER BY score DESC over that same table. No second source of truth, nothing to keep in sync. A spike mostly hits rows that are already warm and fills the board for free.
Two things that bit me. First, my analytics event fired on both fresh and cached reads, so the more the thing spread the worse the funnel looked, because cached reads counted as new generations. Moved it to fire only on a real compute. Second, I wanted the board public but had to hide opted-out rows, so the partial index and the RLS policy filter on the same predicate (where not opted_out), otherwise the count and the visible rows disagree.
Anyone else collapse cache and read model into one table like this, or is there a reason I will regret it at scale?
The user shares a pattern where they use a single Postgres table to handle both caching and leaderboard functionalities. They describe how this approach avoids maintaining separate systems and discuss challenges faced, such as analytics miscounting and handling public visibility with RLS policies. The user seeks feedback on potential scalability issues.