# Database Optimization

> Practical rules for query tuning, schema choices, and safe database performance work.

- Skill: `abivan-tech/database-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add abivan-tech/database-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/abivan-tech/database-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: See repository LICENSE
- Author: ABIvan-Tech (https://skillmd.com/u/abivan-tech)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/abivan-tech/database-optimization

---


# Database Optimization

Use this skill when improving query performance, schema layout, indexing, or database-heavy code paths.

## Priorities

1. **Correctness**
2. **Measured bottlenecks**
3. **Query and index simplicity**
4. **Write/read tradeoff awareness**
5. **Operational safety**

## Core Rules

### 1. Measure before tuning

- Start with the real slow query, workload, or access pattern.
- Use the database's plan/explain tooling before changing indexes or schema.
- Do not optimize based on intuition alone.

### 2. Fix query shape before adding indexes

- Remove unnecessary joins, scans, sorting, or selected columns first.
- Prefer predicates and access paths the optimizer can use well.
- Avoid `SELECT *` in hot paths unless the query genuinely needs the full row.

### 3. Index for actual access patterns

- Add indexes for repeated filters, joins, sorting, or uniqueness constraints.
- Composite index order should match the real query pattern.
- Every index has write and storage cost; do not index everything.

### 4. Keep schema changes conservative

- Prefer narrow schema or index changes tied to a measured problem.
- Avoid denormalization unless read-path gains justify the extra write and consistency cost.
- If changing cardinality or ownership boundaries, think through migrations and rollback first.

### 5. Optimize application behavior too

- Eliminate N+1 access patterns.
- Batch reads or writes where semantics allow it.
- Cache only after correctness and invalidation strategy are understood.

## Query Heuristics

Look for:

- full scans on large tables
- repeated per-row subqueries
- sorts that could be supported by an index
- predicates that prevent index use
- unnecessary wide row fetches
- queries that join far more than the caller needs

## Index Heuristics

Add or adjust indexes when:

- a query is hot and selective enough to benefit
- a join key is repeatedly used
- a uniqueness rule should be enforced at the database layer
- a filtered/partial index can shrink work meaningfully

Avoid indexes when:

- the table is tiny
- the predicate is rarely used
- the column has poor selectivity and the workload is write-heavy

## Schema and Data Rules

- Keep data types aligned with actual semantics.
- Store derived data only when the recomputation or query cost is proven to matter.
- Use constraints to protect invariants whenever the database can enforce them.
- Partition only when scale, retention, or operational boundaries justify the complexity.

## Transaction Rules

- Keep transactions as short as practical.
- Be explicit about isolation expectations in correctness-sensitive flows.
- Avoid holding locks while doing non-database work.
- Tune retry logic for contention and deadlocks deliberately, not blindly.

## Review Heuristics

Look for:

- missing indexes on repeated hot paths
- over-indexed write-heavy tables
- app-level N+1 patterns
- expensive ORM defaults
- schema changes with unclear migration risk
- caching without invalidation clarity
- optimization work without measurements

## Anti-Patterns

Avoid:

- adding indexes before inspecting the actual query plan
- denormalizing just to avoid learning the real bottleneck
- premature partitioning
- relying on ORM convenience while ignoring generated SQL
- "fixing" slow queries by adding hardware assumptions to the code path

## Quick Checklist

- [ ] Slow path is measured, not guessed
- [ ] Query shape is reviewed before index changes
- [ ] Index cost vs benefit is understood
- [ ] N+1 and batching concerns are checked
- [ ] Constraints protect important invariants
- [ ] Transaction boundaries are deliberate
- [ ] Migration and rollback risk is understood