Denormalization in Databases: When and How to Use It

I’ve written a lot about normalization and why it is such a reliable foundation for data integrity. I am not contradicting myself here. I still believe a well-normalized schema is the right starting point for most transactional systems. What this article covers is the deliberate choice to go against that purity in very specific situations where read performance matters more than strict normal form.

Denormalization is not skipping normalization. It is a performance optimization that you apply to a normalized model when real queries, real users, and real SLAs tell you that joins and on-the-fly calculations are too slow or too costly. In practice, you trade faster reads and simpler queries for more storage, more complex writes, and extra consistency work.

In this article, I’ll show you where denormalization is useful and when it is not, and how to implement it safely in SQL databases. The goal is not to abandon good design, but to layer shortcuts on top of it when the workload justifies it.

What is Denormalization in Databases?

If you are familiar with normalization and database design, here is the short answer: 

Denormalization is the deliberate act of adding redundant data to a previously normalized schema to speed up reads and simplify queries. It’s a targeted performance optimization, not an excuse to skip good modelling!

If you’re fairly new to database design, then it might be worth breaking down the meaning of “normalized”, “denormalized” and “unnormalized”. You hear those 3 terms quite often online, and it is important not to get them mixed up.

Normalized: Data is split into well-structured tables that minimise redundancy and protect integrity (see 3NF/BCNF).

Denormalized: You re-introduce selective redundancy with extra columns, precomputed values, or pre-joined tables, on top of that normalized model to make common reads faster. With denormalization, you keep a single source of truth (the normalized tables), then maintain one or more faster representations for hot paths, like dashboards, product listings, search, etc. You trade storage + write complexity for read speed + simpler queries.

Unnormalized: Raw, ad-hoc, or messy data where structure and constraints were never properly designed. That’s not what we’re doing here.

Example in SQL

Here’s a tiny example in SQL to help you visualize the difference between normalized and denormalized modelling.

Normalized modelling

-- Source of truth
CREATE TABLE customers (
  customer_id   BIGINT PRIMARY KEY,
  name          TEXT NOT NULL,
  tier          TEXT NOT NULL
);

CREATE TABLE orders (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customers(customer_id),
  order_total   NUMERIC(12,2) NOT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT now()
);

-- Typical report needs a join
SELECT c.name, c.tier, SUM(o.order_total) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.name, c.tier;

Denormalized for a reporting path

-- Add redundant fields for faster reads
ALTER TABLE orders
  ADD COLUMN customer_name TEXT,
  ADD COLUMN customer_tier TEXT;

-- Now most reports avoid the join
SELECT customer_name, customer_tier, SUM(order_total) AS revenue
FROM orders
GROUP BY customer_name, customer_tier;

In this example, you’d keep customers as the source of truth and ensure orders.customer_name / orders.customer_tier stay in sync (e.g., trigger, CDC job, or a scheduled backfill). Same truth, two representations, each optimised for a different job.

Is denormalization bad design?

There is a common misconception that denormalization is just a product of bad design. It is not the case if it’s measured, scoped, and maintained. Yes, denormalization often violates higher normal forms (that’s the point), but it’s intentional and backed by a plan to preserve consistency. Bad design is skipping normalization altogether or sprinkling redundancy without a sync strategy.

If you want to learn more about database design, our beginner’s course on the topic is a good place to start!

Normalization vs Denormalization

Here’s the side-by-side comparison. It is not exhaustive but will help you understand the tradeoffs of each approach.

Why and When to Denormalize

Denormalization becomes really useful when real users and real queries are blocked by joins, aggregations, or repeated lookups. Once you’ve confirmed that indexing, query tuning, and caching aren’t enough, you might want to turn to denormalization to optimise for fast reads on predictable access patterns.

When denormalization might help

• Read-heavy workloads: p95/p99 latencies dominated by joins or aggregations, CPU spent on hash/merge joins, high buffer cache churn.
• Stable query shapes: The same dashboards/endpoints run all day with similar filters (e.g., yesterday’s sales by category).
• Fan-out joins: One hot table joins to 3-5 others just to render a card or list view.
• Aggregation hotspots: You repeatedly compute totals, counts, or latest values over large ranges.
• SLA pressure: The product needs sub-200 ms responses where current plans spill to disk or scan too much.

