Every question answered once
At nine in the morning a hundred people open the same dashboard. Here is how Xeplr BI answers it once, shares the answer, and never serves a stale number.
At nine in the morning a hundred people open the same sales dashboard. Without a cache, the database answers the same question a hundred times, and everybody waits. Here is how Xeplr BI answers it once, shares the answer, and never serves a stale number.
| 100 people at once | Slowest 5% wait |
|---|---|
| No cache | 17.9 s |
| Just after a rebuild, nothing stored yet | 3.7 s |
| Answered from the cache | 38 ms, about 470× less waiting |
A fast engine still makes people queue
Xeplr BI keeps its biggest tables as cubes in a columnar engine. Columnar storage is the right tool for dashboards: on the same 20 million rows (2 crore), a month-by-region summary took 3.0 seconds on a tuned Postgres and 154 milliseconds on the columnar engine. Across five typical dashboard queries it was 15 to 30 times faster, on an eighth of the disk.
One heavy query at a time, it flies. The trouble starts when many people ask at once. Each query wants every core, so queries line up behind each other. With a hundred people opening the same board, the slowest five percent waited 17.9 seconds, and the server got through about seven requests a second.
The part that makes this wasteful is that most of those hundred people asked the same thing. Same board, same filters, same numbers. The engine did identical work a hundred times.
Load test of the design: five cubes of 48.8 lakh rows (4.88 million) each, 100 concurrent requests, 4 engine threads, in a container with 4 CPUs and 6 GB. The same run served about 7 requests a second with no cache, 66 just after a rebuild, and about 6,000 warm.
The idea: compute once, share with everyone
The result cache sits in front of the cubes and the live databases. When a dashboard asks a question, Xeplr BI first looks for a stored answer to exactly that question. If one exists, it is sent straight back. If not, the question is computed once, stored, and every later viewer gets the stored answer.
Xeplr BI’s own end-to-end test checks this on every change, through the product: the first viewer’s answer is computed, the next viewer gets the same numbers from the cache, and Refresh brings back new numbers when the data has changed.
Every board answer takes the same path:
- The dashboard asks a question.
- Is there a stored answer for exactly this question? If yes, it is sent back in milliseconds.
- If not, is another server process already computing it? If yes, wait for that answer.
- If not, compute it once, from the cube or the live database.
- Store it, checked, and send it back.
The middle step matters most when a hundred people arrive together: one computes, ninety-nine wait for that one answer.
What makes two questions the same
A cache is only as good as its idea of “the same question”. Too loose and someone sees another person’s numbers or yesterday’s. Too strict and nothing is ever reused. Each stored answer is filed under a key built from everything that decides it: the application, company and workspace; the cube and its build version; the dataset and dashboard; and the SQL, filters, widgets and code.
- Who. The application, the company and the workspace. One customer’s answers can never be found by another, even if both ask an identical question.
- From what. For a cube, the cube and the build it was computed from. For a live database, the connection and the database.
- For whom. The dataset and the dashboard, so clearing one board never touches another.
- Exactly what. A fingerprint of the compiled SQL with the filter values in it, its parameters, every widget’s settings, and the code that turns rows into answers.
Change a filter and it is a different question with its own answer. Change a widget’s settings and the old answer is simply never asked for again.
Staying correct without anyone clearing anything
The hard part of caching is knowing when an answer has gone stale. Xeplr BI mostly avoids that problem by putting the reason for staleness into the key itself.
A rebuilt cube replaces its answers at once
Every cube build gets a new version, and the version is part of every key. The moment a rebuild finishes, every new question asks under the new version, and answers from the old build are never read again. No clearing step can be forgotten, and there is no moment where half a board is old and half is new.
Live databases age out
A live database cannot announce that its data changed, so its answers last five minutes by default. The rules are adjustable per dashboard and per workspace, and a workspace-wide off switch beats every other rule.
Refresh means fresh, for everyone
The refresh button on a widget skips the stored answer, computes a fresh one and stores it, so the next viewer gets the new numbers too. Anyone may refresh once every 30 seconds for a given widget and filter set; administrators always may.
New code never serves old maths
Part of the key is a fingerprint of the code that computes answers. Ship a release that changes how a total is calculated, and every answer computed the old way stops matching. Nobody has to remember to bump a version number.
Promises the cache keeps
A cache that is fast but sometimes wrong is worse than no cache. These rules are built in:
- Only complete answers are stored. If a computation fails or is stopped, nothing is saved, and the next request computes again.
- Every answer is checked when it is read. Each stored answer carries a checksum and its row count. Anything damaged is deleted and recomputed, never served.
- If the cache is down or slow, dashboards still work. Any cache operation that takes longer than half a second is abandoned and the answer is computed directly. The cache can make a board faster. It can never make it broken.
- Answers are stored compressed. A typical 21 KB table answer takes 3 KB in memory. Anything still over 10 MB after compression is served but not stored, so one giant table cannot slow down everyone else’s reads.
- Old answers do not pile up. When a cube is rebuilt, the answers of builds older than the two newest are deleted, so memory stays at about two builds’ worth however often cubes are rebuilt.
- The cache can never sign anybody out. Old answers leave because the cache deletes them, never by pushing sign-in sessions out of memory. Larger installs give the cache its own memory store; smaller ones can share one with sign-ins, set never to evict, because the cache’s memory stays small and bounded.
A hundred people, one computation
The worst moment for any cache is the first one: a board nobody has opened since the morning’s rebuild, and a hundred people opening it together. A naive cache would miss a hundred times and send a hundred identical queries to the engine.
Xeplr BI computes each question once across every server process. The first request to miss takes a short lock and computes. Everyone else asking the same question waits for that answer instead of starting their own. If the process that took the lock dies halfway, the lock expires and a waiter takes over, so a crash costs a few seconds and never leaves a board stuck.
Without the cache, identical queries queue in the engine and waiting grows with the crowd. With the cache, the crowd costs one query: the first viewer computes, the rest wait for that same answer, and everyone after reads it from the cache.
One server, many processes
Because answers live in a shared store, Xeplr BI can run several API processes side by side on one server, and any of them can answer for any other. A long-running query’s progress, its result and its stop button all work whichever process a request happens to reach. A dashboard that one process computed is a cache hit for all the others.
The numbers, with where they came from
| What was measured | Result | Setup |
|---|---|---|
| Dashboard query, month by region, one at a time | 3.0 s → 154 ms | Postgres 16, tuned, against the columnar engine; 20 million rows; 4 CPUs, 6 GB each |
| Five dashboard queries | 15–30× faster | Same data and machine |
| Storage for the same rows | 3.5 GB → 419 MB | Table plus indexes against a columnar file |
| Cube stored in time order, one-month filter | 8× faster | Same 20 million rows |
| Busy board, no cache | 17.9 s · ~7/s | 100 concurrent requests; five cubes of 4.88 million rows each; 4 engine threads; 4 CPUs, 6 GB |
| Busy board, just after a rebuild | 3.7 s · 66/s | Same |
| Busy board, warm cache | 38 ms · ~6,000/s | Same |
Latency figures are the 95th percentile: the time within which 95 of every 100 requests were answered. Results on your hardware will differ; Xeplr BI ships a benchmark you run on the target server before trusting any number.
What comes next
The cache is live for every dashboard. Four improvements are planned, starting with the weak spot in the numbers above: the first few seconds after a rebuild.
- Warm before the switch. Compute the most-viewed answers for a new build before readers move to it, so the cold 3.7 seconds never reaches a person.
- A cache control page. Clear one dashboard or one widget, compare a fresh answer with the stored one, and see hit rates and sizes.
- One engine for all cubes. A single query service in front of the cubes, with a fair share per cube, so one busy dashboard cannot starve the rest.
- Rules by filter. Keep this year’s answers for minutes and last year’s for days, on the same dashboard.
Figures from Xeplr BI’s design measurements and test suite. The result cache runs on the customer’s own server, next to the data, like the rest of Xeplr BI.