Database Indexing: What Every Developer Should Know

Why Queries Get Slow

A database table is, at its core, a heap of rows. When you run SELECT * FROM users WHERE email = '[email protected]', the database scans every row and checks each one. With 100 rows, this is instant. With 10 million rows, it takes seconds.

An index solves this by maintaining a separate data structure — usually a B-tree — that maps values to row locations. Looking up a value in a B-tree is O(log n) instead of O(n). For 10 million rows, that's the difference between 23 steps and 10 million.

The B-Tree Intuition

A B-tree is like a phone book. Values are sorted, and the tree structure lets you jump to the right section quickly.

Simplified B-tree for email index:

            [m]
           /   \
     [c, g]     [r, z]
    /  |  \    /  |  \
  [a] [d][h] [n] [s] [za]

To find [email protected], the database:

1. Compares with m → go right
2. Compares with r, z → go left (between r and z)
3. Finds [email protected] in the leaf node → returns the row location

Without the index, the database reads every row in the table.

Creating Indexes

-- Single column index — fast lookups by email
CREATE INDEX idx_users_email ON users (email);

-- Unique index — enforces uniqueness + enables fast lookup
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

-- Composite index — fast lookups by (status, created_at) together
CREATE INDEX idx_transactions_status_date ON transactions (status, created_at DESC);

-- Partial index — only indexes rows matching the condition
CREATE INDEX idx_users_unverified ON users (created_at)
    WHERE verified = false;

Indexes are automatically used by the query planner when it determines they'll be faster than a sequential scan. You don't reference them in queries.

The Composite Index Column Order Rule

This is where most developers get confused. The order of columns in a composite index matters enormously.

An index on (status, created_at) supports:

• WHERE status = 'active'
• WHERE status = 'active' AND created_at > '2024-01-01'
• ORDER BY status, created_at (without WHERE)

It does not efficiently support:

• WHERE created_at > '2024-01-01' (without filtering on status first)

The rule: an index is useful from left to right, stopping at the first range condition. Put the most selective column first, and put range conditions (>, <, BETWEEN) last.

-- Supports: WHERE account_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_txns ON transactions (account_id, status, created_at);

-- For pagination query:
-- WHERE account_id = $1 AND created_at < $2 ORDER BY created_at DESC
CREATE INDEX idx_txns_cursor ON transactions (account_id, created_at DESC);

When NOT to Index

Indexes aren't free. Each index:

• Takes disk space (often 10-30% of the table size)
• Slows down writes — every INSERT, UPDATE, and DELETE must update all indexes on the table
• Requires maintenance (VACUUM in PostgreSQL, OPTIMIZE in MySQL)

Don't index:

• Columns with very low cardinality (e.g., a boolean is_active) — the index barely helps
• Tables with fewer than a few thousand rows — the query planner usually prefers a sequential scan
• Columns that are rarely filtered on
• Every column "just in case" — this is a common mistake on teams that don't monitor write performance

Covering Indexes

A covering index includes all the columns a query needs. The database can answer the query entirely from the index without touching the main table.

-- Query: SELECT id, email, created_at FROM users WHERE status = 'active'
-- Index that covers this query:
CREATE INDEX idx_users_active_covering ON users (status, id, email, created_at);

With this index, PostgreSQL never reads the users table rows — it reads only the index. This is called an "index-only scan" and is significantly faster for large tables.

EXPLAIN ANALYZE Is Your Friend

Never guess whether an index is being used. Ask the database:

EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE account_id = 'acc_123'
  AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

Look for:

• Index Scan or Index Only Scan — index is being used ✓
• Seq Scan — full table scan (may be correct for small tables, a problem for large ones)
• rows= — estimated vs actual rows. A large discrepancy means outdated statistics; run ANALYZE

The most important metric: actual time. If a query takes 1.2 seconds, run EXPLAIN ANALYZE, find the slow node, and ask: is there an index that would help here?

The Practical Checklist

Before releasing a feature with new database queries:

1. Run EXPLAIN ANALYZE on each new query against production-scale data
2. Check for Seq Scan on large tables
3. Add indexes for foreign keys — these are almost always missing and expensive
4. Add composite indexes for multi-column filters that appear together
5. Monitor slow query logs in production

Foreign key indexes deserve special mention. PostgreSQL does not automatically index foreign keys. If you have transactions.account_id referencing accounts.id and you frequently query SELECT * FROM transactions WHERE account_id = $1, you need CREATE INDEX ON transactions (account_id). This is one of the most common performance issues I find in production codebases.