Analytics that does not bloat the database

My own web analytics writes a raw row for every visit and every click. After a while that becomes the biggest table in the database, while nobody needs the raw rows any more, only the summary.

The daily summary

At the end of every day I summarise the day into a small table: visitors, page views, sources, the checkout funnel, errors. I store it by dimension, key and metric, so one table holds many kinds of data and the rows stay small.

A calculation that can be re-run

The rollup is idempotent: recalculating a day replaces that day's earlier rows in one transaction. If something slips, or events arrive late, I simply run it again. That is why I summarise yesterday both after midnight and again before dawn.

// Idempotent: re-running a day replaces that day's rows in one transaction.
DB::transaction(function () use ($date, $rows) {
    DB::table('analytics_daily_rollups')->where('date', $date)->delete();
    foreach (array_chunk($rows, 500) as $chunk) {
        DB::table('analytics_daily_rollups')->insert($chunk);
    }
});

// ... and in the prune service: raw rows are only deleted for days that already have a summary.
foreach ($missing as $date) {
    if ($budget <= 0) {
        // No budget left: delete only up to the first day without a summary.
        $safeCutoff = Carbon::parse($date)->startOfDay();
        break;
    }
    if (! $dryRun) {
        $this->rollup->rollupDay(Carbon::parse($date));
    }
    $budget--;
}

do {
    $n = DB::table($table)->where($column, '<', $safeCutoff)->limit(self::CHUNK)->delete();
} while ($n > 0);

An excerpt of real code, anonymised.

Delete only after the rollup

I delete raw rows after the retention period, but only for days that already have a summary. If a day to be deleted has none yet, I build it first. A run catches up at most ninety days, and if that budget runs out, it only deletes up to the first day without a summary.

The default mode is a dry run, the actual deletion needs its own switch, and it goes in chunks so the table is not locked. I can throw away the raw row at any time, but not the summary.

More articles