Engineering Intelligence. Read the book.
module-45/index-surgeon.mdDELIVERY STANDARD

The Index Surgeon

Indexes chosen from query plans, not from panic.

The query was fine at ten thousand rows and a table-scan disaster at ten million — and the fix applied in panic was three redundant indexes that slowed every write.
Index strategy as a discipline: B-tree, GIN, GiST, partial, and composite chosen from actual query plans, EXPLAIN ANALYZE as a review artifact, and vacuum health monitored — not discovered.

The incident

The dashboard query was instant at ten thousand rows and a forty-second table scan at ten million — the growth had simply arrived. The panic fix was worse than the problem: three overlapping indexes added in one afternoon, none matching the actual query shape, every write now paying to maintain all three. The reads stayed slow. The writes got slower. Everyone was surprised.

Why the obvious approach fails

Indexing fails in both directions. Under-indexing is obvious — sequential scans that grow with the table. Over-indexing is sneakier: every index taxes every write, bloats memory, and an index that doesn't match the query's actual shape (wrong column order, wrong type, unanchored pattern) is pure cost with no benefit. The database will even tell you all of this — EXPLAIN ANALYZE shows exactly what the planner does — but only if someone asks before shipping. Index design is reading query plans and choosing the right structure for the shape: B-tree for equality and ranges, GIN for JSONB and full-text, GiST for geometry, partial indexes for hot subsets, composite indexes in the order the queries actually filter.

The standard

  • Every query on a growth-path table ships with its EXPLAIN ANALYZE output as a review artifact; sequential scans on large tables require justification or an index.
  • Index type matches the workload — B-tree, GIN, GiST, partial, composite — chosen from the plan, never by default.
  • Composite index column order follows real filter patterns; an index the planner won't use is a defect.
  • Unused and duplicate indexes are audited on a schedule and removed — index count is a budget, not a trophy shelf.
  • Slow-query logging and vacuum/bloat health are monitored continuously; regressions surface as alerts, not user complaints.

How Cortex validates it

At plan time

data-heavy Missions declare expected query shapes and growth assumptions; the indexing strategy is part of the blueprint.

At validation

assert: queries(large_tables).plan != seq_scan (or justified)assert: new_indexes.used_by_planner == true on production-scale dataassert: write_latency.regression < budget after index changes

Assertion syntax is illustrative.

At review

the approver sees before/after query plans on production-scale data, plus the write-path cost of every added index — both sides of the trade, on the record.

Composes with

The Connection MarshalThe Partition PlannerThe N+1 Sentinel

See where your codebase stands against this standard.

Get an Agent Readiness AssessmentBack to the library