When Not to Normalise

Someone has proposed copying a column into a second table to make a join disappear, because a screen that joins three tables misses its latency target. Copy the column only for a read pattern you can name and almost never write to, and only once the engine's own answers have failed. Those answers, in PostgreSQL 17, are a materialized view, a stored generated column and a covering index, and each shortens the read without putting a second copy of a fact in a base table.

The price is fixed, and it is not disk space. Once the same value lives in two tables, no PostgreSQL constraint can hold the two copies equal, so the guarantee moves out of the engine and into whatever code happens to write next.

What denormalization changes in the schema

The examples run on PostgreSQL 17 against a normalized pair:

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  company_name text NOT NULL,
  membership_tier text NOT NULL
    CHECK (membership_tier IN ('standard', 'premium', 'enterprise'))
);

CREATE TABLE invoices (
  invoice_id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(customer_id),
  invoice_date date NOT NULL,
  total_amount numeric(12,2) NOT NULL
);

Third normal form puts company_name in exactly one row of the relational schema, so an invoice ledger screen joins invoices to customers to print it. Denormalizing means copying company_name into invoices and taking the join out of the read path. What you buy is one fewer relation per query. What you owe is a write path: when a customer renames the company, every historical invoice row still carries the old name until something updates it.

The CHECK on membership_tier shows exactly how far the engine can help. A check constraint lets you specify that the value in a column must satisfy a Boolean expression, and PostgreSQL does not support check constraints that reference table data other than the new or updated row being checked[1]. It can police a tier because the answer is in the row. It cannot compare a copy against its original, because the original is in another table.

The engine features to try before you duplicate a column

Three mechanisms in PostgreSQL 17 shorten a read path without touching the normal form of the base tables. They fail in different places, which is what makes the choice between them decidable.

Materialized views for an analytical rollup

When the slow query aggregates over a date range or joins several parents for a reporting screen, store the answer instead of the shape. A materialized view keeps its result on disk: for the parser it is a relation just like a table or a view, and when it is referenced in a query the data is returned directly from the materialized view, like from a table, with the rule used only to populate it[2].

CREATE MATERIALIZED VIEW customer_invoice_summary AS
SELECT c.customer_id,
       c.company_name,
       count(i.invoice_id) AS total_invoices,
       sum(i.total_amount)::numeric(12,2) AS lifetime_spend
FROM customers c
JOIN invoices i ON c.customer_id = i.customer_id
GROUP BY c.customer_id, c.company_name;

CREATE UNIQUE INDEX customer_invoice_summary_pk
  ON customer_invoice_summary (customer_id);

The unique index is what lets the refresh run without blocking readers:

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_invoice_summary;

The trade you make here is currency, not correctness. Reads hit a cached relation, and rows committed to invoices after the last refresh are absent until the next one runs, so a materialized view suits a report and not an account balance.

Stored generated columns for a row-level computation

Where the cost is a per-row expression rather than a join, let the engine compute it on write. A stored generated column is computed when it is written, on insert or update, and occupies storage as if it were a normal column; it cannot be written to directly, and its generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row[3].

