Normalization, Denormalization, and Schema Evolution

Normalization, denormalization, and schema evolution are three connected design practices. Normalization decides where facts live. Denormalization decides when a copied or precomputed fact is worth maintaining. Schema evolution decides how to change those choices after real data and applications already depend on them.

In PostgreSQL, these are not abstract modeling exercises. Tables, foreign keys, unique constraints, generated columns, triggers, views, materialized views, indexes, locks, and transactional DDL all affect whether a design stays correct while many sessions read and write the same database. The outcome of this lesson is practical: you should be able to model a small domain, recognize when a normalized design is causing avoidable cost, add redundancy deliberately, and migrate without leaving inconsistent state behind.

Purpose in a PostgreSQL Course

Earlier relational design work focuses on entities, keys, and relationships. This chapter turns those ideas into changeable PostgreSQL schemas. A good schema protects domain rules even when application code is imperfect. A maintainable schema also admits that requirements change: product teams add attributes, queries change shape, and tables grow large enough that a one-step rewrite is no longer acceptable.

How Normalization Works

Normalization reduces duplicate facts so updates have one authoritative target. The usual vocabulary is functional dependency and normal form. A functional dependency means one value determines another. If customer_id determines customer_email, then storing the email on every order row creates an update anomaly: changing the email requires finding every duplicated copy.

First normal form means columns contain atomic values instead of repeating groups. Second normal form matters when a table has a composite key: non-key columns should depend on the whole key. Third normal form says non-key columns should depend on the key, not on another non-key column. PostgreSQL does not label tables as normal forms for you; it enforces the result through primary keys, foreign keys, unique constraints, NOT NULL, checks, and appropriate data types.

The internal mechanism is simple but powerful. A foreign key stores a dependency from child rows to parent rows. PostgreSQL checks that dependency on insert, update, and delete. With ON DELETE RESTRICT, a parent row cannot disappear while children still reference it. With ON DELETE CASCADE, PostgreSQL deletes dependent child rows in the same transaction. With DEFERRABLE constraints, PostgreSQL can postpone validation until commit, which helps with circular or batch changes.

Denormalization Mechanisms

Denormalization intentionally stores a fact in more than one place or stores a derived fact that could be computed from base rows. PostgreSQL gives several ways to do this, each with different maintenance costs.

• Plain copied columns: fast to read, but application code or triggers must keep them synchronized.
• Generated columns: computed from columns in the same row and maintained by PostgreSQL, useful for simple deterministic derivations.
• Summary tables: store aggregates such as counts or totals, usually maintained by triggers, jobs, or application transactions.
• Materialized views: store query results and can be refreshed, but refresh timing determines how stale they may be.
• Expression indexes: avoid storing a visible column while still accelerating a derived lookup.

The key question is ownership. A denormalized value needs one maintenance path and one freshness rule. If orders.total_cents is copied from order items, can it be stale? Is it updated in the same transaction as item changes? Can failed background jobs leave reports wrong? PostgreSQL can help, but the design must state the rule.

Schema Evolution Anatomy

Schema evolution is the controlled movement from one valid schema to another. PostgreSQL DDL is transactional, so many changes can be committed or rolled back atomically. That does not mean every migration is operationally cheap. Adding a nullable column is usually lightweight. Adding a checked constraint to a large table requires validation work. Rewriting a column type can rewrite the table. Building an index without CONCURRENTLY can block writes.

A safe PostgreSQL migration is often staged: add the new structure, backfill existing data in batches, dual-write or derive new data, validate constraints, switch reads, then remove old structure after confidence is high. Important syntax includes ALTER TABLE, ADD CONSTRAINT, NOT VALID, VALIDATE CONSTRAINT, CREATE INDEX CONCURRENTLY, DROP COLUMN, and transaction boundaries.

Example 1: Normalize Customer Orders

Start with a common anti-pattern: every order row repeats customer details. It looks convenient for one query, but email updates and customer renames become multi-row changes.

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(customer_id),
    placed_at timestamptz NOT NULL DEFAULT now(),
    status text NOT NULL CHECK (status IN ('open', 'paid', 'shipped', 'cancelled'))
);

