database-performance-audit

name	database-performance-audit
description	Use when Codex needs to audit Django/Postgres database performance, schema, indexes, migrations, ORM query patterns, full-scan risk, constraints, relations, or transaction/locking risks.

Database Performance Audit

Goal

Evaluate database schema and query performance risks from Django models, migrations, indexes, constraints, relations, and ORM usage. Recommend safe database improvements with evidence.

When to use

Use this skill when the user asks for database performance, query/schema/index audit, full scan risk, relation design, migration safety, constraints, N+1 query risk, transaction or locking risk.

Required steps

1. Inspect database source of truth:
   • Django models in each backend app
   • migrations, especially recent and large-table migrations
   • API/admin views that query those models
   • tests that define expected data behavior
2. Build a table/model map:
   • model, app, key fields, relations, indexes, constraints, ordering
   • high-growth tables and write-heavy tables
3. Identify index candidates:
   • fields used in filter, exclude, order_by, get, joins, foreign keys, uniqueness checks, and dashboards
   • composite indexes for common multi-column filters
   • partial indexes for frequent conditional subsets
4. Identify full-scan and fan-out risks:
   • unbounded list queries
   • aggregations over large tables
   • icontains/search without search index strategy
   • loops that query related rows per item
   • ordering by non-indexed columns on large tables
5. Evaluate schema integrity:
   • missing uniqueness/constraints for business invariants
   • nullable fields that complicate queries
   • cascade behavior and data retention risks
   • migration operations that may lock large tables
6. Optional local-only evidence:
   • use sqlmigrate or read migrations for SQL shape
   • use EXPLAIN only on local/dev data and never print sensitive rows
7. Classify all issues P0/P1/P2/P3.
8. Write the report in Vietnamese to docs/audits/database-performance-audit.md.

Output format

Write a Vietnamese Markdown report with:

1. Schema inventory
2. Bang/model tang truong cao
3. Index hien co va index can them
4. Query/full-scan risks
5. Relation/constraint/transaction risks
6. Migration safety risks
7. Phat hien P0/P1/P2/P3
8. De xuat verification voi EXPLAIN/test

Each index recommendation must name model/table, field(s), query evidence, expected benefit, and write-cost tradeoff.

Quality checklist

• Do not recommend indexes without a query path that uses them.
• Mention write amplification and migration risk for each index.
• Separate ORM N+1 risks from schema/index risks.
• Identify missing constraints only when tied to a business invariant.
• State whether findings are static or measured.

Safety rules

• Do not run migrate, destructive SQL, data cleanup, backfills, DROP, TRUNCATE, or production EXPLAIN ANALYZE.
• Do not print secrets or sensitive data.
• Use local/dev database only when dynamic inspection is needed.
• Do not change migrations or models unless the user explicitly asks for implementation.