A composite index on (user_id, created_at DESC) dramatically reduced p95 latency from 410ms to 24ms, and it was created CONCURRENTLY to avoid locking writes.

Ongoing monitoring includes weekly checks of index effectiveness through hit ratios, bloat assessments with pgstattuple, and scanning for unused indexes with pg_stat_user_indexes to keep performance healthy.

An expression index on LOWER(title) boosted case-insensitive prefix searches from 520ms to 14ms, with notes that pg_trgm and other methods can cover full substring searches.

The indexing strategy highlights that partial, covering, and expression/GIN/BRIN indexes can yield larger gains than plain B-tree indexes on core columns.

Baseline analysis found slow queries were often due to sequential scans rather than bad SQL; using pg_stat_statements and EXPLAIN ANALYZE helped identify bottlenecks and prioritize fixes.

A foreign key index on events.organization_id improved the organization summary endpoint from 480ms to 9ms; audits showed several missing FK indexes needing addition.

A GIN index on JSONB metadata (metadata jsonb_path_ops) sped up country-filtered queries from 320ms to 18ms.

A covering index (including title, type, read_at) using INCLUDE enabled index-only scans, reducing a list endpoint from 96ms to 11ms at the cost of extra disk space.

Lessons include avoiding unused indexes, avoiding low-cardinality full indexes, not indexing during migrations without CONCURRENTLY, and avoiding over-covering indexes that hinder index-only scans.

A partial index on unread events (user_id, created_at DESC) WHERE read_at IS NULL reduced a notification badge query from 180ms to 3ms.

A BRIN index on created_at for time-series scans drastically cut a report query time from 890ms to 62ms while keeping index size small.

A developer notes that adding seven specific PostgreSQL indexes dramatically reduced API latency from 400ms to 40ms for an analytics dashboard over millions of rows across multiple tables.