Database Indexing: The Only Guide That Won't Make Your Queries Slower

I've seen a single missing index turn a 50ms API call into a 30-second timeout that took down a checkout service at 3am. The fix was one line of SQL. The cost of not having it was $40k in lost revenue. Database indexing is the single highest-leverage performance optimization you can make — and the easiest to screw up. Most developers either over-index (slowing writes to a crawl) or under-index (wondering why their queries are slow). After this guide, you'll be able to look at any slow query, identify the missing index, and know exactly when adding one will hurt more than help.

How B-Tree Indexes Actually Work (And Why They Fail)

The default index type in every major database is the B-tree. It's a balanced tree where each node holds a range of sorted keys, and leaf nodes point to the actual rows (or row IDs). The tree depth grows logarithmically with data size — a 1-billion-row table needs only about 4-5 levels. That's why a lookup is O(log n). But here's where it breaks: if your query doesn't match the leftmost prefix of the index, the B-tree is useless. A composite index on (a, b, c) can't speed up WHERE b = 5 — it'll fall back to a full scan. I've seen this bring down a reporting dashboard because the WHERE clause filtered on the second column. The fix: either reorder the columns or create a separate index on b.

-- io.thecodeforge — System Design tutorial

-- Table: orders (50M rows)
-- Index: (user_id, created_at)

-- This query uses the index efficiently:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345 AND created_at > '2024-01-01';
-- Output: Index Scan using idx_orders_user_created (cost=0.56..8.58 rows=100 width=120)

-- This query does NOT use the index:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > '2024-01-01';
-- Output: Seq Scan on orders (cost=0.00..123456.78 rows=5000000 width=120)
-- The index on (user_id, created_at) can't be used because user_id is not in the WHERE clause.

-- Fix: create a separate index on created_at
CREATE INDEX idx_orders_created_at ON orders(created_at);

When to Use Partial Indexes (And When Not To)

A partial index indexes only a subset of rows — those matching a WHERE condition. This shrinks the index size and reduces write overhead. I use them constantly for soft-delete patterns: index only WHERE deleted_at IS NULL. The index is 10x smaller, writes are faster, and queries for active records are still instant. But partial indexes have a gotcha: the query planner only uses them if your query's WHERE clause is a superset of the index's condition. If you index WHERE status = 'active' and query WHERE status = 'active' AND created_at > '2024-01-01', it works. But if you query WHERE status IN ('active', 'pending'), it won't use the partial index — it'll fall back to a full scan. Always match the condition exactly.

-- io.thecodeforge — System Design tutorial

-- Table: users (10M rows, 20% deleted)
-- Without partial index:
CREATE INDEX idx_users_active ON users(active) WHERE active = true;
-- This index is 1/5 the size of a full index on active.

-- Query that uses the partial index:
EXPLAIN ANALYZE
SELECT * FROM users WHERE active = true AND last_login > '2024-06-01';
-- Output: Index Scan using idx_users_active (cost=0.29..8.30 rows=100 width=200)

-- Query that does NOT use the partial index:
EXPLAIN ANALYZE
SELECT * FROM users WHERE active = true OR last_login > '2024-06-01';
-- Output: Seq Scan (cost=0.00..250000.00 rows=5000000 width=200)
-- The OR condition breaks the partial index match.

Covering Indexes: The Secret to Blazing Fast Reads

A covering index includes all columns needed by a query, so the database never has to touch the table (heap). This is called an index-only scan. In PostgreSQL, you use the INCLUDE clause to add extra columns without expanding the index tree. In MySQL, you just add them to the index (but that increases tree size). I once optimized a reporting query from 12 seconds to 3ms by adding two columns to an existing index with INCLUDE. The index size grew by 30%, but the query became instant. The trade-off: covering indexes are great for read-heavy workloads but brutal on writes. Use them sparingly — only for the hottest queries.

-- io.thecodeforge — System Design tutorial

-- Table: orders (50M rows)
-- Frequent query: get order total and status by user_id

-- Before: index on user_id only, query needs heap fetch for total and status
EXPLAIN ANALYZE
SELECT total, status FROM orders WHERE user_id = 12345;
-- Output: Index Scan using idx_orders_user_id (cost=0.56..8.58 rows=100 width=16) + 100 heap fetches

