qeberodev logo
Home Articles About
December 18, 2025

Database Indexing: The Hidden Performance Multiplier

How to design indexes that transform slow queries into lightning-fast lookups.

engineering database performance sql

Introduction

A production database without proper indexes is like a library without a card catalog. Users sit at shelves scanning every book to find the one they need. Query times explode. CPU maxes out. Your app grinds to a halt.

Indexes transform database performance. A well-placed index can take a query from 10 seconds to 10 milliseconds. But bad indexes waste storage, slow down writes, and confuse query planners.

This article covers index design patterns that balance read performance with write efficiency.

B-Tree Indexes: The Default Choice

Most databases use B-Tree indexes by default. They maintain sorted order and enable efficient range queries.

-- Create a basic index
CREATE INDEX idx_users_email ON users(email);

-- Query planner uses index
SELECT * FROM users WHERE email = 'alice@example.com'; -- Fast!

B-Tree indexes excel at:

Composite Indexes: The Performance Hack

A single column index helps one query. Composite indexes help multiple queries.

-- Bad: Three separate indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Good: One composite index (order matters!)
CREATE INDEX idx_orders_lookup ON orders(user_id, status, created_at);

The order of columns in a composite index matters. Use the ESR rule:

  1. Equality columns first (WHERE user_id = ?)
  2. Sort columns second (ORDER BY created_at)
  3. Range columns last (WHERE status IN (…))
-- Efficient queries for idx_orders_lookup(user_id, status, created_at)
SELECT * FROM orders 
WHERE user_id = 5 AND status = 'completed' 
ORDER BY created_at DESC;

-- Good: User_id filters, status filters, created_at sorts
-- Index satisfies all three conditions in one lookup!

-- Bad query structure (still works, less efficient)
SELECT * FROM orders 
WHERE created_at > '2024-01-01' 
ORDER BY user_id;
-- Index can't help with range on first column

Partial Indexes: Indexing What Matters

You don’t always need to index everything. Partial indexes index only matching rows.

-- Bad: Index all orders (most are archived)
CREATE INDEX idx_orders_status ON orders(status);

-- Good: Index only active orders
CREATE INDEX idx_orders_active ON orders(status) 
WHERE status IN ('pending', 'processing', 'shipped');

This index is smaller (faster), uses less storage, and updates are faster (fewer rows to maintain).

Use cases:

-- Only index non-deleted rows (soft deletes)
CREATE INDEX idx_users_active ON users(email) 
WHERE deleted_at IS NULL;

-- Only index recent orders (archive old ones)
CREATE INDEX idx_orders_recent ON orders(user_id) 
WHERE created_at > NOW() - INTERVAL '1 year';

COVERING Indexes: The Ultimate Query Speed

A covering index includes all columns needed for a query. The database never touches the main table.

-- Query needs: user_id, status, amount, created_at
SELECT user_id, status, amount, created_at 
FROM orders 
WHERE user_id = 5;

-- Regular index: finds matching row, fetches from main table (2 steps)
CREATE INDEX idx_orders_user ON orders(user_id);

-- Covering index: has all data in index, no table access (1 step)
CREATE INDEX idx_orders_user_covering ON orders(user_id) 
INCLUDE (status, amount, created_at);

Covering indexes eliminate table lookups for the covered columns. The database reads only the index.

Trade-off: Larger index, slower writes. Use only for frequently accessed queries.

Index Design: Real-World Example

Let’s index a production e-commerce database:

-- Users table
CREATE INDEX idx_users_email ON users(email);
-- Query: SELECT * FROM users WHERE email = ?

CREATE INDEX idx_users_signup ON users(created_at DESC) 
WHERE deleted_at IS NULL;
-- Query: Show recent signups (admin dashboard)

-- Orders table (high volume!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at) 
WHERE cancelled_at IS NULL;
-- Query: SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'

CREATE INDEX idx_orders_by_date ON orders(created_at) 
INCLUDE (user_id, total_amount);
-- Query: Analytics: sum(total_amount) grouped by day

-- Products table
CREATE INDEX idx_products_sku ON products(sku) UNIQUE;
-- SKU must be unique for lookup

CREATE INDEX idx_products_search ON products(category, price, popularity);
-- Query: Find products WHERE category = 'books' ORDER BY popularity

-- Review table (time-series)
CREATE INDEX idx_reviews_recent ON reviews(product_id, created_at DESC) 
WHERE verified = true;
-- Query: Show verified reviews for product, sorted by newest

Index Anti-Patterns to Avoid

Anti-Pattern 1: Indexing Low-Cardinality Columns

-- BAD: Only 2 values (true/false)
CREATE INDEX idx_users_active ON users(is_active);
-- Table scan might be faster than index lookup!

-- GOOD: Partial index on common value
CREATE INDEX idx_users_inactive ON users(id) 
WHERE is_active = false;
-- Only 10% of table needs indexing

Anti-Pattern 2: Too Many Indexes

-- BAD: One index per column
CREATE INDEX idx_users_id ON users(id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(name);
-- Each insert/update pays cost of maintaining all indexes
-- Query planner gets confused choosing between them

-- GOOD: Fewer, smarter indexes
CREATE INDEX idx_users_email ON users(email);
-- Name search can use full-text index if needed
CREATE INDEX idx_users_name_ft ON users USING GIN (to_tsvector('english', name));

Anti-Pattern 3: Unused Indexes

-- Identify unused indexes in PostgreSQL
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY idx_blks_read DESC;

-- Drop unused indexes
DROP INDEX idx_orders_unused;

Monitoring Index Health

Track index performance to catch regressions:

-- PostgreSQL: Find slow queries
SELECT query, calls, mean_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- Find missing indexes (suggested by query planner)
SELECT schemaname, tablename, attname 
FROM pg_stat_user_tables 
WHERE seq_scan > 1000 AND idx_scan = 0;
-- High sequential scans = missing index?

-- Monitor index size
SELECT schemaname, tablename, indexname, 
       pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Set up alerts:

Index Maintenance

Indexes degrade over time. Maintain them:

-- PostgreSQL: Rebuild fragmented indexes
REINDEX INDEX idx_orders_user;

-- Or rebuild entire table's indexes
REINDEX TABLE orders;

-- Analyze table statistics (helps query planner)
ANALYZE orders;

-- Vacuum removes dead rows (after deletes/updates)
VACUUM orders;

Schedule maintenance during low-traffic windows:

-- Daily maintenance job
0 2 * * * reindexdb --all && vacuumdb --all

Index Checklists

Before Indexing:

After Indexing:

Quarterly Maintenance:

Conclusion

Indexes are the difference between production systems and slow systems. B-Tree indexes for equality and range. Composite indexes for multiple predicates. Partial indexes to reduce size. Covering indexes to eliminate table lookups.

Design indexes based on actual query patterns, not guesses. Monitor performance. Maintain regularly.

Index discipline pays dividends in user experience and infrastructure costs.

Comments Arriving Soon 🚀