The PostgreSQL Performance Work That Actually Moved the Needle

Over one quarter we took the slowest endpoints of a production SaaS from "users complain" to p95s that stopped appearing in retros — roughly a 60% cut on the worst offenders — without scaling hardware. Ranked by impact, here is what actually did it.

1. Composite indexes that match real query shapes

Most of the win. Not "add indexes" — every table already had a dozen — but indexes whose column order matches how queries actually filter and sort:

-- The query: recent open claims for a tenant, newest first
SELECT * FROM claims
WHERE tenant_id = $1 AND status = 'OPEN'
ORDER BY created_at DESC LIMIT 50;
 
-- The index that serves it entirely:
CREATE INDEX idx_claims_tenant_status_created
    ON claims (tenant_id, status, created_at DESC);

Equality columns first, then the sort column, so Postgres walks the index in output order and stops at 50 rows — no sort node at all. The single-column indexes this replaced each looked "relevant" and none of them prevented the 200k-row sort.

2. Killing N+1s that hid behind the ORM

Lazy loading turned one list endpoint into 1 + 50 × 3 queries. Nothing was slow in isolation — pg_stat_statements showed each query at under a millisecond, called forty million times a day. JOIN FETCH and @EntityGraph for the read paths, and honestly: for list endpoints, a hand-written projection query beats entity graphs on both clarity and speed. Entities are for writing; projections are for reading.

3. Reading EXPLAIN ANALYZE honestly

Two self-deceptions to avoid. First, testing with parameters that aren't representative — the plan for a tenant with 100 rows says nothing about the tenant with 2M. Test with your biggest tenant's ids. Second, ignoring Rows Removed by Filter:

->  Index Scan using idx_claims_tenant on claims
      (actual time=0.4..312.7 rows=50 loops=1)
      Rows Removed by Filter: 214893

The index scan "was used" — and then threw away 99.98% of what it read. "Uses an index" is not the goal; selectivity is. This one line is the fastest signal that your index doesn't match your query shape (see item 1).

4. Connection pool sizing, downward

The counterintuitive one. Under load spikes, latency ballooned while the database CPU sat at 40%. The pool was set to 100 connections "for headroom" — which just moved queueing into Postgres, where a hundred backends fought over cores. Sizing down to ~2× cores with fail-fast checkout made tail latency better under the same load. Little's Law is undefeated; a queue in front of the pool beats contention inside the database.

What didn't move the needle

Tuning work_mem and friends (marginal until the queries were right), REINDEX rituals, and — the popular one — caching. Caching worked, but layering Redis over the queries above would have hidden problems that were one index away from solved, and added invalidation bugs at multi-tenant complexity. Cache after the query is right, not instead of getting it right.

The meta-lesson: measure with production-shaped data, fix the query shapes, and only then spend complexity on caching. The database is rarely the bottleneck; the way we talk to it usually is.