A year of any hourly report is 8,760 rows. Ten reports at hourly grain, a full year, is under 90,000 tiny rows, fewer than one busy afternoon of raw events. That is the case for aggregating at write time, and the rest is arithmetic.
The move is small. Keep one row per metric per hour, and when an event arrives, bump its counter instead of storing the event.
INSERT INTO hourly_views (hour, views)
VALUES (date_trunc('hour', now()), 1)
ON CONFLICT (hour) DO UPDATE SET views = views + 1;The report is built all along, one increment per event, by the same write that recorded it. Opening it reads a row. The raw event is gone, and the answer is already sitting on disk.
The reflex points the other way. ClickHouse is short for Clickstream Data Warehouse. It was built at Yandex to power Metrica, their web analytics platform, which grew from 200 million events a day to more than 25 billion by 2016. They open sourced it, it’s a marvel, and it became the default answer. Need analytics? Need reports? Store the raw stream and query it later.
The detail is buried in their own history. Metrica ran for years on pre-built aggregates. Yandex moved to querying raw events because, at their scale, the combinations of reports people wanted exploded past what rollups could hold. ClickHouse exists because Yandex outgrew aggregation, and the threshold is measured in billions of events a day.
The warehouse sells more than storage. It promises a generic engine, so you pipe every raw event in, often through something like Kafka, and query it any way you want later. That power is the real product, and it’s the deeper trap. Generic querying is for when you don’t know what you’ll ask. You do. The reports your product has to produce, a usage summary for you or a stats page for your customers, were designed up front and rarely change. You’re not running a research lab, you’re rendering the same reports on a schedule. Renting a generic engine to answer fixed questions is paying twice, the pipeline that feeds it and the engine that serves it, for a number a pre-computed total hands you with one SELECT.
Now look at the size of it. A healthy small product does 10,000 tracked events a day. Stored raw, that’s 3.65 million rows a year, most of a gigabyte, growing forever, re-scanned every time a report runs. Aggregated hourly, it’s 8,760 rows. About a megabyte. A thousand times less storage. Four hundred times fewer rows. The same reports, instant. The work happens once, when the event lands, and never again.
I run my analytics this way, half-hour rollups in SQLite, and every chart is a read of pre-computed rows on a $5 VPS.
There’s a second prize. Raw events stored forever are pure liability, a bill with a breach radius. Aggregates are small by construction and anonymous by arithmetic.
You are not Yandex. Be glad. They need the generic engine, because their questions are unknowable and their data has nine zeros. Yours fit on a phone.
So list the reports your product has to produce and give each one a counter. A question you didn’t plan for means a new counter, counting from the day you add it. That is the one cost of this design, and it’s the one worth paying.