PostgreSQL Optimization

Overview

Specialized workflow for PostgreSQL database optimization including query tuning, indexing strategies, performance analysis, vacuum management, and production database administration.

When to Use This Workflow

Use this workflow when:

• -Optimizing slow PostgreSQL queries
• -Designing indexing strategies
• -Analyzing database performance
• -Tuning PostgreSQL configuration
• -Managing production databases

Workflow Phases

Phase 1: Performance Assessment

#### Skills to Invoke

• -database-optimizer - Database optimization
• -postgres-best-practices - PostgreSQL best practices

#### Actions

1. Check database version

2. Review configuration

3. Analyze slow queries

4. Check resource usage

5. Identify bottlenecks

#### Copy-Paste Prompts

Use @database-optimizer to assess PostgreSQL performance

Phase 2: Query Analysis

#### Skills to Invoke

• -sql-optimization-patterns - SQL optimization
• -postgres-best-practices - PostgreSQL patterns

#### Actions

1. Run EXPLAIN ANALYZE

2. Identify scan types

3. Check join strategies

4. Analyze execution time

5. Find optimization opportunities

#### Copy-Paste Prompts

Use @sql-optimization-patterns to analyze and optimize queries

Phase 3: Indexing Strategy

#### Skills to Invoke

• -database-design - Index design
• -postgresql - PostgreSQL indexing

#### Actions

1. Identify missing indexes

2. Create B-tree indexes

3. Add composite indexes

4. Consider partial indexes

5. Review index usage

#### Copy-Paste Prompts

Use @database-design to design PostgreSQL indexing strategy

Phase 4: Query Optimization

#### Skills to Invoke

• -sql-optimization-patterns - Query tuning
• -sql-pro - SQL expertise

#### Actions

1. Rewrite inefficient queries

2. Optimize joins

3. Add CTEs where helpful

4. Implement pagination

5. Test improvements

#### Copy-Paste Prompts

Use @sql-optimization-patterns to optimize SQL queries

Phase 5: Configuration Tuning

#### Skills to Invoke

• -postgres-best-practices - Configuration
• -database-admin - Database administration

#### Actions

1. Tune shared_buffers

2. Configure work_mem

3. Set effective_cache_size

4. Adjust checkpoint settings

5. Configure autovacuum

#### Copy-Paste Prompts

Use @postgres-best-practices to tune PostgreSQL configuration

Phase 6: Maintenance

#### Skills to Invoke

• -database-admin - Database maintenance
• -postgresql - PostgreSQL maintenance

#### Actions

1. Schedule VACUUM

2. Run ANALYZE

3. Check table bloat

4. Monitor autovacuum

5. Review statistics

#### Copy-Paste Prompts

Use @database-admin to schedule PostgreSQL maintenance

Phase 7: Monitoring

#### Skills to Invoke

• -grafana-dashboards - Monitoring dashboards
• -prometheus-configuration - Metrics collection

#### Actions

1. Set up monitoring

2. Create dashboards

3. Configure alerts

4. Track key metrics

5. Review trends

#### Copy-Paste Prompts

Use @grafana-dashboards to create PostgreSQL monitoring

Optimization Checklist

• -[ ] Slow queries identified
• -[ ] Indexes optimized
• -[ ] Configuration tuned
• -[ ] Maintenance scheduled
• -[ ] Monitoring active
• -[ ] Performance improved

Quality Gates

• -[ ] Query performance improved
• -[ ] Indexes effective
• -[ ] Configuration optimized
• -[ ] Maintenance automated
• -[ ] Monitoring in place

Related Workflow Bundles

• -database - Database operations
• -cloud-devops - Infrastructure
• -performance-optimization - Performance