Normalization
Normalization is the process of organizing a schema's tables so that each fact is stored in exactly one place. The payoff is avoiding update anomalies: situations where the same piece of information is duplicated across many rows, and an update has to remember to change every copy or the data quietly becomes inconsistent. This page recaps the core idea and the standard normal forms in a PostgreSQL context, without going as deep as a dedicated relational-design series would.
Before and after: a denormalized table
Imagine an orders table that stores the customer's name and email directly on every order row, rather than referencing a separate customers table.
Denormalized: customer data repeated on every order
CREATE TABLE orders_denormalized ( order_id SERIAL PRIMARY KEY, customer_name TEXT NOT NULL, customer_email TEXT NOT NULL, total_amount NUMERIC(10, 2) NOT NULL );
If a customer places ten orders, their name and email are stored ten separate times. If that customer changes their email, every one of those ten rows needs to be updated — miss one, and the table now disagrees with itself about what the customer's email is. Splitting the customer data into its own table and referencing it removes the duplication entirely.
Normalized: customer data lives in one place
CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers (customer_id), total_amount NUMERIC(10, 2) NOT NULL );
Now a customer's email lives in exactly one row, in customers. Updating it is a single UPDATE, and every order automatically reflects the change the moment it is joined against customers — there is no way for the data to become inconsistent with itself.
The normal forms, briefly
Form | Requirement |
|---|---|
1NF | Each column holds a single, atomic value — no repeating groups or comma-packed lists in one field |
2NF | 1NF, plus every non-key column depends on the whole primary key, not just part of a composite key |
3NF | 2NF, plus every non-key column depends only on the primary key, not on another non-key column |
The orders_denormalized example above violates 3NF: customer_name and customer_email depend on the customer, not directly on the order_id primary key — exactly the kind of dependency that normalization eliminates by moving that data into its own table.
A PostgreSQL-specific nuance
Normalization's underlying principles — one fact, one place — are universal across every relational database, not something specific to PostgreSQL. What is worth calling out here is that PostgreSQL's richer type system sometimes makes a small amount of deliberate denormalization more reasonable than it would be in a database limited to plain scalar columns.
A product's list of tags, for instance, is technically a repeating group and would traditionally demand a separate tags table and a join. In PostgreSQL, storing it as a native array or a small JSONB column on the product row itself is a defensible choice when the tags are small, tightly coupled to that one product, and never queried independently of it — the Array Types and JSON & JSONB pages cover exactly this kind of data. The normalized junction-table approach, covered on the Relationships page, is still the right call once the related data needs its own attributes or independent querying.
Normalization's goal is reducing redundancy so an update never has to be repeated across many rows.
1NF: atomic columns. 2NF: depends on the whole key. 3NF: depends only on the key.
A denormalized table risks inconsistency the moment a duplicated fact is updated in only some of its copies.
PostgreSQL's arrays and JSONB make limited, deliberate denormalization reasonable for small, tightly-coupled data.