# Real-time dashboard data aggregation: patterns that keep charts fast at scale

*By Roberto Lazar, founder of Dock30 · Published 2026-06-22 · Updated 2026-07-25 · 7 min read*

Why dashboards slow down as data grows, and how rollup tables, materialized views, and continuous aggregates keep queries fast without a rewrite.

Dashboards get slow for one reason: they **aggregate raw rows on every page load**. The fix is to compute summaries ahead of time, in rollup tables, materialized views, or continuous aggregates, so each chart reads a few hundred precomputed rows instead of scanning millions of raw ones. Done right, a dashboard sitting on billions of events loads in well under a second. The win comes from not running the expensive query at view time at all, not from making that query faster.

None of this is exotic data engineering. We build dashboards for a living at Dock30, and the same handful of patterns covers nearly every one, whether it is SaaS analytics or an operations screen pulling from eight different systems.

## Why dashboards melt

A dashboard that runs `SELECT sum(...) GROUP BY ...` over a raw events table works beautifully in the demo. Ten thousand rows aggregate in a millisecond. A hundred million do not. Every chart re-scans the table, every open tab re-runs every chart, and the load peaks at exactly the moment people care most about the numbers, like a launch day. A bigger database instance buys you months, not a solution. The durable fix is changing when the aggregation happens: at write time or on a schedule, instead of at view time.

## The five patterns that matter

| Pattern | What it does | Best for |
|---|---|---|
| Rollup tables | Summaries computed on a schedule into a small table | Known metrics, large volumes |
| Materialized views | Query results stored physically, refreshed by you | Moderate volumes, minimal plumbing |
| Incremental aggregation | Update only buckets touched by new data | High-velocity event streams |
| Caching layer | Reuse computed results across viewers | Hot dashboards, many viewers |
| Time-series rollups | Coarsen old data into minute, hour, day buckets | Metrics over time, retention control |

Most production dashboards combine two or three of these.

### Rollup tables

Compute your totals on a schedule (every minute, five minutes, or hour), write them into a compact summary table, and point the dashboard at that. A chart query goes from scanning millions of raw rows to reading a few hundred precomputed ones. This is the single highest-impact pattern. It turns a table scan into an index lookup, and you can build it in an afternoon with plain SQL and a cron job.

### Materialized views refresh less than you'd hope

A materialized view is a stored query result, which makes it the lowest-effort form of pre-aggregation. There is one catch people keep tripping over: in PostgreSQL, a materialized view **does not refresh itself**. Not on a schedule, not on write, not ever, and that is still true in Postgres 18. You run the refresh yourself, and most teams schedule it with pg_cron:

```sql
-- Roll raw orders up into hourly buckets
CREATE MATERIALIZED VIEW hourly_orders AS
SELECT date_trunc('hour', created_at) AS bucket,
       location_id,
       count(*)   AS orders,
       sum(total) AS revenue
FROM orders
GROUP BY 1, 2;

-- Required for CONCURRENTLY refreshes
CREATE UNIQUE INDEX ON hourly_orders (bucket, location_id);

-- Postgres will not do this on its own. Schedule it:
SELECT cron.schedule(
  'refresh-hourly-orders', '*/5 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_orders$$
);
```

Two caveats worth knowing from the [Postgres docs](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html). `CONCURRENTLY` lets reads continue during the refresh, but it needs that unique index and a view that has already been populated once. And every refresh is a full recompute of the underlying query, even if a single row changed since the last run. Refreshing a modest summary every five minutes is fine. Recomputing a huge one every thirty seconds gets expensive fast, which is where the next pattern comes in.

### Incremental aggregation with continuous aggregates

Recomputing everything from scratch is wasteful when 99.9 percent of the data has not changed. Incremental aggregation updates only the parts touched by new events, so refresh cost tracks new data rather than total history. TimescaleDB's continuous aggregates are the cleanest implementation we have used: per the [TigerData docs](https://www.tigerdata.com/docs/use-timescale/latest/continuous-aggregates/about-continuous-aggregates), they refresh only the time buckets that actually received new data. (Timescale the company renamed itself TigerData in 2025; the extension is still called TimescaleDB.)

