Denormalization as a Read-Optimization Strategy

• Denormalization – deliberately introducing controlled redundancy so reads are served in the exact shape they are queried, without expensive JOINs or aggregations.
• Pre-joined storage – store holder name alongside the transaction so the read needs no JOIN.
• Embedded data – an account document embeds recent transactions as a nested array (document stores).
• Pre-computed counters – increment a counter column in the same transaction that inserts the triggering row.
• Materialized views – a stored, physically persisted query result refreshed on a schedule or incrementally.
• Write amplification – a single logical change now updates several places.
• Guiding rule – normalize for writes, denormalize for reads.

What is denormalization?

Denormalization is the deliberate introduction of controlled redundancy into a schema so that reads can be served in the exact shape they are queried, without expensive JOINs or aggregations at read time. It is the opposite of normalization, applied selectively to read-heavy paths.

What are the four main techniques?

Technique 1: Store data pre-joined

If a screen always shows a transaction with the holder's name, store the holder name alongside the transaction.

-- Normalized: requires JOIN
SELECT t.txn_id, t.amount, a.holder_name
FROM transactions t
JOIN accounts a ON a.account_id = t.account_id;

-- Denormalized: holder_name stored on transaction (no JOIN)
SELECT txn_id, amount, holder_name
FROM transactions_denorm;

Technique 2: Embed related data

Common in document stores: an account document embeds its recent transactions as a nested array, so one read returns everything.

-- PostgreSQL JSONB column with embedded transactions
SELECT account_id, holder_name,
       recent_txns->0->>'amount' AS latest_amount
FROM accounts_with_embedded_txns;

Technique 3: Pre-computed counters

Instead of SELECT COUNT(*) on every read, keep a counter column and increment it in the same transaction.

BEGIN;
INSERT INTO transactions (account_id, amount) VALUES ('SAV2001', -50000.00);
UPDATE accounts SET txn_count = txn_count + 1 WHERE account_id = 'SAV2001';
COMMIT;

-- Dashboard reads one column instead of counting millions of rows:
SELECT txn_count FROM accounts WHERE account_id = 'SAV2001';

Technique 4: Materialized views

A stored, physically persisted result of a query, refreshed on a schedule.

CREATE MATERIALIZED VIEW mv_branch_summary AS
SELECT a.branch_code,
       b.branch_city,
       COUNT(*) AS account_count,
       SUM(a.balance) AS total_balance
FROM accounts a
JOIN branches b ON b.branch_code = a.branch_code
GROUP BY a.branch_code, b.branch_city;

-- Refresh on schedule (e.g., every minute)
REFRESH MATERIALIZED VIEW mv_branch_summary;

-- Dashboard reads pre-aggregated data:
SELECT * FROM mv_branch_summary WHERE branch_code = 'BR001';

What is write amplification?

Every denormalization technique has the same cost shape: a single logical change now updates several places.

Normalized write:                  Denormalized write:
──────────────────                 ─────────────────────
INSERT transaction                 INSERT transaction
                                   + UPDATE account.txn_count
                                   + UPDATE account.balance
                                   + REFRESH materialized view

1 write                            4 writes (write amplification)

How do you choose the consistency model per field?

The decision per field is the consistency model.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class TransferService {

    private final Connection connection;

    public TransferService(Connection connection) {
        this.connection = connection;
    }

    public void transfer(String from, String to, BigDecimal amount) throws SQLException {
        connection.setAutoCommit(false);
        try {
            // Strongly consistent: counter updated in same transaction
            executeUpdate("UPDATE accounts SET balance = balance - ?, txn_count = txn_count + 1 WHERE account_id = ?",
                          amount, from);
            executeUpdate("UPDATE accounts SET balance = balance + ?, txn_count = txn_count + 1 WHERE account_id = ?",
                          amount, to);
            executeUpdate("INSERT INTO transactions (from_acct, to_acct, amount) VALUES (?, ?, ?)",
                          from, to, amount);
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }

    private void executeUpdate(String sql, Object... params) throws SQLException {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof BigDecimal) ps.setBigDecimal(i + 1, (BigDecimal) params[i]);
                else ps.setString(i + 1, (String) params[i]);
            }
            ps.executeUpdate();
        }
    }
}

| Field | Consistency model | Update mechanism |
|-------|-------------------|------------------|
| Balance counter | Strong (must be exact) | Same transaction |
| Transaction count | Strong | Same transaction |
| Monthly activity badge | Eventual (a minute stale is fine) | Materialized view refresh |
| Dashboard summary | Eventual | Background refresh |

What is the guiding rule?

"Normalize for writes, denormalize for reads" means: keep the authoritative write model normalized for correctness, and derive denormalized read models from it.

Write Model (normalized, authoritative):
  accounts → transactions → branches
  Each fact in one place. ACID transactions.

Read Models (denormalized, derived):
  account_dashboard (pre-joined, counters)
  branch_summary (materialized view)
  Redis cache (key-value)

When should you NOT denormalize?

Do not denormalize before measuring. If the normalized schema with proper indexes serves reads fast enough, denormalization adds complexity for no benefit.

Wrong: "JOINs are slow. Denormalize everything."
Right: "This specific query is slow after tuning indexes. Denormalize this one path."

What are common mistakes?

The most common mistake is denormalizing without a plan to keep duplicates consistent.

Wrong: Copy branch_name to transactions table, forget to update on rename.
       → Transactions show old branch name forever.

Right: Update branch_name in both places in the same transaction,
       or use CDC / triggers to propagate changes.

What are common interview follow-up questions?

• "What is a star schema?" A denormalized schema with a central fact table and surrounding dimension tables, used in data warehousing.
• "What is CQRS?" Command Query Responsibility Segregation: separate write models (normalized) from read models (denormalized), connected by events.
• "How do materialized views differ from regular views?" Regular views are virtual (re-executed each time); materialized views are physically stored and refreshed on demand.