I run Fusionaly on one SQLite file. Six months of production traffic came to 14 megabytes.
Web analytics is supposed to need ClickHouse, or Kafka, or at least Postgres with TimescaleDB. Big tools for big data. Most sites don’t have big data. They have thousands of visits a day, maybe tens of thousands, and a dashboard nobody refreshes fifty times a second.
So the question was whether a plain relational database could do the job, and how simple the whole thing could get.
What I got wrong the first time
The first version of this post said a year at 100,000 visits a day works out to about 26 GB, and that half a million visits a day means 130 GB. Then it said a year of data fits in megabytes, two paragraphs earlier. Both sentences were in the same post, and they cannot both be right.
The 26 GB was raw-event math, every event kept at full width, forever. It is the bill you avoid, not the bill you pay.
Storage follows your dimensions, not your traffic
Every pageview updates counters in place. One row per half-hour bucket per distinct value, and an event that lands on an existing row increments it instead of appending anything:
INSERT INTO country_stats (website_id, country, hour, visitors_count, page_views_count)
VALUES (?, ?, ?, ?, 1)
ON CONFLICT (website_id, country, hour) DO UPDATE SET
visitors_count = country_stats.visitors_count + ?,
page_views_count = country_stats.page_views_count + 1;Count what that produces. Site totals get one row per half hour: 48 a day, 17,520 a year. Countries, browsers, operating systems and device types get one row per distinct value per bucket, and there are only so many browsers. Pages and referrers scale with how many distinct pages and referrers you have, never with how often they are hit.
Ten times the visitors on the same set of pages is the same number of rows, with bigger numbers in them. That is the property worth understanding, because it is what makes one file enough.
The events still have to land somewhere
Writes are the part SQLite is supposed to be bad at, since only one writer goes at a time. Analytics does not need instant consistency, so the fix is to spread the pressure across every layer instead of fighting the database.
The browser buffers. The SDK batches events every 200ms, retries three times with exponential backoff, keeps up to 100 events in localStorage when the network drops, and fires sendBeacon() with whatever is left when the tab closes.
The server pushes back. When SQLite is locked or busy, the ingest endpoint answers with a status of its own rather than pretending the write happened, and the SDK treats any failure the same way. Three attempts, each backing off twice as far as the last with a little jitter, then the event goes to localStorage to wait. Events arrive late instead of disappearing.
The database absorbs the rest. Events land in a staging table with a schema built for fast inserts, and a background job drains it in batches into the aggregates above. The file runs in WAL mode, so the dashboard’s reads never queue behind the writer. Under load the dashboard can lag a few minutes. Nobody trading stocks is reading it.
The staging rows are the expensive ones, with the raw URL, the user agent, client hints, and several indexes. A cleanup job runs every 24 hours and deletes the processed ones after 90 days. So the wide rows are temporary and the small ones are permanent, which is the opposite of how a warehouse treats them.
One process. One file. No Redis queue, no Kafka buffer, no worker cluster. The coordination lives in code instead of infrastructure.
Could this keep up at a million visits a day? Maybe. If you are there, you have a team and you can afford managed Postgres, and you are not who I built it for. This is for the rest of us, at 10,000 visits a day, where the honest answer is that the dashboard reads rows that were finished the moment the event arrived.