Database Indexing Strategies Every SaaS Developer Needs to Know

In the early days of a software product with ten thousand rows, PostgreSQL and MySQL will forgive almost every architectural sin. Unindexed foreign keys, wild card text searches, and unconstrained table scans execute in single-digit milliseconds. But as your multi-tenant tables scale to millions of rows, unindexed queries that once ran smoothly will suddenly lock tables, max out CPU usage, and cause cascading timeouts.

Adding random indexes after an outage is not a strategy. You need a systematic understanding of index mechanics, query planner execution, and storage overhead.

How B-Tree indexes actually work

A standard B-Tree index is an ordered, balanced search tree that allows the database engine to find specific records in logarithmic time $O(\log N)$ rather than scanning every page sequentially $O(N)$. Because the index maintains sorted order, it is exceptionally fast for equality checks, range scans (`BETWEEN`, `>`, `<`), and prefix sorting.

However, every index you create carries write overhead. On every `INSERT`, `UPDATE`, or `DELETE`, the database must update the table heap and rebalance the corresponding B-Tree. Over-indexing write-heavy tables can degrade ingestion throughput significantly.

The compound index rule: Equality, Sort, Range (ESR)

When creating composite indexes across multiple columns, the order of columns in the index definition is critical. Follow the ESR rule to ensure your index satisfies the query planner:

1. 01Equality columns first: Place columns filtered with exact matches (`tenant_id = ?`, `status = ?`) at the beginning of the index.
2. 02Sort columns second: Place columns used in `ORDER BY` clauses next, matching the sort direction.
3. 03Range columns last: Place columns with inequality filters (`created_at > ?`, `price < ?`) at the very end.

An index on `(status, created_at)` is completely useless for a query filtering only on `created_at`. Column order in composite keys is not commutative.

High-leverage indexing patterns for SaaS

Partial Indexes

Index only rows matching a condition (`WHERE deleted_at IS NULL`), saving 80% disk space.

Multi-tenant Prefix

Always prefix composite indexes with `tenant_id` / `org_id` for multi-tenant data isolation.

Covering Indexes (INCLUDE)

Attach payload columns via `INCLUDE` so the query resolves directly from index pages without heap scans.

GIN / GiST Indexes

Specialized inverted indexes for JSONB document querying, array containment, and full-text searching.

Diagnosing slow queries with EXPLAIN ANALYZE

Never guess what the database is doing. Run `EXPLAIN (ANALYZE, BUFFERS)` on your slow queries in staging with realistic data volumes. Look specifically for two warning signs: `Seq Scan` (the database read every row in the table) and `Rows Removed by Filter` (the database pulled thousands of rows into memory only to discard most of them).

Fix those hot paths with precise composite indexes, and your existing database hardware will scale comfortably to millions of transactions without costly vertical upgrades.