How do I choose index order for composite indexes?

Match the most selective AND most frequently filtered columns first, then add columns used for sorting. For example, if 95% of queries filter by tenant and 80% by status and sort by created_at, use (tenant_id, status, created_at DESC). Use INCLUDE (Postgres) or covering indexes (MySQL) to add non-filter columns that are returned but not part of the predicate.

Only after you’ve exhausted: correct indexing, query shaping, connection pooling, targeted caching, partitioning, read replicas, and archiving. Shard when write QPS or dataset size can’t be handled by a single primary with replicas even on the largest practical instance. Plan for 3–6 months of platform work, not a sprint.

Aren’t ORMs the problem?

ORMs aren’t evil; unconfigured ORMs are. Turn on lazy/eager loading consciously, add query logging, and codify query hints. Keep critical paths in hand-written SQL with `EXPLAIN (ANALYZE, BUFFERS)` in CI for guardrails. The combo works.

Postgres or MySQL—does it matter?

Both can scale into the tens of thousands of TPS with good schemas and ops. Postgres has richer indexing (GIN/GIST), CTEs, and JSONB; MySQL has rock-solid replication and great support via Aurora and Vitess. Choose based on your team’s operational comfort and ecosystem tools.

What’s the fastest way to prove value to the exec team?

Pick one high-traffic endpoint (search or list), ship a composite index + query rewrite, and show p95 → conversion delta in a weekly report. We routinely show 20–50% latency cuts translating to 1–3% conversion lifts within two weeks.

Performance-optimization · Oct 27, 2025 · 10 minute read

Stop Buying Bigger Boxes: Database Optimizations That Actually Scale With User Growth

User-facing performance is a database problem 8 times out of 10. Here’s the playbook we use to cut p95s and lift conversion without rewriting your app.

Back to all posts

Stop Buying Bigger Boxes: Database Optimizations That Actually Scale With User Growth

Key takeaways

Implementation checklist