When to intentionally denormalize a schema

WHY YOU DENORMALIZE Normalization eliminates redundancy and update anomalies, but it forces joins and aggregations at read time. On read-heavy, latency-sensitive paths those joins become the bottleneck. Denormalization deliberately reintroduces redundancy so reads can avoid expensive joins or repeated computation.

A GOOD ANSWER COVERS You denormalize after profiling shows a specific hot read path, not by default. Common techniques: duplicating a frequently joined column into a child table, storing a precomputed aggregate like a comment count, or materializing a denormalized view. The core trade-off is read speed and simpler queries versus slower, more complex writes and the risk of inconsistency, because the same fact now lives in multiple places.

COMMON WRONG ANSWERS Denormalizing the whole schema preemptively for vague speed. Ignoring the consistency mechanism, leaving duplicated values to silently drift. Confusing denormalization with simply removing constraints. Assuming a materialized view refreshes itself for free.

LIKELY FOLLOW-UPS How do you keep the duplicate in sync, triggers, application code, or an async pipeline? What staleness is acceptable? How do you reconcile drift after a bug? When is a cache a better choice than schema denormalization?

ONE CONCRETE EXAMPLE A social app shows each post with its like count. Counting likes per post with a COUNT join on every feed render is too slow at scale. You add a like_count column to posts, updated whenever a like is inserted or deleted. Reads become a single column fetch, but you now must atomically adjust the counter on every like change, and a missed update or race can leave the count wrong, requiring periodic reconciliation.