When Not to Normalise

What Denormalization Actually Is

For database architects who know the normal forms and are evaluating whether a slow query justifies redundant data; alternatives like materialized views, generated columns, and partitioning are explained where they apply.

Denormalization is the deliberate introduction of redundancy into a normalized database schema to satisfy a specific, high-frequency read query that cannot meet latency targets through indexing or query restructuring. You duplicate a column or pre-aggregate a relationship directly inside a physical table, consciously trading relational integrity guarantees and write throughput for faster read performance.

In a strictly normalized relational schema, each fact exists in exactly one place. When an e-commerce platform queries customer invoices along with the current customer tier and company name, third normal form separates the customer details from the invoice line items to prevent anomalies when customer records change. A PostgreSQL check constraint specifies that the value in a column must satisfy a Boolean expression, and the documentation is explicit that PostgreSQL does not support check constraints referencing table data other than the new or updated row being checked[1].

Consider this normalized structure in PostgreSQL 17:

``sql 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 ); ``

When the application displays an invoice ledger, it joins invoices to customers. Denormalization alters this physical reality by copying company_name directly into the invoices table. Doing so removes the join from the read path, but it introduces an architectural debt: whenever a customer renames their company, every historical invoice row referencing that customer now contains outdated data until an explicit update mutates those rows.

Materialized Views and Generated Columns First

Before you duplicate columns across physical base tables, test the native mechanisms your database engine provides to optimize reads without dismantling schema integrity. In PostgreSQL, three built-in tools resolve common query bottlenecks while preserving the normalized model.

PostgreSQL Materialized Views for Analytical Rollups

When a slow query aggregates rows over large date ranges or joins multiple parent tables for a reporting screen, a materialized view persists query output to disk without modifying base table definitions. For the parser, a materialized view 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; the rule is only used for populating the materialized view[2].

``sql 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 idx_customer_invoice_summary ON customer_invoice_summary(customer_id); ``

You refresh the data on a scheduled cron job or event hook using REFRESH MATERIALIZED VIEW CONCURRENTLY customer_invoice_summary;. The trade-off is data currency: reads operate at sub-millisecond speeds against the cached relation, but changes committed to base tables do not appear until the refresh executes.

Stored Generated Columns for Row-Level Computations

If your read bottleneck comes from repeated row-level expressions, such as concatenating tax rates with line amounts or converting units, do not write application listeners to populate redundant columns. A stored generated column in PostgreSQL is computed when it is written, inserted or updated, 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].

``sql 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 ); ``

Covering Indexes for Index-Only Scans

When a query joins two tables solely to fetch one descriptive column, a covering index satisfies the query directly from the index leaf pages. Adding an INCLUDE clause appends payload columns to the B-tree without altering table schemas or risking cross-table update anomalies.

When the Problem Is Size Rather Than Shape

Slow execution times frequently stem from table volume rather than an inefficient relational shape. When a normalized query degrades because an index no longer fits in memory or sequential scans traverse millions of obsolete records, denormalizing the table does not eliminate the physical I/O barrier.

Partitioning refers to splitting what is logically one large table into smaller physical pieces, and PostgreSQL offers built-in support for range, list, and hash partitioning, declared with a partitioning method plus a partition key[4]. The partitioned table itself is a virtual table having no storage of its own; the storage belongs to the partitions, and the planner scans only those matching the query predicates, reducing I/O without introducing duplicate attributes.

``sql 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'); ``

Partitioning also accelerates data lifecycle management. Dropping an individual partition using DROP TABLE, or doing ALTER TABLE DETACH PARTITION, is far faster than a bulk operation, and these commands also entirely avoid the VACUUM overhead caused by a bulk DELETE[4]. If query performance issues disappear after constraining scans to active partitions, the schema shape was never the defect.

Read Patterns That Justify a Redundant Column

Physical denormalization is warranted only when a specific query profile satisfies three strict technical constraints simultaneously.

1. The read pattern is named, predictable, and accounts for a critical share of total database traffic.
2. The query planner cannot flatten join depth below acceptable latency thresholds despite proper indexing and query restructuring.
3. The write frequency on the duplicated attributes is negligible relative to the read volume.

Consider a high-throughput multi-tenant order feed where every order line must render the seller store name and regional tax code. In a third-normal-form schema, retrieving five hundred orders requires traversing four join boundaries: orders to order_items, order_items to products, and products to stores and tax_jurisdictions.

``sql -- Normalized join requiring four relation scans SELECT o.order_id, oi.item_id, p.product_name, s.store_name, tj.tax_rate FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id JOIN stores s ON p.store_id = s.store_id JOIN tax_jurisdictions tj ON s.jurisdiction_id = tj.jurisdiction_id WHERE o.customer_id = 4201 ORDER BY o.placed_at DESC LIMIT 50; ``

