Database Indexing Strategy

A solid reference for when you're adding indexes to PostgreSQL or MySQL and need to think beyond the defaults. Covers the standard index types (B-tree, Hash, GiST, BRIN), composite indexes, and partial indexes with practical SQL examples. The structure is straightforward: guidance on choosing the right index type, patterns for multi-column scenarios, and maintenance considerations. Most useful when you're optimizing slow queries or designing schemas from scratch and want to avoid the classic mistakes like indexing everything or missing compound index opportunities. The reference guides break down database-specific implementations, which saves you from hunting through docs.

skills.sh installs: 491
GitHub stars: 379
last updated: Aug 21, 2026

View on GitHub

Install

$ npx -y skills add aj-geddes/useful-ai-prompts --skill database-indexing-strategy --agent claude-code

Installs into .claude/skills of the current project.

Files

Overview

Design comprehensive indexing strategies to improve query performance, reduce lock contention, and maintain data integrity. Covers index types, design patterns, and maintenance procedures.

When to Use

Index creation and planning
Query performance optimization through indexing
Index type selection (B-tree, Hash, GiST, BRIN)
Composite and partial index design
Index maintenance and monitoring
Storage optimization with indexes
Full-text search index design

Quick Start

B-tree Indexes (Default):

-- Standard equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Composite indexes for multi-column queries
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
WHERE cancelled_at IS NULL;