Read Models, Summary Tables and Materialized Views

Scenario

Example app: a SaaS analytics dashboard shows each account's revenue by month, top products and order counts. Every page view aggregates the account's entire orders/order_items history.

What you see in production

The dashboard is 8% of requests but 60% of database time. Big accounts' dashboards take 6 seconds; Monday mornings (everyone opens the dashboard) overload the primary.

The naive code

SELECT date_trunc('month', o.created_at) AS month, sum(oi.quantity * oi.unit_price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.account_id = $1
GROUP BY 1 ORDER BY 1;

(OrderItem.objects.filter(order__account_id=account_id)
 .annotate(month=TruncMonth("order__created_at"))
 .values("month").annotate(revenue=Sum(F("quantity") * F("unit_price"))))

Reproduce it

A summary table kept up to date by a trigger, on the real SQLite sample database: reads become a tiny lookup.

CREATE TABLE monthly_revenue (month TEXT PRIMARY KEY, revenue REAL NOT NULL DEFAULT 0);
INSERT INTO monthly_revenue
SELECT strftime('%Y-%m', o.order_date), round(sum(oi.quantity * oi.unit_price), 2)
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled' GROUP BY 1;

CREATE TRIGGER add_item_revenue AFTER INSERT ON order_items
BEGIN
  INSERT INTO monthly_revenue (month, revenue)
  SELECT strftime('%Y-%m', order_date), NEW.quantity * NEW.unit_price FROM orders WHERE order_id = NEW.order_id
  ON CONFLICT (month) DO UPDATE SET revenue = round(revenue + excluded.revenue, 2);
END;

INSERT INTO order_items VALUES (12, 6, 1, 329.00);            -- a new sale in June
SELECT * FROM monthly_revenue ORDER BY month;                  -- dashboard read: no aggregation

month | revenue
2026-01 | 167.98
2026-02 | 561.38
2026-03 | 114.99
2026-04 | 747
2026-05 | 131.45
2026-06 | 431

Root cause

The dashboard recomputes the same aggregates from raw rows on every view, so the cost grows with history size × views. Reads that are expensive to compute but change slowly should be precomputed.

The fix

Pick by freshness need:

CREATE MATERIALIZED VIEW account_monthly_revenue AS
SELECT o.account_id, date_trunc('month', o.created_at) AS month, sum(oi.quantity * oi.unit_price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY 1, 2;
CREATE UNIQUE INDEX ON account_monthly_revenue (account_id, month);   -- required for CONCURRENTLY
-- every 5 minutes (cron / pg_cron); readers aren't blocked during the refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY account_monthly_revenue;

with transaction.atomic():
    item = OrderItem.objects.create(order=order, product=product, quantity=q, unit_price=p)
    month = order.created_at.date().replace(day=1)
    MonthlyRevenue.objects.update_or_create(account=order.account, month=month,
                                            defaults={"revenue": F("revenue") + q * p})  # or raw ON CONFLICT

@TransactionalEventListener(phase = TransactionPhase.AFTER_COMMIT)
public void on(OrderPaid e) {
    jdbc.update("INSERT INTO account_monthly_revenue (account_id, month, revenue) VALUES (?, ?, ?) "
        + "ON CONFLICT (account_id, month) DO UPDATE SET revenue = account_monthly_revenue.revenue + EXCLUDED.revenue",
        e.accountId(), e.month(), e.amount());
}

Trade-offs

• Materialized views are simple but stale between refreshes and recompute everything; CONCURRENTLY needs a unique index and takes longer.
• Summary tables updated on write make writes slower and can become hot rows (one row per account per month) — see Hot Rows.
• Event-driven read models decouple the write path but need idempotent consumers and a way to rebuild from history.
• Always keep the raw data; summaries are disposable and rebuildable.

Detect it in production

• Top statements by total time dominated by aggregates over large ranges.
• Freshness metric: "age of the newest data in the read model"; alert when refreshes fail.
• Periodic reconciliation: compare a sample of summary rows with a fresh aggregate.

Key points

• Precompute expensive, slowly changing reads.
• Choose materialized views, summary tables or event-driven read models by freshness and write cost.
• Make read models rebuildable and monitor their freshness.