Real-Time Metrics Dashboards with SQL: Patterns That Actually Scale

You ship a SaaS app. A customer asks for a dashboard. "Just show us live numbers," they say. Easy.

Three weeks later, your SELECT COUNT(*) FROM events WHERE created_at > now() - interval '24 hours' query is taking eight seconds, your database CPU is pegged, and every dashboard auto-refresh slows down the rest of the app. Sound familiar?

Real-time metrics dashboards look trivial on the product roadmap and turn into a swamp the moment real data shows up. The good news: most of the hard parts have well-understood SQL patterns. You don't need a separate streaming database, a Kafka cluster, or a six-figure analytics vendor to ship a fast, live dashboard. You just need to stop treating "real-time" as one problem.

This post walks through the SQL patterns that hold up when traffic grows: pre-aggregation, materialized views, change streaming with LISTEN/NOTIFY, and how to pick the right refresh strategy for each tile. Examples use PostgreSQL, but the patterns work in MySQL, SQL Server, or any modern relational database.

What "real-time" actually means

Before writing a single query, ask: how fresh does this number need to be?

A lot of "real-time" requirements melt away under that question. A signup funnel doesn't need sub-second updates. An admin status page might need true streaming. A revenue chart for last quarter doesn't need any freshness at all. Group the tiles on your dashboard into three buckets:

Most dashboards are 80% historical, 15% near real-time, and 5% true real-time. The reason your dashboard is slow is almost always that you put a tile in the wrong bucket.

Pattern 1: Pre-aggregation for historical tiles

The cheapest query is one you've already run. For anything that doesn't need to change minute-to-minute, roll it up into a separate table.

Say you have a 200 million row events table and you want a chart of daily active users for the last 90 days. Running the raw query every page load is malpractice:

-- DON'T DO THIS on every dashboard refresh
SELECT date_trunc('day', created_at) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM events
WHERE created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 1;

Instead, build a rollup table and refresh it once a day:

