An index is a trade-off
Indexes can make reads faster by avoiding a full table scan, but they consume storage and make writes more expensive. The goal is not to index every column; it is to support the queries the application actually runs.
Start with the query pattern
Use EXPLAIN ANALYZE to inspect a slow query. Look for sequential scans on large tables, expensive sorts, and filters that remove most rows only after reading them.
Composite index order matters
For a query filtering by tenant and sorting by creation time, an index such as (tenant_id, created_at) is often more useful than two unrelated single-column indexes. The leading columns should match the most selective and common access pattern.
Measure after every change
Database performance depends on data size and distribution. Benchmark with realistic records, compare query plans before and after, and remove indexes that no longer serve a real workload.