When to Pre-Aggregate
Decide between live aggregation, materialised views, and downstream OLAP — based on freshness and cost.
When to Pre-Aggregate is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Three Strategies for Aggregate Queries
- Live — recompute every time
- Materialized — store and refresh periodically
- Triggered/Cached — update incrementally on every change
Live Aggregation
Simple and always fresh:
SELECT user_id, COUNT(*) FROM orders WHERE status = 'paid' GROUP BY user_id;When Live Is Fine
The query is fast enough (good indexes, small result, infrequent calls). Default to live — only optimise when you measure pain.
Materialized Aggregation
For expensive, "reasonably fresh" reports:
CREATE MATERIALIZED VIEW user_revenue_30d AS
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY user_id;
-- Refresh nightly:
REFRESH MATERIALIZED VIEW CONCURRENTLY user_revenue_30d;Triggered / Incremental Aggregation
For real-time dashboards, maintain a summary table with triggers:
CREATE TABLE user_summary (
user_id BIGINT PRIMARY KEY,
order_count INT NOT NULL DEFAULT 0,
revenue NUMERIC(12,2) NOT NULL DEFAULT 0
);
CREATE FUNCTION incr_summary() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_summary (user_id, order_count, revenue)
VALUES (NEW.user_id, 1, NEW.total)
ON CONFLICT (user_id) DO UPDATE
SET order_count = user_summary.order_count + 1,
revenue = user_summary.revenue + EXCLUDED.revenue;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_summary AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION incr_summary();Tradeoffs
| Strategy | Freshness | Write Cost | Read Cost |
|---|---|---|---|
| Live | Instant | None | High |
| Materialized | Stale | Refresh batch | Low |
| Triggered | Instant | Per write | Low |
Pick by Read/Write Ratio
- High writes, occasional reads → live (or batched mat view)
- High reads, moderate writes → materialised view
- High reads AND writes, freshness critical → triggered summary
External Pre-Aggregation
For warehouse-scale analytics, push aggregation to:
- OLAP databases (ClickHouse, Druid)
- dbt models on a separate warehouse
- TimescaleDB continuous aggregates (Postgres extension)
Summary Tables vs Matview
Custom summary tables let you incremental-update; matviews force full refresh. Trade development effort against operational simplicity.
Avoid Triggers on Hot Tables
Trigger-based summaries add write latency to every operation. For high-frequency tables (events, metrics), prefer batched matview refresh.
Beware Cache Invalidation
"There are only two hard things in CS." Triggered summaries are a cache. Bugs there manifest as wrong dashboard numbers. Add a daily reconciliation job that recomputes from source.
Materialize Multi-Stage Pipelines
Chain matviews: stage 1 aggregates events, stage 2 aggregates stage 1. Refresh in order.
Recap
Pre-aggregate when reads dominate cost.
- Live → simplest, always fresh
- Matview → expensive query, stale OK
- Triggered summary → always fresh, costs writes
- Pick by your read/write profile
Quick Check
You have a real-time dashboard that must show up-to-the-second user revenue. Which strategy fits best?
Frequently asked questions
Is the “When to Pre-Aggregate” lesson free?
Yes — the full text of “When to Pre-Aggregate” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “When to Pre-Aggregate”?
Decide between live aggregation, materialised views, and downstream OLAP — based on freshness and cost. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “When to Pre-Aggregate” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.