| name | indexing |
| description | Design and validate database indexes from real query predicates, joins, ordering, selectivity, data distribution, write cost, storage, maintenance, and engine-specific planner behavior. |
Indexing
Use when query performance, uniqueness, lookup behavior, or scaling requires deliberate index design or cleanup.
Procedure
- Start from measured query workloads and execution plans. List the predicates, join keys, ordering/grouping, returned row counts, frequency, and latency/resource targets the index is intended to improve.
- Inspect existing indexes, constraints, table/data size, value distribution, null rates, cardinality, update frequency, and whether similar indexes already overlap.
- Choose the index type supported by the engine and workload: ordered/B-tree, hash, inverted/full-text, spatial, vector, partial/filtered, expression/function, or another engine-specific structure only when its semantics fit.
- For composite indexes, choose key order based on equality/range/order requirements and actual planner behavior rather than folklore. Consider whether included/covering columns reduce costly lookups without bloating writes excessively.
- Use partial/filtered or expression indexes when they match a stable recurring predicate and the engine can actually use them.
- Estimate write amplification, storage, memory/cache pressure, vacuum/maintenance/rebuild cost, replication impact, and migration/creation locking before adding indexes to large or busy datasets.
- Create the index using the safest online/concurrent mechanism available when production impact matters, and monitor build progress and resource pressure.
- Compare representative query plans and runtime before/after, including parameter distributions where planner choices may differ.
- Identify redundant, unused, or superseded indexes cautiously. Verify workload history and constraint dependencies before removal.
- Revisit index strategy as data distribution and query patterns evolve; an index that once helped can become wasteful or misleading.
Decision rules
- Indexes are workload structures, not badges every column should receive.
- More indexes improve reads only until write, storage, cache, and planner costs outweigh the gain.
- Unique constraints may use indexes internally but their correctness role is distinct from performance tuning.
- Engine documentation and measured plans outrank generic index recipes.
Quality gate
The index decision is ready when a real workload motivates it, existing structures and data distribution are understood, creation/removal risk is controlled, before/after plans and runtime demonstrate the effect, write/storage/maintenance costs are acceptable, and no correctness constraint is accidentally removed.
