skillZs
★ LIVE SKILL TAGS ★
>>> LIVE SKILLS INDEX <<<
* OPEN SOURCE *
NO LOGIN, NO TRACKING
※ REAL INSTALL DATA ※
← back to all skills
hainrixz/claude-db75 installs

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-hygiene
view source ↗

Is 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?

<!-- Copyright (c) 2026 Enrique Rocha (@soyenriquerocha) · tododeia.com · id er-f37b7121 -->

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

  1. Exact duplicates — two indexes with the identical key column list (and same type/predicate); one is redundant.
  2. 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).
  3. 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, axis performance, confidence established from 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, established Tier-2 / needs_api at 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, not established.
  • A unique/constraint-backing index is not droppable even if "unused" for scans — it enforces integrity; never recommend dropping it.
  • Drops are proposed (never auto): removal is not trivially reversible at scale and intent may be deliberate. Suggest DROP INDEX CONCURRENTLY in the fix preview.

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>