Denormalization

What is Denormalization

Denormalization is the deliberate introduction of redundant data into a normalized database to improve read performance. While normalization reduces redundancy, denormalization trades some redundancy for faster queries.

-- Normalized: requires JOIN for customer info in order reports
SELECT o.order_id, c.customer_name, c.email, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Denormalized: customer info stored directly in orders table
SELECT order_id, customer_name, customer_email, total_amount
FROM orders_denormalized;

Denormalization is not the opposite of normalization. It is a strategic optimization applied after normalization. The normalized design is the foundation; denormalization adds controlled redundancy where performance demands it.

Common scenarios for denormalization:

• Read-heavy reporting systems
• Data warehouses and analytics databases
• Caching frequently accessed derived values
• Reducing complex JOINs in high-traffic queries

Denormalized data must be kept in sync with source data. This can be done through application logic, triggers, or batch processes.

Trade-offs: Speed vs Consistency

Denormalization improves read speed but introduces challenges.

Advantages:

• Fewer JOINs means faster queries
• Simpler query logic
• Better performance for aggregations and reports
• Reduced load on the database during peak reads

Disadvantages:

• Data redundancy increases storage requirements
• Update anomalies: changing a customer name requires updating multiple rows
• Insert and delete anomalies: maintaining consistency becomes harder
• More complex application logic for data synchronization

-- Denormalized table with redundant data
CREATE TABLE orders_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity INT,
    total_amount DECIMAL(10,2)
);

-- Problem: if customer changes email, must update every row
UPDATE orders_denormalized
SET customer_email = 'new@email.com'
WHERE customer_id = 123;

The decision to denormalize should be based on profiling actual query performance. Never denormalize speculatively, measure first, then optimize.

When to Denormalize

Denormalization is appropriate in specific scenarios.

1. Read-heavy systems: When reads outnumber writes by a large margin (100:1 or more), the write overhead of maintaining consistency is outweighed by read performance gains.

-- Reporting table with pre-calculated aggregates
CREATE TABLE daily_sales_summary (
    sale_date DATE PRIMARY KEY,
    total_revenue DECIMAL(12,2),
    total_orders INT,
    avg_order_value DECIMAL(10,2),
    unique_customers INT
);

2. Data warehouses: OLAP systems prioritize fast analytical queries over write performance. Star and snowflake schemas are intentionally denormalized.

3. Caching expensive calculations: When a calculation is slow and the result changes infrequently, store the result.

-- Cache customer lifetime value
ALTER TABLE customers ADD COLUMN lifetime_value DECIMAL(12,2);

4. Reducing JOIN depth: When queries require joining 5 or more tables, denormalizing can dramatically simplify and speed up the query.

5. High-traffic applications: When thousands of concurrent reads hit the same JOINs, denormalization reduces database load.

Common Denormalization Techniques

1. Pre-joined tables: Store JOINed results as a flat table for reporting.

CREATE TABLE order_report AS
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    c.customer_email,
    p.product_name,
    p.product_category,
    oi.quantity,
    oi.quantity * p.product_price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

2. Cached aggregate columns: Store pre-calculated aggregates directly in the parent table.

ALTER TABLE customers ADD COLUMN total_orders INT DEFAULT 0;
ALTER TABLE customers ADD COLUMN total_spent DECIMAL(12,2) DEFAULT 0;

UPDATE customers SET total_orders = (
    SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id
);

3. Materialized views: Database-supported pre-computed query results.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

REFRESH MATERIALIZED VIEW monthly_sales;

4. Duplicate columns across tables: Copy frequently accessed columns to avoid JOINs.

ALTER TABLE order_items ADD COLUMN product_name VARCHAR(100);

5. JSON columns: Store related data as structured text for flexible queries.

ALTER TABLE orders ADD COLUMN customer_summary JSON;

Choose the technique based on your access patterns, consistency requirements, and database engine capabilities.