SQL 80 🛢️ Denormalization Strategies for Performance
Normalization is the default. It eliminates redundancy, prevents the anomalies that redundancy causes, and keeps every fact in one place. But normalization has a cost: joins. A fully normalized schema spreads a single logical entity across many tables, and every query that reads the entity pays for the joins that reconstruct it. On a read-heavy workload, that cost can dominate. Denormalization is the deliberate reintroduction of redundancy to reduce the join cost, and it is a trade, not a reversal. The developer who denormalizes is deciding that the read performance matters more than the storage cost and the consistency risk. The decision is a trade-off, and it should be made with the workload in mind, not as a default.
Key point: Denormalization is not the absence of normalization. It is a deliberate, targeted decision to store a fact in more than one place, with a plan for keeping the copies consistent. A denormalized schema without a consistency plan is a bug waiting to happen.
Why denormalization exists
The join cost problem. A query that reads a customer and their orders joins two tables. A query that reads the orders with the product names joins three. On a small dataset, the joins are cheap. On a large dataset, they dominate. The cost is the price of reconstruction: the normalized schema stores the facts apart, and every read must put them back together.
The aggregate cost problem. A dashboard that shows total sales per region reads an order table with millions of rows and aggregates. The aggregation is expensive because it processes every row. A precomputed summary table stores the total and updates it incrementally, which trades storage and write complexity for read speed.
The reporting problem. Analytical queries often need the data in a shape that does not match the transactional schema. A star schema is a denormalized form: a fact table surrounded by dimension tables, with the dimension attributes copied into the fact table or the dimension tables kept small and indexed. The star schema is denormalization by design.
The read-heavy problem. A workload that reads far more than it writes benefits from denormalization because the cost of the writes is amortized over many reads. A workload that writes as much as it reads does not, because every write must update every copy.
The latency problem. A user-facing query that must respond in milliseconds cannot afford the joins that a normalized schema requires. The denormalized form reduces the query to a single-table read, which is the fastest access path.
The historical problem. A data warehouse stores historical data. The transactional schema is normalized for updates, but the warehouse is read-only and is optimized for queries. The warehouse denormalizes deliberately, because the write cost is paid once and the read cost is paid many times.
a. Common denormalization patterns
Denormalization takes several forms, each addressing a different kind of join or aggregate cost. The patterns are not exclusive; a schema can use several at once.
The duplicated column
The simplest pattern is to copy a column from one table into another to avoid the join.
-- Normalized: join required.
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- Denormalized: customer_name copied into orders.
SELECT id, customer_name FROM orders;The customer_name is stored in the orders table as well as the customers table. The join is eliminated for queries that need only the name. The cost is that renaming a customer requires updating every order.
The cached aggregate
A summary table stores precomputed aggregates, such as counts, sums, or averages.
CREATE TABLE customer_totals (
customer_id INT PRIMARY KEY,
order_count INT NOT NULL DEFAULT 0,
total_spent DECIMAL(12,2) NOT NULL DEFAULT 0
);The customer_totals table stores the aggregate for each customer. A query that needs the total reads one row instead of aggregating the orders table. The aggregate is updated when an order is inserted or deleted, either by a trigger or by the application.
The summary table
A summary table stores precomputed results for a specific query, such as daily sales per region.
CREATE TABLE daily_sales (
sale_date DATE,
region VARCHAR(50),
total DECIMAL(12,2) NOT NULL,
PRIMARY KEY (sale_date, region)
);The summary table is populated by a scheduled job that runs the aggregate query and stores the result. The dashboard reads the summary table, which is small and fast.
The materialized view
A materialized view is a summary table that the database manages. The view definition is the query, and the database stores the result and refreshes it on demand or on a schedule.
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
DATE(order_date) AS sale_date,
region,
SUM(total) AS total
FROM orders
GROUP BY DATE(order_date), region;
-- Refresh on demand.
REFRESH MATERIALIZED VIEW daily_sales_mv;The materialized view is the database’s built-in denormalization mechanism. It is the right tool when the aggregate is expensive and the data can tolerate some staleness.
The star schema
A star schema is a denormalized data warehouse design. The fact table stores the measures and the foreign keys to the dimension tables. The dimension tables store the descriptive attributes and are usually small.
-- Fact table.
CREATE TABLE fact_sales (
sale_id BIGINT PRIMARY KEY,
date_key INT NOT NULL,
product_key INT NOT NULL,
customer_key INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(12,2) NOT NULL
);
-- Dimension table.
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
brand VARCHAR(50) NOT NULL
);The star schema is denormalized because the dimension attributes are stored in the dimension tables, and the fact table references them by key. The dimension tables are small and heavily indexed. The queries join the fact to a few dimensions, and the joins are cheap because the dimension tables are small.
The snowflake schema
A snowflake schema is a star schema where the dimensions are normalized. The product dimension is split into product, category, and brand tables. The snowflake is more normalized than the star, and the queries require more joins. The choice between the two is a trade-off between storage and join cost.
The JSON column
A JSON column stores a denormalized document alongside the normalized columns.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
customer_info JSON -- {"name": "...", "email": "..."}
);The JSON column duplicates the customer information. The query can read the order and the customer info without a join. The cost is that the JSON must be updated when the customer changes, and the JSON is harder to query and index than a column.
The pattern comparison
| Pattern | What It Stores | Join Eliminated |
|---|---|---|
| Duplicated column | A single column from a related table | One join |
| Cached aggregate | Precomputed SUM, COUNT, AVG | The aggregate query |
| Summary table | Precomputed query result | The entire query |
| Materialized view | Precomputed query result | The entire query |
| Star schema | Dimensions with keys | The normalized joins |
| JSON column | A document of related data | One or more joins |
b. Keeping the copies consistent
The hard part of denormalization is not storing the copy. It is keeping the copy consistent with the source. A denormalized column that is not updated when the source changes is a bug, and the bug is silent until someone reads the stale value.
The trigger approach
A trigger fires when the source table changes and updates the denormalized copy.
CREATE OR REPLACE FUNCTION update_customer_name()
RETURNS TRIGGER AS $$
BEGIN
UPDATE orders
SET customer_name = NEW.name
WHERE customer_id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_customer_name
AFTER UPDATE OF name ON customers
FOR EACH ROW
EXECUTE FUNCTION update_customer_name();The trigger runs in the same transaction as the update. The denormalized copy is consistent with the source at commit time. The cost is the trigger overhead on every update.
The application approach
The application updates the denormalized copy alongside the source. The two updates are in the same transaction.
await db.transaction(async (tx) => {
await tx.query('UPDATE customers SET name = $1 WHERE id = $2', [name, id]);
await tx.query('UPDATE orders SET customer_name = $1 WHERE customer_id = $2', [name, id]);
});The application-level approach is more explicit and easier to debug. The risk is that a developer who writes a new update path forgets to update the copy. The trigger approach is automatic and cannot be forgotten, but the trigger logic is hidden.
The eventual-consistency approach
The denormalized copy is updated asynchronously by a background job. The copy lags the source by a bounded amount of time.
-- A background job runs every minute.
UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE c.id = o.customer_id
AND o.customer_name IS DISTINCT FROM c.name;The eventual-consistency approach is appropriate when the copy can be stale for a short period. It is used in data warehouses, search indexes, and caches. It is not appropriate when the copy must be exact.
The materialized view refresh
A materialized view is refreshed on demand or on a schedule. The refresh replaces the entire view or updates it incrementally.
-- Full refresh.
REFRESH MATERIALIZED VIEW daily_sales_mv;
-- Incremental refresh (PostgreSQL 9.4+).
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;The CONCURRENTLY option allows reads during the refresh, at the cost of a slower refresh and the requirement of a unique index.
The consistency trade-off
Each approach has a different consistency guarantee. The trigger and the application transaction are strongly consistent: the copy matches the source at commit. The background job is eventually consistent: the copy matches the source after the job runs. The materialized view is as consistent as its refresh schedule. The developer chooses based on the requirement.
The consistency comparison
| Approach | Consistency | Complexity | Forgotten-Update Risk |
|---|---|---|---|
| Trigger | Strong | Medium | None |
| Application | Strong | Low | High |
| Background job | Eventual | Medium | None |
| Materialized view | Scheduled | Low | None |
| JSON column | Application | Low | High |
c. When to denormalize
Denormalization is a trade. The decision is driven by the workload, and the workload determines whether the trade is worth it.
The read-to-write ratio
A workload that reads 100 times for every write benefits from denormalization because the read savings are multiplied by 100 and the write cost is paid once. A workload that reads once for every write does not, because every write must update the copy.
| Read:Write Ratio | Denormalization |
|---|---|
| 100:1 | Strongly beneficial |
| 10:1 | Beneficial |
| 1:1 | Neutral |
| 1:10 | Harmful |
The query pattern
Denormalization should target the queries that actually run. A query that runs once a day does not justify a denormalized column. A query that runs on every page load does.
-- Runs on every page load. Denormalize.
SELECT o.id, o.order_date, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42;
-- Runs once a day. Leave normalized.
SELECT region, SUM(total) FROM orders GROUP BY region;The staleness tolerance
Some copies can be stale. A dashboard that shows “last updated 5 minutes ago” is fine with eventual consistency. A payment system that shows the account balance is not.
The measurement
Before denormalizing, measure the query. The EXPLAIN ANALYZE output shows the cost of the joins. If the joins are not the bottleneck, denormalization will not help.
EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42;If the plan shows the join is cheap and the filter is the bottleneck, an index on the filter column is the fix, not denormalization.
The verification
After denormalizing, measure again. The query should be faster. If it is not, the denormalization did not address the bottleneck.
The maintenance plan
A denormalized column needs a maintenance plan. The plan includes the consistency mechanism, the monitoring for staleness, and the process for rebuilding the copy if it diverges. A denormalization without a maintenance plan is a liability.
The decision framework
| Question | If Yes | If No |
|---|---|---|
| Is the query slow? | Continue | Do not denormalize |
| Is the join the bottleneck? | Continue | Fix the bottleneck |
| Is the workload read-heavy? | Continue | Do not denormalize |
| Can the copy be stale? | Eventual consistency | Strong consistency |
| Is there a maintenance plan? | Denormalize | Build the plan first |
Complete Example Session
-- ============================================
-- PART 1: THE NORMALIZED SCHEMA
-- ============================================
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id),
order_date DATE NOT NULL
);
CREATE TABLE order_items (
order_id BIGINT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id)
);
-- ============================================
-- PART 2: THE SLOW QUERY
-- ============================================
EXPLAIN ANALYZE
SELECT
o.id,
o.order_date,
c.name AS customer_name,
p.name AS product_name,
oi.quantity,
p.price * oi.quantity AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;
-- Four tables joined.
-- The query is slow on a large dataset.
-- ============================================
-- PART 3: THE DENORMALIZED COLUMN
-- ============================================
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE c.id = o.customer_id;
-- ============================================
-- PART 4: THE TRIGGER
-- ============================================
CREATE OR REPLACE FUNCTION sync_customer_name()
RETURNS TRIGGER AS $$
BEGIN
UPDATE orders
SET customer_name = NEW.name
WHERE customer_id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_customer_name
AFTER UPDATE OF name ON customers
FOR EACH ROW
EXECUTE FUNCTION sync_customer_name();
-- ============================================
-- PART 5: THE FASTER QUERY
-- ============================================
EXPLAIN ANALYZE
SELECT
o.id,
o.order_date,
o.customer_name,
p.name AS product_name,
oi.quantity,
p.price * oi.quantity AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;
-- Three tables joined instead of four.
-- The customer join is eliminated.
-- ============================================
-- PART 6: THE CACHED AGGREGATE
-- ============================================
CREATE TABLE customer_totals (
customer_id INT PRIMARY KEY REFERENCES customers(id),
order_count INT NOT NULL DEFAULT 0,
total_spent DECIMAL(12,2) NOT NULL DEFAULT 0
);
-- Populate.
INSERT INTO customer_totals (customer_id, order_count, total_spent)
SELECT
o.customer_id,
COUNT(DISTINCT o.id),
COALESCE(SUM(p.price * oi.quantity), 0)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.customer_id;
-- ============================================
-- PART 7: THE AGGREGATE TRIGGER
-- ============================================
CREATE OR REPLACE FUNCTION update_customer_totals()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO customer_totals (customer_id, order_count, total_spent)
VALUES (
(SELECT customer_id FROM orders WHERE id = NEW.order_id),
1,
(SELECT p.price * NEW.quantity
FROM products p WHERE p.id = NEW.product_id)
)
ON CONFLICT (customer_id) DO UPDATE
SET order_count = customer_totals.order_count + 1,
total_spent = customer_totals.total_spent +
(SELECT p.price * NEW.quantity
FROM products p WHERE p.id = NEW.product_id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_update_customer_totals
AFTER INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION update_customer_totals();
-- ============================================
-- PART 8: THE MATERIALIZED VIEW
-- ============================================
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
DATE(o.order_date) AS sale_date,
o.customer_id,
COUNT(DISTINCT o.id) AS order_count,
SUM(p.price * oi.quantity) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY DATE(o.order_date), o.customer_id;
CREATE UNIQUE INDEX idx_daily_sales_mv
ON daily_sales_mv (sale_date, customer_id);
-- ============================================
-- PART 9: THE CONCURRENT REFRESH
-- ============================================
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;
-- The view is refreshed without blocking reads.
-- The unique index is required.
-- ============================================
-- PART 10: THE DENORMALIZATION DECISION
-- ============================================
-- Read-to-write ratio: 100:1
-- Join is the bottleneck.
-- Copy can tolerate 1 minute of staleness.
-- Decision: denormalize the customer_name column.
-- Consistency: application transaction + trigger.
-- Maintenance: monitor for divergence nightly.
-- The query that runs on every page load is now
-- a three-table join instead of a four-table join.
-- The aggregate query reads one row instead of scanning.
-- The dashboard reads the materialized view.The ten parts showed the normalized schema, the slow query, the denormalized column, the trigger, the faster query, the cached aggregate, the aggregate trigger, the materialized view, the concurrent refresh, and the decision framework.
Quick Reference
Denormalization Patterns
| Pattern | What It Stores | Consistency |
|---|---|---|
| Duplicated column | A column from a related table | Trigger or application |
| Cached aggregate | SUM, COUNT, AVG | Trigger or application |
| Summary table | Query result | Scheduled job |
| Materialized view | Query result | Refresh |
| Star schema | Dimensions with keys | ETL process |
| JSON column | Related document | Application |
Consistency Approaches
| Approach | Guarantee | Complexity |
|---|---|---|
| Trigger | Strong | Medium |
| Application transaction | Strong | Low |
| Background job | Eventual | Medium |
| Materialized view | Scheduled | Low |
Read-to-Write Ratio
| Ratio | Denormalization |
|---|---|
| 100:1 | Strongly beneficial |
| 10:1 | Beneficial |
| 1:1 | Neutral |
| 1:10 | Harmful |
The Decision Framework
| Question | Action |
|---|---|
| Is the query slow? | Measure first |
| Is the join the bottleneck? | Denormalize the join |
| Is the aggregate the bottleneck? | Precompute the aggregate |
| Can the copy be stale? | Eventual consistency |
| Is the write rate high? | Do not denormalize |
Materialized View Commands
| Command | Purpose |
|---|---|
CREATE MATERIALIZED VIEW | Define the view |
REFRESH MATERIALIZED VIEW | Full refresh |
REFRESH MATERIALIZED VIEW CONCURRENTLY | Non-blocking refresh |
DROP MATERIALIZED VIEW | Remove the view |
Best Practices
✅ Do This:
-- Measure the query before denormalizing.
EXPLAIN ANALYZE SELECT ...; -- ✅
-- Denormalize the join that is the bottleneck.
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100); -- ✅
-- Keep the copy consistent with a trigger.
CREATE TRIGGER trg_sync AFTER UPDATE ON customers ...; -- ✅
-- Precompute expensive aggregates.
CREATE MATERIALIZED VIEW daily_sales_mv AS ...; -- ✅
-- Refresh materialized views concurrently.
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv; -- ✅
-- Monitor for divergence.
SELECT ... WHERE customer_name != c.name; -- ✅
-- Document the denormalization and its consistency plan.
-- The next developer needs to know. -- ✅❌ Don’t Do This:
-- Don't denormalize without measuring.
ALTER TABLE orders ADD COLUMN customer_name; -- ❌ First, check the plan
-- Don't denormalize a write-heavy workload.
-- Every write must update the copy. -- ❌
-- Don't forget the consistency mechanism.
-- The copy will drift. -- ❌
-- Don't denormalize a query that runs once a day.
-- The join cost is amortized over the day. -- ❌
-- Don't store aggregates without a rebuild path.
-- The aggregate may need to be recomputed. -- ❌
-- Don't denormalize everything at once.
-- Start with the worst bottleneck. -- ❌
-- Don't ignore the storage cost.
-- The copy consumes disk and memory. -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Stale copy | No consistency mechanism | Add a trigger or application update |
| Forgotten update | New code path missed the copy | Use triggers instead of application |
| Denormalization not helping | Join was not the bottleneck | Measure before and after |
| Write performance degraded | Copy updated on every write | Accept the trade or revert |
| Aggregate drift | Incremental update missed | Periodic full rebuild |
| Materialized view stale | No refresh schedule | Schedule the refresh |
| Storage bloat | Too many copies | Limit to the hot queries |
| Trigger overhead | Trigger fires on every update | Measure the trigger cost |
Real-World Examples
1. Duplicated Column
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);2. Trigger Sync
CREATE TRIGGER trg_sync AFTER UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION sync();3. Cached Aggregate
CREATE TABLE customer_totals (customer_id INT PRIMARY KEY, total_spent DECIMAL(12,2));4. Materialized View
CREATE MATERIALIZED VIEW daily_sales_mv AS SELECT ...;5. Concurrent Refresh
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_mv;6. Star Schema
CREATE TABLE fact_sales (...);
CREATE TABLE dim_product (...);7. JSON Column
ALTER TABLE orders ADD COLUMN customer_info JSON;8. Divergence Check
SELECT o.id FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.customer_name != c.name;9. Rebuild the Aggregate
DELETE FROM customer_totals;
INSERT INTO customer_totals SELECT ... GROUP BY customer_id;10. Measure
EXPLAIN ANALYZE SELECT ... FROM orders JOIN customers ...;Visual
The Denormalization Trade
┌────────────────────────────────────────────────────────────┐
│ NORMALIZATION vs DENORMALIZATION │
│ │
│ NORMALIZED │
│ ┌──────────────────────────────────────────────────┐ │
│ │ customers (id, name, email) │ │
│ │ orders (id, customer_id, order_date) │ │
│ │ │ │
│ │ Read: join required. │ │
│ │ Write: one row updated. │ │
│ │ Redundancy: none. │ │
│ │ Anomaly risk: none. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ DENORMALIZED │
│ ┌──────────────────────────────────────────────────┐ │
│ │ customers (id, name, email) │ │
│ │ orders (id, customer_id, customer_name, │ │
│ │ order_date) │ │
│ │ │ │
│ │ Read: no join required. │ │
│ │ Write: two rows updated. │ │
│ │ Redundancy: customer_name. │ │
│ │ Anomaly risk: stale name. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ The trade: read speed vs write cost and consistency. │
└────────────────────────────────────────────────────────────┘The Consistency Mechanisms
┌────────────────────────────────────────────────────────────┐
│ KEEPING COPIES CONSISTENT │
│ │
│ TRIGGER │
│ ┌──────────────────────────────────────────────────┐ │
│ │ UPDATE customers SET name = 'X' WHERE id = 1; │ │
│ │ → trigger fires → orders.customer_name = 'X' │ │
│ │ │ │
│ │ Same transaction. Strong consistency. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ APPLICATION TRANSACTION │
│ ┌──────────────────────────────────────────────────┐ │
│ │ BEGIN; │ │
│ │ UPDATE customers SET name = 'X' WHERE id = 1; │ │
│ │ UPDATE orders SET customer_name = 'X' │ │
│ │ WHERE customer_id = 1; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ Explicit. Strong. Can be forgotten. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ BACKGROUND JOB │
│ ┌──────────────────────────────────────────────────┐ │
│ │ Every minute: │ │
│ │ UPDATE orders SET customer_name = c.name │ │
│ │ FROM customers c WHERE ...; │ │
│ │ │ │
│ │ Eventual consistency. Bounded staleness. │ │
│ └──────────────────────────────────────────────────┘ │
│ │
│ MATERIALIZED VIEW │
│ ┌──────────────────────────────────────────────────┐ │
│ │ REFRESH MATERIALIZED VIEW CONCURRENTLY ...; │ │
│ │ │ │
│ │ Scheduled. Reads unblocked during refresh. │ │
│ └──────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────┘The Decision Framework
┌────────────────────────────────────────────────────────────┐
│ WHEN TO DENORMALIZE │
│ │
│ 1. MEASURE │
│ Is the query slow? │
│ No → Do not denormalize. │
│ Yes → Continue. │
│ │
│ 2. IDENTIFY │
│ Is the join the bottleneck? │
│ No → Fix the real bottleneck. │
│ Yes → Continue. │
│ │
│ 3. CHECK │
│ Is the workload read-heavy? │
│ No → Do not denormalize. │
│ Yes → Continue. │
│ │
│ 4. CHOOSE │
│ Can the copy tolerate staleness? │
│ Yes → Eventual consistency. │
│ No → Trigger or application transaction. │
│ │
│ 5. PLAN │
│ Is there a maintenance plan? │
│ No → Build one first. │
│ Yes → Denormalize. │
│ │
│ 6. VERIFY │
│ Measure again. Is it faster? │
│ No → Revert. │
│ Yes → Monitor for divergence. │
└────────────────────────────────────────────────────────────┘The Read-to-Write Ratio
┌────────────────────────────────────────────────────────────┐
│ READ-TO-WRITE RATIO DECISION │
│ │
│ Reads per write: │
│ │
│ 1:10 1:1 10:1 100:1 │
│ │ │ │ │ │
│ │ Harmful │ Neutral │ Beneficial│ Strongly │
│ │◀──────────▶│◀──────────▶│◀──────────▶│◀─────────────▶ │
│ │ │ │ │ │
│ │
│ At 100:1, the read savings are multiplied by 100, │
│ and the write cost is paid once. │
│ │
│ At 1:10, the read savings are multiplied by 1, │
│ and the write cost is paid 10 times. │
│ │
│ The ratio determines whether the trade is worth it. │
└────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Denormalization | Deliberate redundancy for read performance |
| Duplicated column | Copy a column to avoid a join |
| Cached aggregate | Precompute SUM, COUNT, AVG |
| Summary table | Precompute a query result |
| Materialized view | Database-managed summary |
| Star schema | Fact and dimension tables |
| Trigger | Strong consistency mechanism |
| Application transaction | Strong consistency, manual |
| Background job | Eventual consistency |
| Read-to-write ratio | Determines whether the trade is worth it |
Key takeaways:
- Denormalization is a trade, not a reversal. The normalized schema eliminates redundancy and prevents anomalies. The denormalized schema reintroduces redundancy to eliminate joins. The decision is driven by the workload, not by preference.
- The duplicated column is the simplest pattern. Copying a column from a related table eliminates one join. The cost is that the copy must be kept consistent with the source.
- Cached aggregates eliminate expensive aggregations. A summary table or materialized view stores the precomputed result. The dashboard reads one row instead of scanning millions.
- The consistency mechanism is not optional. A denormalized copy that is not updated when the source changes is a silent bug. The trigger and the application transaction provide strong consistency. The background job provides eventual consistency. The choice depends on the staleness tolerance.
- The read-to-write ratio determines the trade. A workload that reads 100 times for every write benefits from denormalization. A workload that writes as much as it reads does not, because every write must update every copy.
- Measure before and after. The
EXPLAIN ANALYZEoutput shows whether the join is the bottleneck. Denormalizing a query that is not bottlenecked by the join does not help and adds maintenance cost. - Materialized views are the built-in denormalization. The database manages the storage and the refresh. The
CONCURRENTLYoption allows reads during the refresh. - Star schemas are denormalization by design. The fact table stores the measures and the foreign keys, and the dimension tables store the descriptive attributes. The joins are cheap because the dimensions are small.
- A denormalization without a maintenance plan is a liability. The plan includes the consistency mechanism, the monitoring for divergence, and the process for rebuilding the copy.
Remember: Denormalization is the deliberate reintroduction of redundancy to improve read performance. It is a trade: the read savings are paid for with storage, write cost, and consistency risk. The decision is driven by the workload, and the workload is measured, not guessed. The duplicated column eliminates a join. The cached aggregate eliminates an aggregation. The materialized view and the star schema are the database’s built-in denormalization. The consistency mechanism is what keeps the copies from drifting, and it is not optional. The result is a schema that reads as fast as the workload requires, with the redundancy managed and the consistency guaranteed.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!