Why dashboards read from a columnar store, measured on 20 million rows
Cubes answer dashboard queries 15–30× faster than a tuned Postgres, in a fraction of the disk and memory. Postgres still wins at fetching one record. Here is the test.
Dashboards ask one kind of question: group and add up months or years of data. A row store like Postgres is built for a different one, fetching a record by its key. So Xeplr BI keeps cubes in a columnar store and leaves record lookups to your own database. We measured that choice rather than assuming it.
The setup
One orders table of 2 crore (20 million) rows, generated once with a fixed seed and loaded into both databases: timestamps over three years, 1,250 stores, 5 regions, 4 channels, 6 statuses, 8,000 cities, and quantity, revenue, discount and cost.
Each database ran alone in its own container with 4 CPUs and 6 GB. Postgres 16 was tuned rather than left on defaults: 1.5 GB of shared buffers, 64 MB of work memory, four parallel workers per query, a primary key, an index on the timestamp and one on store and timestamp. Times are the median of five warm runs.
One query at a time
| Query | Postgres | Columnar cube | Faster |
|---|---|---|---|
| Month × region, 1 year | 3.0 s | 154 ms | 20× |
| Month × region × channel × status, 1 year | 4.1 s | 267 ms | 15× |
| Month × city × store, top 500 | 9.1 s | 300 ms | 30× |
Ten users at once
With ten concurrent clients the gap widens, because each columnar query needs less CPU, so more of them fit before users queue. The 95th-percentile wait on the first query was 8.9 s on Postgres and 2.4 s on the columnar store. On the third it was 50.4 s against 4.3 s.
The same rows on disk and in memory
| Postgres | Columnar | |
|---|---|---|
| On disk | 3.5 GB (2.1 GB table, 1.4 GB indexes) | 419 MB |
| Peak memory during the run | 5.5 GB | 1.6 GB |
Columns of one type compress far better than mixed rows, and a columnar query reads only the columns it names.
Where Postgres wins
Fetching one order by its id took 0.7 ms in Postgres and 2.4 ms in the columnar store. A B-tree index jumps straight to the row, and a columnar store has nothing to jump with. Under ten concurrent clients Postgres kept answering this kind of lookup in 2 ms at about 9,700 a second.
So each engine keeps its own job: cubes for dashboards, and your own Postgres or SQL Server for looking up single records. Postgres can be made faster for dashboards with summary tables, partitions or block-range indexes, but that is building cubes inside Postgres, on an engine that is 15–30× slower at it.
Time order adds more
A columnar store keeps a minimum and maximum for each block of rows and skips blocks that cannot match a date filter. That only works when rows are stored in time order, so cubes are written that way. On the same 20 million rows, run directly on an Apple M4 Pro, a one-week filter dropped from 38 ms to 2 ms and a one-month filter from 40 ms to 5 ms. With no date filter there was no change.
These numbers come from our own test on one generated table, so treat them as a measurement of this design rather than a promise for your data. If you want to see how it behaves on yours, book a call.