Partial index for “active” rows in Postgres

Not all queries benefit from a full-table index, especially when most rows are ‘inactive’ or archived. A partial index lets you index only the subset you care about (like active users or un-deleted records), which shrinks index size and improves cache hit rates—often a real p95 latency win. The big lesson is aligning indexes with actual query patterns, not hypothetical ones. I also like partial indexes for uniqueness scoped to active rows, like ‘only one active subscription per user’. When you use indexing features intentionally (and create them CONCURRENTLY in production), you can avoid a lot of application-level complexity.