Analytics

Disclaimer: This blog post is automatically generated from project documentation and technical proposals using AI assistance. The content represents our development journey and architectural decisions. Code examples are simplified illustrations and may not reflect the exact production implementation. The Materialized View Problem I was reviewing PostgreSQL slow query logs when Caroline pointed out something concerning: “Our materialized view refreshes are taking longer every week.” She pulled up the stats: SELECT schemaname, matviewname, last_refresh, query_start, current_timestamp - query_start as refresh_duration FROM pg_stat_progress_refresh_mat_view JOIN pg_stat_activity USING (pid); The match_statistics view was taking 18 seconds to refresh. The player_momentum view was at 12 seconds. “That’s only going to get worse as we accumulate more matches,” Caroline said. “And these views lock tables during refresh.” Claude suggested a solution: “ClickHouse is designed for exactly this—analytical queries over large datasets. Keep PostgreSQL for transactions, use ClickHouse for analytics.” We decided to migrate. ...

Disclaimer: This blog post is automatically generated from project documentation and technical proposals using AI assistance. The content represents our development journey and architectural decisions. Code examples are simplified illustrations and may not reflect the exact production implementation. Table of Contents The Query Complexity Problem Three-Tier Improvement Strategy Tier 1: Zero-Risk Quick Wins Tier 1.5: Match Timing Lookup Table Tier 2: Shot Sequence Linking Tier 3: Score Denormalization Performance Impact Key Learnings The Query Complexity Problem When Caroline and I first built the ClickHouse analytics for Scores, we followed a pure event sourcing approach: store raw events, derive everything in queries. This kept writes simple but made reads complex. ...