schema design
Denormalization for Reads
Trade write complexity for read speed — duplicate or pre-aggregate data when the read path is hot and the freshness window is acceptable.
Denormalization is the deliberate reintroduction of redundancy — duplicating data or pre-computing aggregates — to make reads faster. It is the considered counterpart to normalization: where normalization optimises for write integrity and a clean model, denormalization optimises for read performance on hot paths, accepting more complex writes and a managed risk of inconsistency in exchange. The key word is deliberate; denormalization is a targeted optimisation, not a default.
Pre-printing flyers vs typesetting one each time a customer asks. Faster delivery, but you have to reprint when the offer changes.
Key Concepts
1
The common forms each trade write work for read speed. You might duplicate a column to avoid a join — storing the author's name on each post so listing posts needs no join to the users table. You might maintain a pre-computed aggregate — a comment_count on each post updated as comments arrive, rather than COUNT-ing on every page load. You might keep a materialized view that caches the result of an expensive multi-table query and refreshes periodically, or store a summary table for analytics. Each makes the read path cheaper and the write path more involved, because now several places must be kept in step. That synchronisation is the cost: a trigger, application logic, a background job, or a scheduled refresh must update the redundant copy, and there is a window in which it can be stale or, if something fails, wrong.
comment_countCOUNT
2
The judgement interviewers want is knowing when the trade is worth it. Denormalize when a specific read is hot and measured to be slow, the underlying data changes far less often than it is read, and the application can tolerate the chosen freshness window. Keep the normalized tables as the source of truth and treat the denormalized copy as a derived cache you can rebuild. The discipline is to normalise first, prove the read problem with real numbers, and then denormalise the narrow hot spot — not to scatter redundancy everywhere on speculation.