Your dashboard has a chart that is about 1,200 pixels wide.
Behind it sits a table that grows by a few million rows a day. Somebody opens the page, picks "last 30 days", and expects to see request volume, cost, error rate and p95 latency — immediately, and again a second later when they drag across a spike to zoom in.
Somewhere between those two facts — millions of rows on one side, a few hundred visible values on the other — your architecture decides where the data gets smaller. Usually nobody makes that decision on purpose. It happens in the first version of the API, and it stays there until the dashboard gets slow.
This article is about making that decision deliberately. The question is simple to state:
How do we get from a hundred million raw events to the few hundred values the UI actually needs?
And the claim I want to back with measurements rather than opinions:
Performance of a data-heavy frontend starts long before React.
By the time a component renders, almost every important decision has already been made: what was stored, what was scanned, what was aggregated, what crossed the network, and what the browser had to do with it. Drawing 730 points is easy. Getting to the right 730 points is the actual engineering problem — and, as we'll see, a correctness problem as much as a speed problem.
The case study: an AI SaaS usage dashboard
To keep things concrete, imagine a product built on top of large language models. Every model call produces one event:
| Field | Example |
|---|---|
ts |
2025-06-12T14:03:27.481920Z |
user_id |
u_0004211 |
model, provider
|
atlas-mini, northwind
|
tokens_input, tokens_output
|
412, 187
|
latency_ms |
1269 |
cost_usd |
174 (micro-dollars) |
country |
DE |
status |
ok, error or timeout
|
The product team wants the usual dashboard: requests over time, cost, tokens, error rate, latency percentiles, breakdowns by model, provider, country and user, filters on all of them, and zooming from a year down to an hour.
None of this is specific to AI. Replace "model call" with "HTTP request", "payment" or "sensor reading", and you have an observability, fintech or IoT dashboard: a high-volume, append-only stream of events and a UI that needs a compact summary of it. LLM usage is just a convenient example with interesting metrics — money, tokens, heavy-tailed latency — and plenty of dimensions to slice by.
The path every number travels
Every value on the chart takes the same route:
Each arrow is a place where the data can get smaller — or not. The interesting question is where the reduction happens, because every step to the left of that point pays for the full volume, and every step to the right only sees the result.
The browser doesn't need events. It needs answers.
A 30-day chart of p95 latency needs roughly one value per pixel column, maybe with a min/max band. It does not need the thousands of individual requests behind each of those values. The rest of this article follows the data along this path and measures what changes when the reduction moves.
A small lab instead of opinions
Arguments about dashboards and databases tend to be settled by whoever has the most confident anecdote. To avoid that, I built a small, reproducible lab (linked at the end).
One dataset. A generator produces a year of fictional LLM events with realistic structure: daily and weekly seasonality, growth, traffic spikes, heavy-tailed latency that differs per model, provider incidents with bursts of errors, and a few heavy users. The same seed always produces the same events, at 100 thousand, 1 million, 10 million and 100 million events.
Two independent axes. Every result is a cell in a matrix: the engine — PostgreSQL, DuckDB or ClickHouse — and the strategy, which says where and how the data is reduced: naive, server-aggregation, an optimized physical layout, pre-aggregated rollups, and auto, a planner that chooses among them. The goal is not to crown the fastest database. It is to see how the placement of computation changes the whole system, regardless of the engine underneath.
One question, one identity. Every request is normalized into a canonical form — an exact time range, a timezone, sorted filters, the bucket size, a result budget — and hashed. When two engines answer "the same question", their responses carry the same hash, so it can be checked that they really answered the same question.
Measurements were taken on one machine with warm caches: each cell is the median of repeated requests after warm-up (two warm-ups and seven repetitions on the larger datasets), cells run in shuffled order, every comparable response is compressed with gzip at the same fixed level, and browser timings come from a production build in Chromium. Every request has 120 seconds, and every cell has an explicit outcome — completed, timeout, failed — instead of a missing value. Every number is traceable to a recorded run, and the harness rejects any run it cannot prove belongs to the engine, strategy and dataset it claims. The full methodology lives in the repository's README.
The browser receives too much
In the lab:
naive— the browser receives raw events and aggregates them in JavaScript.
The dashboard has to show requests, cost and p95 latency over time. The fastest way to get there is to fetch the events and compute the chart in the browser.
That is not a strawman. It is what most of us build first, for good reasons: the backend already has an endpoint that returns events, JavaScript is perfectly good at grouping by time bucket, the first version ships in an afternoon, and every new chart is a frontend-only change. And the problem never arrives as a failure. It arrives gradually, as a dashboard that was instant in March and takes several seconds by September.
In the lab the naive path streams events as NDJSON, and the browser parses them, assigns them to buckets and sorts latencies for the percentiles. (One detail worth stealing: the stream ends with a trailer line, because once a streamed response has started, an error can only be reported inside the stream — and a stream without a trailer must be treated as truncated, not as a dip in the chart.)
What it costs
Here is the one-year view on the smallest dataset, 100,000 events:
| One-year view, 100k events | naive | server-side aggregation |
|---|---|---|
| Events sent to the browser | 100,000 | 0 |
| Values on the chart | 730 buckets | 730 buckets |
| Payload, uncompressed | 24.4 MB | 82 kB |
| Transferred with gzip | 3.36 MB | 15 kB |
| Total time, request to result | ~1.36 s | ~51 ms |
The cost grows with the range the user looks at: a 30-day view moved 7,781 events (266 kB transferred), a single day 288. And in production it grows with every month of history you keep.
The 1.36 seconds themselves say more about this implementation than about the architecture: a separate profile spent about 0.53 s serializing events in Python and 0.28 s compressing them — work in the API layer, which is the same for every engine. What does not depend on implementation is the volume: to draw 730 points, 100,000 events were read, 24.4 MB produced and 100,000 objects processed in the browser.
Compression is not reduction
Gzip shrank the yearly payload about seven times. That is a real improvement, and it is tempting to stop there. But the database still returned 100,000 rows, the server still serialized 100,000 objects, the browser still parsed all of them — and the chart still showed 730 values.
Compression reduces the size of the representation; aggregation reduces the volume of information.
A compressed list of 100,000 events still contains 100,000 events.
What happened as the data grew
In a real browser, the one-year view on 1 million events downloaded 1,000,000 events — 32.7 MB over the wire — and took 15 to 18 seconds to draw. The same chart from server-side aggregation arrived as 15 kB and was on screen in 63 to 495 ms, depending on the engine.
At 10 million events the naive one-year view did not finish within two minutes on any engine. At 100 million, neither did the one-month view; only the one-day view still completed, by moving 287,370 events (8.9 MB on the wire) to draw 288 points. Every engine failed at the same place, because what grew was the number of events per request — the naive architecture's real unit of cost. The browser was receiving about 137 events for every point it drew, and that ratio only gets worse as history accumulates.
Move the computation to the data
In the lab:
server-aggregation— the same events, filtered, grouped and aggregated inside the engine.
The second strategy changes a single thing: the question travels to the data, instead of the data travelling to the question.
GET /v1/timeseries?from=…&to=…&metrics=requests,cost,p95_latency&max_points=1200
▼
canonical query: range · timezone · bucket size · budget → hash
▼
engine: filter → group into buckets → count / sum / percentile
▼
API → 730 buckets of JSON → browser → chart
Two details matter. The client sends max_points equal to the chart's width in device pixels, and the server picks a bucket size so that the result never has more buckets than that. And the request is no longer "give me rows" but "give me these metrics, at this resolution, for this range, with these filters" — the API stops being a CRUD endpoint and starts behaving like a small query language.
One shared layer defines what a bucket, a percentile or a filter means; a thin adapter per engine decides how to compute it — percentile_cont() in PostgreSQL, quantile_cont() in DuckDB, quantileExactInclusive() in ClickHouse. For every control query, all three engines returned the same canonical hash and equivalent values. The reduction strategy is independent of the engine.
The first measurements showed DuckDB slower than PostgreSQL on small queries. Before writing that down, I traced it: the engine ran the query in 3–4 ms, and about 40 ms went into passing a few hundred bucket boundaries from Python as a query parameter — a property of my adapter, not of the database. Passing them as a small in-memory table took the overhead from about 48 ms to about 7 ms. The measuring apparatus has to be cheaper than the thing it measures.
Server-side aggregation shrank the yearly answer from 100,000 events to 730 buckets — roughly 300 times less data, more than 200 times less on the wire — while the chart stayed exactly the same.
Sidebar: cancelling work nobody needs anymore
Users don't wait for a chart before changing their mind. They drag to zoom, zoom again, flip a filter, and every new action makes the previous query useless. An AbortController per request stops the browser from waiting — but unless the cancellation travels all the way down, from the closed HTTP connection through the application into the engine, the database keeps computing answers nobody will see.
The lab simulates a user who changes zoom and filters four times in quick succession while each query is kept busy in the engine for 1.5 seconds. On all three engines the three superseded requests were cancelled, exactly one query was running while the latest one executed, and no orphaned queries were left afterwards. A frontend concern — "zooming feels sluggish" — was solved by a change that reaches into query execution. The most expensive query is the one whose result is thrown away.
Correctness before performance
A fast dashboard that shows the wrong p95 is worse than a slow one, because nobody notices. So before any benchmark, every engine and strategy has to produce the same answer — and that answer has to be right.
If PostgreSQL and DuckDB return the same number, that proves they agree, not that the number is right. Bucketing, timezones and the definition of a percentile live in shared code; a bug there makes every engine wrong in the same way. So the lab has three independent layers:
- A hand-written fixture of 57 events, each placed to test something: an event exactly at the start of the range and one exactly at its end, a 23-hour Berlin day, a Kolkata day that starts at 18:30 UTC, empty hours, a latency spike, a range that runs past the data. The expected answers were computed by hand.
- An independent reference implementation — plain Python loops that share no code with the query layer and assign events to buckets with a different algorithm.
- The same control queries against every engine and strategy, compared strictly: integers exactly, floating-point values within a relative error of 10⁻⁹.
The suite paid for itself on its first real run. On the 100k dataset, the engines disagreed with the reference on weekly buckets in UTC — and the bug was in the reference. It started UTC weeks on Thursday, because 1 January 1970 was a Thursday, while the specification starts weeks on Monday. Even an independent oracle can be wrong; that is why the hand-computed fixture exists as the one check that trusts no code at all. After the fix, every control query passed on every engine and strategy.
A preview: what you keep decides what you can answer
Correctness also decides what can be pre-aggregated later. One day in the fixture has 36 requests: one hour with 20 slow requests (1,000–1,950 ms), eight hours with two fast ones each. Suppose only hourly summaries were kept:
| How the daily value is computed | Average latency | p95 latency |
|---|---|---|
| Exact, from all 36 events | 868.3 ms | 1,862.5 ms |
| Average of the hourly values | 261.7 ms | 317.2 ms |
| Maximum of the hourly values | — | 1,902.5 ms |
From composable state: hourly sum + count
|
868.3 ms | — |
| From composable state: merged hourly histograms | — | 1,852.4 ms |
The average of averages is off by a factor of three, because it forgets that the slow hour had ten times the traffic; keeping sum and count makes it exact again. Neither the average nor the maximum of hourly p95 values gives the daily p95. Merged hourly histograms gave 1,852.4 ms against the exact 1,862.5 ms — an observation on 36 hand-placed events, not a guarantee, but an approximation of the right thing instead of an exact answer to the wrong question.
Pre-aggregation is only correct if we preserve enough information to compose the metric later.
Sidebar: benchmarking code needs correctness checks too
The measuring code needed the same suspicion as the code it measures. Each of these would have produced a confident, wrong number:
- the cancellation test passed for ClickHouse while proving nothing — the artificial "busy" query was optimized away, so nothing was ever in flight;
- the harness silently dropped a parameter and measured the baseline strategy in every cell labelled "optimized";
- an engine-side time limit stricter than the benchmark's own turned a slow stream into an "error" on one engine and a "timeout" on the others;
- the first PostgreSQL rollups measured slower than the raw events they summarize, because my builder wrote them in hash order, so reading one month of a time-indexed summary touched nearly the whole table;
- an exact percentile on ClickHouse that had "run out of memory" turned out to hold only 427 MiB of the 2.70 GiB the server reported in use when it was stopped. The failure was not attributable to the query's own memory, and in a clean state the same query passed every time.
None of them showed up as a crash; each showed up as a number. The harness now fails closed: every measurement must prove, from what the server reports, which engine, strategy, dataset and canonical question it belongs to, or the run is discarded — and discarded runs stay on record with the reason. A database benchmark can measure work you did not realize was running.
The database is still reading too much
In the lab:
optimized— the same events, in a physical layout each engine can prune.
Server-side aggregation fixed the network. It did not fix the work: the engine still computed every chart from event-level data, and on 100 million events ClickHouse reported reading all 100 million rows to draw a single day.
The OLTP reflex is to index the timestamp. But an index helps you find a few rows; a dashboard wants millions of rows, grouped. What matters at this scale is whether the engine can discard whole partitions, files or blocks before reading them. So each engine got a layout built for pruning: PostgreSQL monthly partitions with a BRIN index on time; DuckDB Parquet partitioned by day and sorted by model and time; ClickHouse monthly partitions sorted by model, country and time. The correctness suite re-checked every query on every layout.
| 100M events, baseline → pruning layout | One day | One month | One year |
|---|---|---|---|
| PostgreSQL | 145 → 127 ms | 4.4 → 3.6 s | 51 → 52 s |
| DuckDB | 109 → 78 ms | 0.92 → 1.22 s | 26 → 27 s |
| ClickHouse | 491 → 51 ms | 533 → 178 ms | 1.2 → 1.8 s |
ClickHouse is the clearest example of pruning doing exactly what it promises: for one day it read 2.74 million rows instead of 100 million. The other two engines are more interesting. PostgreSQL barely changed — consistent with its baseline already having a B-tree on the timestamp, which serves exactly this kind of time-range question. DuckDB's baseline was the generator's time-ordered Parquet, whose per-row-group statistics allow skipping much of a file for a narrow range. Its day-partitioned layout was slower than that baseline everywhere except the one-day view at 100 million events — at 10 million, a day took 36 ms instead of 18. That is consistent with the cost of opening and planning thousands of small files outweighing the rows they let it skip; the lab did not profile where the time went.
And nothing helped the year. A year is the whole dataset: there is nothing to skip.
Layouts also have a price. On 100 million events, PostgreSQL's partitioned table took 16.7 GB instead of 14.0 GB and 390 s to build; ClickHouse's took 1.6 GB instead of 3.6 GB, its compressed column types more than paying for the extra structure; DuckDB's sorted, partitioned Parquet took 1.7 GB instead of 2.1 GB.
A physical layout is a bet on which questions will be cheap. Pruning reduces what the engine reads only when the question is narrower than the data. The widest question a dashboard asks — "show me the whole year" — reads everything, every time, and recomputes history that will never change.
The obvious move is to compute those answers once and keep them. But before storing anything, we have to know what a stored answer must contain — and that depends on the metric.
How expensive is a percentile?
In the lab: one layout, one range at a time, and only the metric changes.
A dashboard tile looks like a number. Behind it is an aggregation, and aggregations are not equally expensive. A count needs one integer per bucket, an average two. An exact percentile needs far more value-level state and work: its answer depends on how all the values in a bucket are distributed, not on a few running totals. Before deciding what to pre-aggregate, I measured what each costs on its own. "Approximate p95" here means a histogram over 142 fixed logarithmic latency bins — the same on every engine, chosen because it can be merged, not because it is fast.
| One month (7.8M events), 100M dataset | count | sum + avg | exact p95 | approximate p95 |
|---|---|---|---|---|
| PostgreSQL | 1.43 s | 1.61 s | 4.12 s | 4.78 s |
| DuckDB | 678 ms | 687 ms | 670 ms | 2.39 s |
| ClickHouse | 106 ms | 162 ms | 176 ms | 1.98 s |
The exact percentile costs memory before it costs time. ClickHouse, the only engine here that reports per-query memory, needed 54 kB to count a year of events and 592 MB for the exact p95 over the same 100 million events, at about the same speed. A count's state does not grow with the data; an exact percentile's does. On PostgreSQL the exact percentile was also about three times slower than the count.
The approximate percentile was not cheaper. Computed from raw events, it was the slowest metric on every engine — eleven times slower than the exact one on ClickHouse for a month. In this lab's implementation each event is assigned to a bin by a generic expression, and ClickHouse reads the data twice; the measured cost is consistent with that extra work, but the lab did not profile it, and a different implementation could behave differently. The measurement does not show that histograms are inherently slow. What the histogram bought was bounded state — 288 MB on ClickHouse for a year, independent of how many events fall into a bucket — and, above all, the ability to merge.
On PostgreSQL the yearly comparison was not interpretable: a full-year scan of a 16.7 GB table under the lab's 3 GB memory limit reads from disk, varied between 25 and 59 seconds within one series, and hit the two-minute limit in three of four cells at least once.
Approximation is an information and representation trade-off, not a performance guarantee.
The percentile is the metric that decides what a summary must contain. An exact percentile needs value-level detail that grows with the data — much closer to the raw events than to a summary — so a summary either keeps that detail or keeps a bounded, mergeable approximation and accepts an error. If we want to stop recomputing history, we first need a representation that can be merged without changing the meaning of the metric. Additive metrics already have one; for the percentile, the histogram is it.
Why recompute history?
In the lab:
rollupandauto— the same questions, answered from pre-aggregated, composable state instead of events.
Physical layout helped when the query could skip data. A year cannot be skipped. Every time someone opened the yearly view, the engine read 100 million events again and recomputed answers for months that had not changed since the last time — and never would.
What if we stop asking 100 million events the same question?
What we decided to keep
The correctness preview already said what not to store: no averages, no percentiles — only state that can be combined. The lab keeps three grains, identical on all three engines:
| Rollup | Time grain | Dimensions kept |
|---|---|---|
r_1m |
minute | model, status |
r_1h |
hour | model, country, status |
r_1d |
day | model, country, status |
Every cell stores a count, cost and token sums, a latency sum, a failure count, the latency minimum and maximum, and a 142-bin latency histogram. The daily rollup is built by merging hourly cells, which makes it a composability test in its own right: its per-model, per-day totals matched the generator's records exactly on every engine. One thing is deliberately missing — user_id — and the next part is about why.
Asking the summary — and being refused
A planner splits every requested bucket into the largest whole rollup cells that fit it and keep every filtered dimension, and computes whatever is left — ragged edges that don't line up with a minute — from event-level data. A filter on country cannot use the minute grain, which doesn't keep country; a Kolkata day, which starts at 18:30 UTC, is assembled from hourly cells plus thirty minutes of minute cells. An exact percentile cannot come from a rollup at all, so that combination is reported as not applicable rather than quietly approximated. And when no rollup can answer — a filter on one user — rollup refuses, while auto answers from event-level data and says so in its plan. Nothing falls back silently.
Is it still the same answer?
Before measuring speed, rollup answers were compared with the independent reference. Additive metrics — requests, cost, tokens, average latency as sum over count, error rate — matched exactly on all three engines, on every control query, from 100 thousand to 100 million events.
The p95 is now an estimate, and its quality depends on how many events fall into a bucket. On 100 million events the histogram stayed within 0.46% of the exact p95 for daily and six-hour buckets; for five-minute buckets of one model or one country the median error was 1.5–2.2% and the worst bucket was off by 36%. On the 1-million-event dataset, one bucket in ten was off by more than 60%. A histogram describes a crowd well and a handful of requests badly. These are observations on this dataset, not a guarantee — and a dashboard that shows an approximate p95 should say so.
What the measurements showed
The yearly view changed most, because that is where recomputation was most wasteful:
| Yearly view, 100M events | From events (exact p95) | From events (approximate p95) | From rollups (approximate p95) |
|---|---|---|---|
| PostgreSQL | 45.3 s | timed out | 10.9 s |
| DuckDB | 41.5 s | 91.6 s | 1.74 s |
| ClickHouse | 1.69 s | 19.8 s | 0.36 s |
The one-month view went from 3.9 s to 0.91 s on PostgreSQL, 0.65 s to 0.18 s on DuckDB and 164 ms to 117 ms on ClickHouse; the one-day view barely changed anywhere, because pruning had already removed nearly all its work. Pre-aggregation pays where the question is wider than the data you can skip.
The funnel explains both the gain and its limit. The 730-point yearly chart uses 12-hour buckets, which daily cells cannot fill, so the engine read the hourly rollup: 37.5 million rows instead of 100 million events. And 94% of those rows were histogram rows. Additive state is cheap; the percentile is most of the rollup.
That is not waste — it is the price of a decision made earlier. Correctness required composable state; a composable percentile requires a histogram per cell; and the histogram is what keeps both the summary large and the p95 answerable for any range. A rollup is not "a few hundred rows".
Rollups also have a price in storage and build time. Built from scratch on 100 million events they took between 219 s (ClickHouse) and 622 s (PostgreSQL). They occupied 367 MB as Parquet and 609 MB in ClickHouse, but 9.8 GB in PostgreSQL — 70% of the size of its event table; the lab measured that difference but did not isolate its causes. Most of it is the minute grain, which keeps 0.71 rows for every event: a "summary" barely smaller than the data it summarizes.
Pre-aggregation moves work from every read to one build — and only for the questions its grain can still answer.
A rollup behaves like a cache with a schema: it precomputes some questions and deliberately gives up others. It made the most expensive question in the dashboard 4 to 24 times cheaper on every engine, kept additive answers exact, and turned the p95 into an estimate whose quality depends on bucket size. Keeping it fresh as new events arrive is outside this investigation.
Reducing data means reducing optionality
In the lab: rollup grain, high-cardinality dimensions, and what happens when a summary cannot answer.
The rollups have no user_id. That was the price of making them small, and it means a support engineer who asks for one customer's latency over the year gets a refusal from rollup and an event-level query from auto. Every dimension left out of a summary is a question you have decided to answer the slow way — or not at all.
So how much would it cost to keep it? The lab counted, exactly, the cells and histogram rows that existing and hypothetical grains would contain on 100 million events. Nothing hypothetical was built; the sizes are counted over the raw events, not estimated.
| Grain (100M events) | Cells | Rows stored per event | Cannot answer a filter on |
|---|---|---|---|
r_1d(model, country, status) |
171,363 | 0.037 | user |
r_1d(model, country, status, user_id) |
29,158,129 | 0.87 | — |
r_1d(user_id) |
10,913,108 | 0.35 | model, country, status |
r_1h(model, country, status) |
2,266,643 | 0.38 | user |
r_1h(model, country, status, user_id) |
53,677,021 | 1.42 | — |
r_1m(model, status) |
5,433,407 | 0.71 | country, user |
r_1m(model, country, status) |
46,999,692 | 1.42 | user |
Adding user_id to the daily rollup multiplies its cells by 170 and brings it to 0.87 stored rows per event. Adding it to the hourly rollup, or adding country to the minute rollup, produces more rows than there are events: the "summary" is larger than the data it summarizes. A user-only rollup is smaller, but answers nothing about models or countries.
The planner makes the trade-off visible instead of hiding it: a query filtered by user is refused by rollup and answered by auto from event-level data, with the reason in its plan — "r_1d cannot be used: it has no user_id dimension".
Every rollup is a decision about which questions remain answerable.
A rollup is not small because it is a rollup. It is small only while its grain and dimensions collapse enough of the original event space — and pre-aggregation reduces data partly by reducing the set of future questions the representation can answer. High-cardinality dimensions belong to a different access path, and the system has to know which path answers which question, and say so. A summary that silently answers a question it cannot answer is worse than one that refuses.
Your chart has only so many pixels
In the lab: a point budget instead of a bucket size, and a min/max envelope that is part of the query, not a trick in the client.
So far the question was what to store and what to read. The last reduction happens at the other end of the pipeline: how much of the answer to send. Every request so far carried max_points=1200, and the server turned it into a bucket size. That choice was never tested. A reasonable engineer could argue the opposite: return as much resolution as you can afford and let the chart sort it out — more points can only be more faithful. So: what does extra resolution cost when the screen cannot show it, and what does a coarse answer lose?
Why "return everything you have" isn't free
The experiment fixed everything — 100 million events, the yearly range, request rate as the metric, the auto strategy, ClickHouse and DuckDB — and changed only the point budget. The server picks the finest bucket from a fixed set that fits it, so budgets form a staircase:
| Point budget | Bucket | Points | Transferred (gzip) | Time to chart, ClickHouse / DuckDB |
|---|---|---|---|---|
| 100 or 300 | 1 week | 53 | 2 kB | 98–164 / 192–203 ms |
| 600 | 1 day | 365 | 5 kB | 110 / 173 ms |
| 1,200 | 12 hours | 730 | 8 kB | 292 / 242 ms |
| 5,000 | 3 hours | 2,920 | 50 kB | 382 / 289 ms |
| 10,000 | 1 hour | 8,760 | 127 kB | 633 / 457 ms |
Twelve times as many points as the 1,200-point chart multiplied the bytes by sixteen and roughly doubled the time to chart on both engines. Drawing was not the bottleneck — the chart rendered 8,760 points about as fast as 730. The cost was everything before the canvas: finer cells to read, more JSON to build, transfer and parse. The budget even decided which summary could be used at all — up to one-day buckets the daily rollup answered; below that, the hourly one, with thirteen times as many cells.
Before measuring, the lab checked every bucket of every response against per-minute rates computed directly from the raw files: buckets tile the year without gaps or overlaps, never exceed the budget, use the finest step that fits — including a Berlin year with its 23- and 25-hour days — and every value matches.
What a coarse answer loses
Fewer points mean fewer bytes; that part is obvious. The real question is what disappears. To avoid picking a flattering example after the fact, a rule fixed before measurement selected the features: the three highest per-minute traffic peaks relative to their 12-hour rolling median that stay above 1.5× for at most three hours. It picked three spikes, 5.6 to 7.1 times the surrounding rate, lasting one to two and a half hours.
On the 1,200-point chart each spike falls into one 12-hour bucket, and the bucket's mean showed only 20–23% of the spike's peak; compared with its neighbours, the spike's bucket stood out in only one of three cases. Even at 10,000 points — eight times the screen — the mean reached only 65–81% of the peak. Averaging is a reduction that decides, silently, that short events don't matter.
Keeping the spike without keeping every sample
The alternative is to decide explicitly what must survive. With an envelope, the server also returns for each bucket the minimum and maximum of the per-minute rate inside it — computed from whole minutes of the minute rollup, and counted against the result budget as two more numbers per point.
At 1,200 points the envelope's maximum equalled each spike's peak per-minute rate exactly, and the spike's bucket stood out from its neighbours by a factor of 3.2 to 6.1. Compared with simply asking for more points, it was the better trade on this workload: 11 kB instead of 127 kB, and the full per-minute peak instead of 65–81% of it, at a similar time to chart. Its cost does not depend on the budget but on the base resolution: every envelope request read the year's 5.4 million minute cells, adding 150 to 310 ms on the server.
It has limits. It is exact only at its base resolution: it reports the busiest minute, so a burst shorter than a minute is averaged into it and no individual event is kept. It preserves magnitude, not timing: the spike is only known to lie somewhere in its 12-hour bucket. And it does not guarantee visibility: in weekly buckets, one spike — on a Saturday morning, when traffic is low — was smaller than the ordinary weekday peaks sharing its bucket, so its envelope looked like any other week's.
Reducing information is useful only when we know which information the consumer can no longer use — and which information it still needs to see.
A 1,200-pixel chart cannot use 8,760 points. What it could use was a little more information per pixel. Resolution is a budget with two dimensions: the point budget limits temporal resolution — how many buckets — and a value budget limits representation richness — how much each bucket carries. The same 730 buckets can carry one number each or three, and only the second version shows the spike.
One fast query is not a production dashboard
In the lab: closed-loop concurrency — the same yearly chart from events, from rollups, and from rollups with the envelope, while more users ask at once.
Every number so far came from one request at a time. A dashboard is not used that way, and a strategy that is fast alone can still collapse when its requests compete for the same cores. Does reducing information per request also improve the system's ability to serve concurrent users — and what happens to the envelope's extra work when many people want it at once?
The lab ran a closed loop: virtual users, each sending the next request as soon as the previous answer arrives. Every user asked for the yearly chart at the 1,200-point budget in three representations — A, event-level aggregation on the pruning layout; B, from rollups; C, from rollups with the per-minute envelope — on ClickHouse and DuckDB, with the same container limits as everywhere else and no cache. Users went up 1, 4, 8, 16, 32, and a rule fixed beforehand stopped each ladder when throughput no longer grew while tail latency rose, or when more than 5% of requests failed. CPU per request came from the containers' own accounting, engine and API together.
The first attempt was discarded. Four event-level DuckDB queries timed out, and two of them kept running for about fifty seconds after their clients had left, competing with the next measurement for the same six cores. Cancellation itself worked — a single heavy query stopped within half a second — but four interrupted heavy queries on one DuckDB instance wound down one at a time. The repeated run waited for every engine to be idle before each step.
Where throughput stopped
Every ladder stopped early, at 4 or 8 users. Each one except DuckDB's event-level ladder stopped when its engine had used up its CPU allowance — ClickHouse all 3 engine cores, DuckDB all 4; the event-level DuckDB ladder failed by timeouts first, at four users, with requests taking over 100 seconds.
| Per representation | A · events | B · rollups | C · rollups + envelope |
|---|---|---|---|
| ClickHouse: max throughput | 0.95 req/s | 45 req/s | 10 req/s |
| ClickHouse: CPU per answer | 3.2–3.6 s | 0.07–0.08 s | 0.26–0.36 s |
| DuckDB: max throughput | 0.06 req/s | 11 req/s | 4.8 req/s |
| DuckDB: CPU per answer | 39 s | 0.35–0.37 s | 0.80–0.83 s |
The ceilings matched, within a few percent, the CPU allowance divided by the CPU each answer cost — for ClickHouse from rollups, 3.8 cores ÷ 0.084 s ≈ 45 requests per second. Answering from rollups did not just make one request faster; it raised capacity by about the same factor as it reduced work: 48 times on ClickHouse, about 190 times on DuckDB.
Past the plateau, adding users bought only waiting: on ClickHouse with rollups the median answer took 32 ms for one user, 86 ms for four and 175 ms for eight, at the same 45 requests per second. The waiting was inside the engine, not in the API's queue. And the engines got there differently: one ClickHouse rollup answer used about 1.6 of its 3 cores, leaving room for a few more users, while one DuckDB answer already kept nearly all 4 cores busy, so its throughput was flat from the first step.
The price of the envelope, multiplied
For one user the envelope cost little: on ClickHouse the median answer went from 32 to 97 ms. Under load, that difference became capacity. Reading a year of minute cells made each answer cost about four times as much CPU, and the system served 10 requests per second instead of 45; on DuckDB, 4.8 instead of 11. The envelope's fidelity is still worth having — but it is paid on every request, and under simultaneous demand it is paid in users.
Capacity is a budget of work. Reduce the work per answer before trying to serve more answers at once.
On this machine, how many users a representation could serve followed from how much work each answer took. The strategy that won for one request also won under load, by a wider margin than its latency alone suggested. These are relative results on a small, fixed allowance of cores; they say nothing about how many users any engine supports in general.
Results: how the bottleneck moved
Each part of this investigation removed one kind of unnecessary information, and each time the bottleneck moved. Followed in order, the same one-year chart went through six transitions:
| Where the data got smaller | What the yearly chart processed | What it cost |
|---|---|---|
| Nowhere — the browser aggregates | every event in the range, sent to the browser | stopped finishing at 10M events; at 1M, 32.7 MB and 15–18 s to chart |
| In the engine | all 100M events read, 730 buckets returned (about 15 kB) | ClickHouse 1.2 s, DuckDB about 26 s, PostgreSQL about 51 s |
| In the physical layout | for one day, 2.74M rows read instead of 100M | 51 ms instead of 491 ms on ClickHouse — and no help for the year |
| In stored history | 37.5M rollup rows instead of 100M events, 94% of them histogram rows | ClickHouse 0.36 s, DuckDB 1.7 s, PostgreSQL 10.9 s — instead of 1.7 s, 41 s and 45 s |
| At the screen | 730 points (8 kB) instead of 8,760 (127 kB); 2,190 values with an envelope (11 kB) | about half the time to chart, with every spike's per-minute peak kept |
| Across users | 45 rollup answers per second instead of 0.95 event-level (ClickHouse); 11 instead of 0.06 (DuckDB) | capacity set by CPU per answer |
Read top to bottom, the table is a list of bottlenecks that each looked like the problem until it was removed: first the network and the browser, then the engine scanning everything, then the engine recomputing history, then the size of what history had to keep to stay correct, then how much of the answer the screen could use, and finally the CPU every answer costs when many people ask at once.
The reductions were cumulative — rollups mattered because the browser no longer received events, and budgets mattered because rollups had made answers cheap enough for their size to show. And each was safe only because something checked it: the same canonical question, answered identically by three engines and an independent reference, before any of its speed was measured.
The lab stopped at 100 million events. The trend a larger run would test — event-level work growing with the data, summaries growing far more slowly — was already visible from 100 thousand upward; another step would have confirmed it rather than raised a new question. Not tested at all: keeping rollups fresh, a cache in front of the engine, behaviour beyond capacity under an open arrival rate, and concurrency on PostgreSQL.
Choosing an architecture
The reader should not take a database ranking from this lab, because none was measured in a way that would support one. What the experiments do support is a sequence of questions about the workload — each changed the outcome more than the choice of engine did.
Are you shipping event-level data to answer aggregate questions? Then the database is not your first problem. The naive dashboard failed at the same point on all three engines, because its unit of cost was events per request.
Are your queries selective enough for the layout to matter? For narrow ranges, yes — but check what the baseline already does. PostgreSQL's ordinary time index already captured most of the benefit; ClickHouse needed a sort order before it could skip anything; DuckDB's extra partitioning helped only one view at the largest scale. For the whole year, no layout helped.
Are broad historical questions repeated? Then stop recomputing them. Rollups made the yearly view 4 to 24 times faster on every engine and raised concurrent capacity by a similar factor, and did little for a single day.
Which metrics need composable state? Sums and counts compose; averages and percentiles don't. A percentile in a summary needs a mergeable representation, which costs storage and returns an approximation whose error depends on bucket size.
Which dimensions must stay available? Every dimension kept in a summary multiplies its size, and a high-cardinality one can make it larger than the data. Decide which questions get the cheap path, and make the system say which path it took.
How much resolution can the consumer use? Returning more points than pixels cost bytes and time without making the chart more faithful. If short-lived features matter, ask for them explicitly and pay for them in the budget.
What does one answer cost when many people ask at once? Under CPU-bound load, capacity was the CPU allowance divided by the work per answer.
The engines differed in where they met these limits, and those differences are evidence about this workload on this machine, not general verdicts:
- PostgreSQL served selective time ranges well with nothing but an index. Its broad scans of 100 million events were I/O-sensitive under the lab's 3 GB container budget and varied widely between repetitions. Rollups still cut the yearly view from 45 s to 10.9 s. Its rollup tables were far larger than the other engines' — 9.8 GB against hundreds of megabytes — for reasons the lab did not isolate.
- DuckDB scanned Parquet quickly and spread a single query across all its cores, which made one request fast and left no headroom for a second user in this setup. Its partitioned layout was a net cost at small scale here — consistent with the overhead of many small files on this data, and not evidence about partitioning in general. Under overload, interrupted heavy queries took tens of seconds to wind down.
- ClickHouse had the fastest broad scans once its data was sorted, and a single rollup answer left part of its CPU free for other users. Its exact yearly percentile needed about 600 MB of memory; an earlier out-of-memory failure turned out not to be attributable to that query alone.
Practical recommendations
These follow from the measurements on this workload and this machine. They are starting points, not laws.
- Reduce data before transport. Don't ship event-level rows when the visualization needs aggregates; send the question and the chart's width, return buckets. It was the largest single improvement in the lab, on every engine.
- Reduce reads through physical access — after checking what you already have. Sorting and partitioning by time helped narrow ranges; a plain time index was already most of the win on PostgreSQL, and small partitioned files cost more than they saved on small DuckDB datasets.
- Store the state needed to compose the metric, not a final value that cannot be composed. Sums and counts instead of averages; for percentiles, a mergeable representation — this lab used a fixed-bin histogram — and label the result as approximate, because its error depends on how many events a bucket holds. Verify the summary against raw data before trusting it.
- Stop recomputing the widest questions you keep asking. Rollups paid off on the year and barely on the day. Choose grains that fit the bucket sizes your charts request: a 12-hour bucket cannot be filled from daily cells.
- Reduce optionality deliberately. Keep high-cardinality dimensions out of summaries, give them their own access path, and make the system refuse or route — with a reason — when a summary cannot answer.
- Budget the representation for its consumer. Ask for the screen's resolution, and name the features that must survive it: when short-lived extremes matter, return an envelope per point instead of more points, and count it in the budget.
- Reduce the work per answer before trying to serve more answers at once. Throughput topped out at the CPU allowance divided by the CPU one answer cost; price every addition to an answer in users, not only in milliseconds.
- Treat the benchmark as code under test. Several confident numbers here measured a bug in the harness, the builder or the environment. Make every measurement prove which question, engine and dataset it belongs to, and keep discarded runs on record.
Where should a chart's numbers come from?
Does the request need individual events (an export, a drill-down to rows)?
├─ yes → serve event-level data, paged or streamed — a different access path from charts
└─ no → aggregate on the server; at most as many buckets as the chart has pixels
│
Must short-lived extremes stay visible?
├─ yes → add a min/max envelope from a fine base, counted in the value budget
│ (it keeps each sub-bucket's peak, not single events or exact times;
│ to locate a spike, ask for a narrower range)
└─ no → the bucket value alone
│
Exact percentile required?
├─ yes → event-level aggregation on a pruning-friendly layout
└─ no → Does a rollup keep every filtered dimension?
├─ no → event-level aggregation, and say why
└─ yes → rollup cells, with event-level data for edges they don't cover
One answer is fast, but many users saturate the engine?
└─ capacity ≈ CPU allowance ÷ CPU per answer: remove work from each answer first,
and price additions such as the envelope in users. Past that point, more users only add waiting.
Conclusion
The chart at the start of this article is 1,200 pixels wide. Behind it were 100 million events. Everything in between was a series of decisions about where to discard information.
The naive dashboard discarded nothing until the browser. Server-side aggregation moved the reduction into the engine; a pruning layout let the engine skip data — but only for questions narrower than the data. Rollups stopped recomputing history, and only worked because they kept composable state rather than finished answers. Their size showed that a summary is small only while it gives up enough questions. The screen set a budget for the answer, and the spikes showed that the budget is not just a number of points but a choice of what each point must carry. Under many users, all of it became a single quantity: the work each answer costs.
One rule ran through every part:
Find the earliest layer where information can be safely discarded.
Earliest, because every layer before the reduction pays for the full volume. Safely, because every reduction is also a decision about which questions remain answerable — and correctness, composability and fidelity are what make it safe.
A data-heavy frontend is not a rendering problem with a database behind it. It is an information-reduction pipeline, and its performance is decided long before the first component renders.
The lab, the full methodology and every raw measurement: https://github.com/godfreyd/100m-to-1200px
Top comments (0)