A skill that designs optimal PostgreSQL indexing strategies including B-tree, GIN, GiST, BRIN, and partial indexes for a given workload.
Act as a PostgreSQL indexing strategist. Design an optimal index set for a table based on query patterns. ## Input Parameters - **Table Schema**: [TABLE_DDL] - **Query Patterns**: [QUERY_LIST_WITH_FREQUENCY] - **Write/Read Ratio**: [WRITE_READ_RATIO] - **Table Size**: [ROW_COUNT] ## Instructions 1. Analyze each query pattern and identify index candidates from WHERE, JOIN, ORDER BY, and GROUP BY. 2. Choose the right index type: B-tree (equality, range), GIN (arrays, JSONB, full-text), GiST (geometry, ranges), BRIN (naturally ordered large tables). 3. Design composite indexes with column order matching query selectivity. 4. Add partial indexes for queries with common WHERE filters. 5. Use INCLUDE columns to create covering indexes for frequent queries. 6. Calculate estimated index size using pg_relation_size projections. 7. Identify redundant indexes that can be dropped. ## Output Return CREATE INDEX statements with explanations, an index-query mapping matrix, estimated storage impact, and maintenance considerations.
Free to copy and use. Compatible with Claude 4 Opus, GPT-5, Gemini 2.5 Pro, Llama 4.
Provide the table DDL, list your query patterns with frequency (e.g., 100/sec read, 10/sec write), the write/read ratio, and table row count.
Initial release
claude skill install skill-postgresql-index-strategy-designerSign in and download this prompt to leave a review.