Database Indexing

Add the right indexes for query patterns, and remove indexes that are never used or harm write performance.

Why

A database without indexes on the query columns does a full table scan — it reads every row to find the matching ones. On a 10M-row table, that is 10M reads instead of 10, and the query goes from 2ms to 2 seconds. The right index turns a scan into a seek, reducing I/O by orders of magnitude. But indexes are not free — every index adds overhead to every INSERT, UPDATE, and DELETE (the index must be updated too), and too many indexes slow writes and waste disk. Indexing is a balance: add indexes for the queries that are slow and frequent, remove indexes that are never used, and use composite indexes for multi-column WHERE clauses. It is the highest-leverage database optimization, and the most commonly missed one.

When

After identifying slow queries with query analysis (EXPLAIN ANALYZE, slow query log). Add an index when a query scans more rows than it returns. Audit indexes quarterly to remove unused ones.

How

1. Find the slow queries
   - Enable the slow query log (PostgreSQL: log_min_duration_statement = 100ms)
   - Or use pg_stat_statements to see the most time-consuming queries
   - Look for queries that do Seq Scan (full table scan) instead of Index Scan

2. Analyze each slow query with EXPLAIN (ANALYZE, BUFFERS)
   - EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
   - Look for "Seq Scan" — that means a full table scan, no index is used
   - Look at "Rows Removed by Filter" — high number means the scan reads many irrelevant rows
   - The cost and actual time tell you how slow it is

3. Create the right index for the query pattern
   - Single column: CREATE INDEX idx_orders_user_id ON orders(user_id);
   - Composite (multi-column WHERE): CREATE INDEX idx_orders_user_status ON orders(user_id, status);
   - Column order matters: put the equality columns first, then range columns
   - Partial index (if most queries filter on a condition): CREATE INDEX ... WHERE status = 'active';

4. Verify the index is used
   - Re-run EXPLAIN ANALYZE — should now show "Index Scan" instead of "Seq Scan"
   - The cost and actual time should drop dramatically
   - If it still shows Seq Scan, the planner chose not to use it (wrong column order, or table too small)

5. Audit and remove unused indexes
   - Query pg_stat_user_indexes to find indexes with 0 scans since last reset
   - Each unused index adds write overhead for no benefit — drop it
   - Be careful: some indexes are used only by rare queries (monthly reports) — check a full cycle

Example

-- Find slow queries (PostgreSQL)
-- pg_stat_statements: top 10 by total time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Analyze a slow query
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending' ORDER BY created_at DESC;
-- Output before index:
--   Seq Scan on orders  (cost=0.00..45231.00 rows=15 actual time=234.567..234.567)
--   Rows Removed by Filter: 999985  ← scanned 1M rows to find 15!

-- Create the composite index (equality columns first, then sort column)
CREATE INDEX idx_orders_user_status_created
  ON orders(user_id, status, created_at DESC);

-- Re-analyze
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending' ORDER BY created_at DESC;
-- Output after index:
--   Index Scan using idx_orders_user_status_created  (cost=0.42..12.50 rows=15 actual time=0.045..0.089)
--   234ms → 0.089ms — 2600x faster

-- Audit unused indexes (run after a full business cycle)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- never used since stats reset
-- DROP INDEX idx_never_used;

Common Problems

⚠Creating a single-column index on user_id and another on status, instead of a composite index on (user_id, status) — the planner can use only one, and the query still scans many rows filtered by the other column.
⚠Putting the range column first in a composite index (created_at, user_id) instead of the equality column first (user_id, created_at) — the index cannot be used for the equality lookup, and the planner falls back to a scan.
⚠Adding indexes on every column "just in case" — each index slows every write and wastes disk, and most are never used by any query, so the write performance degrades for no benefit.
⚠Creating the index but never running ANALYZE (or autovacuum being too slow) — the planner's statistics are stale, it does not know the index exists or is selective, and it chooses a scan anyway.

Best Practices

✓Use EXPLAIN (ANALYZE, BUFFERS) on every slow query to understand the actual plan — guessing at indexes without seeing the plan leads to indexes that the planner does not use.
✓Order composite index columns by equality first, then range, then sort — this follows the access pattern and lets one index serve the WHERE, the range, and the ORDER BY.
✓Use partial indexes for queries that always filter on a condition (WHERE status = 'active') — the index is smaller, faster, and cheaper to maintain than a full index.
✓Audit unused indexes quarterly with pg_stat_user_indexes and drop the ones with zero scans — every unused index taxes every write for no benefit.