PostgreSQL performance tuning for SaaS: the indexes that fix your slow endpoints

Read EXPLAIN before you guess

Never optimize blind. EXPLAIN (ANALYZE, BUFFERS) shows what Postgres actually does with a query. The signals that matter: a Seq Scan on a big table where you filter or join (you're missing an index), a huge gap between estimated and actual rows (stale statistics or a bad query shape), and Sort/Hash steps spilling to disk. The plan tells you where the time goes — you stop guessing.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM invoices
WHERE tenant_id = $1 AND status = 'open'
ORDER BY created_at DESC LIMIT 50;
-- Seq Scan on invoices  (actual rows=2,000,000)  <-- missing index

-- Fix: a composite index matching the filter + sort
CREATE INDEX CONCURRENTLY idx_invoices_tenant_status_created
  ON invoices (tenant_id, status, created_at DESC);

Index the way you query

• Composite index column order = equality filters first, then the range/sort column (tenant_id, status, created_at).
• A well-ordered composite index can satisfy the WHERE and the ORDER BY at once — no separate sort step.
• Partial indexes for skewed data (WHERE status = 'open') are smaller and faster when you mostly query one slice.
• Don't over-index: every index slows writes and costs storage — add them for real query patterns, not hypotheticals.

The N+1 that hides in your ORM

A dashboard that loads 50 rows and then lazily loads each row's relations fires 51 queries. Individually fast, collectively a disaster — and invisible until you count queries per request. The fix is to fetch related data in a single query (a join or a batched IN query / your ORM's eager-load), turning 51 round-trips into one or two.

The rest of the toolkit

Keep statistics fresh (autovacuum/ANALYZE) so the planner makes good choices, use connection pooling (a serverless app opening a connection per invocation will exhaust Postgres), select only the columns you need instead of SELECT *, and paginate with keyset pagination on large tables instead of large OFFSETs that scan and discard rows.

Key takeaways

• Diagnose with EXPLAIN (ANALYZE, BUFFERS) — Seq Scans and bad row estimates point straight at the fix.
• Build composite indexes matching your filter + sort; use partial indexes for skewed slices.
• Hunt N+1 queries — count queries per request and eager-load relations.
• Pool connections, keep stats fresh, select only needed columns, and use keyset pagination at scale.