isdaniel/pgtuner_mcp

# PostgreSQL Bloat Analysis

This skill guides detection, analysis, and remediation of table and index bloat in PostgreSQL databases using pgtuner-mcp tools.

## When to Use This Skill

Use this skill when the user:

- Reports tables or indexes growing unexpectedly large
- Asks about database bloat, dead tuples, or fragmentation
- Mentions disk space running low
- Sees slow sequential scans on tables that should be small
- Asks "why is my table so large?" or "how do I reclaim disk space?"
- Notices `VACUUM` not keeping up or autovacuum running constantly

# PostgreSQL Configuration Tuning

This skill guides systematic PostgreSQL configuration tuning using pgtuner-mcp tools, producing hardware-aware ALTER SYSTEM recommendations.

## When to Use This Skill

Use this skill when the user:

- Asks to tune or optimize `postgresql.conf` settings
- Reports running on default PostgreSQL configuration
- Asks "what settings should I change?" or "how should I configure shared_buffers?"
- Is deploying a new database and wants optimal initial configuration
- Asks about specific settings (work_mem, shared_buffers, effective_cache_size, etc.)
- Reports frequent checkpoints, high I/O, or memory pressure

# PostgreSQL Connection Analysis

This skill guides analysis and optimization of PostgreSQL connection usage using pgtuner-mcp tools.

## When to Use This Skill

Use this skill when the user:

- Reports "too many connections" errors or connection refused
- Asks about connection pooling or PgBouncer setup
- Sees high connection counts in monitoring
- Reports idle connections consuming resources
- Asks "how many connections is my database using?" or "do I need a connection pooler?"
- Reports memory pressure related to per-connection overhead

# PostgreSQL Health Check

This skill guides a comprehensive health assessment of a PostgreSQL database using pgtuner-mcp tools, producing a scored report with prioritized action items.

## When to Use This Skill

Use this skill when the user:

- Asks for a database health check or status report
- Reports general performance degradation without specific symptoms
- Wants a routine database checkup or audit
- Asks "is my database healthy?" or "what should I fix first?"
- Is setting up monitoring or wants to establish a baseline
- Mentions connection issues, high CPU, or unexplained slowness

# PostgreSQL Index Optimization

This skill guides comprehensive index analysis and optimization for PostgreSQL databases using pgtuner-mcp tools, including HypoPG hypothetical index testing.

## When to Use This Skill

Use this skill when the user:

- Asks to optimize or review database indexes
- Wants to find and remove unused or duplicate indexes
- Reports slow queries that might benefit from better indexing
- Asks "what indexes should I create?" or "which indexes can I drop?"
- Mentions high storage usage from indexes or slow write performance
- Wants to test index ideas without creating them in production

# PostgreSQL I/O Deep-Dive

This skill guides a thorough investigation of PostgreSQL I/O performance using the full capabilities of the `analyze_disk_io_patterns` tool, correlating I/O data with query workload and configuration.

## When to Use This Skill

Use this skill when the user:

- Reports high disk I/O or slow disk performance
- Sees queries with high `blks_read` or low cache hit ratios
- Reports `temp_blks_written` in slow queries (work_mem too low)
- Sees frequent checkpoint warnings in PostgreSQL logs
- Has "IO" wait events from `analyze_wait_events`
- Asks "why is my database doing so much disk I/O?"

# PostgreSQL Lock Contention Diagnosis

This skill guides investigation and resolution of lock contention issues in PostgreSQL using pgtuner-mcp tools.

## When to Use This Skill

Use this skill when the user:

- Reports queries being blocked or timing out
- Sees "Lock" wait events in `pg_stat_activity`
- Experiences deadlocks (logged in PostgreSQL error log)
- Reports `idle in transaction` sessions causing problems
- Asks "why are my queries waiting?" or "what is blocking this query?"
- Sees application-level timeout errors due to lock waits

# PostgreSQL Performance Baseline

This skill guides creation of a comprehensive performance baseline report for a PostgreSQL database, intended for comparison before and after making configuration changes, schema modifications, or infrastructure upgrades.

## When to Use This Skill

Use this skill when the user:

- Is about to make database configuration changes and wants a "before" snapshot
- Wants to document current database performance for reference
- Needs to compare performance across time periods
- Asks "how is my database performing?" or "give me a performance report"
- Is planning a migration and needs to capture current metrics
- Wants to establish SLAs or performance targets based on current state

# PostgreSQL Query Rewrite Patterns

This skill guides the agent through analyzing SQL queries and applying common restructuring patterns to improve performance, using pgtuner-mcp execution plan analysis tools.

## When to Use This Skill

Use this skill when the user:

- Has a specific slow query and wants it rewritten for better performance
- Asks "how can I make this query faster?"
- Reports that adding indexes didn't help (query structure is the issue)
- Has queries with subqueries, CTEs, OR conditions, or NOT IN that could be improved
- Asks about query optimization patterns or best practices
- Has queries with `SELECT *` or inefficient pagination

# PostgreSQL Slow Query Diagnosis

This skill guides systematic diagnosis and optimization of slow queries in PostgreSQL databases using the pgtuner-mcp server tools.

## When to Use This Skill

Use this skill when the user:

- Reports slow queries or poor query performance
- Asks "why is my query slow?" or "how can I speed up this query?"
- Wants to find and fix the top resource-consuming queries
- Mentions high query latency, timeouts, or slow API responses
- Asks to investigate `pg_stat_statements` data

# PostgreSQL Vacuum Tuning

This skill guides monitoring, troubleshooting, and tuning of PostgreSQL vacuum operations using pgtuner-mcp tools.

## When to Use This Skill

Use this skill when the user:

- Asks about autovacuum configuration or tuning
- Reports autovacuum running too slowly or too aggressively
- Mentions transaction ID wraparound warnings
- Sees tables accumulating dead tuples
- Asks "why is autovacuum always running?" or "why isn't autovacuum cleaning my table?"
- Reports table bloat growing over time