Indexing and Denormalization

A customer asks for one order, but the database has many records to inspect. Another page repeatedly combines customer and order data. Both reads may be improved, but the changes solve different problems.

An index is an extra access structure that helps suitable queries locate data. Denormalization stores selected duplicated or precomputed data to avoid repeated work on reads. Both add maintenance work, and neither is a universal speed switch.

Suppose normal requests ask for orders by customer and creation time. Evaluate an index that fits that filter and ordering. A query over most of a small table may still be cheaper as a scan. The database planner chooses an access path from statistics and estimated costs.

Indexes occupy space and need updates as records change. More indexes can make writes more expensive. Different index types support different predicates; equality, ranges and text search are not identical workloads.

Read the execution plan and measure realistic data and parameters. An index existing in the schema does not prove a particular query uses it or improves its end-to-end latency.

The lab has eight records in a fixed visible order. Scan mode stops when the requested unique ID is found; lookup mode uses a prebuilt ID-to-record map. The counter measures row comparisons or one map lookup, not disk reads, database pages or milliseconds. Real database indexes have their own structures and costs.

Change the authoritative customer name. The copied read-model name stays unchanged until refreshed. A deliberate historical name snapshot may be correct for an invoice; a field meant to show the customer’s current name needs a freshness policy.

Normalization separates related facts to avoid unnecessary duplication. Denormalization deliberately copies or computes selected facts when the read benefit justifies extra write and repair work.

A read model might store an order summary with the current customer name. Updating the customer now needs a way to update or rebuild that summary. A missed event or delayed refresh can leave different values visible.

Define ownership and acceptable staleness. Some copied values are intended snapshots, such as the delivery address agreed at purchase. Do not overwrite historical facts merely because a current profile changed.

PostgreSQL indexes document access structures and their trade-offs. Materialized views store query results and have explicit refresh behaviour. Sources checked 6 October 2026. PostgreSQL is available for self-hosting; infrastructure, storage and operation still cost resources.

For a selective order lookup, evaluate a suitable index first. For an expensive report whose data may lag, evaluate a stored summary or materialized view. For a balance or inventory decision that must be current, keep the authoritative write rules separate from a potentially stale display model.

Benchmark read/write mix, data volume, index size and refresh cost. A single toy lookup count cannot establish production throughput or justify duplicating every table.

Use CQRS to separate command and query responsibilities. Event Sourcing can supply authoritative history, but is an additional decision; copied fields alone do not require it.

Use query evidence to choose indexes. Use denormalization when a read benefit justifies duplicated-data maintenance, with a clear owner and freshness meaning.

Measure writes, storage and repair as well as reads. Keep authoritative decisions distinct from derived displays and deliberate historical snapshots.