Database Denormalization Patterns#

Normalization eliminates redundancy. Denormalization reintroduces it — deliberately — to make reads faster. Every high-traffic system eventually denormalizes something. The question is not if but where, how, and how to keep it consistent.

When to Denormalize#

Denormalization is warranted when:

• Read-heavy workloads dominate (10:1 or higher read-to-write ratio)
• Join complexity causes unacceptable query latency
• Aggregation queries run frequently and scan large tables
• Caching alone cannot solve the latency problem (stale data tolerance is low)

Denormalization is risky when:

• Write volume is high (every write touches multiple copies)
• Data consistency requirements are strict and real-time
• The schema changes frequently

Pattern 1: Materialized Aggregates#

Store precomputed counts, sums, or averages directly on the parent record.

Normalized (slow):

-- Count comments for every post on every page load
SELECT p.*, COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 20;

Denormalized (fast):

-- comment_count lives on the posts table
ALTER TABLE posts ADD COLUMN comment_count INTEGER DEFAULT 0;

-- Read is now trivial
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

Maintain the aggregate with triggers or application-level updates:

-- Trigger approach
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1
    WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1
    WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

Use cases: like counts, follower counts, order totals, average ratings.

Pattern 2: Embedded Documents#

In document databases, embed related data directly inside the parent document instead of referencing it.

Referenced (normalized):

// orders collection
{ "_id": "order-1", "user_id": "user-42", "items": ["item-1", "item-2"] }

// users collection (separate lookup)
{ "_id": "user-42", "name": "Alice", "email": "alice@example.com" }

// items collection (N separate lookups)
{ "_id": "item-1", "name": "Widget", "price": 29.99 }

Embedded (denormalized):

{
  "_id": "order-1",
  "user": {
    "id": "user-42",
    "name": "Alice",
    "email": "alice@example.com"
  },
  "items": [
    { "id": "item-1", "name": "Widget", "price": 29.99, "quantity": 2 },
    { "id": "item-2", "name": "Gadget", "price": 49.99, "quantity": 1 }
  ],
  "total": 109.97,
  "created_at": "2026-03-29T10:00:00Z"
}

One read returns everything needed to render the order. No joins, no extra queries.

Trade-off: If Alice changes her email, you must update it in every order that embedded her data — or accept that orders capture a point-in-time snapshot (often the correct choice for orders).

Pattern 3: Precomputed Joins#

Store the result of a frequently executed join as a dedicated table.

-- This join runs on every product listing page
SELECT p.id, p.name, p.price,
       c.name as category_name,
       b.name as brand_name,
       AVG(r.rating) as avg_rating,
       COUNT(r.id) as review_count
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name, p.price, c.name, b.name;

-- Precomputed join table
CREATE TABLE product_listing AS
SELECT p.id, p.name, p.price,
       c.name as category_name,
       b.name as brand_name,
       COALESCE(AVG(r.rating), 0) as avg_rating,
       COUNT(r.id) as review_count
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN brands b ON p.brand_id = b.id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name, p.price, c.name, b.name;

CREATE INDEX idx_product_listing_category ON product_listing(category_name);
CREATE INDEX idx_product_listing_rating ON product_listing(avg_rating DESC);

Refresh on a schedule or via change-data-capture:

-- Periodic refresh (simple)
REFRESH MATERIALIZED VIEW CONCURRENTLY product_listing;

-- Or via CDC: listen for changes and update affected rows

Pattern 4: Cache Tables#

A dedicated table that duplicates data optimized for a specific query pattern.

-- User activity feed — normalized requires scanning multiple tables
-- Cache table stores the prebuilt feed

CREATE TABLE user_feed_cache (
  user_id    BIGINT NOT NULL,
  entry_id   BIGINT NOT NULL,
  entry_type VARCHAR(20) NOT NULL,  -- 'post', 'comment', 'like'
  actor_name VARCHAR(100) NOT NULL,
  actor_avatar VARCHAR(255),
  summary    TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (user_id, created_at DESC, entry_id)
);

-- Read the feed in one query
SELECT * FROM user_feed_cache
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;

Populate the cache table asynchronously via a background worker that listens to events:

async def handle_new_comment(event):
    comment = event.data
    # Find all followers of the post author
    followers = await get_followers(comment.author_id)

    # Fan-out: insert into each follower's feed cache
    entries = [
        FeedEntry(
            user_id=follower_id,
            entry_type="comment",
            actor_name=comment.author_name,
            summary=f"commented on '{comment.post_title}'",
            created_at=comment.created_at,
        )
        for follower_id in followers
    ]
    await bulk_insert_feed(entries)

Pattern 5: Column Duplication#

Copy a frequently accessed column from a related table to avoid a join.

-- Before: join orders with users to get user name
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);

-- Populate from existing data
UPDATE orders o
SET customer_name = u.name
FROM users u
WHERE o.user_id = u.id;

This is the simplest denormalization and the easiest to get wrong. You must update the duplicate whenever the source changes.

Maintaining Consistency#

The fundamental challenge: multiple copies of the same data must stay in sync.

Recommended approach by use case:

• Counters and aggregates — triggers or application-level atomic updates
• Cache tables and feeds — CDC or event-driven async
• Materialized views — scheduled concurrent refresh
• Embedded documents — accept point-in-time snapshots or event-driven propagation

Monitoring Denormalized Data#

Always track drift between the source of truth and denormalized copies:

-- Detect stale comment counts
SELECT p.id,
       p.comment_count as cached,
       COUNT(c.id) as actual
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.comment_count
HAVING p.comment_count != COUNT(c.id);

Run reconciliation queries on a schedule and alert when drift exceeds a threshold.

Decision Framework#

Before denormalizing, ask:

1. Can an index solve this? — Add composite indexes first
2. Can a materialized view work? — Less invasive than manual denormalization
3. What is the write amplification? — Every denormalized column multiplies write cost
4. Who owns consistency? — Define whether triggers, app code, or CDC maintains the copy
5. Can you detect drift? — Build reconciliation checks from day one

Conclusion#

Denormalization is not a failure of design — it is a deliberate trade-off. Materialized aggregates, embedded documents, precomputed joins, and cache tables each solve specific read-performance problems. The key is choosing the right pattern, defining a consistency strategy, and monitoring for drift. Normalize first, denormalize where the data proves you must.

---

Article #403 — part of the Codelit engineering blog. Explore all articles at codelit.io.