Database Query Optimization

---
name: database-query-optimization
description: When addressing slow application endpoints, high database CPU usage, or standardizing data access patterns.
version: 2.0.0
category: backend
tags: [backend, database, performance, sql, optimization]
skill_type: workflow
author: skiLLM
license: MIT
compatible_agents: [claude-code, cursor, copilot, codex]
estimated_context_tokens: 2000
dangerous: true
requires_review: true
security_level: review-required
dependencies: []
triggers: [slow query, n+1, database performance, sql optimization]
permissions:
  filesystem: { read: true, write: true }
  network: { outbound: true }
  shell: { execute: true }
input_requirements: [slow query, orm code, database schema]
output_contract: [no select *, no n+1 queries, indexes on where/join columns]
failure_conditions: [database unavailable, lack of query profiling data, migration cannot be rolled back]
last_updated: 2026-05-15
---

# Database Query Optimization

## Purpose
Slow databases kill applications. This skill replaces guesswork with systematic performance analysis, using EXPLAIN plans and profiling to eliminate N+1 queries, eliminate unnecessary scans, and add targeted indexes so the database bears the computational load, not the application.

## When to use
- Writing complex SQL queries or ORM access functions
- Resolving performance bottlenecks on read-heavy endpoints
- Designing schema migrations for growing datasets
- Refactoring loops that make repeated database calls

## When NOT to use
- Schema design (different concern)
- Database selection (architectural decision)
- Caching strategies (use Caching Strategies skill)
- Application-level performance (profilers, algorithms)

## Inputs required
- Slow query logs or endpoint metrics
- ORM code accessing the database
- Database schema (tables, columns, indexes)
- EXPLAIN ANALYZE capability (test environment)

## Workflow
1. **Profile the Bottleneck**: Run EXPLAIN ANALYZE on slow queries to identify sequential scans and high-cost operations
2. **Identify N+1**: Locate loops making repetitive database calls for the same entity type
3. **Measure Selectivity**: Analyze WHERE clause filters and add indexes to highly selective columns
4. **Replace Loops**: Replace N+1 with single IN queries or ORM eager-loading (JOINs)
5. **Remove Over-Fetching**: Replace SELECT * with explicit column names
6. **Add Indexes**: Add B-Tree indexes to WHERE, JOIN, and ORDER BY columns in slow queries
7. **Paginate**: Enforce LIMIT and OFFSET (or cursor pagination) on all collection queries
8. **Verify Performance**: Re-run EXPLAIN ANALYZE and benchmark end-to-end latency

## Rules
- MUST explicitly define selected columns (NEVER use SELECT * in production)
- MUST NEVER have database operations inside loops
- MUST perform filtering and aggregation in the database, not application memory
- MUST enforce LIMIT and OFFSET on collection queries
- MUST EXPLAIN before adding indexes (verify they reduce cost)
- MUST rollback indexes if they degrade INSERT/UPDATE performance
- MUST NOT over-index (each index has maintenance cost)

## Anti-patterns
- **The N+1 Problem**: Fetching 50 users, then making 50 individual queries to fetch each user's profile
- **Over-Indexing**: Adding an index to every single column (degrades INSERT/UPDATE performance)
- **Application-Side Filtering**: Fetching 10,000 rows from DB and using `filter()` in JavaScript
- **SELECT * in Production**: Fetching all columns including BLOBs when needing only `id` and `name`
- **Unbounded Queries**: Collection endpoints without LIMIT/OFFSET returning millions of rows
- **Ignoring Selectivity**: Adding indexes to low-cardinality columns (gender, status)

## Failure conditions
- Database unavailable for profiling
- No query metrics/logs available
- EXPLAIN ANALYZE not supported by database
- Migration lacks rollback strategy
- Index changes cause lock timeouts on large tables

## Validation checklist
- [ ] EXPLAIN ANALYZE shows acceptable query cost (< 1000 for simple queries)
- [ ] No sequential scans on large tables (use indexes)
- [ ] SELECT explicitly lists columns (no SELECT *)
- [ ] No loops making repeated database calls
- [ ] N+1 problems replaced with JOIN or ORM eager-load
- [ ] WHERE/JOIN/ORDER BY columns are indexed
- [ ] Collection queries enforce LIMIT and OFFSET
- [ ] No unused indexes (maintenance burden)
- [ ] Low-cardinality columns (gender, status) are NOT indexed
- [ ] Benchmark shows latency improvement (confirm end-to-end)

## Output format
- **SQL format**: ANSI standard, explicitly selecting columns, using parameterized queries
- **ORM calls**: Using eager-load or relationship methods (not loops)
- **Schema changes**: Migration files with forward and rollback steps
- **Indexes**: B-Tree on high-selectivity columns, documented in schema
- **Validation**: EXPLAIN ANALYZE output showing acceptable costs

## Security considerations
- All user input MUST be parameterized (prevent SQL injection)
- Query results MUST be accessible by user (respect permissions)
- Large result sets MUST be paginated (prevent DOS)
- Timing attacks: ensure query timing doesn't leak existence of data

## Agent execution notes
- Agent MAY: Add indexes, create ORM relationships, replace loops with joins, add LIMIT/OFFSET
- Agent MUST NEVER: Use SELECT *, create loops with queries, add untested indexes, bypass pagination
- Agent MUST ASK: Before dropping existing indexes, before major query rewrites, before schema changes
- Agent MUST VALIDATE: EXPLAIN ANALYZE shows improvement, no N+1 remaining, pagination enforced

## Example

**❌ Anti-pattern (N+1, SELECT *, no pagination, missing indexes):**
```javascript
// N+1 problem: 1 query for users + 50 queries for posts
const users = await User.findAll(); // SELECT * (over-fetching)
for (const user of users) {
  const posts = await Post.find({ userId: user.id }); // 50 individual queries
  console.log(user, posts);
}

// Application-side filtering
const allUsers = await User.find({});
const filtered = allUsers.filter(u => u.status === 'active');
```

**✅ Correct pattern (Join, eager-load, explicit columns, paginated):**
```javascript
// Single optimized query with JOIN and explicit columns
const usersWithPosts = await User.findAll({
  attributes: ['id', 'username', 'email'],
  include: [{
    model: Post,
    attributes: ['id', 'title', 'createdAt'],
    required: true
  }],
  limit: 20,
  offset: 0,
  order: [['createdAt', 'DESC']]
});

// Or raw SQL with pagination
const query = `
  SELECT u.id, u.username, p.id, p.title
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  WHERE u.status = $1
  ORDER BY u.created_at DESC
  LIMIT $2 OFFSET $3
`;
const result = await db.query(query, ['active', 20, 0]);

// Indexes created
// CREATE INDEX idx_posts_user_id ON posts(user_id);
// CREATE INDEX idx_users_status ON users(status) WHERE status = 'active';
```