Problem
No indexes = slow queries. Too many indexes = slow writes, bloat. Wrong indexes = unused, misleading EXPLAIN plans.
data-stateestablished · medium operational burden
Database Indexing Strategy
Also known as: index-design, query-optimization, b-tree, partial-index, covering-index
Intent
Design indexes that accelerate queries without killing write performance, using covering indexes, partial indexes, and query-driven design.
Forces
- •Read queries need indexes for WHERE, JOIN, ORDER BY, GROUP BY
- •Every index slows INSERT/UPDATE/DELETE (write amplification)
- •Index size = memory pressure, backup time, restore time
- •Query patterns evolve; indexes must be reviewed
Solution
✓ When to Use
- •Any production database with query performance needs
- •Write-heavy tables (index carefully)
- •Query patterns changing
✗ When Not to Use
- •Tiny tables (seq scan faster)
- •Append-only logs (few queries)
- •Team not ready for index review process
Pros
- +Orders of magnitude query speedup
- +Partial indexes: tiny size, big win
- +Covering indexes: index-only scans (no heap fetch)
Cons
- −Write amplification: each index = extra write
- −Bloat: dead tuples, page splits
- −Planner errors: wrong stats → seq scan
- −Schema lock: `CREATE INDEX` locks (use CONCURRENTLY)
Cost Profile
Infrastructure
Low — disk, memory
Operational
Medium — review, bloat monitoring, REINDEX
Cognitive
High — query plans, column order, selectivity
Failure Modes
Unused indexes: write cost, no read benefit
Missing index: sequential scan on large table
Wrong column order: index not used
Statistics stale: planner chooses seq scan
Lock contention: `CREATE INDEX` without CONCURRENTLY
Real-World Examples
Alternatives
- partitioning
- materialized-view
- caching
- read-replica
- columnar-store
Related Patterns
- query-optimization
- partitioning
- materialized-view
- covering-index
- partial-index
- database-migration
Competency Domains
data statereliability opseconomics evolutionscalingdeployment