Back to Details

postgresql-optimization

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

PostgreSQL Optimization Workflow

PostgreSQL数据库优化工作流

Overview

概述

Specialized workflow for PostgreSQL database optimization including query tuning, indexing strategies, performance analysis, vacuum management, and production database administration.
专为PostgreSQL数据库优化设计的工作流，涵盖查询调优、索引策略、性能分析、VACUUM管理以及生产数据库运维。

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
在以下场景使用此工作流：
  • 优化缓慢的PostgreSQL查询
  • 设计索引策略
  • 分析数据库性能
  • 调优PostgreSQL配置
  • 管理生产数据库

Workflow Phases

工作流阶段

Phase 1: Performance Assessment

阶段1：性能评估

Skills to Invoke

需调用的技能

  • database-optimizer
    - Database optimization
  • postgres-best-practices
    - PostgreSQL best practices
  • database-optimizer
    - 数据库优化
  • postgres-best-practices
    - PostgreSQL最佳实践

Actions

操作步骤

  1. Check database version
  2. Review configuration
  3. Analyze slow queries
  4. Check resource usage
  5. Identify bottlenecks
  1. 检查数据库版本
  2. 查看配置信息
  3. 分析慢查询
  4. 检查资源使用情况
  5. 识别性能瓶颈

Copy-Paste Prompts

可复制粘贴的提示词

Use @database-optimizer to assess PostgreSQL performance
Use @database-optimizer to assess PostgreSQL performance

Phase 2: Query Analysis

阶段2：查询分析

Skills to Invoke

需调用的技能

  • sql-optimization-patterns
    - SQL optimization
  • postgres-best-practices
    - PostgreSQL patterns
  • sql-optimization-patterns
    - SQL优化
  • postgres-best-practices
    - PostgreSQL模式

Actions

操作步骤

  1. Run EXPLAIN ANALYZE
  2. Identify scan types
  3. Check join strategies
  4. Analyze execution time
  5. Find optimization opportunities
  1. 运行EXPLAIN ANALYZE
  2. 识别扫描类型
  3. 检查连接策略
  4. 分析执行时间
  5. 寻找优化机会

Copy-Paste Prompts

可复制粘贴的提示词

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

Phase 3: Indexing Strategy

阶段3：索引策略

Skills to Invoke

需调用的技能

  • database-design
    - Index design
  • postgresql
    - PostgreSQL indexing
  • database-design
    - 索引设计
  • postgresql
    - PostgreSQL索引

Actions

操作步骤

  1. Identify missing indexes
  2. Create B-tree indexes
  3. Add composite indexes
  4. Consider partial indexes
  5. Review index usage
  1. 识别缺失的索引
  2. 创建B-tree索引
  3. 添加复合索引
  4. 考虑部分索引
  5. 检查索引使用情况

Copy-Paste Prompts

可复制粘贴的提示词

Use @database-design to design PostgreSQL indexing strategy
Use @database-design to design PostgreSQL indexing strategy

Phase 4: Query Optimization

阶段4：查询优化

Skills to Invoke

需调用的技能

  • sql-optimization-patterns
    - Query tuning
  • sql-pro
    - SQL expertise
  • sql-optimization-patterns
    - 查询调优
  • sql-pro
    - SQL专业技能

Actions

操作步骤

  1. Rewrite inefficient queries
  2. Optimize joins
  3. Add CTEs where helpful
  4. Implement pagination
  5. Test improvements
  1. 重写低效查询
  2. 优化连接操作
  3. 在合适的场景添加CTE
  4. 实现分页
  5. 测试优化效果

Copy-Paste Prompts

可复制粘贴的提示词

Use @sql-optimization-patterns to optimize SQL queries
Use @sql-optimization-patterns to optimize SQL queries

Phase 5: Configuration Tuning

阶段5：配置调优

Skills to Invoke

需调用的技能

  • postgres-best-practices
    - Configuration
  • database-admin
    - Database administration
  • postgres-best-practices
    - 配置调优
  • database-admin
    - 数据库运维

Actions

操作步骤

  1. Tune shared_buffers
  2. Configure work_mem
  3. Set effective_cache_size
  4. Adjust checkpoint settings
  5. Configure autovacuum
  1. 调优shared_buffers
  2. 配置work_mem
  3. 设置effective_cache_size
  4. 调整检查点设置
  5. 配置autovacuum

Copy-Paste Prompts

可复制粘贴的提示词

Use @postgres-best-practices to tune PostgreSQL configuration
Use @postgres-best-practices to tune PostgreSQL configuration

Phase 6: Maintenance

阶段6：维护

Skills to Invoke

需调用的技能

  • database-admin
    - Database maintenance
  • postgresql
    - PostgreSQL maintenance
  • database-admin
    - 数据库维护
  • postgresql
    - PostgreSQL维护

Actions

操作步骤

  1. Schedule VACUUM
  2. Run ANALYZE
  3. Check table bloat
  4. Monitor autovacuum
  5. Review statistics
  1. 安排VACUUM任务
  2. 运行ANALYZE
  3. 检查表膨胀情况
  4. 监控autovacuum
  5. 查看统计信息

Copy-Paste Prompts

可复制粘贴的提示词

Use @database-admin to schedule PostgreSQL maintenance
Use @database-admin to schedule PostgreSQL maintenance

Phase 7: Monitoring

阶段7：监控

Skills to Invoke

需调用的技能

  • grafana-dashboards
    - Monitoring dashboards
  • prometheus-configuration
    - Metrics collection
  • grafana-dashboards
    - 监控仪表盘
  • prometheus-configuration
    - 指标收集

Actions

操作步骤

  1. Set up monitoring
  2. Create dashboards
  3. Configure alerts
  4. Track key metrics
  5. Review trends
  1. 搭建监控系统
  2. 创建仪表盘
  3. 配置告警
  4. 跟踪关键指标
  5. 查看趋势变化

Copy-Paste Prompts

可复制粘贴的提示词

Use @grafana-dashboards to create PostgreSQL monitoring
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
  • database
    - 数据库操作
  • cloud-devops
    - 基础设施
  • performance-optimization
    - 性能优化