Classic use cases

• Dashboards & BI reporting (OLAP/analytics): Precompute daily/monthly aggregates, keep materialized views of expensive group-bys, or store denormalised fact tables for common slices.
• E-commerce/catalogue & search/listing pages: Duplicate category_name, brand_name, price_with_tax, or a pre-joined product projection for fast lists and filters.
• CMS/blog/news feeds: Store author_name, primary_topic, or rendered_excerpt on the article/post table to avoid joins or runtime transforms.
• Activity feeds & counters: Keep like_count, follower_count, or latest_comment_at as derived attributes instead of recalculating.
• Event/log analytics at scale (OLAP/NoSQL): Flatten nested data for columnar stores and keep partition-friendly wide rows to make scans predictable.

When not to denormalize and use something else first

• Write-heavy OLTP with strict consistency (orders, payments, inventory adjustments).
• The bottleneck is missing/poor indexes, N+1 queries, or chatty ORM. Fix those first.
• High-volatility fields (e.g., product availability changing every second) where duplication amplifies churn.
• Teams without a clear ownership and sync plan (triggers, CDC/jobs, refresh policies, drift monitoring). Without this in place, you risk doing more damage than good!
• The dataset is small enough that a covering index or cache already makes it fast.

You really want to use denormalization where it gives you the most read speed for the least additional operational burden, and only after you’ve ruled out cheaper fixes.

If you have realised that denormalization isn’t quite what you need right now, stay put! We’ll explore the alternatives to denormalization in the next section.

Alternatives to Denormalization

Denormalization is not the answer to all your problems. Before you add redundancy, you need to squeeze everything you can out of the engine and your app. These fixes are cheaper to maintain and often deliver the same wins.

1) Indexing

• Composite/covering indexes: In your SQL queries, put the filter columns first, then the GROUP BY/ORDER BY columns. I include select-list columns so the engine can serve the query from the index alone.

• Filtered/partial indexes: Index just the hot slice (e.g., status = 'ACTIVE'), keeping the index small and fast.

• Expression/functional indexes: Index on LOWER(email) or date_trunc('day', created_at) to avoid computed scans.

2) Query tuning & pagination

• Avoid SELECT *. Fetch only what you display.

• Replace unnecessary joins with EXISTS/SEMI when you only need to check presence.

• Push predicates down: filter early, aggregate late.

• Use keyset pagination (WHERE created_at < ? ORDER BY created_at DESC LIMIT 50) for stable, fast scrolling.

3) Caching

• Use application cache (e.g., Redis) for hot queries and rendered fragments.
• Use HTTP caching (ETag/Last-Modified) for public pages and dashboards.
• Short-lived caches (30–120 s) often remove the need for schema changes.

4) Read replicas

• Offload heavy reads to replicas. Great for dashboards and exports.

5) Partitioning & pruning

• Range/Hash partition big tables so your scans only touch relevant partitions (e.g., last 30 days). This isn’t denormalization, by the way, it’s simply reducing the amount of data scanned.

6) Columnar/OLAP stores

• Ship heavy analytics to Snowflake/BigQuery/ClickHouse (via ELT/dbt). Keep OLTP normalized and let the warehouse handle wide, scan-friendly shapes.

7) ORM hygiene

• Kill N+1 queries (eager-load or batch), set sensible select lists, and cap page sizes. A clean ORM layer can remove the need to denormalize.

8) Computed/generated columns (DB-maintained)

• Let the DB maintain derived values (e.g., price_with_tax) as generated/computed columns or via expression indexes. That will get you fast reads without app-level sync logic.

Denormalization Techniques

Here are the most common ways to add controlled redundancy to a normalized model. For each, I’ll show what it does, when to use it, and how to keep it in sync.

Setup: assume a normalized core with customers, orders, order_items, products.

