skills.rest
chriso789
chriso789COMMUNITY
Explore all skills by this author →
GitHub

database-index-optimizer

Audits Postgres databases for missing, duplicate, and unused indexes and generates reviewable migration files.

Updated Sep 24, 2025
Search Tags:#migration#supabase#query performance#postgres#index optimization#pg_stat_statements#database audit
One-click install
npx skills add https://github.com/chriso789/pitch-1 --skill database-index-optimizer-chriso789
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-index-optimizer
Source: https://github.com/chriso789/pitch-1/tree/main/.agents/skills/database-index-optimizer
Command: npx skills add https://github.com/chriso789/pitch-1 --skill database-index-optimizer-chriso789

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Slow queries, sequential scans on large tables, and bloated or missing indexes degrade multi-tenant Postgres/Supabase application performance, and fixing them ad-hoc risks production downtime. This Skill performs a read-only, six-gate audit and produces safe, reviewable SQL migrations instead of risky direct DDL. ## Core Features & Use Cases - Six-Gate Index Audit: Checks for missing indexes on hot columns (tenant_id, foreign keys, lookup keys), verifies tenant-leading composites, and detects duplicate, unused, and slow-query patterns via pg_stat_user_indexes and pg_stat_statements. - Safe Migration Generation: Emits CREATE INDEX CONCURRENTLY migrations with partial predicates and post-deploy verification queries, while scheduling DROP INDEX statements as separate follow-up migrations after 7+ days of evidence. - Guardrails and Refusals: Refuses non-concurrent index creation on large tables, drops without idx_scan proof, and non-tenant-leading indexes on tenant-scoped tables. - Use Case: When a CRM contacts page times out, run the audit to find a missing (tenant_id, phone) composite index, receive a concurrent migration file, and verify the Seq Scan is eliminated with EXPLAIN ANALYZE. ## Quick Start Ask the assistant to audit the Supabase database for missing indexes and slow queries on the contacts and jobs tables and propose safe migrations.

Frequently Asked Questions about database-index-optimizer

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I find missing indexes on foreign keys in Postgres?▼

Query pg_constraint joined with pg_attribute to list foreign key columns, then check pg_index for any index whose first key column matches. Columns without a leading index are candidates for new indexes, typically created with CREATE INDEX CONCURRENTLY.

How to detect unused indexes in PostgreSQL?▼

Query pg_stat_user_indexes for entries where idx_scan equals zero, excluding unique and primary key indexes. Before dropping, confirm the index does not back a foreign key, unique constraint, or RLS policy, and verify sufficient uptime since the last stats reset.

Why should I use CREATE INDEX CONCURRENTLY in production?▼

CREATE INDEX CONCURRENTLY avoids blocking writes on the table while the index builds, which is essential for tables with more than 10k rows or tenant-facing traffic. The tradeoff is slower build time and the requirement to run outside a transaction.

Can I drop an index in the same migration as creating its replacement?▼

No, drops should go in a separate follow-up migration after the new index has been observed in production for at least seven days. This protects against query plans that still depend on the old index and allows safe rollback.

What is a tenant-leading composite index and when is it required?▼

A tenant-leading composite index places tenant_id as the first column, matching queries that always filter by tenant. On multi-tenant tables, any hot index that does not lead with the tenant scoping column is a finding rather than a fix.

Related Skills

Explore More Software Engineering Skills→
selrai-companyselrai-company

postgresql-optimization

Analyze EXPLAIN ANALYZE and pg_stat_statements to recommend PostgreSQL indexes and configuration.

Official
Advanced
GDSDNGDSDN
3

analyze-performance

Generate EXPLAIN ANALYZE plans and detect hotpaths in SQL queries.

Community
Advanced
koala-man-64koala-man-64

performance-bottleneck-investigator

Diagnose latency, throughput, and scaling bottlenecks across services, databases, queues, and infrastructure.

Community
Intermediate
lagarcesslagarcess
1

supabase-postgres-best-practices

Optimize Supabase Postgres queries and schemas with SQL rewrites and EXPLAIN-based diagnosis.

Community
Advanced