Why Your Embedded Dashboards Are Slow (And the SQL Patterns That Fix Them)

You shipped a customer-facing analytics page. It looked great in the demo with 500 rows of seed data. Then a real customer with 4 million events logged in, opened the dashboard, and watched a spinner for 22 seconds before their browser tab quietly gave up.

Here's the uncomfortable truth: when an embedded dashboard is slow, it is almost never the chart library, the network, or React. It's the SQL. You are asking your database to scan, join, and aggregate millions of rows every single time someone opens a report — and most of those rows haven't changed since the last time you did it.

This post walks through why that happens and the concrete SQL patterns that fix it, roughly in the order you should reach for them. We'll use plausible SaaS tables — users, orders, events, subscriptions — and show the query, the problem, and the fix.

The core problem: you're recomputing everything, every time

A typical dashboard tile runs something like this:

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)                      AS order_count,
  SUM(amount_cents) / 100.0     AS revenue
FROM orders
WHERE tenant_id = 42
  AND created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 1;

For a customer with millions of orders, that means a full scan of the last 90 days of data on every page load, for every customer, refiring whenever someone changes a filter. The result — daily revenue for the last three months — barely changes minute to minute, but you pay the full computation cost again and again.

The performance ladder below goes from cheapest fix to most involved. Most teams can stop after step 2.

Step 1: Index for the filters your dashboards actually use

Before anything fancy, make sure the database isn't scanning the whole table to answer a filtered query. Embedded dashboards almost always filter by tenant and a time range, so index for exactly that:

CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at);

Now the query above can jump straight to tenant 42's recent rows instead of reading everyone's history. Confirm it's working with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders
WHERE tenant_id = 42 AND created_at >= now() - interval '90 days';

If you see Seq Scan on a large table in the output, your index isn't being used. If you see an Index Scan or Bitmap Index Scan, you're on the right track. This one change alone often turns a multi-second query into a sub-100ms one.

Step 2: Pre-aggregate with a materialized view

Indexing helps the database find rows fast. But if the dashboard genuinely needs to aggregate millions of rows, the fastest possible aggregation is the one you did earlier and saved. That's a materialized view: a stored snapshot of a query's results that dashboards read directly.

CREATE MATERIALIZED VIEW daily_order_stats AS
SELECT
  tenant_id,
  date_trunc('day', created_at) AS day,
  COUNT(*)                      AS order_count,
  SUM(amount_cents)             AS revenue_cents
FROM orders
GROUP BY tenant_id, date_trunc('day', created_at);

-- required for concurrent refresh (more on that below)
CREATE UNIQUE INDEX idx_daily_order_stats
  ON daily_order_stats (tenant_id, day);

Your dashboard tile now reads from a table that already has one row per tenant per day:

SELECT day, order_count, revenue_cents / 100.0 AS revenue
FROM daily_order_stats
WHERE tenant_id = 42
  AND day >= now() - interval '90 days'
ORDER BY day;

This is the pattern behind the "28 seconds to 180 milliseconds" numbers people quote — you're reading a few hundred pre-summarized rows instead of scanning millions. The catch: the data is only as fresh as your last refresh.

-- rebuilds the snapshot; blocks reads unless you use CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_stats;

CONCURRENTLY lets dashboards keep reading during the refresh, but it requires that unique index and is slower and more resource-hungry than a plain refresh. Schedule it with a cron job or pg_cron every few minutes or hourly, depending on how fresh the numbers need to be. If your users can tolerate data being a few minutes behind — and for most analytics, they can — this is the single highest-leverage change you can make.

Step 3: Roll up incrementally when refreshes get expensive

Materialized views have a sharp edge: plain PostgreSQL rebuilds the entire result on every refresh. There's no built-in incremental mode. If you have three years of history but only today's rows changed, you're recomputing three years of totals to capture one day of new data. As the table grows, the refresh itself becomes the bottleneck.

The fix is a rollup table you update incrementally with an upsert, touching only the days that changed:

CREATE TABLE order_rollups (
  tenant_id   BIGINT,
  day         DATE,
  order_count BIGINT,
  revenue_cents BIGINT,
  PRIMARY KEY (tenant_id, day)
);

-- run every N minutes; only recomputes recent days
INSERT INTO order_rollups (tenant_id, day, order_count, revenue_cents)
SELECT
  tenant_id,
  date_trunc('day', created_at)::date,
  COUNT(*),
  SUM(amount_cents)
FROM orders
WHERE created_at >= current_date - 1     -- yesterday + today only
GROUP BY 1, 2
ON CONFLICT (tenant_id, day)
DO UPDATE SET
  order_count   = EXCLUDED.order_count,
  revenue_cents = EXCLUDED.revenue_cents;

Now the cost of keeping the dashboard fresh scales with how much data changed, not with how much data you have. Rollup tables are more code than a materialized view, so reach for them only when refresh time becomes a real problem.

Step 4: Cache the final result for repeated views

Even a fast query is wasteful if 200 users on the same team load the identical "last 30 days" tile within a minute. Put a cache in front of the query — Redis or Memcached — keyed by the query's inputs, with a short TTL:

cache_key = "dash:orders:tenant=42:range=30d"
value     = <serialized rows>, TTL = 300s

On a cache hit you skip the database entirely. Invalidate on a time-to-live for simple cases, or on write events (a new order arrives) when you need tighter freshness. Caching is the cheapest layer conceptually, but put it last — caching a query that's fundamentally doing a full-table scan just hides the problem until the cache misses.

Common mistakes and gotchas

Mistake	What happens	Do this instead
Refreshing a materialized view without CONCURRENTLY	Dashboards block and show errors during every refresh	Add a unique index and use REFRESH ... CONCURRENTLY
Refreshing far more often than the data changes	Table and index bloat; refresh itself becomes the slow query	Match refresh frequency to real freshness needs; vacuum after refreshes
Full rebuilds on a huge history table	Refresh time grows without bound	Switch to an incremental rollup with ON CONFLICT upserts
Caching before indexing	First load and every cache miss is still painfully slow	Fix the query first, then cache
Trying to serve truly real-time data from a materialized view	Users see stale numbers and lose trust	Use materialized views for near-real-time/historical; query live tables (well-indexed) for the few tiles that must be current

The freshness trade-off deserves emphasis: materialized views and rollups are, by design, a little behind. That's perfect for "revenue over the last 90 days" and wrong for "orders in the last 60 seconds." Know which tiles need which, and don't pay for real-time where nobody needs it.

Key takeaways

Slow embedded dashboards are a data-access problem, not a front-end one. Work up the ladder: index for the tenant-plus-time-range filters your dashboards actually use; pre-aggregate with a materialized view so you read summarized rows instead of scanning raw ones; move to incremental rollup tables when full refreshes get expensive; and cache the finished result for repeated views. Most teams get the win they need from the first two steps, and each step buys you a little staleness in exchange for a lot of speed — a trade almost every analytics tile is happy to make.

Your turn

What's the slowest tile in your product right now, and which layer of this ladder haven't you tried yet? If you're building customer-facing analytics, tools like Draxlr let you build SQL dashboards, add pre-aggregated metrics, and embed them into your app without hand-rolling every one of these layers yourself. Drop a comment with the query pattern that finally fixed your slowest dashboard — I'd love to see the before-and-after numbers.

---

Sources: Metabase — SQL performance tuning, Stormatics — PostgreSQL Materialized Views, Citus Data — Materialized views vs. Rollup tables in Postgres, PostgreSQL docs — REFRESH MATERIALIZED VIEW.