INSERT INTO customers (email, display_name)
VALUES ('ada@example.com', 'Ada')
RETURNING customer_id;

The deterministic behavior is the integrity rule: PostgreSQL accepts an order only when customer_id exists in customers. If an insert references customer_id = 999999 and no such customer exists, the statement fails with a foreign key violation. The design stores the customer email once, so changing it is one update against customers, not a search through every historical order.

Example 2: Add Line Items Without Losing Meaning

Order totals are tempting to store directly. Before doing that, normalize the facts that produce the total: products, orders, and order items. The line item should record the price charged at purchase time because the current product price may change later.

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    name text NOT NULL,
    current_price_cents integer NOT NULL CHECK (current_price_cents >= 0)
);

CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(product_id),
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),
    PRIMARY KEY (order_id, product_id)
);

SELECT oi.order_id,
       sum(oi.quantity * oi.unit_price_cents) AS total_cents
FROM order_items AS oi
GROUP BY oi.order_id;

This example shows a subtle normalization decision. products.current_price_cents is not the same fact as order_items.unit_price_cents. One is the price currently offered; the other is the historical price used for a specific order. Keeping both is not accidental duplication because they have different meanings and different update rules.

Example 3: Denormalize a Maintained Order Total

If the total query becomes hot, a stored total can be justified. The cost is synchronization. One robust pattern is to update the summary in the same transaction that changes line items.

ALTER TABLE orders
ADD COLUMN total_cents integer NOT NULL DEFAULT 0
CHECK (total_cents >= 0);

CREATE OR REPLACE FUNCTION refresh_order_total(target_order_id bigint)
RETURNS void
LANGUAGE sql
AS $$
    UPDATE orders AS o
    SET total_cents = COALESCE((
        SELECT sum(quantity * unit_price_cents)::integer
        FROM order_items
        WHERE order_id = target_order_id
    ), 0)
    WHERE o.order_id = target_order_id;
$$;

BEGIN;
INSERT INTO order_items (order_id, product_id, quantity, unit_price_cents)
VALUES (1, 1, 2, 2500)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = EXCLUDED.quantity,
              unit_price_cents = EXCLUDED.unit_price_cents;
SELECT refresh_order_total(1);
COMMIT;

After this transaction commits, orders.total_cents reflects the sum for order 1 at commit time. The trade-off is clear: reads no longer need to aggregate every item, but every item change has extra write work. If two sessions update different items for the same order, row locking on the orders update serializes the final summary update for that order.

Example 4: Evolve a Schema in Stages

Suppose the application originally stored a free-form status, and you now need a stricter state machine. A direct constraint may fail because old rows contain unexpected values. PostgreSQL supports a staged approach.

ALTER TABLE orders
ADD CONSTRAINT orders_status_known
CHECK (status IN ('open', 'paid', 'shipped', 'cancelled'))
NOT VALID;

SELECT status, count(*)
FROM orders
GROUP BY status
ORDER BY status;

UPDATE orders
SET status = 'cancelled'
WHERE status IN ('void', 'refunded');

ALTER TABLE orders
VALIDATE CONSTRAINT orders_status_known;

NOT VALID means new or changed rows must satisfy the constraint, while existing rows are not scanned immediately. The diagnostic query exposes old values. After cleanup, VALIDATE CONSTRAINT checks existing rows. If any invalid rows remain, validation fails and the constraint stays unvalidated; it does not silently accept bad historical data.

Design Choices and Trade-offs

Normalize when the main risk is inconsistent facts, unclear ownership, or updates that should affect one row. Denormalize when repeated joins or aggregations dominate a critical workload and you can define a reliable maintenance rule. Use views when you want a stable query interface without stored redundancy. Use materialized views when stale but fast results are acceptable. Use summary tables when freshness and write-time maintenance need to be explicit.

