Database Indexing Strategies That Actually Work

My app was slow. Like, "users are complaining" slow.

I checked the logs. One query was taking 8 seconds. Eight seconds! For a simple user lookup.

I added one index. Query time dropped to 80ms.

Here's what I learned about database indexing (and the mistakes I made along the way).

The Problem: Full Table Scans

My query looked innocent:

SELECT * FROM users WHERE email = 'user@example.com';

Postgres was scanning all 50,000 rows to find one user. Every. Single. Time.

I checked with EXPLAIN:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Seq Scan on users (cost=0.00..1234.00 rows=1)

"Seq Scan" = full table scan = slow.

The Fix: Add an Index

CREATE INDEX idx_users_email ON users(email);

Now the same query:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Index Scan using idx_users_email (cost=0.29..8.31 rows=1)

Query time: 8 seconds → 80ms.

One line of SQL. Huge difference.

When to Add Indexes

Add indexes on columns you use in WHERE clauses:

-- This query needs an index on status
SELECT * FROM orders WHERE status = 'pending';

CREATE INDEX idx_orders_status ON orders(status);

Add indexes on foreign keys:

-- This query needs an index on user_id
SELECT * FROM orders WHERE user_id = 123;

CREATE INDEX idx_orders_user_id ON orders(user_id);

Add indexes on columns used in JOINs:

-- This needs indexes on both join columns
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id;

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Composite Indexes: Order Matters

I had a query that filtered by status AND date:

SELECT * FROM orders 
WHERE status = 'pending' 
AND created_at > '2025-01-01';

I added two indexes:

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Still slow. Postgres could only use one index.

Fixed it with a composite index:

CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Now it's fast. But order matters!

-- This uses the index
WHERE status = 'pending' AND created_at > '2025-01-01'

-- This uses the index
WHERE status = 'pending'

-- This does NOT use the index efficiently
WHERE created_at > '2025-01-01'

Put the most selective column first (the one that filters out the most rows).

The Index I Shouldn't Have Added

I got excited and added indexes everywhere:

CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_updated_at ON users(updated_at);
CREATE INDEX idx_users_last_login ON users(last_login);

My writes got slower. Way slower.

Every INSERT or UPDATE now had to update 4 indexes. My database was spending more time maintaining indexes than serving queries.

I checked which indexes were actually being used:

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;

Three of those indexes had idx_scan = 0. Never used.

I dropped them:

DROP INDEX idx_users_created_at;
DROP INDEX idx_users_updated_at;
DROP INDEX idx_users_last_login;

Writes got fast again.

Partial Indexes: Index Only What You Need

I had a query that only looked at active users:

SELECT * FROM users WHERE status = 'active' AND email = 'user@example.com';

I could index the whole table, but 95% of my users are active. That's a lot of wasted index space.

Used a partial index instead:

CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE status = 'active';

Smaller index, faster queries, less disk space.

JSONB Indexes: For JSON Columns

I had a JSONB column with user preferences:

SELECT * FROM users WHERE preferences->>'theme' = 'dark';

Slow. Added a GIN index:

CREATE INDEX idx_users_preferences ON users USING GIN (preferences);

Now JSONB queries are fast.

What Didn't Work

Indexing everything: Made writes slow and wasted disk space.

Using LIKE with leading wildcard: Indexes don't help with LIKE '%search%'. They only help with LIKE 'search%'.

Indexing low-cardinality columns: Don't index boolean columns or columns with only a few distinct values. Not worth it.

Monitoring Index Usage

I check index usage monthly:

-- Find unused indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

If an index hasn't been used in a month, I drop it.

The Results

Before:

• Slow queries (8 seconds)
• Users complaining
• Database CPU at 80%

After:

• Fast queries (80ms)
• Happy users
• Database CPU at 20%

Cost: $0. Just better indexes.

Should You Add Indexes?

Yes, but not everywhere.

Add indexes on:

• Columns in WHERE clauses
• Foreign keys
• Columns used in JOINs

Don't index:

• Every column "just in case"
• Low-cardinality columns
• Columns you never query

And always check if your indexes are actually being used.

---

Fighting slow queries? I'd love to hear what worked for you. Hit me up on LinkedIn.