Indexing Strategies for Read-Heavy Apps

# Indexing Strategies for Read-Heavy Apps

Proper indexing can make your app 100x faster.

## When to Index

- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
- Columns in GROUP BY

## Index Types

### Single Column

```sql
CREATE INDEX idx_users_email ON users(email);
```

### Composite

```sql
CREATE INDEX idx_posts_author_status ON posts(user_id, status);
```

### Covering

Include all queried columns:

```sql
CREATE INDEX idx_posts_covering ON posts(user_id, status, published_at);
```

## Common Mistakes

1. **Too many indexes** — slow writes
2. **Wrong order** — composite indexes need proper column order
3. **Unused indexes** — monitor and remove

## Monitoring

```sql
-- Find unused indexes (PostgreSQL)
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
```

## Conclusion

Strategic indexing is essential for read-heavy applications. Measure, don't guess!