This note focuses on design reasoning, failure behavior and operational evidence—the parts that matter in code review, system design and incident response.
Why this problem matters
An index is useful only when its ordering matches the way a query narrows or sorts data. Adding an index to every column increases storage, write amplification and maintenance while still failing to serve multi-column query patterns.
A useful mental model
A B-tree is an ordered structure. For a composite index on (tenant_id, status, created_at), the database can efficiently use the leftmost prefix. Equality predicates usually belong before range or ordering columns. Selectivity and returned row count determine whether an index scan is cheaper than a sequential scan.
Design principles
The following principles are useful because each one creates a boundary that can be reviewed, tested and observed. They are not independent checkboxes: together they define the behavior of the system under normal load and partial failure.
Design indexes from real query predicates and ordering
Treat this as an architectural constraint rather than a cleanup item. Put the boundary in code, configuration or the data model so a reviewer can see exactly where it is enforced.
Put stable equality filters before range columns in composite indexes
The benefit becomes visible when timing changes under load or failure. Define the limit explicitly and make the fallback, rejection or recovery behavior observable.
Use covering indexes selectively for hot read paths
Ownership matters here. The component that owns the invariant should also own the validation, compatibility rule and operational response when the assumption is violated.
Use partial indexes when a small subset, such as active rows, dominates access
Prefer the smallest mechanism that preserves correctness. Add sophistication only after measurements show that the simpler design cannot meet the workload.
Remove redundant indexes after verifying workload impact
Convert this principle into an automated test, deployment check or runbook step. Otherwise it will drift as dependencies, traffic and team ownership change.
How to validate: Verify the choice with realistic data cardinality, concurrent access and actual query plans. Small development datasets hide the costs that dominate production.
Key trade-offs
Good engineering makes the cost of a choice visible. For this topic, the most important trade-offs are:
| Read speed | Indexes and caches accelerate reads while adding write, storage and consistency cost. |
|---|---|
| Isolation | Stronger guarantees simplify reasoning but may increase conflicts and retries. |
| Model clarity | A model that explains history is usually more valuable than one optimized only for the latest value. |
Concrete example
The example below is intentionally small. Its purpose is to expose the control point or data flow that the design depends on, not to present a complete framework implementation.
Query: WHERE tenant_id=? AND status=? ORDER BY created_at DESC LIMIT 50
Candidate index: (tenant_id, status, created_at DESC)
The index aligns filtering and ordering, avoiding a separate sort for the bounded result.When applying this pattern, define what happens immediately before and after every durable boundary. That is where duplicate work, stale state, lock duration, timeout overlap or deployment risk usually enters the design.
Common failure modes
Failure modes are more useful than generic “best practices” because they describe the condition the design must survive. Review each one as a concrete test scenario.
- Indexing a low-selectivity boolean alone. The usual consequence is hidden backlog, duplicate work or state that can no longer be explained. Add a bounded guardrail and reproduce the condition under load.
- Creating separate single-column indexes when the query needs a composite order. This often passes unit tests because the timing, cardinality or dependency behavior is too clean. Test it with realistic concurrency and an intentionally slow or failing dependency.
- Ignoring write cost on frequently updated columns. During restart or replay, the defect can turn a recoverable incident into inconsistent state. Preserve enough context to detect, stop and safely resume the workflow.
- Assuming the planner must use an index because it exists. The safest mitigation is to make the assumption explicit in a constraint, deadline, queue limit or state transition, then alert when the boundary is approached.
What to measure
Production behavior should be visible before a failure becomes a customer complaint. Metrics should connect a technical symptom to a workload, business state or recovery objective.
- Index hit and scan countsUse this as an early saturation signal and define what healthy, warning and overloaded behavior look like.
- Rows read versus rows returnedBreak this down by service version, endpoint, partition or tenant so aggregate averages do not hide one failing path.
- Write latency and WAL volumeCorrelate this with user-visible latency and error rate to distinguish harmless internal work from customer impact.
- Unused or duplicate indexesTrack both the level and the age of the condition; an old small backlog can be more serious than a brief large spike.
- Index size and bloatReview this after deployments and failure drills so the dashboard proves recovery, not only steady-state health.
Interview-ready explanation
A strong explanation starts with the invariant: state what must remain true even when requests repeat, dependencies slow down or instances restart. Then describe the mechanism that preserves it, the failure mode that mechanism introduces and the signal that proves it is working.
For Database Indexes: A Practical Guide to Selectivity and Query Shape, avoid listing tools first. Explain the workload and boundary, walk through the normal path, introduce one realistic failure and show how the system recovers. Finish with the metric or test that validates the claim. That structure demonstrates senior engineering judgment more clearly than naming patterns without context.
Review checklist
Use this checklist during design review, implementation planning or incident follow-up:
- Start from slow queries.
- Inspect predicates and order.
- Validate with EXPLAIN ANALYZE.
- Measure write impact.
- Review unused indexes periodically.
A sound design is not the one with the most patterns. It is the one whose invariants, limits and recovery paths are explicit—and can be demonstrated.