| name | db-efficiency-audit |
| description | Audit database workloads for energy-wasting query and schema patterns — full table scans, missing indexes, SELECT * over-fetching, N+1 query storms, chatty ORMs, oversized instances, missing connection pooling. Use this skill whenever the user shares slow-query logs, EXPLAIN plans, ORM code, schema DDL, or asks why the database is slow/expensive/hot, or wants a database efficiency or sustainability review. Part of Lean Agentic AI Skills; emits lean-findings.json. |
DB Efficiency Audit
Producer skill. Input: slow-query logs, EXPLAIN/EXPLAIN ANALYZE output, schema, application data-access code. Output: lean-findings.json.
Databases convert bad access patterns into heat with perfect reliability, on every request, forever. This is E-reduction at the hottest point of most stacks.
Subject type: emit subject.type: "database" in findings.
Signatures
- Full scans on hot paths — Seq Scan / type=ALL in plans for frequent queries. High. Fix: the specific index (name the columns), or query rewrite.
- N+1 storms — loops issuing per-row queries (visible in ORM code or repeated-shape log entries). High. Fix: eager loading / JOIN / batched IN.
- SELECT * over wire for wide tables when few columns are used. Medium. Reduces E and network.
- Missing pagination — unbounded result sets to app or API. Medium-high.
- Chatty transactions — many round trips where one statement/procedure would do. Medium.
- No connection pooling — connection churn burning CPU on handshakes. Medium.
- Index bloat — many near-duplicate or unused indexes (from stats if provided) taxing every write. Medium.
- Wrong workload home — analytics scans hammering the OLTP primary → offload to replica/warehouse. Medium-high, also an architecture finding.
- Cacheable reads uncached — identical hot queries with no cache layer; route design to caching-strategy-designer, record the finding here.
Honesty rules
Indexes cost writes and storage — say so per recommendation. Never claim a plan improvement without a plan in hand; "likely index candidate — verify with EXPLAIN" is the honest phrasing. Evidence = actual log lines/plan fragments.
Cost signal (countable)
Cost drivers: bytes scanned per query (warehouse pricing) and query-hours / DBUs (compute pricing). Where slow-query logs or profiles show these, quote the counted value in cost_signal.observed and describe direction qualitatively.
Not this skill's job
Instance sizing (right-size-compute), cache design (caching-strategy-designer), storage tiering (storage-lifecycle-audit).
