Database Indexing Strategies for High-Volume Transactional Systems

Every index is a tax on writes

The framing that helps most is this: an index doesn't just speed up reads, it slows down every write to that table, because the database has to update the index alongside the row itself. A table with eight indexes pays that cost eight times per insert. This isn't a reason to avoid indexes — it's a reason to index deliberately, based on actual query patterns, rather than indexing every column that might someday appear in a WHERE clause.

Compound indexes and column order

A compound index on (tenantId, status, createdAt) can serve a query filtering on tenantId alone, tenantId + status, or all three — but not a query filtering on status alone, because the index is only useful as a left-to-right prefix match. Getting the column order right, based on your actual query patterns (which columns appear in nearly every query, versus which are sometimes omitted), is the difference between one compound index covering a dozen query shapes and needing a dozen separate indexes.

-- Serves: WHERE tenant_id = ?
-- Serves: WHERE tenant_id = ? AND status = ?
-- Serves: WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Partial indexes for skewed data

If 95% of orders are status = 'completed' and your hottest query is "find all pending orders," indexing the entire column wastes space and write cost maintaining an index entry for millions of rows your query never touches. A partial index scoped to only the relevant rows is smaller, faster to maintain, and faster to query:

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

The EXPLAIN ANALYZE habit

Guessing which index a query needs, without checking, is how systems accumulate indexes nobody uses and are missing the one that actually matters. EXPLAIN ANALYZE on a slow query tells you directly whether it's doing a sequential scan (no useful index), an index scan that's still slow (wrong column order, or fetching too many matching rows), or something else entirely (a lock wait, not an indexing problem at all). I run this against every new query that touches a table over a few hundred thousand rows before it ships, not after it's identified as slow in production.

Indexes you should remove, not add

Unused indexes are pure cost — every write pays for them, no read benefits. Most databases can tell you index usage statistics directly (PostgreSQL's pg_stat_user_indexes, for instance). Reviewing this periodically and dropping indexes with zero or near-zero scans is as valuable as adding the right new one, and it's the step teams skip because removing something feels riskier than adding something — even when the data says otherwise.

The rule

Index for the queries you actually run, in the order your WHERE and ORDER BY clauses actually use the columns, and periodically prune what isn't earning its write-cost. An index added "just in case" is a write-performance cost paid indefinitely for a read benefit that may never materialize.