// Case studies
PostgreSQL slow-query tuning, API p95 1.2s to 180ms
Missing indexes, N+1 ORM patterns, and bloated JSONB columns, backend performance pass on a billing service.
By Simplileap · Published August 5, 2025 · 9 min read
A subscription billing API, Django ORM on PostgreSQL 14, degraded to p95 1.2s on invoice list endpoints as customer count crossed 8,000. RDS CPU pegged 78% during month-end billing runs.
Profiling: pg_stat_statements showed sequential scans on invoice_line_items; ORM prefetch missing on nested serializers; JSONB metadata column indexed with generic GIN but queried wrong operator class.
Fixes: composite indexes on (customer_id, created_at DESC); partial index on open invoices; serializer refactor with select_related/prefetch_related; materialized view for AR aging refreshed every 15 minutes; connection pool sizing via PgBouncer.
Problems: migration lock on 40M-row table, used CONCURRENTLY index builds in maintenance window; one index dropped wrong plan on rare filter, iterative EXPLAIN ANALYZE.
Verification: pgbench-derived workload test; Datadog APM before/after; regression alert if p95 > 250ms.
Outcome: list endpoint p95 1.2s → 180ms; month-end RDS CPU peak 78% → 41%. Billing SaaS, name withheld.
// Related services
Ready to scope your next initiative?
Share your goals with our Bangalore team. We respond within one business day with a clear path from discovery to delivery.