CREATE TABLE invoice_items (
  item_id integer PRIMARY KEY,
  invoice_id integer NOT NULL REFERENCES invoices(invoice_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
  line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

That last restriction is the whole reason a generated column is safe: it cannot reach another row, so it cannot go stale. It also means it can never hold a copy of company_name, which is the case people reach for denormalization to solve.

Covering indexes for an index-only scan

When a query joins to a second table only to fetch one descriptive column, put that column in the index that already answers the lookup. The INCLUDE clause appends payload columns to the B-tree leaf without adding them to the key, so the planner can answer from the index alone:

CREATE INDEX invoices_customer_date_idx
  ON invoices (customer_id, invoice_date) INCLUDE (total_amount);

The write cost is the same as any other index, and the table keeps its shape. A covering index is the cheapest of the three to undo, which makes it the one to try first.

When the problem is size rather than shape

A slow query on a large table is often a volume problem wearing a schema problem's clothes. If the index no longer fits in memory, or the scan walks millions of rows nobody queries any more, copying a column into that table changes none of the physical work.

Partitioning refers to splitting what is logically one large table into smaller physical pieces, and PostgreSQL 17 has built-in support for range, list and hash partitioning. The partitioned table itself is a virtual table with no storage of its own; the storage belongs to the partitions, which are otherwise-ordinary tables[4].

CREATE TABLE raw_events (
  event_id bigint NOT NULL,
  customer_id integer NOT NULL,
  event_payload jsonb NOT NULL,
  created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE raw_events_2026_q1 PARTITION OF raw_events
  FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE raw_events_2026_q2 PARTITION OF raw_events
  FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-07-01 00:00:00+00');

Retention gets cheaper at the same time. Dropping an individual partition with DROP TABLE, or running ALTER TABLE DETACH PARTITION, is far faster than a bulk operation, and both entirely avoid the VACUUM overhead caused by a bulk DELETE[4]. If the latency goes away once scans are confined to the current partition, the shape of the schema was never the defect.

The read pattern that justifies a redundant column

Three conditions have to hold at once. The read pattern is one you can name, and it accounts for a share of traffic you can measure. The planner cannot get under your latency target with the indexes and the query rewrites available, which you know because you tried them. And the duplicated value is written far less often than it is read, so the write path you are about to take on stays small.

A line-item feed meets all three when it has to print the customer's company name on every row. Reaching company_name from invoice_items costs two joins:

SELECT ii.item_id, ii.line_total, c.company_name
FROM invoice_items ii
JOIN invoices i ON ii.invoice_id = i.invoice_id
JOIN customers c ON i.customer_id = c.customer_id
WHERE i.invoice_date >= date '2026-01-01'
ORDER BY ii.item_id
LIMIT 500;

Give invoice_items its own copy, written once when the line is created, and the same feed reads one table:

ALTER TABLE invoice_items ADD COLUMN company_name text;

The copy is defensible here because a settled invoice line is immutable: nothing rewrites its company name afterward, so the update cost is close to zero for the rows that already exist.

Keep an inventory of what you have duplicated, because the second copy is invisible in the diagram once it looks like an ordinary column. The information schema columns view contains information about all table columns in the database[5], so one query lists every table that now carries the name:

SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name = 'company_name'
ORDER BY table_name;

What breaks when the same fact is stored twice

The engine stops being able to help. PostgreSQL's own advice for cross-row and cross-table restrictions is to use UNIQUE, EXCLUDE or FOREIGN KEY constraints[1], and none of the three can express "this column equals that column in another table". A foreign key can require that company_name in invoice_items matches some company name that exists; it cannot require that it matches the one belonging to that line's own customer.

Three failures follow from that gap. An update to customers.company_name leaves stale values in every row that copied it, unless the same transaction rewrites them all. An insert into invoice_items can carry a misspelled company name that no constraint rejects, because nothing compares it to the source row. And a delete or an archival job that removes the last remaining copy takes the value with it, if anyone had started treating the duplicate as the record.

PostgreSQL points at a trigger for checks it cannot express as a constraint, and names the catch in the same breath: pg_dump does not reinstall triggers until after restoring data, so the check is not enforced during a dump and restore[1]. A trigger is therefore a guard for live traffic and not a guarantee about the contents of a restored database. Whichever way you go, the parity between the copies is now something your code owns, and every write path that touches customers.company_name has to know about every table that copied it.

Keeping the normalized model as the source of truth

Throwing away the normalized shape once the physical tables diverge from it is one of the database design mistakes that gets expensive later. Developers who only ever see the deployed schema write code against invoice_items.company_name as though it were a fact of its own, and the copies multiply from there.

DbSchema holds both pictures. A logical design is modeled before any engine is chosen, in entities, attributes and relations rather than tables, columns and foreign keys, and it is where the third-normal-form version of the domain stays intact; Convert Model → Generate Physical Design turns it into a physical schema for the database you target. Logical and conceptual design are in the Architect edition.

On the physical side, one DbSchema project holds many diagrams. Add one from the Diagram menu or with the plus tab at the top of the diagram area, and give it only the denormalized tables: each diagram carries its own layout and table visibility while the schema definition underneath is shared. The reviewer then has one diagram that says what the domain is and another that says what was deployed, instead of inferring the difference.

What to check after denormalizing

Measure the query that motivated the change, against production-sized data, with EXPLAIN (ANALYZE, BUFFERS). Compare the execution time and the shared buffer hits against the normalized query with its covering index in place. If the numbers are close, revert while reverting is still one migration.

Then keep the copies honest with a reconciliation query in the test suite, running against a staging copy after every migration:

SELECT ii.item_id, ii.company_name AS copied_name, c.company_name AS canonical_name
FROM invoice_items ii
JOIN invoices i ON ii.invoice_id = i.invoice_id
JOIN customers c ON i.customer_id = c.customer_id
WHERE ii.company_name <> c.company_name;

Zero rows is the only passing result, and the first non-empty run tells you which write path forgot about the duplicate. Note that <> skips rows where either side is NULL, so add an IS DISTINCT FROM variant if the copy is nullable.

Last, keep the two pictures of the schema in step, so the next person can see where storage diverges from the domain. Download DbSchema at https://dbschema.com/download.html, reverse-engineer the database you denormalized, and put the redundant tables on a diagram of their own. The logical and conceptual design that holds the normalized version is in the Architect edition, saving the model to a file is in Pro, and connecting, reverse-engineering and diagrams cost nothing in the free Community edition.

Frequently asked questions

What is denormalization with an example?

Denormalization stores the same fact in two places to shorten a read path. Copying company_name from customers into invoice_items is the example above: the line-item feed then reads one table instead of joining three, and every write to the customer's name has to reach both copies.

Is denormalization bad practice?

Denormalization is a trade rather than a mistake, and it goes wrong when it is made before the alternatives are measured. A materialized view, a stored generated column or a covering index removes the same join while leaving one copy of each fact, so those come first.

What is the difference between normalization and denormalization?

Normalization puts each fact in exactly one place, which lets primary and foreign keys carry the consistency. Denormalization puts a fact in a second place to avoid a join, which moves that consistency into application code, because PostgreSQL check constraints cannot see beyond the row being written.

When should you denormalize a database?

Three conditions have to hold at once, and the section above on the read pattern that justifies a redundant column sets them out. An immutable record is the clearest case: nothing rewrites the company name on a settled invoice line, so the copy costs nothing to keep in step.

Can check constraints enforce denormalized data?

PostgreSQL check constraints validate one row at a time and cannot reference data in another table, so they cannot compare a copy with its original. A trigger can, for live traffic, but pg_dump does not reinstall triggers until after the data is restored, so the check does not run during a restore.

Sources

1. postgresql.org
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org