# Database Optimization

Query optimization, indexing strategies, and database performance tuning for PostgreSQL...

- Repository: https://github.com/rohitg00/awesome-claude-code-toolkit (2800 stars)
- Category: Data and ML
- License: Apache-2.0
- Last updated: 2026-05-12
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -g -a claude-code -y`
Codex: `npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -g -a codex -y`
Cursor: `npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -g -a cursor -y`
Gemini CLI: `npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add rohitg00/awesome-claude-code-toolkit`

## Description

Query optimization, indexing strategies, and database performance tuning for PostgreSQL and MySQL

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# Database Optimization

## EXPLAIN Analysis

Always run `EXPLAIN ANALYZE` before optimizing. Read the output bottom-up.

```sql
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- MySQL
EXPLAIN ANALYZE SELECT ...;
```

Key metrics to watch:
- **Seq Scan** on large tables = missing index
- **Nested Loop** with high row count = consider hash/merge join
- **Sort** without index = add index on sort column
- **Rows estimated vs actual** divergence = stale statistics, run `ANALYZE`

## Index Strategies

### B-tree (default, most cases)
```sql
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);
```
Use for: equality, range queries, sorting. Column order matters in composite indexes: put equality columns first, then range/sort columns.

### Partial Index (PostgreSQL)
```sql
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';
```
Use when queries always filter on a specific condition. Dramatically smaller than full indexes.

### GIN (PostgreSQL - arrays, JSONB, full-text)
```sql
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_docs_search ON documents USING GIN (to_tsvector('english', content));
```

### GiST (PostgreSQL - spatial, range types)
```sql
CREATE INDEX idx_locations_point ON locations USING GiST (coordinates);
CREATE INDEX idx_events_period ON events USING GiST (tsrange(start_at, end_at));
```

### Covering Index (index-only scans)
```sql
-- PostgreSQL
CREATE INDEX idx_users_email_name ON users (email) INCLUDE (name);

-- MySQL
CREATE INDEX idx_users_email_name ON users (email, name);
```

## N+1 Query Detection

Symptom: 1 query to fetch parent + N queries for each child.

```python
# BAD: N+1
users = db.query(User).all()
for user in users:
    print(user.orders)  # triggers query per user

# GOOD: eager load
users = db.query(User).options(joinedload(User.orders)).all()
```

The rest: https://github.com/rohitg00/awesome-claude-code-toolkit/blob/HEAD/skills/database-optimization/SKILL.md