Database Indexing for High-Traffic Apps: When Good Indexes Go Bad

You’ve added indexes to your database. Your queries are faster. Everything seems great.

Then your app hits 100,000 concurrent users, and suddenly everything falls apart.

Welcome to Part 3 of this series, where we talk about what nobody tells you: indexes can hurt you just as much as they help you.

The Problem Most Developers Don’t See Coming

Here’s what happens in the real world:

Your startup launches. Traffic grows. You add indexes every time something feels slow. Within six months, you’ve got 15+ indexes on a single table.

Then writes start taking forever. INSERT and UPDATE operations that used to take milliseconds now take seconds. Your database CPU spikes during peak hours. Users complain about lag when submitting forms or checking out.

What went wrong?

You over-indexed.

Every index speeds up reads but slows down writes. When you insert a new row, the database doesn’t just write to the table — it updates every single index on that table. The more indexes you have, the heavier that write penalty becomes.

This is the tradeoff nobody talks about when they say “just add an index.”

Understanding Read-Heavy vs Write-Heavy Workloads

Not all applications are created equal.

Read-heavy apps (analytics dashboards, reporting tools, search engines):

• Users run complex queries constantly
• Data doesn’t change much
• More indexes = better performance
• Examples: Google Analytics, Stripe’s dashboard, business intelligence tools

Write-heavy apps (social media feeds, messaging apps, real-time collaboration):

• Users create, update, and delete data constantly
• Reads are usually simple lookups
• Too many indexes = performance disaster
• Examples: Twitter’s feed, Slack messages, collaborative docs

Balanced workloads (most SaaS apps, e-commerce):

• Mix of reads and writes
• Need strategic indexing
• Examples: Shopify stores, project management tools, CRM systems

The key question: what does your app do more — read or write?

Strategy 1: Partial Indexes for Active Data

Imagine you have a orders table with 50 million rows. But only 2% are active orders — the rest are completed, canceled, or archived.

Do you really need to index all 50 million rows?

No. Use a partial index:

CREATE INDEX idx_active_orders 
ON orders(customer_id, created_at) 
WHERE status = 'active';

This index only covers active orders. It’s smaller, faster, and cheaper to maintain. Perfect for queries like:

SELECT * FROM orders 
WHERE customer_id = 12345 
AND status = 'active' 
ORDER BY created_at DESC;

When to use partial indexes:

• You frequently query a specific subset of data (active records, recent data, premium users)
• The subset is significantly smaller than the full table
• You want to reduce index size and write overhead

Real-world example: Stripe uses partial indexes extensively. They don’t index every transaction ever — they index recent transactions and active subscriptions. Older data lives in slower, cheaper storage.

Strategy 2: Covering Indexes to Eliminate Table Lookups

Here’s a query most developers run thousands of times per day:

SELECT user_id, email, last_login 
FROM users 
WHERE status = 'active' 
ORDER BY last_login DESC 
LIMIT 50;

Even with an index on status, the database still has to:

1. Find matching rows in the index
2. Go back to the main table to fetch email and last_login
3. Sort the results
4. Return the top 50

That second step — going back to the table — is called an index lookup. It’s slow.

Solution: create a covering index that includes all columns in your SELECT:

CREATE INDEX idx_active_users_covering 
ON users(status, last_login DESC, user_id, email);

Now the database can answer the entire query using just the index. No table lookup needed. This is called an index-only scan, and it’s dramatically faster.

Warning: Covering indexes are wider (they store more data), so they take up more space and slow down writes. Only use them for your most critical queries.

Strategy 3: Index Maintenance for Production Systems

Indexes don’t maintain themselves. Over time they get bloated, fragmented, and slow.

Here’s what happens:

• You delete 40% of your user records
• The index still reserves space for those deleted rows
• Queries get slower even though you have less data
• Index scans waste time on “dead” entries

Solution: Rebuild or reorganize indexes regularly.

For PostgreSQL:

-- Check index bloat
SELECT 
    schemaname, 
    tablename, 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild bloated index
REINDEX INDEX idx_users_email;

-- Or rebuild all indexes on a table
REINDEX TABLE users;

For MySQL:

-- Optimize table (rebuilds indexes)
OPTIMIZE TABLE users;

-- Check index statistics
SHOW INDEX FROM users;

When to rebuild:

• After bulk deletes or updates (more than 20% of table)
• Monthly for high-traffic tables
• When query performance degrades without obvious cause

Pro tip: Schedule index maintenance during low-traffic windows (3–5 AM in your primary timezone). Use database replicas to avoid downtime on production.

Strategy 4: Monitor Index Usage

You might have indexes that nobody uses. They’re just sitting there, slowing down every write operation for zero benefit.

