When a records system starts feeling slow, the instinct is to reach for caching or a bigger server. Nine times out of ten, the actual fix is an index that was never created.
The first index that matters: foreign keys. Postgres does not index them automatically. Every `record.patient_id`, every `entry.barangay_id`, if you join or filter on it, index it. This alone fixes most N+1-feeling slowdowns.
The second: the columns you filter on. `created_at` for date-range reports, `status` for workflow queues, `last_name` for search. Look at your WHERE clauses, they're a spec for your indexes.
The third: partial indexes for hot subsets. An index on `WHERE status = 'pending'` covers the queue your staff hits all day without bloating writes on the full table.
What to skip: indexing everything 'just in case.' Every index slows writes and eats storage. Index for the queries you have, measure with EXPLAIN, and add more only when a real query demands it.