Indexes are part of the trade-off. Normalized designs often need indexes on foreign keys and join columns. PostgreSQL does not automatically create an index on every referencing column, so a foreign key from order_items(order_id) to orders(order_id) should usually be paired with an index if deletes, joins, or lookups use that column. Denormalized columns may reduce join cost but increase table width, cache pressure, and write amplification.

Failure Modes and Troubleshooting

Symptom: updating a customer email leaves reports with mixed old and new emails. Cause: email was copied into multiple tables without a maintenance rule. Diagnose: search for columns with similar names and compare counts grouped by email. Correct: choose one authoritative customer table, backfill references to customer IDs, and remove or formally maintain copied email columns.

Symptom: a migration blocks application writes. Cause: a DDL statement took a strong lock or an index was built without the concurrent option. Diagnose: inspect pg_stat_activity and pg_locks for waiting sessions and the blocking PID. Correct: cancel the blocker if appropriate, retry with a staged migration, and use CREATE INDEX CONCURRENTLY outside an explicit transaction when write availability matters.

Symptom: order totals occasionally disagree with line items. Cause: application code updated items but skipped the summary update on one path. Diagnose: compare stored totals with recomputed totals for a bounded set of orders. Correct: move summary maintenance into a single stored function, trigger, or transactional service path, then repair mismatches with a backfill.

Symptom: adding a foreign key fails on an existing table. Cause: child rows reference missing parent rows. Diagnose: run an anti-join from child to parent. Correct: insert missing parent rows when legitimate, delete orphaned rows when invalid, or map them to a known replacement before validating the constraint.

Security, Performance, and Reliability

Normalization supports security because sensitive facts can be isolated. For example, customer profile data can live in a table with narrower grants while order fulfillment queries use customer IDs and shipping fields only. Denormalization can accidentally spread sensitive data into logs, exports, and reporting tables, so copied columns need the same classification as the source.

Performance should be measured with representative data. Use EXPLAIN (ANALYZE, BUFFERS) on normalized joins before assuming denormalization is necessary. PostgreSQL may use nested loops, hash joins, merge joins, or index scans depending on statistics and indexes. Reliability improves when migrations are reversible, constraints are validated, and backfills are idempotent so they can resume after interruption.

Hands-on Lab: Normalize Then Evolve

Prerequisites: a PostgreSQL database where you can create and drop tables, plus a SQL client such as psql. Run the lab in a disposable schema or database.

1. Create the customers, orders, products, and order_items tables from the examples.
2. Insert one customer, one order, two products, and two line items. Verify the aggregate total with the grouped SELECT.
3. Add orders.total_cents and run refresh_order_total for the order. Verify that the stored total equals the aggregate total.
4. Attempt to insert an order with a missing customer_id. Verification is the expected foreign key failure.
5. Add the orders_status_known constraint as NOT VALID, inspect existing statuses, clean any invalid rows, and validate the constraint.
6. Cleanup with DROP TABLE order_items, products, orders, customers CASCADE; in the lab database.

Assessment Exercises

1. A table stores customer_id, customer_email, and customer_tier on every invoice. Which functional dependencies are likely present, and what update anomaly can occur?
2. You need a dashboard showing daily revenue by product. Compare a live aggregate query, a materialized view, and a summary table. Which freshness rule would you document for each?
3. A migration adds NOT NULL to a column with existing nulls. Design a staged PostgreSQL migration that avoids surprising application failures.
4. An order total is stored on orders but can be recomputed from order_items. Write a diagnostic query conceptually: what rows should it return, and how would you repair them?
5. When would copying a product name into an order item be correct rather than a normalization mistake?

Summary

Normalization gives each fact one authoritative home and lets PostgreSQL enforce relationships with keys and constraints. Denormalization is a deliberate performance or usability choice that must include a maintenance and freshness rule. Schema evolution is the discipline of moving between designs with staged, validated, reversible changes. In PostgreSQL, the strongest designs combine clear relational meaning with concrete mechanisms: constraints for correctness, indexes for access paths, transactions for atomicity, and migrations that respect existing data.