db-index-hygiene
Audit index bloat — exact-duplicate indexes, redundant indexes whose key is a prefix of another, and unused indexes that only cost write amplification and storage. Module M12. Feeds the Performance & Scale score.
How do I install this agent skill?
npx skills add https://github.com/hainrixz/claude-db --skill db-index-hygieneIs this agent skill safe to install?
- Gen Agent Trust Hubpass
The skill is a database utility designed to identify redundant or unused indexes in a PostgreSQL environment. It provides SQL queries for auditing duplicates and prefix overlaps and correctly distinguishes between static analysis (DDL parsing) and runtime analysis (usage statistics). The skill enforces best practices, such as requiring representative monitoring windows before flagging indexes as unused and explicitly prohibiting the removal of unique constraints. No malicious patterns or dangerous operations were detected.
- Socketpass
No alerts
- Snykpass
Risk: LOW · No issues
What does this agent skill do?
db-index-hygiene (M12)
The inverse of M11: M11 adds missing indexes, M12 removes the ones that only cost. Every index slows
writes and consumes storage and cache; duplicates and never-read indexes are pure overhead. Feeds the
Performance & Scale score (axis performance, relational Higiene de índices w16).
What it checks
- Exact duplicates — two indexes with the identical key column list (and same type/predicate); one is redundant.
- Redundant prefix — index on
(a)is fully covered by an existing index on(a, b); the shorter one can usually be dropped (B-tree leftmost-prefix rule). - Unused — an index with no scans over a representative window (Tier-2
idx_scan = 0). This is runtime truth: never declared unused from static analysis alone.
Score / axis
Feeds performance only (relational Higiene de índices w16; folds into Indexación in NoSQL profiles).
Tier-0 (static)
Parse declared indexes and detect exact duplicates and leftmost-prefix redundancy from the DDL alone
(established for duplicates — purely structural). Unused-index detection is runtime-only and
emits status: needs_api at Tier-0 (never a silent pass) with a nudge to Tier-2.
Tier-1/2 (verification query, Postgres)
Duplicate/redundant (Tier-1 catalog):
SELECT indrelid::regclass AS tbl, array_agg(indexrelid::regclass) AS dupes
FROM pg_index GROUP BY indrelid, indkey, indclass, indpred
HAVING count(*) > 1;
Unused (Tier-2, requires sustained stats):
SELECT relname AS tbl, indexrelname AS idx, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS sz
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Method index_check / query_stat. The Tier-2 result confirms M12.*.unused as established;
without a representative window it stays directional, and if stats are needed to decide → needs_api.
Findings
Emit findings per schema/finding.schema.json. Examples:
M12.users.duplicate_email_index— two indexes on the same key (severity:2,warn, axisperformance, confidenceestablishedfrom DDL,fixable: proposed—DROP INDEX).M12.orders.redundant_prefix_index—(status)covered by(status, created_at)(severity:1,warn,established/directional,fixable: proposed).M12.events.unused_index— zero scans over the window (severity:2,warn,establishedTier-2 /needs_apiat Tier-0,fixable: proposed).
Each finding: evidence.observed quotes the index DDL or the catalog/stats row verbatim;
verification.reproduce is the query above referencing $DATABASE_URL; expected_impact is banded +
confidence-tagged (no naked %).
Honesty
- An unused index over a short window may be hit by month-end/quarterly jobs — always require a
representative window and mark short-window verdicts
directional, notestablished. - A unique/constraint-backing index is not droppable even if "unused" for scans — it enforces integrity; never recommend dropping it.
- Drops are
proposed(neverauto): removal is not trivially reversible at scale and intent may be deliberate. SuggestDROP INDEX CONCURRENTLYin the fix preview.
How can the creator link this skill?
Add the canonical catalog link to the repository README so users can inspect current installs and available audits. The publishing guide covers the complete discovery path.
<a href="https://skillzs.dev/skills/hainrixz/claude-db/db-index-hygiene">View db-index-hygiene on skillZs</a>