From 35 seconds to half a second: 70× faster dashboards in plain Postgres
Pick "Last 12 months" on an analytics dashboard and Postgres reads every event of the year, once for every panel. At scale, ours took 35 seconds. Now it takes half a second on the same database, thanks to one table and one property of our numbers that most people forget to check.
Every panel re-read the year
A Foresite dashboard has about 18 panels, and each one is its own GROUP BY over the events in the chosen period: SQL for "sort the events into piles and count each pile", like pageviews per country or visitors per page. That's fine for a month. For a year, the demo site's 250,000 events were read eighteen times over: 2.4 seconds on a laptop, and 35 seconds at ten times the traffic.
We could have bought a bigger database, but the bill would grow with our biggest customer. We could have moved to ClickHouse, a column store that keeps each column together on disk so it can add one up across billions of rows in a blink, but its smallest managed plan cost more than the rest of our hosting combined. We could have cached responses (saved each finished answer and handed it out again), but every period ends today, so they'd go stale within a minute. Instead, we count each day once and write the answer down. That pre-counted summary is called a rollup: one day's events, squashed into a handful of totals.
First, check that your numbers add up
A daily rollup only works if the number for a range is the sum of the numbers for its days. Pageviews always add up. Unique visitors usually don't. Someone who visits on Monday and Tuesday is one visitor across the two days, but two in the daily counts.
For us, the sum is the right answer, on purpose. Foresite doesn't use cookies: we recognise a visitor within one day using a hash, a scrambled fingerprint of their IP address and browser mixed with a secret, and that secret is destroyed at midnight. After that, nobody (us included) can match today's fingerprints to yesterday's. The same person on two days is two visitors by design, and sessions (one visit's run of pageviews) never cross midnight. So every number on the dashboard is already a sum of days.
One table for every daily number
table rollups:
site_id
dim // '' for totals, else 'source', 'page', 'country'...
local_date
value // 'google.com', '/pricing', 'DE'
visitors, pageviews, sessions
duration // seconds, summed over sessions
// one row per site, dim, day and value
table rollup_marks:
site_id
through // rollups are complete through this day
Each row reads like "site 7, by source, 3 March, google.com: 40 visitors". dim (short for dimension) says which breakdown the row belongs to, and an empty dim holds the day's overall totals. rollup_marks is one bookmark per site: everything up to through has been rolled up, everything after hasn't.
Store sums, never averages. Total duration and session counts add up across days, so you can divide at the very end. The average of two averages is a number, just rarely the one you wanted: one 10-minute visit on Monday and 99 one-minute visits on Tuesday average out to 5.5 minutes, when the real answer is about 1.
Settled days from rollups, recent days from events
Today isn't over, so it can't be rolled up yet, and "today" depends on where you're standing: midnight in Tokyo is 5 a.m. yesterday in Hawaii. So a background worker (a job that runs on its own schedule, away from anyone's page load) only rolls up a day three days after its date in UTC (the world's reference clock), once it has ended everywhere. A query reads the settled days from rollups and the last few from raw events, then adds them together.
mark = rollup_marks.through for this site
SELECT local_date, value, visitors, pageviews
FROM rollups // settled days: small, ready-made rows
WHERE dim = 'source' AND local_date in range AND local_date <= mark
UNION ALL
SELECT local_date, referrer_source, count(distinct visitors), count(pageviews)
FROM events // the last few days, counted from scratch
WHERE local_date in range AND local_date > mark
GROUP BY local_date, referrer_source
UNION ALL stacks the two sets of rows into one list. Wrap that in an outer GROUP BY value with sum(), which adds up each source's days, and a year's top sources come from 365 small rows per source plus three days of events.
Three rules that keep it honest
- Write the per-day SQL once. The worker, the recent-days half of every query, and filtered queries all run the same "this number per day, from events" SQL. Two copies would drift apart, and one day the year wouldn't add up to the sum of its months.
- Late data moves the mark back. After an outage, our collector (the server that receives events from websites) can replay events days late. The transaction that stores them, a bundle of database changes that all happen or none do, also moves rollup_marks.throughback to before the earliest one, so those days are read from events until the worker redoes them. Both take the same row lock first, a claim on that site's mark row that makes the other wait its turn, so they can't interleave and nothing is counted twice or missed.
- Know what rollups can't answer. Filtered views (Germany and mobile) need every combination, but the table only has one breakdown per row. Prefix Goals like /blog/*count a visitor once across many pages, and adding up per-page rows would count them once per page. Hourly and realtime charts need finer slices than a day. All of these still read raw events, exactly as fast as before.
The result
The demo's 12-month dashboard went from 2.4 s to 80 ms. At ten times the traffic, it went from 35 s to 0.5 s. We still run and back up just one database, and the column store can wait until there's traffic to pay for it.
Try "Last 12 months" on the public demo. Don't bother putting the kettle on.