How do you decide which indexes a table needs?

Start from the queries, not the table. Look at what appears in WHERE, JOIN and ORDER BY, then build a composite index with the equality columns first and the range or sort column last. Confirm with EXPLAIN ANALYZE that an index scan replaced a sequential scan. Every index costs write throughput and storage, so drop unused ones and avoid indexing a low cardinality column on its own.

I work backwards from the slow query log rather than guessing at design time. Take a query filtering on tenant id and status and sorting by created at. The right index there is on tenant id, status, created at in that order, because equality predicates go first and the sort column goes last so the index can satisfy the ordering too. Put created at first and the index is nearly useless for that query. Then I actually run EXPLAIN ANALYZE and check the plan changed and the row estimates are sane, because the planner will ignore an index if it thinks it will match most of the table anyway. On the cost side, I have seen a write heavy table with eleven indexes where inserts were the bottleneck, and dropping four unused ones roughly doubled ingest throughput. In Postgres I check pg_stat_user_indexes for indexes with zero scans before proposing anything gets dropped, and I create them concurrently on a live table.