Materialized Views

The Core Trade

 expensive aggregate queried constantly:

 SELECT city, DATE(created_at), COUNT(*), AVG(fare)
 FROM trips WHERE created_at > now() - '30 days'
 GROUP BY city, DATE(created_at);
 → scans millions of rows, EVERY dashboard refresh

 MATERIALIZED VIEW: compute ONCE, store as a table,
 refresh on schedule or incrementally:

 CREATE MATERIALIZED VIEW city_daily AS SELECT ...;
 reads become trivial index scans of the small result.

The Spectrum of Materialization

 FULL materialized view (Postgres style):
   REFRESH MATERIALIZED VIEW city_daily;
   → full recompute; simple; staleness = refresh interval
   CONCURRENTLY option avoids read-blocking during refresh

 INCREMENTAL views:
   only changed rows processed (Timescale continuous aggregates,
   Materialize, ksqlDB tables, ClickHouse materialized columns)
   → near-real-time freshness at scale

 APP-MAINTAINED summary tables:
   triggers/CDC update counters and rollups in transactions
   maximum control, maximum responsibility

 all four = same idea with different freshness/complexity dials.

Choosing What to Materialize

 candidates share a signature:

 □ aggregation over MANY rows producing FEW rows
   (30-day × 200-city → 6000 rows from 100M)
 □ stable query shape (same GROUP BY repeatedly)
 □ tolerance for staleness (dashboards, reports, badges)

 anti-candidates:
 ✗ point lookups (indexes already solve)
 ✗ highly personalized queries (low reuse per computation)
 ✗ real-time correctness needs (freshness gap unacceptable)

Refresh Strategy Design

 staleness vs load tradeoff:

 REFRESH cadence patterns:
 - time-based:      every 5min/1h — predictable load
 - event-based:     after N writes / specific triggers
 - tiered rollups:  1-min rolls feed 1-h rolls feed daily
                    (TSDB downsampling pattern applied generally)

 load protection:
 - refreshes are QUERIES TOO — throttle, run off-peak,
   monitor like any heavy statement
 - concurrent-refresh to avoid reader blocking (Postgres)
 - incremental > full wherever available; full refreshes
   at 100M+ rows become their own incidents

Materialized Views + Sharding

 cross-shard aggregates get a second life here:

 global "top cities" can't be one SQL query across shards.
 options:
 - per-shard local MVs + coordinator merge layer
 - CDC → single analytics node builds global MVs
 - warehouse handles it entirely (often right answer!)

 rule: MVs fix WITHIN-node repetition;
 DISTRIBUTED rollups need pipeline architecture beyond them.

Interview Framing

Dashboard/analytics beats score with MVs: identify the repeated-aggregation signature, choose refresh strategy WITH staleness budget (“5-minute concurrent refresh fits this dashboard”), mention incremental options by name for scale. The sharding caveat (global aggregates need pipelines) prevents the classic mistake of prescribing MVs past their jurisdiction.