database-optimization — Claude Code skill for database tuning
The database-optimization skill helps inspect query plans and choose indexes for PostgreSQL and MySQL. Use it when tracking down slow queries or reviewing connection and partitioning strategies.
Apache-2.0 ★ 2,858 updated 2026-05-12
npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a claude-codeWhat database-optimization does
The skill starts query tuning with EXPLAIN ANALYZE, using the plan to spot sequential scans, unnecessary sorts, and gaps between estimated and actual row counts. It then relates index choices to query patterns: B-tree for filtering or sorting, and PostgreSQL GIN for JSONB or full-text search.
It also covers N+1 detection, batched data loading, connection pools, read replicas, and table partitioning. Both PostgreSQL and MySQL are included, though some techniques are PostgreSQL-specific. Index choice still depends on the query and table structure.
A checklist flags patterns such as deep pagination with OFFSET, ORDER BY RANDOM(), and updates or deletes without WHERE. Replica reads need care: a read immediately after a write should go to the primary when consistency matters, since a replica may lag.
What it helps with
- trace a slow query to its execution plan
- choose an index for filtering, ordering, or search
- spot N+1 queries in a request handler
- route reads and writes between a database and replicas
- select a partitioning strategy for a large table
Example prompts for the agent
- Inspect this query's
EXPLAIN ANALYZEplan and suggest an index to test. - Find N+1 queries in this handler and show how to batch the related data load.
- Review these PostgreSQL queries for risky patterns and replace the deep
OFFSETpagination.
How to install database-optimization
1Skills CLI (Vercel)
npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a claude-code2Manually: put the files in place
~/.claude/skills/database-optimization/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/database-optimization/
1Skills CLI (Vercel)
npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a codex2Manually: put the files in place
.agents/skills/database-optimization/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a cursor2Manually: put the files in place
.cursor/skills/database-optimization/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install rohitg00/awesome-claude-code-toolkit skills/database-optimization2Manually: put the files in place
.github/skills/database-optimization/SKILL.md1Skills CLI (Vercel)
npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a gemini-cli2Platform CLI
gemini skills install https://github.com/rohitg00/awesome-claude-code-toolkit --path skills/database-optimizationManually: put the files in place
.devin/skills/database-optimization/SKILL.mdThe legacy .windsurf/skills/ path still works
Claude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about database-optimization
How do I install database-optimization in Claude Code?
Run npx skills add rohitg00/awesome-claude-code-toolkit --skill database-optimization -a claude-code. The skill file is at ~/.claude/skills/database-optimization/SKILL.md.
What does the database-optimization skill do?
It helps inspect query plans, select indexes, and find issues such as N+1 queries. It also provides guidance on connection pools, replicas, and partitioning.
Which databases does database-optimization cover?
It covers PostgreSQL and MySQL. Some index types and options, including GIN and partial indexes, are specific to PostgreSQL.
Source text
SKILL.md · Apache-2.0
---
name: database-optimization
description: Query optimization, indexing strategies, and database performance tuning for PostgreSQL and MySQL
---
# 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()
```
```javascript
// BAD: N+1
const users = await User.findAll();
for (const user of users) {
const orders = await Order.findAll({ where: { userId: user.id } });
}
// GOOD: batch load
const users = await User.findAll({ include: [Order] });
```
Detection: enable query logging, count queries per request. More than 10 queries for a single endpoint is a red flag.
## Connection Pooling
```
Rule of thumb: pool_size = (core_count * 2) + disk_count
Typical web app: 10-20 connections per app instance
```
PostgreSQL:
- Use PgBouncer in transaction mode for serverless/high-connection scenarios
- Set `idle_in_transaction_session_timeout = '30s'`
- Monitor with `pg_stat_activity`
MySQL:
- Set `max_connections` based on available RAM (each connection uses ~10MB)
- Use ProxySQL for connection multiplexing
- Monitor with `SHOW PROCESSLIST`
## Read Replicas
- Route all `SELECT` queries to replicas
- Route all writes to primary
- Account for replication lag (typically 10-100ms)
- Never read-after-write from a replica; use primary for consistency-critical reads
- Use connection-level routing, not query-level
```python
# SQLAlchemy read replica routing
class RoutingSession(Session):
def get_bind(self, mapper=None, clause=None):
if self._flushing or self.is_modified():
return engines["primary"]
return engines["replica"]
```
## Partition Strategies
### Range Partitioning (time-series data)
```sql
-- PostgreSQL
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
data jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2025_q1 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
```
### Hash Partitioning (even distribution)
```sql
CREATE TABLE sessions (
id uuid PRIMARY KEY,
user_id bigint NOT NULL
) PARTITION BY HASH (user_id);
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
```
Partition when tables exceed 50-100GB or when you need to drop old data quickly.
## Query Optimization Checklist
1. Run `EXPLAIN ANALYZE` and read the plan
2. Check for sequential scans on tables with >10K rows
3. Verify index usage (check `idx_scan` in `pg_stat_user_indexes`)
4. Look for implicit type casts that prevent index use
5. Replace `SELECT *` with specific columns
6. Add `LIMIT` to queries that only need a subset
7. Use `EXISTS` instead of `COUNT(*) > 0`
8. Batch `INSERT`/`UPDATE` operations (500-1000 rows per batch)
9. Avoid functions on indexed columns in `WHERE` clauses
10. Monitor slow query log (pg: `log_min_duration_statement = 100`)
## Dangerous Patterns
- `LIKE '%term%'` on unindexed columns (use full-text search instead)
- `ORDER BY RANDOM()` (use `TABLESAMPLE` or application-level randomization)
- `SELECT DISTINCT` masking a join problem
- Missing `WHERE` on `UPDATE`/`DELETE` (always verify with `SELECT` first)
- Long-running transactions holding locks
- Using `OFFSET` for deep pagination (use keyset/cursor pagination instead)
© rohitg00 · Apache-2.0 · rohitg00/awesome-claude-code-toolkit