Partial Indexes and Expression Indexes in PostgreSQL: The Query Optimization Patterns That Cut Our Mobile API P99 Latency by 80%

--- title : " Partial Indexes in PostgreSQL: Cut Your API P99 by 80%" published : true description : " A hands-on workshop on PostgreSQL partial indexes, expression indexes, and composite column ordering — the patterns that dropped our mobile API P99 from 200ms to 12ms." tags : postgresql, api, performance, architecture canonical_url : https://blog.mvpfactory.co/partial-indexes-postgresql-api-p99-latency --- ## What We Will Build By the end of this workshop, you will know how to replace generic B-tree indexes with surgical partial indexes, expression indexes for JSONB fields, and properly ordered composites. We will walk through the exact changes that dropped a critical mobile API endpoint from **200ms P99 to 12ms** — with real `EXPLAIN ANALYZE` output at each step. ## Prerequisites - A running PostgreSQL instance (14+) - Basic familiarity with `CREATE INDEX` and `EXPLAIN` - A table with soft deletes or status filtering (most production apps qualify) ## Step 1: Audit Your Existing Inde