Which indexes should I know?

Definition:

An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.

Key points:

• B-tree handles many equality and range queries; composite indexes contain multiple columns.

• Partial indexes cover a subset of rows; unique indexes reject duplicate keys.

• Index order should follow real filters and sorting; extra indexes slow writes.

• For a query filtering by tenant and sorting by creation time, index column order should reflect those access patterns.

Interview point:

• Explain the supported query and the write/storage cost; low cardinality does not make a field universally useless in every index.