PostgreSQL Indexes Implementation Assistant

Database optimization for coding assistants like Claude Code or Cursor.

When implementing PostgreSQL indexes in this codebase, follow these specific guidelines for production-ready database performance optimization.

## Project Context Analysis
- Scan existing schema files (migrations, models, SQL files) to understand current table structure
- Identify query patterns from ORM usage, raw SQL, and application code
- Analyze slow query logs if available in project documentation
- Review existing indexes to avoid duplication or conflicts

## Index Implementation Standards

### File Organization
- Place index definitions in dedicated migration files
- Use descriptive naming: `YYYYMMDD_add_index_table_column.sql`
- Include rollback statements for all index operations
- Document index purpose and expected query patterns in comments

### Naming Conventions
```sql
-- Standard format: idx_table_column(s)_type
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at_status ON orders(created_at, status);
CREATE INDEX idx_posts_content_gin ON posts USING gin(content);
```

### Performance-First Implementation

#### Pre-Implementation Analysis
```sql
-- Always measure baseline performance
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';

-- Check table size and growth patterns
SELECT 
  schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
  n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables 
WHERE tablename = 'target_table';
```

#### Index Type Selection Matrix
**B-Tree (Default)** - Use for:
- WHERE clauses with `=, <, >, <=, >=, BETWEEN`
- ORDER BY operations
- Foreign key columns
- Primary lookup columns

**GIN** - Implement for:
- JSONB columns with nested queries
- Array containment operations
- Full-text search columns
- Trigram similarity searches

**BRIN** - Apply to:
- Time-series tables >1M rows
- Append-only data with natural ordering
- Date/timestamp columns in large tables

### Production Implementation Patterns

#### Migration Template
```sql
-- Migration: add_performance_indexes.sql
BEGIN;

-- Create indexes concurrently to avoid table locks
CREATE INDEX CONCURRENTLY idx_orders_user_id_created_at 
ON orders(user_id, created_at) 
WHERE status IN ('pending', 'processing');

-- Verify index creation
SELECT indexname, indexdef FROM pg_indexes 
WHERE tablename = 'orders' AND indexname = 'idx_orders_user_id_created_at';

COMMIT;
```

#### Code Integration Best Practices
- Add index hints in ORM configurations where supported
- Document expected query patterns in model files
- Include index usage in API endpoint documentation
- Set up monitoring for index hit ratios

### Query Optimization Workflow

#### 1. Identify Bottlenecks
```sql
-- Find slow queries
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;
```

#### 2. Analyze Execution Plans
```sql
-- Use in development/staging
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM complex_query_here;
```

#### 3. Implement Targeted Indexes
```sql
-- Multi-column strategy: most selective columns first
CREATE INDEX idx_analytics_date_user_event 
ON analytics(date, user_id, event_type)
WHERE date >= '2024-01-01';
```

### Advanced Implementation Patterns

#### Conditional Indexes
```sql
-- Index only active records
CREATE INDEX idx_users_active_email 
ON users(email) 
WHERE status = 'active';

-- Index recent data only
CREATE INDEX idx_logs_recent_level 
ON logs(level, created_at) 
WHERE created_at > NOW() - INTERVAL '30 days';
```

#### Expression Indexes
```sql
-- Case-insensitive searches
CREATE INDEX idx_users_lower_email 
ON users(LOWER(email));

-- JSON path optimization  
CREATE INDEX idx_metadata_tags 
ON products USING gin((metadata->>'tags'));
```

#### Maintenance Automation
```sql
-- Include in scheduled maintenance
REINDEX INDEX CONCURRENTLY idx_large_table_column;

-- Monitor index bloat
SELECT schemaname, tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_read > 0
ORDER BY pg_relation_size(indexrelid) DESC;
```

## IDE Integration Guidelines

### Code Comments Standard
```sql
/*
Index: idx_orders_customer_status_date
Purpose: Optimize customer order history queries
Query Pattern: WHERE customer_id = ? AND status = ? ORDER BY created_at DESC
Expected QPS: ~500
Maintenance: Reindex monthly due to high update volume
*/
CREATE INDEX idx_orders_customer_status_date 
ON orders(customer_id, status, created_at);
```

### Development Workflow
1. **Local Testing**: Create indexes on representative dataset
2. **Staging Validation**: Measure performance impact with production-like data  
3. **Production Deployment**: Use `CREATE INDEX CONCURRENTLY`
4. **Monitoring Setup**: Track index usage and performance metrics
5. **Documentation Update**: Record index purpose and maintenance requirements

### Error Prevention Checklist
- [ ] Verify column data types match query patterns
- [ ] Check for existing similar indexes before creation
- [ ] Estimate index size impact on storage
- [ ] Confirm concurrent creation won't block application
- [ ] Plan rollback procedure for problematic indexes
- [ ] Set up alerts for unused or low-efficiency indexes

## Integration with Application Code
- Coordinate index creation with ORM schema definitions
- Update query hints and optimization annotations
- Modify application queries to leverage new indexes effectively
- Include index dependencies in deployment documentation
- Set up performance regression testing for critical queries

Generate production-ready index implementations with proper migration scripts, monitoring setup, and integration with existing codebase patterns.

Start a conversation

Ask a follow-up question to continue the conversation