Materialized Views and Pre-Aggregation

A dashboard shows page views per minute. Each refresh runs a query that scans millions of raw events and groups them by minute. Fine for a small site; at scale, with hundreds of people refreshing, it melts the database. A materialized view fixes this by computing the aggregate as data arrives and storing the result, so a dashboard read becomes a quick lookup instead of a scan.

The problem: queries that don't scale

SELECT date_trunc('minute', ts) AS minute, COUNT(*)
FROM page_events
WHERE ts > NOW() - INTERVAL '1 hour'
GROUP BY minute;

At 10 million events a day, the last hour holds roughly 400,000 rows. Fifty dashboard users refreshing every 10 seconds means 5 queries per second, each scanning 400,000 rows: 2 million row scans per second for one chart. An index on the timestamp only narrows the range; the GROUP BY still reads every matching row. Query cost keeps growing with data volume.

Compute on write, not on read

A regular view is a saved query, recomputed on every read. A materialized view stores the computed result, so the work moves from read time to write time. In streaming systems, the stream processor keeps running aggregates updated by every event, and when someone asks, the answer is already there. See precomputation.

1. Events flow from the source: page-view events (page ID, user ID, timestamp, metadata) land in Kafka.
2. The stream processor windows them: 1-minute tumbling windows grouped by (page_id, minute).
3. Window results go to a fast store: when a window closes, write key page:{id}:minute:{timestamp} → count to Redis.
4. The dashboard reads the store: the last hour is 60 key lookups, microseconds each, no matter how many events happened.

import json
from collections import Counter
from kafka import KafkaConsumer
import redis

r = redis.Redis()
consumer = KafkaConsumer("page-views", bootstrap_servers="localhost:9092",
                         group_id="views-per-minute", value_deserializer=json.loads)
counts, current_minute = Counter(), None

for msg in consumer:
    e = msg.value                                   # {"page_id": 42, "ts": 1760950000}
    minute = e["ts"] // 60 * 60
    if current_minute is not None and minute > current_minute:   # window closed
        pipe = r.pipeline()
        for page_id, n in counts.items():
            key = f"page:{page_id}:minute:{current_minute}"
            pipe.incrby(key, n); pipe.expire(key, 2 * 3600)      # keep 2 hours
        pipe.execute()
        counts.clear()
    current_minute = max(current_minute or minute, minute)
    counts[e["page_id"]] += 1
# Simplified: real engines use event-time watermarks so late events still count.

def last_hour(page_id, now_minute):                 # dashboard read: 60 lookups
    keys = [f"page:{page_id}:minute:{now_minute - 60 * i}" for i in range(60)]
    return [int(v or 0) for v in r.mget(keys)]

The dashboard query goes from "scan 400,000 rows" to "fetch 60 keys". Databases offer the same idea natively: CREATE MATERIALIZED VIEW … ; REFRESH MATERIALIZED VIEW in PostgreSQL, incrementally maintained views in ClickHouse, Materialize or RisingWave. See database optimization.

The trade-offs

• Storage grows with every dimension. Page × minute is manageable. Add region, device, browser and experiment variant and keys multiply: 10,000 pages × 50 regions × 5 devices × 10 variants = 25 million keys per minute, about 1.5 billion per hour. Pre-aggregating every possible slice is impractical.
• Late events complicate updates. An event stamped 10:05 arrives at 10:08, after its window closed. Ignore it (simple, but counts run low), update the view (correct, but consumers must handle changing values), or wait longer before closing windows (more accurate, slower). It depends on whether accuracy or freshness matters more.
• Pre-aggregation locks in the questions. Aggregated by page and minute? "Hourly views by device" now needs new code and time for data to accumulate. You trade query flexibility for query speed.

The 80/20 approach

A few dashboards usually account for most query load: request rates, error counts, active users. Pre-aggregate those. For ad-hoc exploration ("click rate for German mobile users last Tuesday?"), keep raw events in a warehouse, where seconds of latency is fine. That two-tier design gives instant answers for the metrics everyone watches and flexibility for questions nobody has asked yet. See real-time analytics architecture.