Indexing Strategy

Indexes are not added per column; they are designed per query shape. The method is to list the queries the table actually serves, in order of frequency and importance, and design the smallest set of indexes that serves them.

The rules that decide whether an index is used:

Column order in a composite index — usable only for a left-to-right prefix, so an index on (tenant_id, status, created_at) serves queries filtering on tenant alone, or tenant plus status, but not status alone. Order equality predicates first, then ranges, then sort columns.

Selectivity — an index on a column with three distinct values is usually ignored.

Covering — an index containing every column the query selects answers it without touching the table, which can be a large win for a hot read path.

Partial — indexing only the rows a query cares about (WHERE status = 'active') makes it far smaller and cheaper to maintain.

The cost side is the discipline: every index is updated on every write to its columns, so a table with a dozen indexes has a write path roughly a dozen times more expensive. Periodically look for unused indexes — most engines report index usage — and remove them. And build indexes concurrently on large tables, because the index creation itself can be the outage.