1) Flattened / pre-joined “projection” tables

What: Create a table that pre-joins columns you need for fast reads (e.g., product listing or an orders dashboard).

When: Your hot path joins 3 to 5 tables in predictable ways.

How (PostgreSQL):

-- Read-optimised projection for a typical orders list
CREATE TABLE orders_projection (
  order_id        BIGINT PRIMARY KEY,
  created_day     DATE NOT NULL,
  customer_id     BIGINT NOT NULL,
  customer_name   TEXT  NOT NULL,
  total_amount    NUMERIC(12,2) NOT NULL
);

-- Initial backfill
INSERT INTO orders_projection (order_id, created_day, customer_id, customer_name, total_amount)
SELECT o.order_id,
       o.created_at::date AS created_day,
       c.customer_id,
       c.name AS customer_name,
       SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, created_day, c.customer_id, c.name;

-- Index for fast filtering by day/customer
CREATE INDEX ON orders_projection (created_day, customer_id);

Sync options:

• After-write job (enqueue “order.updated” → recompute row)
• Nightly/15-min batch job (dbt/cron)

2) Redundant columns (copy a few attributes)

What: Duplicate a handful of frequently-read attributes onto another table to avoid joins (e.g., orders.customer_name).

When: You only need 1 or 2 values and don’t want a full projection.

How (PostgreSQL trigger example):

ALTER TABLE orders
  ADD COLUMN customer_name TEXT,
  ADD COLUMN customer_tier TEXT;

-- Keep the redundant fields correct on insert/update
CREATE OR REPLACE FUNCTION sync_order_customer_fields()
RETURNS TRIGGER AS $
BEGIN
  SELECT name, tier INTO NEW.customer_name, NEW.customer_tier
  FROM customers WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_sync_customer
BEFORE INSERT OR UPDATE OF customer_id ON orders
FOR EACH ROW EXECUTE FUNCTION sync_order_customer_fields();

-- Propagate customer name/tier changes to existing orders
CREATE OR REPLACE FUNCTION propagate_customer_changes()
RETURNS TRIGGER AS $
BEGIN
  UPDATE orders
  SET customer_name = NEW.name,
      customer_tier = NEW.tier
  WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_propagate
AFTER UPDATE OF name, tier ON customers
FOR EACH ROW EXECUTE FUNCTION propagate_customer_changes();

Trade-off: Simple reads, but writes fan out when a customer updates.

3) Derived attributes (store computed values)

What: Persist values you otherwise compute on the fly (e.g., price_with_tax, latest_comment_at, item_count).

When: The calculation is done constantly and is deterministic.

Two common patterns:

• Generated column (DB recomputes on write: you have less control, no cross-table refs)
• Stored column + trigger/job (you control when/how it changes)

-- Generated column example (Postgres 12+; same-table expressions)
ALTER TABLE products
  ADD COLUMN price_with_tax NUMERIC(12,2) GENERATED ALWAYS AS (price * 1.20) STORED;

-- Counter maintained by triggers (likes per post)
ALTER TABLE posts ADD COLUMN like_count INTEGER NOT NULL DEFAULT 0;

CREATE OR REPLACE FUNCTION inc_like_count() RETURNS TRIGGER AS $
BEGIN
  UPDATE posts SET like_count = like_count + 1 WHERE post_id = NEW.post_id;
  RETURN NEW;
END; $ LANGUAGE plpgsql;

CREATE TRIGGER trg_like_insert
AFTER INSERT ON post_likes
FOR EACH ROW EXECUTE FUNCTION inc_like_count();

CREATE OR REPLACE FUNCTION dec_like_count() RETURNS TRIGGER AS $
BEGIN
  UPDATE posts SET like_count = GREATEST(like_count - 1, 0) WHERE post_id = OLD.post_id;
  RETURN OLD;
END; $ LANGUAGE plpgsql;

CREATE TRIGGER trg_like_delete
AFTER DELETE ON post_likes
FOR EACH ROW EXECUTE FUNCTION dec_like_count();

4) Aggregate / summary tables

