Write Cost, Storage Cost, and Index Maintenance

Learning outcomes

The fastest read plan is not automatically the best database design. Every index consumes resources throughout its lifecycle: inserts and updates must maintain it, storage must hold it, caches must accommodate it, backups must copy it or rebuild it, and migrations may lock or stress the system while creating it.

Write amplification

Insert one WorkOrder row into a table with:

• primary-key index;
• asset_id index;
• status index;
• asset_id + opened_at index;
• active partial index;

and the database may need to modify several physical structures for one logical insert.

Updates can be index writes too

Updating a column included in an index can require index maintenance. Changing status_code may move a row into or out of a partial active-status index.

Delete cost

Deletes also remove or mark entries across indexes. MVCC engines may retain dead tuples/index entries until vacuum/cleanup processes reclaim them.

Index width and storage

Index entries include key values plus row locators and internal overhead. Wide text keys or many included columns can create very large indexes.

Why narrower indexes often perform better

Narrower entries mean more entries per page, better cache density, fewer pages read, and less write volume.

Memory/cache pressure

If hot indexes fit in memory, point lookups can be fast. If unnecessary indexes crowd the buffer cache, both table and index pages may churn.

Bulk-load impact

Loading millions of rows into a heavily indexed table can be much slower than loading into a minimally indexed table and building some indexes afterward. Whether this is safe depends on availability and constraint requirements.

Index build operations

Creating a large index may:

• scan the full table;
• consume CPU and I/O;
• generate temporary files;
• block writes or require concurrent-build features;
• increase replication lag.

Online/concurrent creation

Some engines support index creation modes that reduce blocking but cost more work and may take longer. Migration planning should account for production workload.

Statistics maintenance

Indexes are useful only if the optimizer knows when to use them. Keep table/index statistics current enough for realistic cardinality estimation.

Fragmentation and bloat

Repeated updates, deletes, and page splits can reduce physical density. Terminology and remediation differ by database engine: vacuum, reindex, reorganize, rebuild, compact, or online maintenance.

Do not rebuild by superstition

Maintenance should be evidence-based. Rebuilding every index weekly can create unnecessary I/O, locks, and replication pressure.

Unused indexes

Many engines expose statistics about index scans or usage. An index that has not been used over a representative workload window may be a removal candidate—unless it exists primarily to enforce a constraint or support rare critical operations.

Redundant indexes

Example:

INDEX(asset_id)INDEX(asset_id, opened_at)

The first may be redundant if the second adequately supports all asset_id-only queries. But verify plans, index width, uniqueness, and engine behavior before removing it.

Duplicate indexes

ORMs, migrations, and manual tuning can accidentally create equivalent indexes under different names. Periodic inventory review is worthwhile.

Index lifecycle

1. Identify a specific workload problem.
2. Capture baseline plan and timing.
3. Create candidate index safely.
4. Measure read benefit.
5. Measure write/storage cost.
6. Observe production usage.
7. Remove if no longer justified.

WorkshopHub write-heavy scenario

If PartUsage receives millions of inserts/day, avoid building a separate index for every reporting dimension. Consider:

• indexes needed for integrity;
• one or two critical operational lookup paths;
• moving heavy analytics to replicas/warehouse structures;
• batch/materialized summaries for expensive reports.

Read-heavy scenario

If WorkOrder history is read constantly but written modestly, broader composite/covering indexes may be worthwhile.

Storage budgeting

Track:

• table size;
• total index size;
• largest indexes;
• growth rate;
• backup/restore implications;
• replication bandwidth.

Index changes are schema changes

Even when indexes do not alter logical query results, they should be versioned, reviewed, tested, and deployed through controlled migrations.

Chapter 11 checkpoint

Chapter 11 synthesis

Good index design balances four dimensions:

read latencywrite throughputstorage/cache footprintoperational maintenance

An index that improves one dimension may worsen the others.

Summary and next chapter

Chapter 11 connected logical modeling to physical access. You can now reason about index candidates using selectivity and cardinality, build composite indexes around query shapes, use covering/partial/specialized indexes deliberately, and account for their lifecycle cost. Chapter 12 shifts from individual indexes to complete workload modeling: queries, commands, reports, OLTP hot paths, analytical scans, read/write balance, and measurement-driven schema change.

References

• PostgreSQL documentation on indexes, statistics, vacuum, and maintenance.
• Markus Winand, SQL Performance Explained.
• Martin Kleppmann, Designing Data-Intensive Applications.
• Vendor documentation for engine-specific index maintenance behavior.