Indexing Strategy

Concept of the skill

Indexing is the design of auxiliary search structures (precomputed lookups) that let the database find rows without scanning every row. Every index maps column values to row locations through a chosen structure: B-tree (the right default; serves equality, range, prefix-match, and ORDER BY; flexible across most patterns), hash (equality only; no range; WAL-logged and crash-safe since PostgreSQL 10), bitmap (low-cardinality columns in data warehouses; AND-combinations efficient), GIN (inverted index for arrays/JSON/full-text — many keys per row), GiST / R-tree (geospatial, range overlaps, and the only structure that backs EXCLUDE overlap constraints), SP-GiST (space-partitioned, non-overlapping regions — text-prefix tries, point/quadtree spatial, IP/hierarchical ranges), BRIN (small summary indexes for naturally-ordered append-only data — timestamps), Bloom (probabilistic multi-column equality filter for wide tables where no single column is selective), LSM-tree (write-optimized point-write workloads — Cassandra, RocksDB), columnstore (column-oriented storage for analytic scans over many rows and few columns), and vector / ANN indexes (HNSW graph and IVFFlat clustering, e.g. pgvector — approximate nearest-neighbor over high-dimensional embeddings, where B-tree and GiST do not scale to hundreds of dimensions). Composite indexes on (A, B, C) serve queries with leading-column prefixes: WHERE A, WHERE A AND B, WHERE A AND C (uses A prefix, skips B in scan), ORDER BY A, B, C; but classically not WHERE B or WHERE C alone — though PostgreSQL 18+ B-tree skip scan relaxes this when the omitted leading column has low cardinality. Column-order heuristic: equality-predicate columns first, then the ORDER BY column, then range-predicate columns last — "most selective column first" is a debunked myth. Covering indexes (INCLUDE clause) avoid the row-fetch step only when the heap page is all-visible. Partial / filtered indexes target a small subset (e.g., WHERE status = 'pending'); expression indexes index f(col).

Replaces full-table scans with structure-aware lookups, and replaces per-column index guessing with workload-based portfolio design. Without indexes, finding a few rows in millions requires reading every row. But every index speeds up some queries (those whose WHERE / JOIN / ORDER BY clauses match the index's structure) and slows down every write (the index must be updated on every INSERT, on every UPDATE touching indexed columns, and on every DELETE). The strategic question is not "which columns deserve an index" considered in isolation; it is the whole-database trade-off between read speed and write cost given the workload's actual access patterns. The object of design is the index portfolio attached to a table or workload, kept intentionally small and tied to workload evidence.

This skill is distinct from query-optimization, which diagnoses one slow query by reading EXPLAIN ANALYZE — query optimization may conclude "add an index," but this skill decides which index belongs in the durable schema. It is distinct from database-migration, which owns the safe production mechanics of creating or dropping the chosen index (locks, CONCURRENTLY, rollback). It is distinct from entity-relationship-modeling, which decides stored entities, constraints, and access patterns before indexes are selected, and from schema-evolution, which owns how those change over time. An index is to a database what the back-of-the-book index is to a reference manual — you go to the I section, find the page numbers, and jump; adding an index for every word in the book is technically possible and obviously wrong. The wrong mental model is that the answer to a slow query is always "add an index" and that a high-write table should carry all read-relevant indexes anyway. Adding an index without reading EXPLAIN ANALYZE is guessing — sometimes the existing index isn't used because of a type coercion, a function on the column, a poor cardinality estimate, or low selectivity, and the right response is the underlying diagnosis, not a new index. Every index has a per-write cost; write-heavy tables should have minimal indexes. B-tree is the default but not always right; GIN, GiST, SP-GiST, BRIN, Bloom, columnstore, LSM, and vector/ANN indexes serve specific patterns far better. An index that "exists" is not necessarily used — EXPLAIN ANALYZE confirms it; the most-selective-first composite rule is a myth (equality, then sort, then range is the mechanism); vector/ANN indexes are approximate, not exact; and an index is never set-and-forget.

Coverage

The discipline of designing and maintaining a database's index portfolio so it finds rows quickly without scanning every row. Covers the structure catalog (B-tree, hash, bitmap, GIN, GiST, SP-GiST, BRIN, Bloom, LSM-tree, columnstore, and vector/ANN — HNSW and IVFFlat) and the access patterns each matches; composite indexes and column-order rules (equality/sort/range, skip-scan, mixed sort direction, NULL ordering, the NoSQL ESR guideline); covering / INCLUDE indexes and index-only scans (with the visibility-map/MVCC caveat); partial / filtered, expression, hidden/invisible, wildcard, and hypothetical/advisor-tested indexes; vector index tuning (HNSW vs IVFFlat, recall budget, quantization); the add/keep/drop lifecycle (usage counters, observation windows, redundant indexes, constraint ownership); the maintenance cost of every index (storage, write amplification, lock impact, planner overhead, bloat) and online rebuild; the operational mechanics of building an index without blocking writes (the production DDL handed off to database-migration); and the strategic question of treating the index set as an optimized portfolio rather than a per-column checklist.

Philosophy of the skill

Indexes are a write/read trade, and a workload contract. Every index speeds up some queries and slows down every write. The strategic discipline is not "which columns deserve an index" considered in isolation; it is the whole-database trade-off between read speed and write cost, given the workload's actual access patterns. The same table needs a different index set for a write-heavy event stream, a tenant-scoped SaaS table, a full-text search collection, a geospatial catalog, an append-only time-series table, or an analytics fact table. A column does not "deserve" an index because it appears in a WHERE clause; a workload earns an index when important queries repeatedly benefit enough to pay the write and operational cost.

The wrong default is "add an index for every column ever filtered on." The wrong response to a slow query is always "add an index." The right discipline is to make the access pattern concrete — name the predicate shape, join key, sort, projection, result size, frequency, table size, write rate — then choose the narrowest structure that supports it, count the queries that would benefit and the writes that would pay, and check whether the planner actually uses it before keeping it.