Denormalization — When and Why to Break Normalization Rules

Concrete Example

A blog feed needs each post's title, author name, comment count, and like count. From a clean 3NF schema (posts / users / comments / likes), assembling that list forces a JOIN to users plus a correlated subquery that scans comments and likes per post. The data is correct — it is just expensive to re-assemble every time, on a feed served millions of times a day.

-- Normalized: correct but the listing query melts under load
--   (correlated subqueries scan comments + likes per post)
SELECT p.title,
       (SELECT count(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
FROM posts p ORDER BY p.created_at DESC LIMIT 20;

-- Denormalized: store the count, read it directly
ALTER TABLE posts ADD COLUMN comment_count INT NOT NULL DEFAULT 0;

After the ALTER, the listing read becomes a plain column fetch — no subquery, no per-post scan. The cost did not vanish; it moved. Every comment INSERT must now bump the counter and every DELETE must decrement it, forever. For a DB-managed alternative that covers the whole row, use a materialized view:

CREATE MATERIALIZED VIEW post_stats AS
SELECT p.id, p.title, u.name AS author_name,
       count(DISTINCT c.id) AS comment_count,
       count(DISTINCT l.id) AS like_count
FROM posts p
JOIN users u ON u.id = p.author_id
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN likes l    ON l.post_id = p.id
GROUP BY p.id, p.title, u.name;

-- Postgres does NOT auto-refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY post_stats;  -- 9.4+, needs a UNIQUE index, keeps serving reads

The expensive JOIN+aggregate runs once per REFRESH rather than once per read. The CONCURRENTLY variant rebuilds while still serving SELECTs (it requires a UNIQUE index); a plain refresh locks reads out for the duration.

Visual Model

Picture a single dial labelled "where do I pay?" Normalization parks the dial fully on the read side: writes are trivially correct, but every read re-derives the answer. Each denormalization technique turns the dial the other way — making reads cheap by pushing cost onto writes, storage, or freshness. There is no free setting; you are only choosing which resource absorbs the cost. The table below scores the four techniques (plus the normalized baseline) on read cost, write cost, consistency guarantee, and staleness. Greener is cheaper / safer.

Read the rows as a single trade restated four ways: every approach that brightens the read column dims at least one of write, consistency, or staleness. That is denormalization in one glance.