Dashboards That Stay Fast: Rollup Tables Over Raw Events

Analytics dashboards degrade in a specific way. They are fast in development, acceptable at launch, and nine seconds per page a year later. Nothing broke — the query just started scanning a table that grew while nobody watched.

Adding indexes buys you a quarter. The structural fix is to stop querying raw events at read time.

Separate the write model from the read model

Raw events are append-only, high-volume and shaped for writing. Dashboards want small, pre-grouped, shaped for one specific chart. Those are different tables.

CREATE TABLE campaign_daily_rollups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    touchpoints INT UNSIGNED NOT NULL DEFAULT 0,
    unique_leads INT UNSIGNED NOT NULL DEFAULT 0,
    attributed_pipeline DECIMAL(14,2) NOT NULL DEFAULT 0,
    UNIQUE KEY campaign_date (campaign_id, date)
);

The dashboard reads this table. A year of data for one campaign is 365 rows, and the query plan does not change as the event table grows.

Rebuild incrementally, not nightly-from-scratch

A full nightly rebuild is simple until it takes four hours. Rebuild only the days that changed, and make the operation safe to repeat:

public function handle(): void
{
    Event::query()
        ->select('campaign_id', DB::raw('DATE(occurred_at) as date'))
        ->selectRaw('COUNT(*) as touchpoints')
        ->selectRaw('COUNT(DISTINCT lead_id) as unique_leads')
        ->selectRaw('SUM(attributed_value) as attributed_pipeline')
        ->whereBetween('occurred_at', [$this->from, $this->to])
        ->groupBy('campaign_id', 'date')
        ->chunkById(1000, function ($rows) {
            CampaignDailyRollup::upsert(
                $rows->toArray(),
                ['campaign_id', 'date'],
                ['touchpoints', 'unique_leads', 'attributed_pipeline'],
            );
        });
}

The unique key plus upsert makes the job idempotent: run it twice for the same window and the numbers do not double. That property matters more than the speed, because it lets you re-run any window without thinking about it — after a late-arriving batch, after a bug fix, after a backfill.

Make staleness visible instead of hiding it

Pre-aggregated data is stale by definition. Users forgive that easily as long as you tell them; they lose trust immediately when they find out on their own by comparing to the CRM.

Show the rollup timestamp on the dashboard — Updated 4 minutes ago — and give impatient users a refresh that queues a rebuild for the visible window. In practice that single line of text ends more "is this number right?" conversations than any amount of query tuning.

When not to do this

If your event table is under a few million rows and growing slowly, indexes and a covering query are cheaper and simpler. Rollups introduce a second thing to keep correct. Reach for them when read latency is a product problem, not because the architecture diagram looks better with them.