Start free trial

Blog ·

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. 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.

Demo site · about 250,000 events a year Before After Before: 2.4 seconds After: 80 milliseconds 2.4 s 80 ms 30× faster At 10× the traffic · about 2.5 million a year Before After Before: 35 seconds After: 0.5 seconds 35 s 0.5 s 70× faster
Time to load a 12-month dashboard. Each pair has its own scale.

We could have bought a bigger database, but the bill would grow with our biggest customer. We could have moved to ClickHouse, but its smallest managed plan cost more than the rest of our hosting combined. We could have cached responses, but every period ends today, so they'd go stale within a minute. Instead, we count each day once and write the answer down.

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 whose secret is destroyed at midnight. The same person on two days is two visitors by design, and sessions never cross midnight. So every number on the dashboard is already a sum of days.

One table for every daily number

CREATE TABLE rollups (
    site_id    BIGINT NOT NULL,
    dim        TEXT   NOT NULL,  -- '' for totals, else 'source', 'page', 'country'...
    local_date DATE   NOT NULL,
    value      TEXT   NOT NULL,  -- 'google.com', '/pricing', 'DE'
    visitors   BIGINT NOT NULL DEFAULT 0,
    pageviews  BIGINT NOT NULL DEFAULT 0,
    sessions   BIGINT NOT NULL DEFAULT 0,
    duration   BIGINT NOT NULL DEFAULT 0,  -- seconds, summed over sessions
    PRIMARY KEY (site_id, dim, local_date, value)
);

CREATE TABLE rollup_marks (
    site_id BIGINT PRIMARY KEY,
    through DATE  -- rollups are complete through this day
);

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.

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 only rolls up a day three days after its UTC date, once it has ended everywhere. A query reads the settled days from rollups and the last few from raw events, then adds them together.

Last 12 months last two weeks, zoomed in 362 settled days: read from rollups Last 3 days: read from raw events Settled days: rollups one small row per day per value Last 3 days raw events rollup_marks.through
A 12-month query reads 362 days of small rollup rows, and only the last three days of raw events.
SELECT local_date, value, visitors, pageviews FROM rollups
WHERE site_id = $1 AND dim = 'source' AND local_date BETWEEN $2 AND $3
  AND local_date <= (SELECT through FROM rollup_marks WHERE site_id = $1)
UNION ALL
SELECT local_date, referrer_source, count(DISTINCT visitor_hash), count(*) FILTER (WHERE name = 'pageview')
FROM events
WHERE site_id = $1 AND local_date BETWEEN $2 AND $3
  AND local_date > coalesce((SELECT through FROM rollup_marks WHERE site_id = $1), '-infinity')
GROUP BY local_date, referrer_source

Wrap that in an outer GROUP BY value with sum(), 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 can replay events days late. The transaction that stores them also moves rollup_marks.through back to before the earliest one, so those days are read from events until the worker redoes them. Both take the same row lock, so nothing is counted twice or missed.
  • Know what rollups can't answer. Filtered views (Germany and mobile), prefix Goals like /blog/* that count a visitor once across many pages, and hourly or realtime charts 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.