Skip to content
Tutorials Center
My learning

Topics

Lesson 86 of 125 ยท Designing Databases

Designing a Database for Real-Time Reporting

Design a SQL database for fast dashboards: summary tables, incremental aggregates, materialized views, partitioning, read replicas, CDC and covering indexes.

A dashboard that says "sales today" is easy to build and painful to keep fast. At first it is one SUM over the orders table. Then the table grows to 200 million rows, twenty people keep the dashboard open, and every refresh scans the whole history while customers are trying to check out. Reports slow down, checkouts slow down, and someone suggests buying a bigger server.

A bigger server is the last resort. Almost every "real-time" reporting problem is solved by one of a few design moves: do the aggregation once instead of on every page view, keep it up to date incrementally, and keep heavy reads away from the machine that takes the writes. This lesson walks through those moves, from simple to elaborate, and shows you when each one is worth its complexity.

Here "real time" means seconds to a few minutes behind the source, which is what nearly every business dashboard actually needs. Truly instant (sub-second) analytics is a different, more specialized problem.

Step 1: measure what the dashboard really asks

Write down every number the dashboard shows and how fresh it must be. A typical list looks like this:

Tile Question Freshness
Revenue today Sum of paid orders since midnight 1 minute
Revenue by day, last 30 days One row per day 5 minutes
Top products this week Top 10 by units 15 minutes
Lifetime customers Count 1 hour

The slowest-changing, most-queried numbers are the best candidates for pre-aggregation. Do not pre-aggregate a number that is only looked at once a month.

Step 2: a summary table

A summary table (also called an aggregate or rollup table) stores the answer at a coarser grain than the source. Instead of one row per order line, it keeps one row per day. The dashboard reads a few hundred rows instead of millions.

SQL Build a daily summary table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Choose the grain (what one row means) carefully. A row per day can answer "revenue by day" but not "revenue by hour". If you may need hourly numbers, store the hour. You can always roll hours up into days, but you can never split a day into hours.

Always add a unique key on the grain columns. It documents the grain and makes incremental updates (below) simple and safe.

Step 3: keep it fresh

A summary table is only useful if it matches the source. You have three ways to keep it current, and they trade complexity against delay.

Option A: rebuild periodically

A scheduled job recomputes the summary every few minutes. It is the simplest approach and works well when the data is small enough to recompute, or when you only recompute the part that can still change, such as the last two days. Make the job idempotent: running it twice gives the same result.

SQL Rebuild only the recent days ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Delete-then-insert inside one transaction is the usual pattern, so a dashboard never reads a half-refreshed table.

Option B: update incrementally

Instead of recomputing, add each new fact to the summary as it arrives. A trigger can do it inside the same transaction as the insert, so the summary cannot disagree with the source. With an upsert, one statement handles both "first sale of the day" and "another sale today":

SQL An incremental summary with a trigger

Press Run (or Ctrl + Enter) to see the result here.

