Django Index Design
Use this skill to add or change indexes only after profiling shows a specific query can benefit. Indexes speed selected reads and constraints, but they add write cost, migration risk, and maintenance overhead.
Workflow
Start from a query plan.
- Capture the SQL, filters, joins, ordering, selected columns, table sizes, and current indexes.
- Confirm that the query is important enough to optimize.
Choose the index shape.
- Equality filters first, then range/order fields when useful.
- Match
ORDER BY for LIMIT queries when possible.
- Use partial indexes for highly queried subsets.
- Use covering indexes only when selected columns are stable and index-only scans are plausible.
- Use GIN for JSONB containment, arrays, and full-text patterns; do not expect a B-tree to solve those.
Express the index in Django models or migrations.
- Prefer
Meta.indexes for normal model-owned indexes.
- Prefer
AddIndexConcurrently and RemoveIndexConcurrently for live PostgreSQL tables.
- Use raw SQL only when Django cannot express the index.
Validate on production-like data.
- Re-run
QuerySet.explain() or EXPLAIN ANALYZE.
- Verify the planner uses the index for the target query.
- Check write-path impact when the indexed table is hot.
See index-patterns.md for index examples and migration templates.
Production Rules
- For PostgreSQL live tables, use concurrent index operations and set the migration
atomic = False.
- Keep names short, stable, and explicit.
- Do not add duplicate indexes that are already covered by a unique constraint or a left-prefix composite index.
- Remove unused indexes only after checking production usage and deployment rollback needs.
Verification
Finish with before/after plan evidence, the exact migration operation, and the operational risk assessment for locks, writes, and rollback.
1---2name: django-index-design3description: Design safe Django and PostgreSQL indexes from measured query plans, including B-tree, composite, covering, partial, expression, GIN/JSONB, BRIN, and concurrent migration patterns. Use when a Django query plan suggests missing or mismatched indexes, a production table needs an index without write downtime, or existing indexes need review.4license: MIT5compatibility: Codex, Claude Code, and other Agent Skills-compatible clients.6metadata:7 version: "0.1.0"8 displayName: Django Index Design9 category: Django10 tags: django,database,indexes,postgres,performance11---1213# Django Index Design1415Use this skill to add or change indexes only after profiling shows a specific query can benefit. Indexes speed selected reads and constraints, but they add write cost, migration risk, and maintenance overhead.1617## Workflow18191. Start from a query plan.20 - Capture the SQL, filters, joins, ordering, selected columns, table sizes, and current indexes.21 - Confirm that the query is important enough to optimize.22232. Choose the index shape.24 - Equality filters first, then range/order fields when useful.25 - Match `ORDER BY` for `LIMIT` queries when possible.26 - Use partial indexes for highly queried subsets.27 - Use covering indexes only when selected columns are stable and index-only scans are plausible.28 - Use GIN for JSONB containment, arrays, and full-text patterns; do not expect a B-tree to solve those.29303. Express the index in Django models or migrations.31 - Prefer `Meta.indexes` for normal model-owned indexes.32 - Prefer `AddIndexConcurrently` and `RemoveIndexConcurrently` for live PostgreSQL tables.33 - Use raw SQL only when Django cannot express the index.34354. Validate on production-like data.36 - Re-run `QuerySet.explain()` or `EXPLAIN ANALYZE`.37 - Verify the planner uses the index for the target query.38 - Check write-path impact when the indexed table is hot.3940See [index-patterns.md](references/index-patterns.md) for index examples and migration templates.4142## Production Rules4344- For PostgreSQL live tables, use concurrent index operations and set the migration `atomic = False`.45- Keep names short, stable, and explicit.46- Do not add duplicate indexes that are already covered by a unique constraint or a left-prefix composite index.47- Remove unused indexes only after checking production usage and deployment rollback needs.4849## Verification5051Finish with before/after plan evidence, the exact migration operation, and the operational risk assessment for locks, writes, and rollback.
ConcertoNotes