← read more

Dashboard Queries That Don't Crawl: Rollups and Materialized Views

A dashboard that sums a year of rows on every load will feel slow no matter how you cache it. How to compute aggregates ahead of time with summary tables and Postgres materialized views, and the threshold where reporting outgrows a single database.

The Monday-morning dashboard problem

The pattern repeats on almost every SaaS with a stats page. All week nobody looks at it. Monday morning, the owner opens the dashboard and waits for a query that counts a year of events, sums revenue per month, and sorts the top ten customers. Second visit is fast if you cached it; first visit still hurts, and the row count only grows.

Caching treats the symptom, not the math. The real fix is to stop computing the same aggregate on every page load and compute it once, before anyone asks. There are three rungs on that ladder, and you should stop at the lowest one that works.

First, make the raw query honest

Before adding infrastructure, check the query itself. Most slow dashboard queries fail at the basics:

  • Group by day/week/month in SQL with DATE_TRUNC or GROUP BY, not in PHP, so ten thousand rows become thirty buckets before they cross the wire.
  • Bound the range by an indexed timestamp column. A "last 12 months" filter on created_at with an index turns a table scan into a range scan.
  • Avoid SELECT * over the full table when you need two columns for the aggregate. Reading every column costs IO for nothing.

These three moves often buy enough that you can stop here. If they don't, precompute.

Summary tables

A summary table is a table of pre-computed buckets you keep up to date yourself. The shape is boring on purpose:

Schema::create('daily_usage_rollups', function (Blueprint $t) {
    $t->date('day')->unique();
    $t->string('tenant_id')->index();
    $t->unsignedBigInteger('requests')->default(0);
    $t->unsignedBigInteger('tokens')->default(0);
    $t->unsignedBigInteger('cost_minor')->default(0);
    $t->primary(['day', 'tenant_id']);
});

A scheduled command rolls yesterday's rows up (and on first run, the whole history):

DB::table('daily_usage_rollups')->upsert(
    DB::table('usage_events')
        ->selectRaw("date_trunc('day', created_at)::date as day, tenant_id")
        ->selectRaw('count(*) as requests, sum(tokens) as tokens, sum(cost_minor) as cost_minor')
        ->whereBetween('created_at', [$from, $to])
        ->groupBy('day', 'tenant_id')
        ->get()
        ->map(fn ($r) => (array) $r)->all(),
    ['day', 'tenant_id'],
    ['requests', 'tokens', 'cost_minor'],
);

The dashboard then reads one small table and never touches the event rows. Upsert with a composite primary key makes reruns idempotent, so a job that fires twice does no damage. Cost is one extra write path to maintain, and the code is a dozen lines.

Materialized views

When the aggregate is static in shape but you don't want to write maintenance code, Postgres materialized views do it in SQL:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', paid_at) AS month, tenant_id, sum(amount_minor) AS total
FROM payments WHERE paid_at IS NOT NULL
GROUP BY month, tenant_id;

CREATE UNIQUE INDEX ON monthly_revenue (month, tenant_id);

From Laravel, create it in a migration with DB::statement, read it like a table, and refresh on a schedule:

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

That unique index is not optional: CONCURRENTLY requires it, and without it every refresh takes a lock that blocks dashboard reads. Schedule the refresh with your existing task scheduler, hourly or nightly depending on how fresh the numbers must look.

The tradeoff versus summary tables: less code, but the rebuild touches every row in the source, so at tens of millions of rows a full refresh gets slow and you end up writing incremental maintenance anyway, which is a summary table by another name.

Where this stops being enough

Rollups on a single Postgres handle reporting up to absurd sizes when the questions stay stable. The ladder ends when the questions become ad-hoc: users slicing by arbitrary dimensions, drilling down on demand, scanning years of detail interactively. That's a different product, and bolting it onto the app database is a mistake at any size. Until a client genuinely asks for that, summary tables plus one or two materialized views, refreshed on the scheduler and read in one indexed query, are the whole story.