Incremental maintenance gives the freshest numbers at the lowest read cost, but it has a price. Every write now does extra work, rows that are updated or deleted need matching decrement logic, and a hot summary row (today's total) becomes a lock everyone queues on. For busy systems it is common to buffer: append raw events to a log, and let a small background job fold them into the summary every few seconds.

Option C: let the database do it (materialized views)

A materialized view stores a query's result like a table and refreshes it on demand. PostgreSQL, Oracle and Snowflake have them; SQL Server uses indexed views; MySQL and SQLite do not, so you build the summary table yourself, as above.

SQL
-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT order_date::date AS sale_date, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_sales_mv (sale_date);

-- Later, from a scheduled job. CONCURRENTLY lets dashboards keep reading during the refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;

PostgreSQL recomputes the whole view on every refresh, so it fits medium-size data and "every few minutes". Oracle can do incremental (fast) refresh with materialized view logs, and SQL Server's indexed views are maintained automatically on every write, with many restrictions on what the query may contain.

Step 4: index for the dashboard

When the dashboard must read the source table anyway (a "last hour" chart over fresh rows), make that query an index-only read. A covering index holds every column the query touches, so the database never visits the table. Check it with the plan (see Reading Query Plans):

SQL A covering index for a dashboard query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN orders; the second says SEARCH orders USING COVERING INDEX. Put the range column (ordered_at) first and the grouped column next. Resist adding an index for every tile: each index slows every insert, and a summary table usually removes the need.

Step 5: partition by time

Reporting data is almost always append-mostly and time-oriented. Partitioning the fact table by month or day has two benefits for dashboards. Queries with a time filter touch only the partitions they need, and old partitions can be detached or dropped instantly instead of with a slow bulk DELETE. Pair it with a retention policy: raw events for 90 days, daily summaries for years.

Step 6: separate reads from writes

Even a well-tuned report competes with transactions for CPU, memory and disk. Move it away:

  • Read replicas. A copy of the database that applies the primary's changes a moment later (see Replication and Scaling). Point dashboards at the replica. Expect a lag of milliseconds to seconds, and design the UI to tolerate it ("as of 14:02").
  • A separate analytics store. A column-oriented warehouse (for example ClickHouse, BigQuery, Snowflake or Redshift) is built for scanning and aggregating huge tables. It is the destination of the star schema design.
  • Change data capture (CDC). Instead of the warehouse querying the source on a schedule, a tool reads the database's write-ahead or binary log and streams every committed change to the analytics store (Debezium and the cloud providers' replication services do this). It keeps the load on the source small and the delay in seconds, but it adds moving parts: an event stream, schema evolution to handle, and a store to operate.

A decision guide

Situation Reach for
Small data, slow queries The right index, then a better query
One expensive number read often Summary table refreshed every few minutes
Must be seconds-fresh, moderate write rate Incremental summary (trigger or event job)
Reports slow down checkout Read replica
Billions of rows, many dimensions Warehouse fed by CDC, star schema
Retention and cleanup pain Time partitioning

Start at the top and move down only when the simpler step stops being enough.

Pitfalls

  • Summaries that drift. Late-arriving, edited or cancelled rows make summaries wrong. Decide how corrections reach the summary: a periodic rebuild of recent days is the safety net for an incremental design.
  • Time zones. "Today" means different days for different users. Store timestamps in UTC and decide the reporting time zone before you pick the daily grain.
  • Non-additive measures. Sums and counts roll up; averages and distinct counts do not. Store the sum and the count, then divide at read time, and keep distinct counts at the level you need.
  • Hot rows. One summary row updated by every request becomes a lock contention point. Shard the counter (several rows per day, summed on read) or batch the updates.
  • No definition of "correct". Reconcile summaries against the source regularly (the SUM of the summary equals the SUM of the facts) and alert on any difference.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does a summary table make a dashboard faster?

    Aggregating once and reading many times trades a little storage and write work for far cheaper reads.
  2. 2. What does the "grain" of a summary table mean?

    Grain is the level of detail of one row. You can roll a fine grain up to a coarser one, but you cannot split a coarse grain into finer detail.
  3. 3. A periodic summary refresh should be idempotent. What does that mean in practice?

    Deleting the affected period and re-inserting it (in one transaction) means a retry or a double run cannot double-count.
  4. 4. Which statistic cannot be rolled up by simply adding daily values?

    A customer who ordered on two days is counted twice if you add daily distinct counts. Sums and counts are additive; distinct counts and averages are not.
  5. 5. What is the main purpose of change data capture (CDC) in a reporting design?

    CDC reads the database's change log, so the analytics system gets near-real-time data without running heavy queries against the transactional database.

Summary

  • Fast dashboards come from doing the aggregation once and reading it many times, not from bigger servers.
  • Pick the grain deliberately, add a unique key on it, and prefer additive measures (sums and counts).
  • Keep a summary fresh by periodic idempotent rebuilds, incremental updates (triggers or an event job), or a materialized view where the database has one.
  • Use covering indexes for the queries that must hit the fresh source rows; check the plan.
  • Partition fact tables by time and apply retention to raw rows.
  • Move reporting reads to a replica or an analytics store; CDC streams changes there with a delay of seconds.
  • Reconcile summaries against the facts regularly, and decide the reporting time zone up front.

Next: Database Migrations and Schema Changes, where you'll learn how to change a live schema without breaking the systems that depend on it.