# Database Index

An auxiliary data structure that speeds selected database lookups and ordering at the cost of storage and additional write work.

- Type: term
- Domain: Engineering & architecture
- Level: Intermediate
- Last reviewed: 2026-09-23
- Original: [Soroush’s Product Wiki](<https://wiki.soroush.uk/topic/database-index/>)

## What it means

A database index is a maintained structure that helps a database locate rows or values without scanning all stored data. An index is built over one or more expressions or fields and may support equality, range, ordering, uniqueness, or specialized searches depending on its type and database.

## Why it matters

The right index can turn an unusably slow product operation into a predictable one and can enforce uniqueness. Each index also consumes space, slows writes, and requires maintenance, so indexing is a workload decision rather than a universal optimization.

## When to use it

Add an index when measured queries repeatedly filter, join, order, or enforce uniqueness on suitable data and the read benefit outweighs write and storage cost. Avoid speculative indexes on small tables, rarely used paths, or frequently changing low-selectivity values unless plans show value.

## How it works

1. Capture the slow or important query together with representative parameters, data volume, and frequency.
2. Inspect its execution plan to identify scans, joins, sorting, selectivity, and row-estimate problems.
3. Choose index fields, order, type, predicates, or included data to match the access pattern.
4. Create the index using an operational approach appropriate to table size and live traffic.
5. Measure read improvement alongside write latency, storage, memory, and maintenance effects.
6. Monitor usage and remove redundant or unused indexes after confirming they serve no constraint or query need.

## A practical example

An order-history query filters by account and sorts by creation time. An index on account identifier followed by creation time lets the database retrieve the newest matching orders directly, while measurements confirm the extra write cost is acceptable.

## Where it breaks

Indexes do not repair incorrect queries, poor models, network latency, or returning excessive data. A database may ignore an index, data distribution can change its value, and too many overlapping indexes increase write amplification, storage, backup size, and maintenance work.

## Common mistakes

- Adding indexes to every column without examining real queries and plans.
- Assuming the database will use an index merely because a queried field is included.
- Ignoring field order, selectivity, predicates, and sorting in a composite index.
- Measuring only faster reads while overlooking write latency, locks, storage, and maintenance.

## Visualization

[View the visualization on the website](<https://wiki.soroush.uk/topic/database-index/#visualization-database-index>)

## Sources

1. [Introduction to Indexes](<https://www.postgresql.org/docs/current/indexes-intro.html>) — PostgreSQL Global Development Group · Official technical documentation
2. [Using EXPLAIN](<https://www.postgresql.org/docs/current/using-explain.html>) — PostgreSQL Global Development Group · Official technical documentation

## Related topics

- [Database](<https://wiki.soroush.uk/topic/database/>)
- [SQL](<https://wiki.soroush.uk/topic/sql/>)
- [Database Schema](<https://wiki.soroush.uk/topic/database-schema/>)
