5 Database Indexes Every Backend Engineer Should Know

The indexes that solved 80% of my performance problems—and when to use each one

Every backend engineer eventually faces the same problem: queries that worked fine in development become painfully slow in production.

Last quarter, I spent three weeks optimizing our slowest endpoints. Five strategic indexes fixed 80% of our performance issues.

Here are the indexes that matter most—and when to use them.

1. B-Tree Index (Your Default Choice)

What it is: The standard index type in PostgreSQL, MySQL, and most databases.

When to use it:

• Equality queries: WHERE user_id = 123

• Range queries: WHERE created_at > '2024-01-01'

• Sorting: ORDER BY created_at DESC

Example:

sql:

CREATE INDEX idx_users_email ON users(email);

-- This query now uses the index
SELECT * FROM users WHERE email = 'user@example.com';

Real impact: Reduced our user lookup time from 450ms to 12ms.

Watch out for: Indexes slow down writes. Don’t index everything.

---

2. Composite Index (Multiple Columns)

What it is: An index on multiple columns, order matters.

When to use it:

• Queries filtering on multiple columns

• Common WHERE clause combinations

Example:

sql:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Fast query
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- Also fast (uses leftmost column)
SELECT * FROM orders WHERE user_id = 123;

-- Slow! Index can't be used
SELECT * FROM orders WHERE status = 'pending';

Rule: Column order matters. Put the most selective column first, or the column you filter on most often.

Real impact: Cut our order history queries from 2.3s to 85ms.

---

3. Partial Index (Filtered Index)

What it is: An index on a subset of rows matching a condition.

When to use it:

• Queries that always include the same WHERE condition

• When most rows don’t match your filter

Example:

sql:

-- Only index active users
CREATE INDEX idx_active_users ON users(created_at) 
WHERE status = 'active';

-- This query is blazing fast
SELECT * FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC;

Why it’s powerful: Smaller index = faster queries, less disk space, faster writes.

Real impact: Reduced index size by 60% for our user analytics queries.

---

4. JSONB GIN Index (For JSON Columns)

What it is: Specialized index for PostgreSQL JSONB columns.

When to use it:

• Storing flexible/dynamic data in JSON

• Querying nested JSON fields

Example:

sql:

CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Fast JSON queries
SELECT * FROM users 
WHERE metadata @> '{"premium": true}';

SELECT * FROM users 
WHERE metadata->>'country' = 'US';

Real impact: Made our feature flag queries 100x faster.

Trade-off: GIN indexes are larger and slower to update than B-tree.

---

5. Covering Index (Include Columns)

What it is: An index that includes extra columns, so the database never needs to look at the table.

When to use it:

• Queries that select specific columns

• Want to avoid table lookups

Example:

sql:

-- PostgreSQL syntax
CREATE INDEX idx_orders_user_covering 
ON orders(user_id) 
INCLUDE (total_amount, created_at);

-- This query ONLY uses the index, never touches the table
SELECT total_amount, created_at 
FROM orders 
WHERE user_id = 123;

Real impact: Dashboard queries went from 800ms to 45ms.

---

How to Choose the Right Index

Here’s my decision tree:

1. Start with EXPLAIN ANALYZE - See what’s actually slow

2. Single column filter? → B-tree index

3. Multiple columns in WHERE? → Composite index

4. Always filtering on the same condition? → Partial index

5. JSON queries? → GIN index

6. Selecting specific columns repeatedly? → Covering index

Common Mistakes to Avoid

❌ Creating indexes without measuring - Always benchmark before and after

❌ Too many indexes - Each index slows down INSERT/UPDATE

❌ Wrong column order in composite indexes - Test different orders

❌ Indexing low-cardinality columns - Don’t index boolean/status fields alone

❌ Forgetting to maintain - Use REINDEX or VACUUM periodically

My Index Checklist

Before creating any index, I ask:

• Does EXPLAIN ANALYZE show a sequential scan?

• Will this query run frequently? (>100 times/day)

• Is the table large enough to matter? (>10K rows)

• Will the index be selective? (affects <20% of rows)

• Have I tested the write performance impact?

Quick Wins You Can Implement Today

1. Run EXPLAIN ANALYZE on your slowest endpoints

2. Look for “Seq Scan” in the output

3. Create a B-tree index on the filtered column

4. Measure the improvement

5. Monitor write performance

What’s Next

Next Monday, I’ll dive deep into “API Design Patterns That Scale” - the architectural decisions that saved us from rewriting our entire API.

Got questions about indexing? Hit reply and let me know what’s confusing.

—Anas Issath

P.S. The #1 mistake I see? Creating indexes before profiling. Always measure first.