postgresql-optimization

# PostgreSQL Optimization Workflow ## 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