What: Precompute rollups such as daily revenue, active users per day, orders per category.

When: Dashboards repeat the same group-bys and raw scans are expensive.

CREATE TABLE daily_sales (
  sales_day    DATE PRIMARY KEY,
  gross_amount NUMERIC(14,2) NOT NULL,
  order_count  INTEGER NOT NULL
);

-- Incremental upsert for "yesterday" (run hourly/15-min)
INSERT INTO daily_sales (sales_day, gross_amount, order_count)
SELECT (o.created_at AT TIME ZONE 'UTC')::date AS sales_day,
       SUM(oi.quantity * oi.unit_price)       AS gross_amount,
       COUNT(DISTINCT o.order_id)             AS order_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.created_at >= date_trunc('day', now() - interval '1 day')
  AND o.created_at <  date_trunc('day', now())
GROUP BY sales_day
ON CONFLICT (sales_day) DO UPDATE
  SET gross_amount = EXCLUDED.gross_amount,
      order_count  = EXCLUDED.order_count;

Sync options: scheduled job (cron/dbt), streaming updates (CDC), or event-driven aggregators.

5) Materialized views (persisted query results)

What: Store the output of an expensive query as a physical table you can index.

When: The result set is expensive, relatively stable, and you can tolerate refresh windows.

-- Expensive report
CREATE MATERIALIZED VIEW mv_category_sales AS
SELECT p.category_id,
       date_trunc('day', o.created_at) AS day,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category_id, date_trunc('day', o.created_at);

-- Make it fast to query
CREATE INDEX ON mv_category_sales (category_id, day);

-- Refresh patterns
-- Full, blocking:
REFRESH MATERIALIZED VIEW mv_category_sales;

-- Non-blocking reads (requires unique index on the MV):
-- 1) ensure a unique index exists (e.g., (category_id, day))
-- 2) then:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_category_sales;

Trade-off: You’ll manage refresh cadence because incremental refresh isn’t native in Postgres. That said, it is easy enough when you use dbt or custom “append-only + windowed rebuild” patterns.

6) Precomputed JSON “document” projections

What: Store a denormalized JSON blob that matches your API/UI needs (e.g., product card), alongside targeted JSON indexes.

When: Your API serves the same shape repeatedly, and you want one read.

CREATE TABLE product_cards (
  product_id BIGINT PRIMARY KEY,
  card_json  JSONB NOT NULL
);

-- Build or rebuild card documents
INSERT INTO product_cards (product_id, card_json)
SELECT p.product_id,
       jsonb_build_object(
         'id', p.product_id,
         'name', p.name,
         'brand', b.name,
         'price_with_tax', p.price * 1.20,
         'category', c.name
       )
FROM products p
JOIN brands b    ON b.brand_id = p.brand_id
JOIN categories c ON c.category_id = p.category_id
ON CONFLICT (product_id) DO UPDATE SET card_json = EXCLUDED.card_json;

-- GIN index for JSON queries if needed
CREATE INDEX idx_product_cards_gin ON product_cards USING GIN (card_json);

Sync options: event-driven rebuild on product/brand/category changes, or frequent batch refresh for recently-updated products.

Choosing a sync strategy (quick guide)

• Triggers: They are immediate and ensure transactional consistency. They’re generally great for small fan-out and low write volume but they can hurt write latency if overused.
• Application dual-write: The app writes both source + denormalization. It is simple but riskier. You can mitigate that with idempotent retries and outbox/CDC patterns.
• CDC → worker: That syn strategy is reliable and scalable as it propagates changes asynchronously. It is ideal when eventual consistency is acceptable.
• Scheduled jobs: Simplest option for aggregates and materialized views. Just pick refresh windows that match your UX tolerance.

I would recommend that you always build observability into your solution. There is nothing worse than when you are unsure if the changes have been processed or replicated correctly. It can also help you account for your drift checks and backfill scripts.

How to Implement Denormalization Step-by-Step

As always, messing with your database is quite risky. I would highly recommend that you replicate your database and try to implement the denormalization on the replica first, just to make sure everything is behaving the way you expect. If you have a dev environment, even better!

