Flattened Tables vs Denormalized Tables vs Materialized Views: the Decision Guide, and How Incremental Flattened Tables Fixed Superset

TL;DR: A BI virtual dataset is just stored SQL — every chart on it re-runs that SQL against the source on every render, per open browser tab, and a timed refresh bypasses the cache. On our estate 89% of 879 datasets were virtual, and the worst chart took 244–300 s p95 while two open tabs produced ~961 MB/s of buffer traffic into a shared primary that 30+ tenants also transact on. The three ways to fix it — denormalized table, materialized view, flattened (snapshot) table — all store a pre-computed shape, but they differ in where the copy lives, who maintains it, and how it's refreshed — and they fail differently. Denormalized tables tax every write forever and couple reports to application code. Materialized views re-run the same expensive query on the same production server, on a timer. The one that scaled: a flattened table in a separate reporting database, refreshed incrementally every 15 minutes by a dedicated, resource-restricted, read-only ETL role. Result: worst chart 286 s → 2.8 s, the primary's exposure cut from entire scan per render to one bounded query per table per 15 min — about 1–2 minutes of DB time per hour total — with full attribution in every statistic.

This started with the most useful kind of complaint: tenants without a single dashboard open. Their queries got slow, then slower, with no Monday release and no schema change — just two browser tabs in the analytics platform doing what they were told. When I looked at what those two tabs were doing, the measured number was 128,109 rows/s and ~961 MB/s of buffer and disk traffic. The transfer function of the noisy-neighbour problem: one dashboard's scan streaming through the shared page cache and evicting the hot pages every other tenant depended on.

What is the database supposed to do when served that? My answer. Your answer is probably faster.

The problem all three patterns solve

A BI tool's virtual dataset is stored SQL. Superset, Metabase, Grafana — same shape. Every chart on that dataset re-executes the SQL against the source on every render: once per tile, once per open browser tab, and the dashboard's auto-refresh bypasses the cache out of the box. On our estate, 89% of 879 datasets were virtual. The worst one was 11,396 characters of SQL — 20+ CTEs, window functions, an 11-branch UNION ALL — sitting behind 14 tiles auto-refreshing every 300 s with force=true. Sampled on the busiest tenant database over a 12.5-second window:

Two connections producing a gigabyte per second. Every pass over the multi-GB audit/message heap streamed through the shared page cache and evicted the pages everyone else was using — which is why the tenants complaining had no dashboards open.

The fix in one sentence: pre-compute the shape, and move the cost from "every render" to "once per refresh interval."

                     dashboard render
                            │
   ┌────────────────────────┼─────────────────────────────┐
   │ virtual dataset        │ pre-computed shape          │
   │                        │                             │
   │  SELECT ... 20 CTEs    │  SELECT ... FROM snapshot   │
   │  ↓ every render        │  ↓ every render             │
   │  tenant DB: 30-300 s   │  reporting DB: 0.01-2 s     │
   │  CPU + cache eviction  │  nothing touches prod       │
   └────────────────────────┴─────────────────────────────┘

The three patterns that store that pre-computed shape differ in exactly three ways: where the copy lives, who maintains it, and how it is refreshed. Everything in this post — including the production system I now run — follows from those three axes.

Definitions

Denormalized table. A table the application maintains at write time: when a case is created or updated, application code (or a trigger) also writes the wide, join-free row the reports want. Reads are trivial; the cost is paid on every write, forever, in the application's transaction.

Materialized view. A database object in the same database as the source: CREATE MATERIALIZED VIEW ... AS <query>. PostgreSQL stores the query result; REFRESH MATERIALIZED VIEW re-runs the whole query and replaces the contents. CONCURRENTLY avoids the read lock but requires a unique index and still recomputes everything, then diffs.

Flattened table (snapshot table). A plain table in a separate reporting database, filled by an external worker that runs the dataset's query against the source on a schedule and writes the result. In our implementation the refresh is incremental: the query is bounded by a watermark and only the entities that changed since the last run are recomputed and merged. The BI tool reads the reporting database only.

They all store a pre-computed shape. The comparison table is where the differences stop looking academic:

Where each one breaks

Denormalized tables break the write path

Every extra column the reports want is a write the application must perform, in its own transaction, forever. Report requirements change monthly; the application's write path should not. On a multi-tenant platform where dozens of tenants share one codebase, this also means a report for one tenant changes code for all of them. Right tool when the "report" is really a product feature — an activity feed, a counter on a screen — and the wrong tool for analytics.

Materialized views break the production database

This is the one people keep treating as free, and it is the opposite:

• REFRESH runs the full query on the production server. For the 11-CTE dataset above: 168 s average / 594 s max, every 30 minutes. Of 486 scheduled runs, 415 were skipped because the previous one still held the lock. The refresh is the load you were trying to remove — just on a timer.
• REFRESH ... CONCURRENTLY needs a unique index and then recomputes everything before it can diff instead.
• There is no incremental refresh in core PostgreSQL (pg_ivm exists with restrictions: no outer joins, no window functions, no CTE materialisation — i.e. not for real dashboard SQL).
• The definition lives in the database catalog. When a schema holding the materialized views was dropped by mistake, the definitions went with it, unrecoverable — only the registry row of the flattened table survived, and that's what the rebuild was reconstructed from.
• A now() in the definition is frozen at refresh time: an "open cases older than 24 h" tile is wrong for the entire interval.