Find unused indexes in PostgreSQL:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexrelid) DESC;

Find unused indexes in MySQL:

SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;

If an index hasn’t been scanned in 30+ days, consider dropping it. Your writes will thank you.

Strategy 5: Separate Indexes for Analytical Queries

Your app has two types of queries:

1. OLTP (Online Transaction Processing): User-facing queries that need to be instant
2. OLAP (Online Analytical Processing): Business reports and analytics that can be slower

Don’t let analytical queries slow down your production database.

Better approach: Use read replicas.

Set up a read-only replica of your database specifically for analytics and reporting. Add heavy indexes there without impacting production writes.

Architecture:

• Primary database: minimal indexes, optimized for writes
• Read replica: aggressive indexing, optimized for complex analytical queries
• Lag: typically 1–5 seconds behind primary (acceptable for analytics)

This is how companies like Netflix and Airbnb handle massive scale — they don’t run reports on production.

Strategy 6: Index Hints When the Query Planner Gets It Wrong

Sometimes the database makes bad decisions about which index to use. This happens more often than you’d think, especially with complex queries.

MySQL example:

-- Database chooses wrong index
SELECT * FROM orders 
WHERE customer_id = 12345 
AND status = 'active';

-- Force it to use the right one
SELECT * FROM orders USE INDEX (idx_customer_status)
WHERE customer_id = 12345 
AND status = 'active';

PostgreSQL doesn’t support index hints directly, but you can:

• Disable sequential scans temporarily: SET enable_seqscan = OFF;
• Adjust query planner settings: SET random_page_cost = 1.1;

Use hints sparingly. If you’re constantly forcing index usage, it means:

• Your statistics are outdated (run ANALYZE)
• Your indexes are poorly designed
• Your query needs rewriting

Real-World Case Study: Shopify’s Index Strategy

Shopify processes millions of transactions daily. Here’s how they handle database indexing at scale:

1. Composite indexes for common query patterns: Every merchant dashboard query uses the same filters (shop_id, status, created_at), so they have one covering index for all three.
2. Partial indexes on time-ranges: They only index recent orders (last 90 days) for merchant dashboards. Older orders require a separate query that’s allowed to be slower.
3. Separate databases for analytics: Their data warehouse has 50+ indexes. Production has fewer than 10 per table.
4. Aggressive monitoring: They track index hit rates and automatically alert when a query does a full table scan.
5. Regular index rebuilds: Automated weekly maintenance during low-traffic windows.

Result: They handle Black Friday traffic (1,000+ orders per second) without breaking a sweat.

Common Mistakes That Kill Performance

Mistake 1: Indexing low-cardinality columns

Don’t index columns with only a few distinct values:

-- Bad: only two values (true/false)
CREATE INDEX idx_is_active ON users(is_active);

-- Better: use partial index if needed
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Mistake 2: Forgetting column order in composite indexes

Column order matters:

-- This index works for queries filtering by last_name or last_name + first_name
CREATE INDEX idx_name ON users(last_name, first_name);

-- But NOT for queries filtering only by first_name
SELECT * FROM users WHERE first_name = 'John'; -- won't use the index

Rule: Put the most selective (filtering) columns first, then the ones you sort by.

Mistake 3: Indexing before you have data

Premature optimization strikes again. Don’t add indexes until:

• You have real production data (at least 10,000+ rows)
• You’ve identified slow queries with EXPLAIN
• You’ve confirmed the index actually helps

Adding indexes “just in case” wastes space and slows down writes.

The Checklist for Production-Ready Indexing

Before you deploy, ask yourself:

Read-heavy table?

• Add covering indexes for your top 5 queries
• Use partial indexes for subsets of data
• Monitor cache hit rates

Write-heavy table?

• Keep indexes minimal (3–5 max)
• Use partial indexes to reduce size
• Consider delaying index updates with asynchronous processing

Balanced workload?

• Index foreign keys and frequently filtered columns
• Drop unused indexes monthly
• Use read replicas for reports

Any table:

• Run EXPLAIN on your slowest queries
• Monitor index bloat weekly
• Schedule maintenance during low-traffic hours
• Track query performance over time

The Bottom Line

Indexes aren’t fire-and-forget. They need strategy, monitoring, and maintenance.

Good indexing:

• Speeds up reads without destroying writes
• Covers your most common queries
• Gets maintained and monitored
• Adjusts as your app grows

Bad indexing:

• Over-indexes everything “just in case”
• Ignores write performance
• Never gets cleaned up
• Doesn’t account for changing traffic patterns

The apps that scale aren’t the ones with the most indexes — they’re the ones with the right indexes in the right places.

Now go audit your database. You probably have indexes that need attention.