The process is quite simple: measure → change as little as possible → keep it correct → verify → monitor → repeat.

1) Profile and set a success target

• Capture the exact query (text + parameters) and its frequency.
• Record a baseline: EXPLAIN (ANALYZE, BUFFERS) plan, p95 latency, CPU/IO, rows scanned.
• Agree a success criterion (for instance “p95 < 120 ms with ≤1.2× write cost”).
• Confirm the consistency tolerance (e.g., “analytics can be 5 min stale”).

2) Choose the smallest denormalisation that works

• Ideally try one redundant column or one summary table over a big projection.
• Sketch the new shape:
• Redundant columns? (which ones, why)
• Aggregate table or materialized view? (grain, keys, refresh window)
• Decide the consistency model:
• Strong (trigger/transactional) vs eventual (CDC/job/materialized view refresh).
• Write down the write amplification budget (how many extra writes per event are acceptable).

3) Design the sync path

• Triggers (strong consistency, small fan-out).
• CDC/outbox → worker (scales well, eventual consistency):
• App writes to source tables and an outbox row in the same transaction.
• A worker reads the outbox and updates the denormalised target idempotently.
• Scheduled jobs / MV refresh (great for aggregates):
• Define refresh cadence, windowing, and backfill strategy.

Idempotency is non-negotiable. The updates should be safely repeatable (e.g., UPSERT with deterministic recompute). Trust me on this, you’ll thank yourself later.

4) Create, backfill, and validate

1. Create structures (tables, columns, indexes, triggers/jobs) but keep the app unchanged.
2. Backfill from source of truth.
3. Validate parity with invariants:

-- Example parity check: orders_projection vs live join
SELECT COUNT(*) AS mismatches
FROM orders_projection p
JOIN orders o   ON o.order_id = p.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE p.customer_name <> c.name
   OR p.total_amount <> (
        SELECT SUM(oi.quantity * oi.unit_price)
        FROM order_items oi WHERE oi.order_id = o.order_id
      );

4. Fix mismatches; re-run until zero (or within an agreed error budget).

5) Cut over safely

• Ship the app change behind a feature flag or canary (e.g., 10% traffic reads from the denormalised path).
• Run shadow reads: compute the old result in the background and compare hashes/aggregates for a subset of requests.
• Keep a fast rollback (toggle back to the normalized path instantly).

6) Monitor the right things

Create a small dashboard in your observability tool of choice:

• Read: p50/p95 latency, rows read, buffer hits, query plan stability.
• Write: extra write time, trigger/job errors, queue lag (CDC), MV last-refreshed timestamp.
• Data quality: drift counters (daily parity checks), number of backfilled rows, % of mismatches.
• Cost/footprint: table sizes, index bloat, MV size growth.

7) Operate & iterate

• Assign ownership (team/on-call system) and a runbook (how to rebuild, backfill, repair). This is often overlooked, but you’ll want to be able to respond as quickly as possible when an incident happens. 
• Re-evaluate quarterly: still needed? Still the right shape? Can we simplify? Document the decision and link to dashboards.

Pros and Cons of Denormalization

We covered the details in the Normalization vs Denormalization table in an earlier section, so here is a quick recap.

What you gain

• Faster reads and more predictable p95/p99 latencies
• Simpler queries on hot paths (fewer joins/aggregations)
• Lower CPU/IO per read and cheaper analytics queries

What it costs

• Write amplification (extra updates/inserts)
• Consistency risk (stale/drifting data unless you sync well)
• Storage growth (duplicate columns/tables/views)
• Operational overhead (refreshes, backfills, monitoring)
• Change fan-out (schema/logic updates must be mirrored)

Tools and Technologies That Support Denormalization

Not every database helps you denormalize in the same way. Some give you first-class features to persist expensive query results while others expect you to roll your own with jobs, triggers, or pipelines.

PostgreSQL

Postgres gives you several ways to materialise read-optimised shapes. 

