Indexing Postgres without regret

Indexes are not free. Every write pays a tax, every plan has to consider them, and storage adds up faster than you'd think.

The default answer for almost any equality or range query on a normal column is a B-tree. It is boring and that is the point — it handles 90% of what an OLTP app needs.

GIN shines when you have arrays, JSONB, or full-text search columns. Pair it with the right operator class and you can replace what used to be a dedicated search service.

BRIN is the secret weapon for append-only tables ordered by time. A multi-gigabyte log table can be summarised in a few kilobytes of index because adjacent rows share a value range.

Partial indexes are the cheapest performance win in the book: filter the rows you actually query, skip the rest, and shave both build time and lookup cost.

The rule I keep coming back to: measure before you add, and re-measure after.