PostgreSQL Optimization Workflow

Overview

When to Use This Workflow

• 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