Materialized views persist the result of a query, so reads are instant. You choose when to refresh (on a schedule or ad hoc), and you can even refresh concurrently so readers aren’t blocked.

For lightweight duplication, generated columns and expression indexes let the database maintain derived values and accelerate common filters without writing extra sync logic. 

When you need strong consistency between the source and a denormalised copy, you can use triggers to keep things in sync (but remember, they add work to your write path!). For event-driven or batch refreshes, you can pair Postgres with a scheduler (e.g., pg_cron or an external job runner) or with logical replication/CDC to stream changes into a worker that updates your denormalised tables asynchronously.

If you want to get hands-on and create your own databases and test denormalization, have a look at our PostgreSQL course.

SQL Server

SQL Server leans hard into indexed views, which are kind of always-on materialized views, where the engine keeps the view in sync on every insert/update/delete, so reads are super quick. The flip side is that writes now maintain both the base tables and the view, so heavy OLTP workloads can slow down. 

Other than that, you can make use of persisted computed columns to cover single-row derived attributes. 

For reporting at scale, columnstore indexes are an alternative to denormalising: they compress and scan large datasets very efficiently, which can remove the need to duplicate data in the first place.

Oracle

Oracle’s Materialized Views are a mature option with FAST refresh that uses change logs to update only what’s changed, or COMPLETE refresh when you’re fine with rebuilding the whole thing. 

With Query Rewrite, the optimiser can transparently use your materialized view when someone queries the underlying tables, so teams get the speed-up without changing application SQL. As always, faster reads mean extra work somewhere: maintaining logs and refreshing views increases write and operational overhead.

MySQL / MariaDB

MySQL doesn’t have native materialized views, so denormalisation is usually built with physical tables plus scheduled jobs or triggers to keep them up to date. 

Generated columns help with simple derived values. This works well for predictable dashboards and summaries, but again, it’s worth being intentional about the refresh cadence and trigger complexity so you don’t accidentally tax critical write paths. Many teams combine this with read replicas to offload reporting queries entirely.

Snowflake / BigQuery (analytics layer)

Columnar warehouses are built for wide, scan-friendly, denormalised data. You’ll typically model wide fact tables with helpful attributes baked in, then lean on partitioning/clustering to prune what’s scanned. 

Both platforms support materialized views and scheduled tasks/queries to keep aggregates fresh without touching the OLTP system. You trade storage and refresh costs for very predictable, cheap reads at scale. It is perfect for dashboards and BI.

This is a little more advanced than our PostgreSQL course, so if you are already comfortable with databases feel free to try our introduction to data modeling with Snowflake.

dbt (modelling and orchestration)

dbt is known as the “infrastructure as code” layer for denormalised analytics. You define models once, choose a materialisation (table, view, incremental), and let dbt handle builds, dependencies, and tests. Incremental models are particularly useful for summary tables that only need new data appended and merged. It is easily one of my favourite tools!

CDC & data pipelines

When you want application changes to flow into denormalised structures reliably, Change Data Capture is the go-to. Tools like Debezium (self-hosted) or managed connectors (Fivetran, Airbyte) stream row-level changes from OLTP into workers or warehouses that update projection tables, counters, or aggregates. This is typically eventually consistent (great for dashboards and feeds), and it scales far better than cramming everything into triggers. Make updates idempotent, monitor lag, and keep a backfill path for late or missed events.

Key Takeaways & Final Thoughts

Denormalization is a pragmatic optimization layered on top of a normalized design, not a rejection of it. You’re trading storage and write complexity for faster, simpler reads on specific and well-understood paths.

Remember:

• Start normalized. Use denormalization only where a named, measured query needs it.
• Exhaust cheaper fixes first (indexes, query rewrites, caching, replicas, OLAP).
• Choose the smallest denormalization that pays off (one redundant column, a tiny aggregate, or a materialized view).
• Be explicit about freshness (strong versus eventual consistency) and build an idempotent sync.
• Measure before/after, monitor drift, and keep a rollback switch handy.
• Treat normalized tables as the source of truth! Denormalized pieces are read-optimized copies you can rebuild.