PostgreSQL: index real queries, not guesses

Start with the slow query

Indexing every column is not a strategy. Start with a representative query, such as a customer's recent bookings filtered by status and ordered by date. Record its execution time, table sizes and usage frequency.

Inspect before changing

Use EXPLAIN, then EXPLAIN ANALYZE in an appropriate environment, to inspect the chosen plan and rows actually read. An index on a filtered column may help, but value depends on selectivity, sorting and data volume. Each additional index also costs storage and write time.

For multi-tenant applications, include the tenant key in relevant queries and index design. An index cannot fix a query that fails to isolate one tenant's data: that is first a data-model and security issue.

Measure again after the change using representative data, and keep the index only when its benefit justifies the cost.

Further reading: PostgreSQL indexes and EXPLAIN.