Optimize PostgreSQL Database Performance

A 7-phase PostgreSQL optimization workflow covering performance assessment, query analysis, indexing strategy, query optimization, configuration tuning, maintenance, and monitoring, each paired with named sub-skills. Phase 1 (Performance Assessment) invokes database-optimizer and postgres-best-practices to check the PostgreSQL version, review configuration, analyze slow queries, check resource usage, and identify bottlenecks. Phase 2 (Query Analysis) invokes sql-optimization-patterns to run EXPLAIN ANALYZE, identify scan types, check join strategies, and analyze execution time. Phase 3 (Indexing Strategy) invokes database-design to identify missing indexes, create B-tree and composite indexes, consider partial indexes, and review index usage. Phase 4 (Query Optimization) invokes sql-optimization-patterns and sql-pro to rewrite inefficient queries, optimize joins, add CTEs where helpful, and implement pagination. Phase 5 (Configuration Tuning) invokes postgres-best-practices and database-admin to tune shared_buffers, work_mem, effective_cache_size, checkpoint settings, and autovacuum. Phase 6 (Maintenance) invokes database-admin to schedule VACUUM, run ANALYZE, check table bloat, monitor autovacuum, and review statistics. Phase 7 (Monitoring) invokes grafana-dashboards and prometheus-configuration to set up dashboards, configure alerts, and track key metrics over time. The workflow tracks two separate closing lists: a six-item optimization checklist (slow queries identified, indexes optimized, configuration tuned, maintenance scheduled, monitoring active, performance improved) confirming each phase's own work was actually done, and a five-item quality gate (query performance improved, indexes effective, configuration optimized, maintenance automated, monitoring in place) confirming the overall outcome before the workflow is considered complete - the checklist tracks activity, the gate tracks results.

Use when optimizing slow PostgreSQL queries, designing an indexing strategy, analyzing database performance, tuning PostgreSQL configuration parameters, or managing a production PostgreSQL database end to end. Not for other database engines such as MySQL or NoSQL stores, broader infrastructure work beyond the database itself, which is the sibling cloud-devops bundle, or general performance work unrelated to PostgreSQL, which is performance-optimization; this workflow is specifically PostgreSQL-scoped.

Input is a PostgreSQL instance exhibiting a performance problem, plus its current configuration and query workload. Output is EXPLAIN ANALYZE-backed query diagnoses, a set of new or revised indexes, tuned configuration parameters (shared_buffers, work_mem, effective_cache_size, checkpoint settings, autovacuum), a scheduled maintenance plan, and live monitoring dashboards and alerts.

Orchestrates named sub-skills database-optimizer, postgres-best-practices, sql-optimization-patterns, database-design, sql-pro, database-admin, grafana-dashboards, and prometheus-configuration.

Database administrators and backend engineers optimizing a production PostgreSQL database who want a phased sequence - assessment, query analysis, indexing, configuration, maintenance, monitoring - rather than tuning parameters ad hoc.