New to Claude Skills? Learn how to install them →

Get Claude Skills
davila7 on GitHub

PostgreSQL Optimization

Free

Enhance your PostgreSQL database performance efficiently.

by davila730.2k stars on davila7/claude-code-templates
1 views
Updated Aug 10, 2026
Get this skill

Free · Opens the source repo

What PostgreSQL Optimization does

The PostgreSQL Optimization workflow provides a structured approach for improving the performance of PostgreSQL databases. It encompasses various phases including performance assessment, query analysis, indexing strategy, query optimization, configuration tuning, maintenance, and monitoring. Each phase is designed to address specific aspects of database optimization, ensuring a comprehensive enhancement of database operations.

In the initial phase, users assess the current performance by checking the database version, reviewing configurations, and analyzing slow queries. This sets the groundwork for identifying bottlenecks that may hinder performance. Subsequent phases focus on analyzing queries in detail, designing effective indexing strategies, and optimizing SQL queries to ensure efficient execution. By utilizing best practices, users can significantly improve query response times and overall database efficiency.

The workflow also emphasizes the importance of configuration tuning and regular maintenance. By adjusting settings such as shared buffers and autovacuum configurations, users can optimize resource usage. The maintenance phase includes scheduling necessary tasks like VACUUM and ANALYZE, which are crucial for keeping the database healthy. Finally, the monitoring phase allows users to set up dashboards and alerts to track performance metrics over time, ensuring that the database remains optimized as usage patterns evolve.

This workflow is particularly beneficial for database administrators, developers, and anyone managing production PostgreSQL databases. It provides clear steps and prompts for each phase, making it easy to follow and implement best practices for database optimization.

When to use it

Use this workflow when you need to enhance the performance of your PostgreSQL database, especially when dealing with slow queries or resource bottlenecks.

When not to use it

This workflow may not be suitable for databases that are already performing optimally or for users unfamiliar with PostgreSQL concepts and best practices.

What you can build with it

Optimizing a Slow Query

When a specific query is running slower than expected, use the workflow to analyze and optimize it using the provided phases.

Implementing Indexing Strategies

If you notice performance issues related to data retrieval, follow the indexing strategy phase to identify and create necessary indexes.

Regular Database Maintenance

Schedule routine maintenance tasks such as VACUUM and ANALYZE to ensure your PostgreSQL database remains healthy and performs optimally.

How to install PostgreSQL Optimization

View source

1. Install with the skills CLI

npx skills add davila7/claude-code-templates/postgresql-optimization --agent claude-code

2. Or install it manually

Download the skill folder and drop it into ~/.claude/skills/ for all projects, or .claude/skills/ to scope it to one repo. Restart Claude Code so it picks up the new skill.

Anthropic's agentic coding CLI, and the reference implementation of Agent Skills. Drop a skill folder into ~/.claude/skills and Claude Code loads it automatically whenever a task matches the skill's description. Claude Code docs

Inside SKILL.md

Written by davila7

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

Frequently asked questions about PostgreSQL Optimization

Similar skills

ClickHouse Logs Queries

Efficiently manage Supabase logs with ClickHouse SQL.

EF Core D2 Database Diagram Generator

Visualize your EF Core models as D2 diagrams effortlessly.

Safe SQL Execution

Ensure secure SQL execution in Supabase applications.

Oracle to PostgreSQL Migration

Identify migration risks between Oracle and PostgreSQL.

SSMA Console

Streamline Oracle to SQL Server migrations with ease.

SQL Performance Optimization

Enhance SQL query efficiency across all databases.