Database Denormalization Strategies

Denormalization permits duplicate data to improve read performance. It also adds work to maintain data integrity.

What denormalization is

Denormalization is a design technique that intentionally breaks normalization and permits duplicate data to improve read performance.

Normalization reduces duplicate data and update anomalies. Business requirements can justify denormalization when integrity guarantees must be traded for performance.

flowchart LR
    S[Normalized source] --> W[Synchronization path<br/>trigger, batch, or application]
    W --> D[Duplicated data<br/>aggregate or reference copy]
    D --> R[Read query]
    D -. detect drift .-> W

Why denormalize

Performance

-- normalized structure: requires a 3-table JOIN
SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id;
 
-- after denormalization: a single-table query
SELECT order_id, customer_name, product_name
FROM orders_denormalized;

Simpler development

Collapsing complex multi-way joins keeps both SQL and application logic concise.

Optimizing OLAP and BI environments

Online analytical processing (OLAP) and business intelligence (BI) tools scan large volumes of data repeatedly.

Removing the cost of repeatedly joining several tables in an analytics tool shortens analysis time.

The downsides

Denormalization techniques

Adding duplicate attributes

-- store the customer name redundantly on the orders table
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
 
-- upside: query directly with no JOIN
-- downside: changing a customer name also requires updating orders

Storing aggregate results

-- daily sales aggregate table
CREATE TABLE daily_sales_summary (
    date DATE PRIMARY KEY,
    total_sales DECIMAL(15, 2),
    order_count INT,
    avg_order_value DECIMAL(10, 2)
);
 
-- query precomputed results instead of aggregating in real time

Materialized views

-- PostgreSQL
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    date_trunc('day', order_date) as day,
    SUM(amount) as total,
    COUNT(*) as count
FROM orders
GROUP BY 1;
 
-- periodic refresh
REFRESH MATERIALIZED VIEW order_summary;

A MATERIALIZED VIEW stores aggregate results on disk like a table. Changes to the underlying orders table do not appear automatically. Run REFRESH periodically to recompute the current state.

Replicating tables

Replicating frequently used reference data across several databases or shards cuts remote lookups, which reduces network traffic and latency together. Each replica needs its own synchronization path, so this suits data that changes infrequently.

Stepping down a normal form

Reducing normalization from Boyce-Codd normal form (BCNF) to third normal form (3NF) places more attributes in one table. This reduces joins but can introduce update anomalies. Apply it only to attributes that rarely change.

What to consider before applying it

Analyze the access pattern

• Read-heavy (99% reads, 1% writes): denormalization is a good fit
• Write-heavy (50% reads, 50% writes): evaluate denormalization carefully

Set explicit performance targets

• Quantify the target query response time
• Run adequate benchmark tests
• Avoid denormalizing unconditionally

Choose a data synchronization strategy

Check these first

Things to try before deciding to denormalize:

1. Analyze the execution plan (EXPLAIN)
2. Optimize indexes
3. Introduce caching (Redis, Memcached)
4. Tune the queries

Consider denormalization only when these are not enough.

Summary

Denormalization benefits systems where read performance is the bottleneck. Before adoption, decide who will synchronize duplicate data and when. Choose triggers, batch jobs, or application logic for synchronization. Measure how long inconsistency is tolerable.

Applying denormalization without measuring the read ratio and synchronization cost increases work during writes. Define when to revert to indexes and caching if synchronization costs exceed the benefit.