CREATE TABLE daily_active_users (
  day        DATE PRIMARY KEY,
  dau        INTEGER NOT NULL,
  computed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Run nightly via pg_cron, your job runner, or a scheduled task
INSERT INTO daily_active_users (day, dau)
SELECT date_trunc('day', created_at)::date AS day,
       COUNT(DISTINCT user_id)
FROM events
WHERE created_at >= current_date - interval '2 days'
  AND created_at <  current_date
GROUP BY 1
ON CONFLICT (day) DO UPDATE
  SET dau = EXCLUDED.dau,
      computed_at = now();

Your dashboard query becomes a trivial scan of 90 rows:

SELECT day, dau
FROM daily_active_users
WHERE day >= current_date - 90
ORDER BY day;

Latency drops from seconds to under a millisecond. This is the same trick used inside almost every analytics product you've ever paid for.

Pattern 2: Materialized views for near real-time

For tiles that need to be a few minutes fresh — open tickets, signups today, MRR this hour — a materialized view gives you the same precompute benefit with less plumbing.

CREATE MATERIALIZED VIEW mv_signups_today AS
SELECT plan,
       COUNT(*)                          AS signups,
       COUNT(*) FILTER (WHERE paid)      AS paid_signups,
       MAX(created_at)                   AS last_signup_at
FROM users
WHERE created_at >= current_date
GROUP BY plan;

CREATE UNIQUE INDEX ON mv_signups_today (plan);

Refresh it on a schedule:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_signups_today;

CONCURRENTLY is the magic word — it lets readers keep querying the old data while the new snapshot is built. Without it, your dashboard locks every time the view refreshes. And CONCURRENTLY requires a unique index on the view, which is why we created one above.

How often to refresh depends on the tile. A pg_cron job that runs every minute is fine for most "live" SaaS metrics:

SELECT cron.schedule('refresh-signups', '* * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_signups_today$$);

Vanilla PostgreSQL materialized views always do a full rebuild — there's no built-in incremental mode. For big tables you'll want incremental view maintenance via an extension like pg_ivm, or a streaming database that maintains views incrementally for you.

Pattern 3: True real-time with LISTEN/NOTIFY

Some tiles genuinely need to update the instant something happens — an order placed, a build finished, a user joined a call. Polling for those is wasteful, especially across many concurrent dashboard users. PostgreSQL has a built-in pub/sub mechanism that's perfect for this.

A trigger emits a notification whenever a row changes:

CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'orders_channel',
    json_build_object(
      'op',     TG_OP,
      'id',     NEW.id,
      'amount', NEW.amount,
      'status', NEW.status
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_notify
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Your app subscribes once per process and fans the update out to every dashboard client over Server-Sent Events or WebSockets:

# pseudo-Python with asyncpg
async with pool.acquire() as conn:
    await conn.add_listener('orders_channel', on_order_change)
    await asyncio.Future()  # keep the listener alive

def on_order_change(conn, pid, channel, payload):
    data = json.loads(payload)
    broadcast_to_dashboard_clients(data)

Now a dashboard with 50 active viewers makes one database connection for the listener, instead of 50 clients each polling every few seconds. This is the same idea Supabase Realtime productizes through a managed WebSocket layer.

Pattern 4: Time-bucketed aggregations that match the chart

Even with rollups in place, the SQL that drives a chart should match the chart's resolution. A line chart for the last 24 hours needs about 1,440 data points at minute granularity — anything finer just slows things down and looks worse.

SELECT date_trunc('minute', created_at) AS bucket,
       COUNT(*)                          AS event_count,
       COUNT(DISTINCT user_id)           AS unique_users
FROM events
WHERE created_at >= now() - interval '24 hours'
GROUP BY 1
ORDER BY 1;

For longer windows, drop the resolution. A monthly chart at minute granularity is 43,200 rows — your browser will choke. Hour buckets give 720 rows, daily buckets give 30. Match the bucket size to the window:

Pair this with the right index — usually a B-tree on created_at, and a BRIN index if the table is append-mostly and large — and these queries stay under 100 ms even on billions of rows.

Common mistakes that kill dashboards

A few patterns to avoid:

The first is counting everything on every load. SELECT COUNT(*) FROM orders on a 50 million row table will scan the table and ruin your day. Either keep a running counter in a small summary table, or use pg_class.reltuples for an estimate when exact accuracy doesn't matter.

The second is SELECT * in dashboard queries. Every extra column is bytes over the wire and memory pressure on the database. Pick the columns you need.

The third is N+1 dashboard queries. A dashboard with 12 tiles often issues 12 separate queries. With a 50 ms round trip, that's 600 ms of latency before anything renders. Batch related tiles into a single query using CTEs, or run them concurrently from the application.

The fourth is refreshing a materialized view without CONCURRENTLY. The first time it happens during business hours, you'll know. The view takes an ACCESS EXCLUSIVE lock and every other query waits.

The fifth is forgetting time zones. date_trunc('day', created_at) uses the session time zone, so a customer in Mumbai and one in Berlin will see different "today" numbers. Always be explicit: date_trunc('day', created_at AT TIME ZONE 'UTC').

Putting it together

A practical dashboard on PostgreSQL alone looks like this: historical tiles (last 90 days revenue, all-time signups) query nightly rollup tables; near real-time tiles (signups today, open tickets, MRR this week) read from materialized views refreshed every minute; the one or two true real-time tiles (active users right now, last order) subscribe to a LISTEN/NOTIFY channel and update via SSE.

That stack handles thousands of concurrent dashboard users on commodity hardware. When you outgrow it — usually around tens of millions of events per day — you can graduate to TimescaleDB continuous aggregates, ClickHouse, or a streaming database without rewriting your dashboard logic.

Key takeaways

Real-time is a spectrum, not a setting. Sort your tiles by freshness requirement first, and most of your performance problems disappear. Pre-aggregate anything historical. Use materialized views with CONCURRENTLY for near real-time. Reach for LISTEN/NOTIFY only when you genuinely need sub-second updates. Match bucket granularity to chart resolution. And test with realistic data volumes — a dashboard that's fast on 10,000 rows tells you nothing about how it behaves on 100 million.

Over to you

What's the slowest dashboard query you've ever shipped, and what fixed it? Did you end up with a rollup table, a materialized view, a streaming database, or did you just throw more hardware at it? Drop a comment — especially if you've battle-tested one of these patterns at scale, or if you use a different one entirely.

If you're building this inside your own SaaS product, the patterns above will get you far. When you want to stop hand-rolling chart components and SQL editors, tools like Draxlr, Metabase, or Cube can handle the embedding layer so you can focus on the queries.