Denormalization: Storing Copies on Purpose to Make Reads Faster
Learn why a database may store the same data twice, how teams do it, and what it costs when the data changes.
In short
Denormalization means storing the same data in more than one place on purpose, so reads become simple and fast.
9 min readFree
What you will learn
This lesson explains denormalization. You will learn why a team would store the same fact in more than one place, how it is done, and what it costs. By the end you should be able to tell when a slow read screen might benefit from it, and when it is a bad idea. You will also follow a social feed step by step and answer two short quizzes.
A few words come up often. Read these first.
Key words
- Denormalization
- Deliberately storing the same data in more than one place, so that reads need fewer lookups.
- Normalization
- Splitting data into small related tables so that each fact is stored once.
- Join
- Combining rows from two or more tables at read time, using a shared key such as a user id.
- Redundant copy
- An extra copy of a value kept in a second place.
- Aggregate
- A summary value made from many rows, such as a count or a total.
- Materialized view
- A saved query result stored as a real table, which must be refreshed to stay current.
- Read-heavy
- A system where reads happen far more often than writes.
- Drift
- When copies of the same fact stop matching each other.
Why does normalized data get slow to read?
Most databases start with a normalized design. Each fact lives in one table. A user's name sits in a users table, a post sits in a posts table that stores only the author's id, and each like sits in a likes table. This keeps the data tidy and makes updates safe, because a name is changed in one place.
The cost appears when one screen needs data from several tables at once. The database must join the tables on every read. A small app may never notice. As the data grows, the joins take longer, and a page that joins many tables can feel slow even when each table on its own is fast.
One fact, one home
Normalized tables avoid repeated values, so writes are simple and the stored data stays consistent.
Reads pay the join bill
Each page view repeats the matching work, so a busy screen repeats it thousands of times a minute.
Spread data makes joins harder
Teams in that position often store the joined answer ahead of time instead.
Denormalization is the response. Instead of joining at read time, the team keeps an extra copy of the data, or a value that was already worked out, so the read becomes one simple lookup. The trade is clear: reads get faster and simpler, while writes and storage get harder.
How is denormalization done?
There are three common moves. Teams often combine them in one design.
How do you copy columns into other tables?
The first move is to copy a column that a screen needs into the table that the screen reads. For example, an order row might hold the customer's name, so the order list does not need to join the customers table. The copy is a plain value stored beside the rest of the row. Designers also sometimes merge several tables into one wider table, a pattern often seen in reporting.
Tip
Copy only the columns the screen really shows. Every extra copied column is one more thing that must stay up to date.
How do you store counts and totals?
The second move stores a value that would otherwise be calculated on every read. A common example is a count of child rows, such as the number of likes on a post. Instead of counting likes each time, the post row holds a like count. When a like is added or removed, the stored count changes too. Totals and other summary values, called aggregates, work the same way.
What are materialized views?
The third move is a materialized view. A normal view is only a saved query, so the database runs that query every time it is read. A materialized view stores the query result as a real table, which can be read quickly. The stored result can fall behind the original tables until it is refreshed. In PostgreSQL version 9.3, a materialized view is not refreshed automatically and must be refreshed manually. Some systems, such as SQL Server with indexed views, keep the stored result in step with the base tables automatically, but only for queries of a limited kind.
Worked example: a social feed
Take the feed screen of a social app. Each post shows the author's name and photo, plus its like count. In a normalized design, the app joins three tables: users, posts, and likes. Every time the feed loads, the database joins each post to its author and counts the matching likes. The team decides to store the author's name and a running like count directly on each post row, so the feed reads one table.
- 1
Find the slow read
List what one feed page needs: the post text, the author's name and photo, and the like count.
- 2
Pick the copies
Copy the author's name and photo onto each post row. Add a like_count number to the same row.
- 3
Update the copies when data changes
When someone likes a post, add one to its like_count in the same step. When a user changes a name, update that name on all of their posts.
- 4
Read the feed with one lookup
The feed query now reads only the posts table. Each row already holds everything the screen shows, so no join is needed.
- 5
Measure the result
Time the feed before and after the change. If the speed-up is small, the extra copies were not worth their cost.
users = {1: {'name': 'Ana', 'photo': 'ana.jpg'}}
posts = [{'id': 10, 'author_id': 1, 'text': 'Hello'}]
likes = [{'post_id': 10, 'by': 2}, {'post_id': 10, 'by': 3}]
# Normalized: join and count at read time
def read_feed_normalized():
rows = []
for post in posts:
author = users[post['author_id']]
count = len([l for l in likes if l['post_id'] == post['id']])
rows.append((author['name'], post['text'], count))
return rows
print(read_feed_normalized())
# Denormalized: the post row holds the name and the count
feed_row = {'author_name': 'Ana', 'text': 'Hello', 'like_count': 2}
print(feed_row['author_name'], feed_row['like_count'])
# Ana changes her name, but the copy on the post is not updated
users[1]['name'] = 'Anna'
print(feed_row['author_name'])Output
[('Ana', 'Hello', 2)]
Ana 2
Ana
The first read needed a lookup and a count for every post. The second read needs neither. The last print shows the risk: the copy still says Ana after the user changed the name to Anna. A real app must update every copy, or accept that the screen is briefly out of date.
What denormalization costs
Denormalization is a trade, not a free speed-up. Three costs matter most.
Slower and harder writes
Every copy must change when its source changes. One rename may touch many rows, and a failed step can leave some copies updated and others not.
More storage
Copies take disk space, and stored totals and wide rows make the data larger. In the 2020s, increased storage is a relatively small problem, except for truly enormous systems.
Drift between copies
Copies can drift out of sync. A screen may show an old name or a stale count. Bugs in update code cause this slowly, so it can be hard to spot.
Keeping copies in sync
Teams handle drift in a few ways. They can update all copies inside one transaction, which is a group of changes that either all succeed or all fail together. They can accept a short delay and update the copies in the background, if the screen can live with that. They can also rebuild a stored total or a materialized view from the source data when they find drift.
Tip
Write down which copy is the source of truth. When copies disagree, the source wins.
When to use it
Several guides give the same advice: start with a normalized design, then denormalize where measurement shows a real problem. Denormalization suits systems that read far more than they write, such as dashboards and feeds. It is a poor fit for data that changes constantly.
- 1
Measure the slow query
Use the database's query plan tool to confirm that joins are the real cause, not a missing index or a slow disk.
- 2
Compare reads with writes
Check how often the page is read against how often its data changes. One guide suggests considering it when reads outnumber writes by about one hundred to one. Treat that as a rough guide, not a rule.
- 3
Copy only what the hot read needs
Make narrow copies for the one screen that is slow, rather than redesigning the whole schema.
- 4
Plan the update path first
Decide which code updates each copy, and how you will find drift if it happens.
Tip
Adding an index is often a cheaper first step than copying data. Try the simpler fix before the bigger one.
Check your understanding
Try these two short scenarios. Each one needs the idea from this lesson.
Quick check
A shopping site's order history page is slow. Every load joins the orders, customers, and products tables. Users view their history far more often than they place new orders. What change would speed up the page, and what new problem must the team handle when a customer changes their name?
Quick check
A reporting screen shows total sales for each day. Each time a manager opens it, the database adds up millions of order rows. New orders arrive all day, but the screen is opened only a few times an hour. Which change fits best?
Key takeaways
Denormalization stores extra copies, sums, or pre-joined results so that reads become simple and fast. It is most useful for read-heavy screens with slow joins. The cost is slower writes, more storage, and the risk of copies that drift apart. Measure the real bottleneck first, copy only what the hot read needs, and plan how every copy will be updated.
Sources
- Denormalization - Wikipedia
- Materialized view - Wikipedia
- Normalization vs Denormalization in Databases - Exasol
- Denormalization in Databases - GeeksforGeeks
- Normalization vs Denormalization Explained - Algoroq
- Database Normalization and Denormalization - ITUonline
- Normalization vs Denormalization: When Each Wins - Data Architect
- What is Denormalization and When Do You Need It? - System Overflow
- Normalization vs Denormalization: Stop Wasting Queries - The Code Forge
- When to Normalize vs Denormalize Your Database - bswen
- Normalization vs Denormalization: Database Design at Scale - Matter AI
Last updated on 17 November 2026
Related lessons
Learn with me
Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.