The single most common cause of "our database got slow as we scaled" isn't the database engine, the hosting tier, or the schema design — it's a missing index on a column that gets queried constantly.
A database index is a separate data structure (typically a B-tree) that lets the database find rows matching a condition without scanning every row in the table. Without an index on a queried column, the database performs a sequential scan — reading every row to check if it matches — which is fine for a thousand rows and genuinely painful for a hundred million.
Why Indexing Matters (and When to Skip It)
The performance difference between an indexed and unindexed lookup on a large table isn't incremental — it's often the difference between milliseconds and seconds, because an index turns an O(n) scan into an O(log n) lookup. This is consistently the highest-leverage, lowest-effort performance fix available in most applications, ahead of caching, ahead of query rewriting, ahead of hardware upgrades.
Skip adding an index on columns rarely or never used in WHERE, JOIN, or ORDER BY clauses — every index has a real write cost (each insert/update must also update the index) and storage cost, so indexing every column "just in case" is its own performance mistake.
Getting Started with Indexing
Identify what needs an index by looking at your actual query patterns:
-- this query needs an index on user_id
SELECT * FROM orders WHERE user_id = 'abc123';
CREATE INDEX idx_orders_user_id ON orders(user_id);
Composite indexes for queries filtering on multiple columns:
-- query filters on both status and created_at
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
Core Indexing Concepts Every Developer Should Know
Column order in a composite index matters enormously. An index on (status, created_at) efficiently serves queries filtering on status alone or on both columns together, but doesn't help a query filtering on created_at alone — the leftmost column is the one the index can be used for independently:
-- uses the index efficiently
WHERE status = 'pending'
WHERE status = 'pending' AND created_at > '2026-01-01'
-- does NOT use this index efficiently
WHERE created_at > '2026-01-01'
EXPLAIN (or EXPLAIN ANALYZE) is how you verify an index is actually being used, rather than assuming it is because it exists:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 'abc123';
-- look for "Index Scan" vs "Seq Scan" in the output
Not every index type is a B-tree. GIN indexes for JSONB/array/full-text search columns, hash indexes for pure equality lookups, and specialized index types for geospatial data (PostGIS) each fit different query patterns — using the wrong index type means paying the write cost without getting the expected read benefit.
Indexes have a write cost proportional to how many exist on a table. Every insert, update, or delete must also update every index on that table — a table with ten indexes is meaningfully slower to write to than one with two, which is why indiscriminately indexing every column is itself a mistake, not just an obviously-safe optimization.
Common Indexing Mistakes and How to Fix Them
Mistake 1: no index on foreign key columns. Many databases (Postgres notably) don't automatically index foreign keys, so joins on them default to a full scan unless explicitly indexed. Fix: index every foreign key column that's actually used in joins or filters.
Mistake 2: over-indexing — adding an index for every column "to be safe." This bloats storage and slows down every write without a corresponding read benefit for indexes that never get used. Fix: index based on actual query patterns (use pg_stat_statements or equivalent to find slow, frequent queries), and periodically audit for unused indexes.
Mistake 3: wrong column order in composite indexes. An index built in the wrong order for your actual query patterns provides far less benefit than expected, and teams often don't notice because the query still "has an index," just not one that helps. Fix: order composite index columns by matching your most common query's filter structure — equality filters before range filters, in general.
When Should You Add an Index vs. Optimize the Query Itself?
Add an index when a specific column is genuinely and frequently filtered/joined/sorted on and EXPLAIN confirms a sequential scan is the bottleneck. Optimize the query itself (restructuring joins, avoiding SELECT *, adding pagination) when the issue is the query's shape rather than a missing lookup path — indexing a poorly structured query only helps so much.
Indexing in Production
Monitor slow query logs continuously, not just during initial development — data volume and query patterns both change as an app grows, and a query that was fine at 10,000 rows can become the top bottleneck at 10 million without any code change at all. Also periodically audit for unused indexes (most databases expose index usage statistics) and drop them — every unused index is pure write overhead with no benefit.
If a query is slow and you haven't checked EXPLAIN yet, that's the actual first step — guessing at the fix without seeing whether an index is even being considered wastes real time.