B-tree, GIN and BRIN — which index when?

B-tree is a good starting point for equality, ranges and sorting. GIN helps search for elements inside values, e.g. arrays or JSONB. BRIN stores summaries of block ranges and suits large data correlated with physical order.

CREATE INDEX orders_customer_date_idx
ON orders (customer_id, created_at DESC);

In practice: the example fits a given customer's order history. The choice of columns follows the filters and sorting.

Pitfall: an index increases write cost and takes up space. Not every operator can use every index type. I verify the decision with the plan instead of indexing every column.