When the store name and tax rate are physically copied into order_items upon row insertion, the query collapses to a two-table join between orders and order_items. To audit which columns now carry duplicated attributes, query the information schema columns view, which contains information about all table columns in the database[5]. Because historical order records are immutable once settled, storing store_name directly in order_items introduces zero ongoing update cost for completed transactions.

The Update Anomaly You Just Bought

When you denormalize a schema, you dismantle the database engine's ability to maintain consistency. PostgreSQL does not support check constraints that reference table data other than the new or updated row being checked, and the documentation recommends UNIQUE, EXCLUDE, or FOREIGN KEY constraints for cross-row and cross-table restrictions instead[1]. None of those can enforce value parity between an original attribute and a copy of it in another table.

Every redundant column introduces three classic update anomalies:

• Update divergence: modifying an attribute in the parent entity leaves stale duplicates in dependent tables unless every duplicate row is synchronously updated in the same transaction.
• Insertion divergence: creating a dependent record with an invalid or misspelled redundant attribute bypasses relational checks because no constraint validates the duplicated string against the source record.
• Deletion loss: removing an entity or aggregating records can inadvertently delete the only copy of an attribute if historical records were relied upon as the attribute store.

Object-relational mapping (ORM) frameworks cannot guarantee cross-table consistency during high-concurrency writes, network partitions, or bulk asynchronous jobs. Application code must explicitly own synchronization logic through transactional service layers or database triggers. If you copy company_name into five distinct transaction tables, every write path that modifies customers.company_name must execute locking multi-row updates across all five relations.

Keeping the Normalized Model as the Source of Truth

Discarding the normalized model once physical tables are denormalized is one of the common database design errors that costs a team later. When database documentation reflects only physical storage optimizations, developers write code against denormalized structures as if they were independent entities, multiplying data anomalies over time.

Maintain a formal logical database design that captures entities, true business keys, and relationships in third normal form. That logical representation defines how the domain functions conceptually, while the physical schema records the performance adjustments actually deployed to the server.

A modelling tool with a dedicated workspace for database-independent logical and conceptual models, kept alongside the physical implementation, makes this practical. By placing the logical model on one diagram and the physical storage schema on another diagram within the same model file, architects keep the true entity boundaries explicit for development teams while preserving migration traceability.

What to Check After Denormalizing

Denormalization is an engineering compromise that requires empirical validation. Before considering a schema modification permanent, verify three operational metrics against production-scale data volumes.

First, measure the motivated read query under production-equivalent load using EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL. If the query execution time or shared buffer hits do not show a measurable improvement over the normalized query with a covering index, revert the schema immediately.

Second, implement automated integrity tests in your application test suite that scan base tables against denormalized copies. Because the information schema columns view contains information about all table columns in the database, you can query it to keep an inventory of which columns are duplicates. A reconciliation query must report zero divergent rows:

``sql -- Integrity check: verify copied company_name matches customers table SELECT oi.order_id, oi.item_id, oi.company_name AS denormalized_name, c.company_name AS canonical_name FROM order_items oi JOIN orders o ON oi.order_id = o.order_id JOIN customers c ON o.customer_id = c.customer_id WHERE oi.company_name <> c.company_name; ``

Third, confirm that your visual documentation clarifies where physical schemas diverge from domain entities. To map physical database tables against clean conceptual models and version control your schema changes in Git, download DbSchema and open the model against your own database: the free Community Edition covers reverse engineering and diagrams, while the design and documentation tools for logical and conceptual models come with the Architect trial.

Frequently asked questions

What is denormalization with an example?

Denormalization intentionally adds redundant data to a schema to speed up specific read queries. For example, storing a customer's total order value directly on the customer table avoids joining and summing the orders table on every read.

Is denormalization bad practice?

It is not bad practice, but it is a deliberate trade-off. It introduces update anomalies because two copies of the same fact now exist. It should only be applied to resolve a proven bottleneck after native engine features fail.

What is the difference between normalization and denormalization?

Normalization structures tables to eliminate data redundancy and maintain consistency using primary and foreign keys. Denormalization deliberately reintroduces redundancy to a normalized schema to reduce the need for complex joins.

When should you denormalize a database?

You should denormalize only when you have a specific, named read pattern that is too slow, the query planner cannot efficiently flatten the join depth, and the update rate on the data is low enough to accept the cost.

Can check constraints enforce denormalized data?

No. PostgreSQL check constraints only validate data within a single row. Because denormalization duplicates data across tables, the database engine cannot natively enforce cross-table consistency, forcing your application code to handle it.

Sources

1. postgresql.org
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org
6. postgresql.org
7. postgresql.org
8. postgresql.org
9. postgresql.org
10. postgresql.org