pgtuner_mcp

Detects and remediates table and index bloat in PostgreSQL using pgstattuple analysis. Identifies fragmentation, dead tuples, and wasted space with prioritized maintenance actions including VACUUM, REINDEX, and pg_repack recommendations.

Guides PostgreSQL configuration tuning based on hardware profile, workload type, and current settings. Covers memory, WAL, checkpoints, autovacuum, planner, and connection settings with specific ALTER SYSTEM recommendations. Teaches the agent to use review_settings with individual categories and understand restart vs reload requirements.

Analyzes PostgreSQL connection utilization patterns including active, idle, and idle-in-transaction sessions. Diagnoses connection leaks, saturation, and provides PgBouncer pooling recommendations. Guides the agent to use get_active_queries with targeted filters and health resources for connection monitoring.

Performs comprehensive PostgreSQL database health assessments covering connections, cache performance, locks, replication, wait events, active queries, and configuration review. Produces a scored health report with prioritized recommendations.

Performs comprehensive PostgreSQL index optimization including unused index cleanup, duplicate detection, missing index recommendations, and hypothetical index testing with HypoPG. Provides actionable CREATE/DROP INDEX statements with estimated impact.

Deep-dive investigation of PostgreSQL I/O patterns using targeted analysis types (buffer pool, tables, indexes, temp files, checkpoints). Covers pg_stat_io for PG16+, correlates I/O patterns with slow queries, and guides diagnosis of cache misses, temp file spills, and checkpoint storms.

Diagnoses PostgreSQL lock contention including blocking lock chains, deadlock patterns, idle-in-transaction problems, and wait event analysis. Guides the agent through identifying blockers, understanding lock types, and recommending transaction and configuration changes.

Generates a comprehensive PostgreSQL performance baseline report capturing health metrics, query workload patterns, table statistics, configuration settings, index utilization, and I/O patterns. Designed for before/after comparison when making changes.

Guides SQL query optimization through execution plan analysis and common rewrite patterns. Covers subquery-to-JOIN conversion, CTE materialization control, OR-to-UNION, NOT IN-to-NOT EXISTS, pagination optimization, and SELECT * elimination. Uses analyze_query with format and settings parameters for deep plan analysis.

Diagnoses slow PostgreSQL queries by analyzing pg_stat_statements, execution plans, index opportunities, and table statistics. Guides the agent through a systematic investigation workflow using pgtuner-mcp tools to produce actionable optimization recommendations.

Monitors and tunes PostgreSQL vacuum operations including autovacuum configuration, vacuum progress tracking, wraparound prevention, and per-table vacuum strategy. Provides tuning recommendations for high-churn OLTP and large analytical tables.