Postgresql Optimization

---
name: postgresql-optimization
description: PostgreSQL database optimization workflow for query tuning, indexing strategies, performance analysis, and production database management.
---

# 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

#### 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

#### 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

- `cloud-devops` - Infrastructure
- `performance-optimization` - Performance

## Limitations
- Use this skill only when the task clearly matches the scope described above.
- Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
- Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.