The real-time variant is the one we reach for on dashboards. With `materialized_only = false`, queries combine the precomputed buckets with an on-the-fly aggregation of whatever arrived since the last refresh ([TigerData](https://docs.tigerdata.com/use-timescale/latest/continuous-aggregates/real-time-aggregates/)). Fresh numbers, cheap queries, no waiting for a refresh cycle. That is the exact combination this whole article is chasing.

```sql
CREATE MATERIALIZED VIEW hourly_orders
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', created_at) AS bucket,
       location_id,
       count(*) AS orders
FROM orders
GROUP BY 1, 2;

-- Precomputed buckets plus the newest raw rows, in one query
ALTER MATERIALIZED VIEW hourly_orders
  SET (timescaledb.materialized_only = false);
```

Timescale has reported dashboard queries dropping from minutes to milliseconds after moving to continuous aggregates ([Timescale blog](https://timescale.com/blog/postgresql-timescaledb-1000x-faster-queries-90-data-compression-and-much-more/amp)). Our own numbers are less dramatic, mostly because we try not to let queries reach minutes in the first place.

### Caching for many viewers

When fifty people watch the same dashboard, do not compute the same answer fifty times. Cache the aggregated API response (Redis, or plain HTTP caching) with a TTL that matches your refresh interval. Caching is a multiplier on the other patterns rather than a substitute for them; a cached slow query still means somebody pays full price on every miss.

### Time-series rollups and retention

Nobody needs second-level granularity for last quarter. Keep raw events for 30 days, hourly buckets for a year, daily buckets forever, and drop the rest. Storage shrinks, historical queries speed up, and no chart looks any different. In TimescaleDB this is a retention policy plus a coarser continuous aggregate; in plain Postgres it is one more scheduled job.

## Polling or push

Be honest about how real-time the requirement is. Polling a fresh pre-aggregated table every 5 to 30 seconds covers the vast majority of business dashboards, and it is boring in the best way: stateless, cacheable, easy to debug. Push over WebSockets or server-sent events brings connection management, reconnect logic, and stateful servers, and it earns that cost only on genuinely live screens such as trading or incident response. On most projects we ship polling first, and nobody ever asks for more.

## Postgres or ClickHouse

The benchmark numbers cut both ways, which is what makes them useful. At around 10,000 rows, Postgres is roughly **2x faster** than ClickHouse, with parity arriving near 50,000 rows, per [fiveonefour's comparison](https://www.fiveonefour.com/blog/PostgreSQL-vs-ClickHouse). Past that point the columnar engine takes over: ClickHouse runs analytical aggregations **10-100x faster** than row-store Postgres and has aggregated a billion rows in under 400 ms in [Tinybird's benchmarks](https://www.tinybird.co/blog/clickhouse-vs-aurora-postgresql-performance).

So start boring. A well-indexed Postgres with rollup tables carries dashboards much further than people expect, and it is one database to operate instead of two. Postgres is our default backend for exactly this reason; we wrote up the full stack in our [Next.js, NestJS, and Postgres on Railway guide](/blog/fullstack-nextjs-nestjs-postgres-railway). Add TimescaleDB when time-series volume grows, and reach for ClickHouse when aggregation queries are still slow after pre-aggregation. That day arrives later than most architecture diagrams assume.

## Where this shows up in real work

These patterns matter most when the data comes from more than one place. We built an [operations dashboard for a McDonald's franchisee](/work/mcdonalds-dashboard) that aggregates 8+ systems across 3 restaurants. Each source syncs into the database on its own cadence, rollups normalize everything into shared metric tables, and the dashboard reads only those tables. No page load ever waits on a third-party API. Builds like this are the bread and butter of our [dashboards and tools work](/services/dashboards-tools), backed by the [backend and API engineering](/services/custom-development) that keeps them fast, and like everything we scope, they come with the [exact price and delivery date in writing](/pricing/project) before we start.

## A practical order of operations

1. List the metrics the dashboard actually shows. It is usually fewer than ten.
2. Pre-aggregate those into rollup tables or scheduled materialized views.
3. If refreshes get expensive, move the hot summaries to continuous aggregates.
4. Cache responses when many viewers share the same screen.
5. Poll on a sensible interval, and add push only where seconds change decisions.

If your dashboard is already slow, the fastest path is usually a little embarrassing: find the three worst queries, pre-aggregate them, and be done by Friday. If you would rather have someone who has built a few dozen of these look at it with you, book a [free 15-minute call](https://calendly.com/dock30/15min) or reach us through [contact](/contact). We will point at the three queries costing you the most, whether or not you hand us the work.

## Frequently asked questions

**Why is my analytics dashboard so slow?**

Almost always because every chart aggregates raw rows at view time. A query that scans a few thousand rows is instant, but the same query over tens of millions of rows takes seconds, and every open browser tab multiplies the load. Precomputing summaries into rollup tables or materialized views turns those table scans into index lookups, which is why it is the first fix worth trying.

**Do PostgreSQL materialized views refresh automatically?**

No. As of Postgres 18, a materialized view only updates when you run REFRESH MATERIALIZED VIEW, which most teams schedule with pg_cron. Every refresh recomputes the full query, and the CONCURRENTLY option that keeps reads unblocked requires a unique index on the view. For incremental refresh you need something like TimescaleDB continuous aggregates.

**Do I need ClickHouse for a real-time dashboard?**

Not until your data forces it. Benchmarks show Postgres is roughly twice as fast on tables of about 10,000 rows, with parity around 50,000 rows, and ClickHouse pulling far ahead beyond that. Start with well-indexed Postgres plus pre-aggregation, and switch engines only if aggregation queries stay slow after that.

**Should a dashboard use polling or WebSockets?**

Polling a pre-aggregated table every few seconds is enough for most dashboards and is far simpler to run. WebSockets or server-sent events make sense for genuinely live screens like trading or incident monitoring, where a few seconds of delay changes decisions. We ship polling first and add push only when a specific view needs it.

**How much does a custom analytics dashboard cost?**

It depends mostly on how many data sources you connect and how much cleanup the data needs, not on the charts themselves. At Dock30, fixed-scope projects start at EUR 350 with the exact price and delivery date in writing before work begins. A multi-source operations dashboard is typically weeks of work, not months.

---

Written by Roberto Lazar, founder of Dock30. Book a call: https://dock30.com/contact