Denormalization Patterns in Practice

The normalization page introduced the idea that denormalization is sometimes the right engineering call, not a mistake. This page gets concrete: the specific patterns teams actually reach for in production, what each one costs to maintain, and a full before/after example showing the trade-off in numbers rather than in the abstract.

Pattern 1: read replicas#

A read replica is a copy of the primary database that stays continuously synchronized and serves read-only queries, so expensive reporting and analytics traffic never competes with the primary's write-path OLTP traffic for the same resources. This isn't denormalization by itself — the replica's schema is identical to the primary's — but it's the first tool worth reaching for before reshaping any schema, because it solves a real class of the same underlying problem (expensive reads slowing down the system) with zero schema changes and no risk of the copy drifting out of sync with reality.

Primary  (all writes, plus latency-sensitive reads)
  |
  |  continuous replication
  v
Replica  (reporting queries, analytics dashboards, ad-hoc exploration)

The MySQL section of this track covers exactly how primary-replica replication works under the hood (binlog shipping, replication lag, read/write splitting) — the relevant point here is simpler: once a report or dashboard query is routed to a replica instead of the primary, that query can be as expensive as it needs to be without ever risking the primary's write throughput, and the schema underneath it doesn't have to change at all.

A read replica has a real limit, though: it still runs the exact same expensive join-and-aggregate query the primary would have run, just on different hardware. Once a report is expensive enough (a five-table join with aggregation over tens of millions of rows, run on every dashboard page load), moving it to a replica buys headroom but doesn't remove the underlying cost — that's what the next two patterns are for.

Pattern 2: denormalized reporting tables#

A denormalized reporting table (sometimes called a reporting mart or a summary table) stores the result of an expensive query, kept up to date by a scheduled job or a trigger, so a dashboard reads a plain, cheap SELECT instead of re-running the expensive join every time someone looks at it.

Before: computing the report live, every time#

Take a monthly revenue-by-category dashboard, built on the schema from the case-study page:

SELECT c.name AS category, DATE_FORMAT(o.created_at, '%Y-%m') AS month,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o     ON oi.order_id = o.id
JOIN products p   ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY c.name, DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month, revenue DESC;

Against a small dataset this runs instantly. Against tens of millions of order_items rows, every single dashboard page load re-scans and re-aggregates the entire order history from scratch — the query gets slower every month as more historical data accumulates, even though last month's revenue never changes again once the month is over.

After: a table that already holds the answer#

CREATE TABLE category_revenue_monthly (
    category    VARCHAR(100) NOT NULL,
    month       CHAR(7)      NOT NULL,   -- 'YYYY-MM'
    revenue     DECIMAL(14,2) NOT NULL,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (category, month)
);

A nightly job (or, in PostgreSQL, a materialized view refreshed on a schedule) recomputes and upserts one row per category per month:

INSERT INTO category_revenue_monthly (category, month, revenue)
SELECT c.name, DATE_FORMAT(o.created_at, '%Y-%m'),
       SUM(oi.quantity * oi.unit_price)
FROM order_items oi
JOIN orders o     ON oi.order_id = o.id
JOIN products p   ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY c.name, DATE_FORMAT(o.created_at, '%Y-%m')
ON DUPLICATE KEY UPDATE revenue = VALUES(revenue);

The dashboard's query is now trivial regardless of how much order history has accumulated:

SELECT category, month, revenue
FROM category_revenue_monthly
ORDER BY month, revenue DESC;

The cost moved, it didn't disappear: the expensive aggregation still runs, just once per night against the whole dataset instead of once per page load — and the dashboard is now up to a day stale, which is the trade-off this pattern is explicitly making.

Pattern 3: materialized aggregates kept live#

Sometimes even a nightly refresh isn't fresh enough — a live "total revenue today" counter on an admin homepage, for instance. Rather than re-aggregating on every page load or accepting a day of staleness, a small aggregate table is updated incrementally, in the same transaction as the write that changed it:

CREATE TABLE daily_revenue (
    day     DATE PRIMARY KEY,
    revenue DECIMAL(14,2) NOT NULL DEFAULT 0
);

START TRANSACTION;

-- ... the order's INSERTs from the case-study page's order-placement transaction ...

INSERT INTO daily_revenue (day, revenue)
VALUES (CURRENT_DATE, 24.99 * 2)
ON DUPLICATE KEY UPDATE revenue = revenue + VALUES(revenue);

COMMIT;

Now SELECT revenue FROM daily_revenue WHERE day = CURRENT_DATE is a single-row primary-key lookup, correct to the second, at the cost of one extra small write inside every order-placing transaction — a genuinely different trade-off than the nightly-batch reporting table, buying real-time accuracy in exchange for a bit more write-side work on the hot path.

When each pattern earns its cost#

The rule from the normalization page still holds here: reach for these because a specific, measured query is genuinely too expensive to run live at the frequency it's actually needed, not preemptively. A dashboard nobody has complained about yet doesn't need a reporting table; a dashboard that's measurably slowing down the primary database does.

Common mistakes#

• Building a nightly reporting table before confirming the live query is actually a measured problem — every one of these patterns adds an ongoing maintenance burden (a job that can fail silently, a trigger that can drift) that isn't worth paying for a report nobody has complained about.
• Forgetting that a nightly batch table is supposed to be stale between refreshes, then getting confused when "today's" numbers look wrong on a dashboard reading from it — that's an argument for the live-aggregate pattern instead, not a bug in the reporting table.
• Updating a live aggregate table outside the same transaction as the write that changed it — if the order insert commits but the daily_revenue update fails (or vice versa) as two separate transactions, the aggregate silently drifts from reality with no automatic way to notice.
• Reaching straight for a denormalized reporting table when a read replica alone would have solved the actual problem (the primary being overloaded by read traffic) with far less ongoing maintenance.