-- After: covering index with INCLUDE (PostgreSQL 11+)
CREATE INDEX idx_orders_user_id_covering ON orders(user_id) INCLUDE (total, status);

EXPLAIN ANALYZE
SELECT total, status FROM orders WHERE user_id = 12345;
-- Output: Index Only Scan using idx_orders_user_id_covering (cost=0.56..4.58 rows=100 width=16)
-- No heap fetches!

Hash Indexes: Fast Equality Lookups, Nothing Else

Hash indexes map keys to a fixed-size hash bucket. They're O(1) for equality lookups — faster than B-tree's O(log n). But they can't do range queries, sorting, or partial matches. PostgreSQL supports hash indexes natively; MySQL uses them only in MEMORY tables. I use hash indexes for lookup tables — like a country code to timezone mapping. But never use them on columns with many duplicates (low cardinality) because hash collisions degrade performance. And never use them for columns you'll sort or filter with >, <, or LIKE. The query planner will ignore the index and do a full scan.

-- io.thecodeforge — System Design tutorial

-- Table: country_codes (250 rows)
-- Frequent query: get timezone by country code

CREATE INDEX idx_country_code_hash ON country_codes USING hash(code);

-- Fast equality lookup:
EXPLAIN ANALYZE
SELECT timezone FROM country_codes WHERE code = 'US';
-- Output: Index Scan using idx_country_code_hash (cost=0.00..8.02 rows=1 width=16)

-- Range query (ignores hash index):
EXPLAIN ANALYZE
SELECT timezone FROM country_codes WHERE code > 'US';
-- Output: Seq Scan on country_codes (cost=0.00..4.50 rows=125 width=16)

GiST and GIN Indexes: Full-Text Search and Beyond

GiST (Generalized Search Tree) and GIN (Generalized Inverted Index) are for complex data types: arrays, JSONB, full-text search, geometric data. GIN is faster for lookups but slower to build; GiST is faster to build but slower for lookups. I use GIN for JSONB queries and full-text search. The classic mistake: indexing a JSONB column with a GIN index on the whole column, then querying with @> (contains) — that works. But if you query with -> (access by key), the index is useless. You need a path-specific index. I've seen this cause a 10-second query on a 1M-row table. The fix: create an index on the specific path, e.g., CREATE INDEX idx_data_email ON users USING gin ((data -> 'email'));

-- io.thecodeforge — System Design tutorial

-- Table: users with JSONB column 'data'
-- Frequent query: find users by email inside JSONB

-- Wrong: index on whole column
CREATE INDEX idx_users_data ON users USING gin(data);
-- This index helps @> queries but not -> queries.

-- Query that uses the index:
EXPLAIN ANALYZE
SELECT * FROM users WHERE data @> '{"email": "test@example.com"}';
-- Output: Bitmap Index Scan on idx_users_data

-- Query that does NOT use the index:
EXPLAIN ANALYZE
SELECT * FROM users WHERE data->>'email' = 'test@example.com';
-- Output: Seq Scan (slow!)

-- Fix: path-specific index
CREATE INDEX idx_users_data_email ON users USING gin ((data->'email'));
-- Now the -> query uses the index.

Index Maintenance: The Silent Performance Killer

Indexes bloat over time. In PostgreSQL, updated rows leave dead index entries that VACUUM cleans up. If autovacuum can't keep up, the index grows and queries slow down. I've seen a 10GB index bloat to 40GB because autovacuum was disabled on a busy table. The symptom: queries that used to take 1ms suddenly take 100ms. The fix: monitor index size over time, and run REINDEX periodically. In MySQL, the equivalent is OPTIMIZE TABLE. But be careful — REINDEX locks the table for writes. Use CONCURRENTLY in PostgreSQL to avoid downtime: REINDEX INDEX CONCURRENTLY idx_name;

-- io.thecodeforge — System Design tutorial

-- Check index bloat in PostgreSQL:
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
    round(100 * pg_relation_size(indexrelid) / nullif(pg_relation_size(indrelid), 0), 2) AS bloat_pct
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild index without locking writes:
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
-- Note: CONCURRENTLY requires extra disk space and takes longer, but avoids downtime.