How Indexes Change Physical Design

Learning outcomes

Indexes are often introduced as a performance feature added after schema design. In production systems, that is incomplete. Indexes influence how tables are physically accessed, which constraints are practical, how write-heavy workloads behave, and which logical designs remain operationally affordable. Good index design begins from the workload and from the invariants the database must protect.

Logical design versus physical design

A logical schema answers questions such as:

• What entities exist?
• What is the row grain of each relation?
• What are the keys and functional dependencies?
• Which relationships and constraints must hold?

Physical design answers different questions:

• How will rows be located efficiently?
• Which indexes support the dominant access patterns?
• What sort orders or clustering choices matter?
• How much write and storage overhead is acceptable?

Why indexes exist

Without a useful index, a query may need to inspect a large fraction of a table. An index organizes selected key values so the engine can locate matching rows through a much smaller search structure.

SELECT *FROM work_orderWHERE work_order_id = 84217;

A primary-key index makes this lookup fundamentally different from scanning every WorkOrder row.

A simplified B-tree intuition

Many relational databases use B-tree or B+-tree-family structures for ordinary indexes. Values are maintained in sorted order through internal pages and leaf pages. Searches descend from root to leaf instead of checking rows sequentially.

             [root]            /      \       [1..500]   [501..]        /   \       /   \      leaf leaf   leaf leaf

The exact implementation differs by database engine, but the design intuition is stable: sorted index structures make equality, ordered traversal, and many range predicates efficient.

Indexes do not change relational meaning

Adding an index does not change the logical result of:

SELECT ...FROM work_orderWHERE asset_id = ?;

It changes the possible execution plan. This separation is powerful: you can often tune access without changing the logical schema or application query contract.

Primary keys and indexes

Most relational engines implement primary-key enforcement using a unique index or an equivalent physical structure. The logical constraint is “key values are unique and non-null”; the index is one implementation mechanism.

Unique constraints

WorkshopHub may require:

UNIQUE (employee_number)UNIQUE (sku)UNIQUE (manufacturer_id, serial_number)

These rules usually imply unique indexes. Therefore integrity requirements already influence physical design.

Foreign keys and indexes

A foreign key does not universally imply that the referencing column is automatically indexed. Yet many common queries need it:

SELECT *FROM work_orderWHERE asset_id = ?;

and parent deletion/update checks may also benefit. Therefore child-side foreign-key columns are common index candidates.

Indexing joins

Consider:

SELECT wo.work_order_id, a.serial_numberFROM work_order AS woJOIN asset AS a  ON a.asset_id = wo.asset_idWHERE a.customer_id = ?;

Useful indexes might include Asset.customer_id and WorkOrder.asset_id depending on row counts, selectivity, and the optimizer's chosen join strategy.

Indexes and ORDER BY

An index can sometimes deliver rows in the order a query requests:

SELECT *FROM work_orderWHERE asset_id = ?ORDER BY opened_at DESC;

A composite index beginning with asset_id and then opened_at may support both filtering and ordering.

Indexes and range predicates

SELECT *FROM work_orderWHERE opened_at >= :start  AND opened_at < :end;

Sorted indexes are naturally suited to ranges because the engine can seek to the beginning and scan only the relevant portion.

Indexes and normalization

Developers sometimes denormalize because a normalized query joins several tables. Before duplicating data, first ask whether the logical design is correct and whether appropriate indexes make the normalized access path fast enough.

Indexes are not free

Every inserted, deleted, or relevant updated row may require changes to multiple index structures. This creates:

• additional writes;
• more storage;
• more memory/cache pressure;
• longer bulk-load or migration time;
• maintenance work as indexes grow.

Physical design is workload-specific

A schema serving:

• 100 writes/second and a few point reads;
• mostly dashboard reads;
• large time-range reports;
• batch imports;

may need very different index portfolios even if the logical tables are identical.

WorkshopHub initial index map

Explain plans belong to physical design

Do not assume the optimizer uses an index merely because it exists. Query planners estimate costs using statistics, row counts, data distribution, and available access paths. Always inspect actual plans for important queries.

Practice: identify index roles

Summary and next lesson

Indexes are part of physical database design, not a late-stage decoration. They support key enforcement, joins, filters, ranges, and ordering while imposing real write and storage costs. The next lesson focuses on selectivity and cardinality so index candidates are chosen from evidence rather than intuition.

References

• PostgreSQL documentation on index types and query planning.
• Markus Winand, SQL Performance Explained.
• Martin Kleppmann, Designing Data-Intensive Applications.