Flattened tables break when the merge key is wrong

The incremental merge recomputes entities, not rows. If the merge key is a child row (an audit event) instead of the parent (the case), a child that should disappear is simply absent from the batch and is never deleted — the table drifts silently for weeks. This is the one failure mode that is worse than the others because it is quiet: 764 daily buckets "matched" while the actual tile — an all-time SUM — was off by 26%. Which is why the grain is a closed list, and why every new table is verified at the grain the consumer uses.

The production fix: incremental flattened tables

The exhaustion, re-measured as an architectural claim

Everything above is the decision guide; what I actually run is the flattened-table column of that table, on a dedicated cluster. The architecture has three parts, each doing one thing.

 before                                     after
 ┌────────────┐   every render,             ┌────────────┐  one bounded query
 │ Superset   │──20 CTEs, 30-300 s──►       │ Superset   │──every 15 min, ETL role──►  tenant DB
 │ dashboards │   tenant DB (primary)       │ worker     │  (only entities changed in the last hour)
 └────────────┘                             └─────┬──────┘
   ↑ 961 MB/s, cache eviction,                    ▼  merge
   ↑ 100 % CPU, everyone slows                ┌────────────────────┐
                                               │ reporting DB       │◄── dashboards read here,
                                               │ flattened tables   │    1-2 s per chart
                                               └────────────────────┘

1. A dedicated reporting database

A reporting database on a separate cluster. Dashboards read only this database, through a read-only role. Whatever a dashboard does — 14 tiles refreshing, a user paging through 5,000 rows, an ad-hoc query in SQL Lab — it happens here, against snapshot tables with their own indexes, and the primary never sees it.

2. A dedicated, resource-restricted refresh user

The only thing that still touches the primary is the refresh worker, and it logs in as an ETL role — created once per cluster with a profile that bounds every query it can run:

ALTER ROLE superset_etl CONNECTION LIMIT 10;                  -- 5 on the 2-core server
ALTER ROLE superset_etl SET default_transaction_read_only       = on;
ALTER ROLE superset_etl SET statement_timeout                   = '600s';
ALTER ROLE superset_etl SET transaction_timeout                 = '900s';
ALTER ROLE superset_etl SET lock_timeout                        = '5s';
ALTER ROLE superset_etl SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE superset_etl SET work_mem                            = '64MB';   -- 32MB on the 2-core server
ALTER ROLE superset_etl SET max_parallel_workers_per_gather     = 1;        -- 0 on the 2-core server
ALTER ROLE superset_etl SET jit                                 = off;

It cannot write, cannot hold more than a handful of connections, cannot fan out across cores, and cannot run past its cap. The application keeps its own roles with their own caps; the admin role stays unrestricted as the recovery path. One thing comes free — in pg_stat_statements and pg_stat_activity you now see exactly what Superset costs: on the busiest tenant, superset_etl totals ~14,000 s of execution over its lifetime against ~244,000 s for the application itself. Previously the answer to "what does the analytics platform cost us?" was one shared admin role doing everything — i.e. "nobody knows."

3. Incremental refresh, every 15 minutes

A full rebuild of a flattened table is the same expensive query on a timer — it was 168 s average / 594 s max for the support-KPI table, and 415 of 486 runs were skipped because the previous one still held the lock. The fix is to recompute only what changed.

Each table is a registry row: the query text, the source connection, interval 900 s, timeout 450 s, lookback 3600 s, the merge key, and a last_watermark. A beat task runs every minute and dispatches every enabled table whose last success is older than its interval. The query is written once, bounded by :watermark:

WITH changed_cases AS MATERIALIZED (            -- the entities to recompute
  SELECT DISTINCT case_id FROM (
    SELECT c.id AS case_id FROM public.cases c
     WHERE c.updated_at >= :watermark                       -- the case row changed
    UNION ALL
    SELECT e.entity_id FROM public.audit_events e
     WHERE e.created_at >= :watermark                      -- or an event was written against it
       AND e.attribute IN ('owner', 'queue', 'status')
  ) z
),
base AS MATERIALIZED (
  SELECT c.* FROM public.cases c
  WHERE c.id IN (SELECT case_id FROM changed_cases)         -- every later CTE joins this
    AND COALESCE(c.is_deleted, false) = false
),
-- ... the dataset's own CTEs, unchanged, reading base
SELECT b.id::text AS case_uuid, ...

The worker merges by entity — DELETE FROM live WHERE (case_uuid) IN (staging); INSERT staging — recomputing the whole case, not just the rows since the watermark, which is what keeps sequence numbers, "next assignment" lookups, and removed events correct. The 1-hour lookback is what makes a missed tick harmless. Every attempt is written to a run log; a failed run leaves the last good snapshot serving and does not advance the watermark.

Two rules, each paid for by a defect:

