A hot query is WHERE status = $1 AND created_at >= $2 ORDER BY created_at. Should the index be (status, created_at) or (created_at, status)? What general rules decide composite index column order?

Use (status, created_at). PostgreSQL first matches status with equality. All matching entries then sit next to each other, and they are already sorted by created_at. So PostgreSQL does one range scan and needs no sort.

With (created_at, status), PostgreSQL must scan every entry in the date range. Then it must filter out the rows with other statuses.

The general rules for column order:

• Put columns compared with equality (=) first.
• Put the range column or the ORDER BY column after them. Why? Any column after a range condition cannot narrow the scan.
• Think about reuse. An index on (a, b) also serves queries on a alone, but not queries on b alone. Pick the order that lets one index cover several queries.

Popular advice says to always put the most selective column first. Selective means the column filters out most rows. This advice matters much less than the rules above.