dog830228/pgtuner_mcp repository overview

⁠PostgreSQL Performance Tuning MCP

A Model Context Protocol (MCP) server that provides AI-powered PostgreSQL performance tuning capabilities. This server helps identify slow queries, recommend optimal indexes, analyze execution plans, and leverage HypoPG for hypothetical index testing.

⁠Features

⁠Query Analysis

• Retrieve slow queries from pg_stat_statements with detailed statistics
• Analyze query execution plans with EXPLAIN and EXPLAIN ANALYZE
• Identify performance bottlenecks with automated plan analysis
• Monitor active queries and detect long-running transactions

⁠Index Tuning

• AI-powered index recommendations based on query workload analysis
• Hypothetical index testing with HypoPG extension (no disk usage)
• Find unused and duplicate indexes for cleanup
• Estimate index sizes before creation
• Test query plans with proposed indexes before implementing

⁠Database Health

• Comprehensive health scoring with multiple checks
• Connection utilization monitoring
• Cache hit ratio analysis (buffer and index)
• Lock contention detection
• Vacuum health and transaction ID wraparound monitoring
• Replication lag monitoring
• Background writer and checkpoint analysis

⁠Configuration Analysis

• Review PostgreSQL settings by category
• Get recommendations for memory, checkpoint, WAL, autovacuum, and connection settings
• Identify suboptimal configurations

⁠MCP Prompts & Resources

• Pre-defined prompt templates for common tuning workflows
• Dynamic resources for table stats, index info, and health checks
• Comprehensive documentation resources

⁠Configuration

⁠Environment Variables

Connection String Format: postgresql://user:password@host:port/database

⁠Docker

docker pull  dog830228/pgtuner_mcp

# Streamable HTTP mode (recommended for web applications)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode streamable-http

# Streamable HTTP stateless mode (for serverless)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode streamable-http --stateless

# SSE mode (legacy web applications)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode sse

# stdio mode (for MCP clients like Claude Desktop)
docker run -i \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  dog830228/pgtuner_mcp --mode stdio