We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Intentionally adding redundant data to database tables to speed up read queries by avoiding expensive joins. Trades storage and write complexity for read performance.
In short
Denormalization is the practice of deliberately storing redundant or pre-computed data in a database so that read queries can return results without expensive joins or aggregations. It trades extra storage and harder writes for faster, simpler reads.
In short
Denormalization is the practice of deliberately storing redundant or pre-computed data in a database so that read queries can return results without expensive joins or aggregations. It trades extra storage and harder writes for faster, simpler reads.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Denormalization across a run of lessons, not one page. These 4 alone are about 84 minutes of step-by-step reading, every one with a quiz.
A precomputed aggregate answered in 0.024 ms what took 107.729 ms to compute, 4,489 times. Maintaining it cost 3.4 times on every insert, and doing the maintenance the obvious way lost 391 of 480 concurrent increments.
Combine data from multiple sources into a unified view, building the single source of truth.
How traditional databases store data row by row, and why that matters for your queries.
Your database already cached the disk, so what a query cache saves is CPU, and that varies by 430 times between queries.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Denormalizationpay once,
yours for life
A normalized database splits data into many small tables so each fact is stored exactly once. That keeps writes clean and prevents inconsistency, but answering a real question often means joining five or ten tables together. Denormalization reverses some of that splitting on purpose. You copy a column into a second table, or store a count you could otherwise compute, so the read no longer has to do the work.
A common example is an orders table that stores customer_name and customer_email directly, even though those values also live in the customers table. The name is now duplicated. If you query the last 100 orders, you read one table and you are done. No join to customers, no extra index lookup.
Denormalization is not the same as a bad schema. It is a conscious decision made after you know your read patterns. The data is intentionally redundant, and you accept responsibility for keeping the copies in sync.
The core mechanic is moving cost from read time to write time. In a normalized design the join cost is paid on every read. In a denormalized design you pay once when you write, by also updating the redundant copy, and every read after that is cheaper.
Several patterns fall under denormalization. Storing a derived column means keeping a value like order_total alongside the line items so you never re-sum them. Pre-joining means embedding fields from a parent row into a child row, like the customer_name example above. Pre-aggregation means maintaining a running counter such as comment_count on a post instead of running COUNT(*) every time. Materialized views are the database-managed version of the same idea: the engine stores the result of an expensive query and refreshes it on a schedule or on demand.
The hard part is consistency. When the source value changes, every copy must change too. You handle this with application code that updates both places in one transaction, with database triggers, with a background job that reconciles drift, or in event-driven systems with a stream that fans the update out to each copy. If you get this wrong, the customer renames themselves and old orders still show the old name.
Reach for denormalization when reads vastly outnumber writes, when a specific query is hot and slow, or when you are on a database that cannot do joins efficiently. Read-heavy feeds, analytics dashboards, and product listing pages are classic candidates. It is also close to mandatory on most NoSQL stores like DynamoDB and Cassandra, where cross-partition joins do not exist and you model data around the queries you will run.
The costs are real. You use more storage because data is duplicated. Writes become slower and more complex because one logical change now touches several rows. You introduce the risk of stale or inconsistent data if an update path is missed. And the schema becomes harder to evolve because a single field now lives in many places.
The usual advice holds up: normalize first, then denormalize only where measurements prove a read is too slow. Premature denormalization buys you a write headache before you have any read problem to solve. Keep the normalized source of truth where you can, and treat the redundant copies as a cache you are responsible for.
Think of a social feed like the one Instagram or X runs. Counting likes by running SELECT COUNT(*) on a likes table with billions of rows, every time anyone loads a post, would crush the database. Instead the post row stores a like_count integer that is incremented on each new like. The read becomes a single column fetch. The write does a little more work, and a background job occasionally recounts to fix any drift.
Data warehouses take this further. A star schema in Snowflake or BigQuery deliberately denormalizes into wide fact and dimension tables so analysts can scan and aggregate without deep join chains. The whole design assumes reads dominate and the data is loaded in controlled batches, which makes the consistency problem easy to manage.
The pattern shows up everywhere reads are hot: storing author_name on a comment, embedding shipping_address on an order so it stays frozen at purchase time, or keeping a denormalized search document in Elasticsearch that mirrors rows from a relational source. In each case you trade a clean single source of truth for a read that is fast and simple.
Amazon DynamoDB
Single-table design denormalizes related entities into one table so every access pattern is served by a key lookup, since cross-table joins do not exist.
Apache Cassandra
You model one table per query and duplicate data across them, because Cassandra has no joins and reads must hit a single partition.
Snowflake and BigQuery warehouses
Star schemas use wide pre-joined fact and dimension tables so analytical scans avoid deep join chains across billions of rows.
Instagram and X feeds
Posts store a pre-aggregated like_count and comment_count column instead of counting billions of rows on every page load.
Normalization splits data into separate tables so each fact is stored once, which keeps writes clean and avoids inconsistency. Denormalization deliberately reintroduces redundant or pre-computed data so reads avoid expensive joins. They are opposite ends of the same trade-off between write safety and read speed.
No. It speeds up reads but slows down writes and uses more storage. If your workload is write-heavy or your reads were already fast, denormalizing can make overall performance worse. It only pays off when reads dominate and a specific query is measurably too slow.
Update every copy of the value in the same transaction as the source, or use database triggers, a background reconciliation job, or an event stream that fans the change out to each copy. Whatever method you pick, you own the responsibility to keep the duplicates in sync, because the database will not do it for you automatically.
Yes. A materialized view stores the result of an expensive query, often one with joins or aggregations, and refreshes it on a schedule or on demand. It is denormalization that the database manages for you instead of you maintaining the redundant data by hand.
Usually not in a relational database. Normalize first, measure your real read patterns, then denormalize only the hot, slow queries. In NoSQL stores like DynamoDB or Cassandra it is different, since they have no joins, so you design denormalized around your access patterns from day one.
Normalization
Organizing database tables to reduce redundancy by splitting data into related tables connected by foreign keys. Follows normal forms (1NF, 2NF, 3NF).
Index
A data structure that speeds up database lookups. Like the index at the back of a book that lets you jump to the right page instead of reading every page.
Caching
Storing frequently accessed data in a faster storage layer so you don't have to fetch it from the original (slower) source every time.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The "Database Denormalization" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Denormalization lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Denormalization in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
