Design an index
A table serves a set of queries, and you must decide which indexes it gets: which column orders the table itself, which secondary indexes to build, in what column order, and which existing ones to remove. Every index speeds some reads and taxes every write. Which indexes earn their place?
You will end with a set of indexes, each justified by a query it serves, and a list of indexes to drop.
Work through these in order. The last one is the one people skip, and it yields the most.
Work through it
Start from the queries, not the table
List the predicate shapes the workload actually runs. Designing from the schema produces indexes nobody uses.
Spend the clustered order first
A table can be physically ordered one way only. Give that order to the most valuable range scan or join key. In a lakehouse this is your partitioning plus sort or Z-order, and it also decides which per-block statistics can skip data.
Skip indexes past the crossover
An unclustered index beats a scan only when pages it touches ÷ total pages < cost of a sequential read ÷ cost of a random read. With PostgreSQL's defaults (1.0 and 4.0) on a 100-million-row table, that is under about 0.25% of rows. If a query selects more, and the matching rows are scattered: the index will be built, maintained and never chosen. Do not build it.
Several weak filters together: bitmaps
If several filters each keep a large share of rows but are selective together: combine bitmap indexes rather than adding B-trees, which each fail the crossover on their own.
A hot query on few columns: a covering index
Include the columns the query returns, so it never visits the table. Then verify that it stays index-only under your vacuum regime: in PostgreSQL, if pages are not marked all-visible, it fetches table rows anyway.
A small, fixed hot subset: a partial index
Index only the rows the query wants (WHERE status = 'pending'). Then verify the planner uses it with the real parameterized query from your application, not with a literal typed into a SQL console: it cannot use the index if it cannot prove the parameter matches.
Order composite columns: equality, then one range, then sort
Put columns compared with = first, then one range column, then columns used for ordering. Order the equality columns so other queries can reuse the prefix, not by selectivity. For a = 1 AND b > 5 AND c = 3, index (a, c, b), not (a, b, c).
Audit key width
Every byte of key reduces how many entries fit per page, on every lookup. In InnoDB the primary key is copied into every secondary index, so a 36-byte text UUID taxes all of them; a 16-byte binary, time-ordered one does not.
Check correlation before reaching for a tree
If the column follows the physical order of rows: per-block min/max statistics may skip as well as an index, at about a thousandth of the size.
Then subtract
Pull scan counts for every index over a month and drop the ones with zero. An unused index is pure write tax with nothing on the other side.