• The changed-set predicates use only indexed columns on the source. The audit table is indexed on created_at, not updated_at — an OR updated_at turned a 15-minute tick into a 28 GB scan.
• now() is never stored. Handling-hours and case-ageing columns are computed in a thin view at read time, because an idle entity is exactly the one the watermark never recomputes. A stored now()-derived value freezes at refresh time on precisely the rows that matter.

What it changed

Per dataset:

Across the estate, two weeks later:

The remaining live datasets still go to the primary — as the read-only role, with a 120 s cap, 8 MB work_mem, no parallelism, and through the transaction-mode pooler so excess queues instead of erroring. A spike is now bounded: at most the pool size in single-threaded queries for at most 120 s. And the ranking that says which dataset to flatten next comes from Superset's own logs.

The decision guide

Does the number have to be exact this second?
   yes → stay on the live query; cap it with a read-only role +
         statement_timeout + a pooler, and accept it.
   no  ↓
Is the shape a product feature the app already writes?
   yes → denormalized table / counter in the app.
   no  ↓
Is the source DB otherwise idle and the query cheap (< 1 s, < 1 M rows)?
   yes → materialized view + pg_cron is fine.
   no  → flattened table in a reporting DB, incremental refresh.

The rule of thumb comes from measurement, not taste: rank by executions_per_day × p95_seconds (DB-seconds/day), not by query count or duration alone. A 40 ms query run 90,000 times costs more than a 20 s query run twice — and only one of them shows up in a slow-query log. Then check feasibility: min_interval = greatest(60, ceil(p95 × 5)). If the dashboard needs fresher data than that, flattening is the wrong tool — the query has to get cheaper first. Flattening moves load; it does not make a slow query fast.

Cheat sheet:

The rules that cost me

1. A materialized view is not free. The refresh is the same query, on the same server, on a timer — and it skips itself when it overruns (415/486 skipped). Flattening with a full rebuild on a timer is the identical mistake wearing a different name.
2. Never run the refresh as the admin or the application's role. No cap, no attribution. The reason the fix still works today is that superset_etl is measurable in every statistic.
3. The merge key is the parent, not the child. Choose the child row and deletes silently stop propagating; the drift is invisible until the tile is off by 26% while 764 bucket-counts "match."
4. Never store now()-derived columns. They freeze at refresh exactly on the rows the watermark never touches. Store the raw timestamps; compute ageing in a view.
5. Changed-set predicates must be indexed on the source. One OR updated_at on an audit table turned a 0.1–0.3 s tick into a 28 GB scan.
6. Declare the schema; seed with COPY. Letting the writer infer types (pandas.to_sql) turns a nullable integer into double precision, an all-NULL column into text, and a numeric money value round-trips through a float. Explicit DDL, types from the source planner.
7. Verify at the grain the tile uses, not the grain you designed. A sum in a tile can be wrong while every daily count is right.

Why this scales

• Cost is paid once per interval, not once per render. 14 tiles on one dataset were 14 executions per refresh per tab; now they are one 3-second merge every 15 minutes, shared by every viewer.
• Reads and refreshes are decoupled. Adding a dashboard, a user, or a tenant adds load to the reporting DB — not to the primary, whose exposure is tables × (tick time / 900 s): small and linear.
• The refresh is proportional to change, not to history. A 418k-row table with 8 changed cases per tick costs the same as a 40k-row one. History grows; the tick does not.
• Failure is contained. A broken definition is one failed row in the run log with the old snapshot still serving; a broken chart is a slow tile behind a cap, not a server at 100% CPU.
• It is operable by someone without server access. The definition is a registry row with history — creating, seeding, verifying and cutting over a table is a sequence of tool calls against the registry, not a production DDL session.

The lessons, condensed

1. A virtual dataset is the same query as a materialized view, just without the schedule — and both run on the production server. When dashboards are slow, the first question is not "how do I tune this" but "why is this rendering doing analytical work against the transactional DB at all?"
2. Where the copy lives is the important axis. Same DB as the source means every refresh competes with the workload it was meant to protect. A separate reporting database moves moves the blast radius from "every tenant on the server" to "that dashboard."
3. Incremental is not optional at tenant scale. A full rebuild on a 594 s max query fails itself 85% of the time; recomputing ~14 changed rows per 15 minutes makes the tick an afterthought.
4. Roles are the instrumentation. The ETL role's caps make the risk bounded today; its name makes the cost knowable every day — 14,000 s vs 244,000 s, attributable for the first time.
5. The quiet failure is the dangerous one. A wrong merge key, a frozen now(), an unindexed watermark predicate — each one drifts for weeks before anyone notices, and each one was caught only by verifying at the consumer's grain.
6. Decide by executions_per_day × p95, and pick the cheapest honest tool — denormalized counters for product features, materialized views only on quiet small sources, flattened tables for everything that serves dashboards at tenant scale.

Related posts from the same operating theme: why a busy primary needs PgBouncer and every line of its config (the pooler that bounds the remaining live datasets), and VACUUM vs VACUUM FULL vs pg_repack (the other half of keeping a shared primary alive).

Find me on GitHub — corrections and dashboard-vs-production war stories welcome.