Postgres MCP Pro Plus

Advanced PostgreSQL Database Analysis & Optimization Suite
Extended version based on crystaldba/postgres-mcp

🚀 Key Features

• 🔍 Comprehensive Database Analysis: Deep insights into schema structure, relationships, and performance
• ⚡ AI-Powered Optimization: Intelligent index recommendations using Database Tuning Advisor (DTA) and LLM methods
• 🩺 Advanced Health Monitoring: Multi-dimensional health checks with predictive analytics
• 🔒 Lock & Blocking Analysis: Real-time detection and resolution of query blocking and deadlocks
• 🧹 Smart Maintenance: Automated vacuum analysis with bloat detection and maintenance scheduling
• 📊 Performance Intelligence: Query performance analysis with resource usage optimization
• 🔐 Security Assessment: Comprehensive security analysis and recommendations
• 🐳 Docker Ready: Containerized deployment with Docker Compose support

📋 Available Tools

Core Database Operations

Performance & Optimization

Health & Monitoring

Advanced Analysis

🔧 Tool Details & Capabilities

🔍 Database Overview Analysis

Enterprise-grade comprehensive database assessment

The get_database_overview tool provides multi-dimensional analysis:

• 📊 Schema Analysis: Complete structure with table relationships and dependency mapping
• ⚡ Performance Metrics: Query performance, index efficiency, and resource utilization patterns
• 🔐 Security Analysis: User permissions, role assignments, and security configuration assessment
• 💾 Storage Analysis: Table sizes, index bloat detection, and disk usage optimization
• 🩺 Health Indicators: Connection health, vacuum statistics, and system performance metrics

Configuration Options:

• max_tables (default: 500): Maximum tables to analyze per schema for performance control
• sampling_mode (default: true): Statistical sampling for large datasets to optimize execution time
• timeout (default: 300): Maximum execution time with graceful timeout handling

🔒 Advanced Blocking Queries Analysis

Real-time lock contention detection and resolution

The get_blocking_queries tool features enterprise-grade capabilities:

🎯 Core Features:

• Modern Detection: Uses PostgreSQL's pg_blocking_pids() function for accurate blocking identification
• Lock Hierarchy Visualization: Complete blocking chains and process relationships
• Comprehensive Metrics: Process details, wait events, timing, lock types, and affected relations
• Intelligent Recommendations: Severity-based suggestions with specific optimization guidance
• Production Ready: Designed for enterprise database monitoring and performance troubleshooting

📋 Analysis Output:

• Process Information: PID, user, application name, client address, and connection details
• Query Context: Full query text, execution timing, and resource consumption
• Lock Details: Lock types, modes, affected database objects, and wait events
• State Analysis: Process states, wait information, and blocking duration
• Trend Analysis: Summary statistics and pattern recognition
• Categorized Recommendations: 🚨 Critical, ⚠️ Warning, 💡 Optimization, 🎯 Hotspot alerts

🔧 PostgreSQL Compatibility:

• Minimum: PostgreSQL 9.6+ (requires pg_blocking_pids() function)
• Recommended: PostgreSQL 12+ (enhanced lock monitoring features)
• Optimal: PostgreSQL 14+ (includes pg_locks.waitstart for precise wait timing)

🧹 Vacuum Analysis & Maintenance

Comprehensive maintenance planning with bloat detection

The analyze_vacuum_requirements tool provides:

• 📈 Bloat Analysis: Table and index bloat detection with severity assessment
• ⚙️ Autovacuum Configuration: Settings analysis and optimization recommendations
• 📊 Performance Impact: Vacuum operation performance analysis and bottleneck identification
• 🗓️ Maintenance Planning: Intelligent scheduling recommendations based on workload patterns
• 🚨 Critical Issue Detection: Immediate attention alerts for maintenance-related problems
• ⚡ Configuration Optimization: Tuning suggestions for vacuum parameters

🗺️ Schema Relationship Analysis

Advanced dependency mapping and visualization

The analyze_schema_relationships tool offers:

• 🔗 Dependency Mapping: Complete inter-schema relationship visualization
• 📊 Coupling Analysis: Schema coupling metrics and isolation scoring
• 🎯 Impact Assessment: Change impact analysis for schema modifications
• 📈 Relationship Quality: Foreign key relationship quality and consistency scoring
• 🔍 Pattern Detection: Common anti-patterns and architectural recommendations

⚡ Index Optimization Intelligence

AI-powered index recommendations with advanced algorithms

Database Tuning Advisor (DTA) Features:

• 🧠 Pareto Optimization: Multi-objective optimization balancing performance and storage
• 📊 Workload Analysis: Pattern recognition from pg_stat_statements data
• 💰 Cost-Benefit Analysis: Storage budget constraints with performance impact assessment
• 🎯 Query-Specific Tuning: Targeted optimization for specific query sets
• ⏱️ Time-bounded Analysis: Anytime algorithm with configurable runtime limits

LLM-Powered Optimization:

• 🤖 Intelligent Analysis: Natural language understanding of query patterns
• 📝 Contextual Recommendations: Human-readable explanations with implementation guidance
• 🔍 Advanced Pattern Recognition: Complex query pattern detection and optimization

🚀 Quick Start

Prerequisites

• PostgreSQL 9.6+ (PostgreSQL 12+ recommended, 14+ optimal)
• Python 3.8+
• Optional: HypoPG extension for hypothetical index analysis

Installation & Setup

1. Environment Configuration

Create a .env file in the project root:

DATABASE_URI=postgresql://username:password@localhost:5432/database_name

2. Native Deployment

# Start the MCP server (default: stdio transport, unrestricted mode)
./start.sh

# Start in read-only mode for safer analysis
./start.sh --access-mode restricted

# Start with SSE transport for web integration
./start.sh --transport sse --sse-port 8099

# Start SSE server accessible externally
./start.sh --transport sse --sse-host 0.0.0.0 --sse-port 8099

# Show all available options
./start.sh --help

3. Docker Deployment

# Start with Docker Compose
docker-compose up -d

# View logs
docker-compose logs -f postgres-mcp

4. Interactive Testing (MCP Inspector)

# Terminal 1: Start the MCP server with SSE transport
./start.sh --transport sse --sse-port 8099

# Terminal 2: Start the MCP Inspector (opens web interface)
./start-inspector.sh

The MCP Inspector provides:

• Interactive Tool Testing: Test all database analysis tools with a web UI
• Parameter Exploration: Discover tool capabilities and configuration options
• Real-time Results: View formatted analysis results in a user-friendly interface
• Documentation: Built-in tool documentation and usage examples

🔧 Access Modes

Unrestricted Mode (Default):

• Full SQL execution capabilities
• Database modification operations
• Complete administrative access

Restricted Mode (Recommended for analysis):

• Read-only operations with safety controls
• SQL injection protection
• Timeout enforcement (30s default)
• Safe for production analysis

📊 Usage Examples

Basic Server Operations

# Show help and configuration options
./start.sh --help

# Start with default settings (stdio, unrestricted)
./start.sh

# Start in production-safe mode
./start.sh --access-mode restricted

# Start web server for HTTP/SSE integration
./start.sh --transport sse --sse-port 8099

Health Check Examples

# Comprehensive health analysis (via MCP client)
analyze_db_health --health-type all

# Specific component checks
analyze_db_health --health-type index,vacuum,buffer

# Performance optimization workflow
get_top_queries --sort-by resources
analyze_workload_indexes --method dta --max-index-size-mb 1000
get_blocking_queries

🏗️ Architecture & Components

Core Architecture

postgres-mcp/
├── 🔧 server.py              # MCP server & tool registration
├── 📊 database_health/       # Multi-dimensional health monitoring
├── ⚡ explain/               # Query execution plan analysis
├── 🎯 index/                 # AI-powered index optimization
├── 📈 top_queries/           # Performance query analysis
├── 🔒 blocking_queries.py    # Lock contention analysis
├── 🔍 database_overview.py   # Comprehensive assessment
├── 🗺️ schema_mapping.py      # Relationship visualization
├── 🧹 vacuum_analysis.py     # Maintenance optimization
└── 🛡️ sql/                   # SQL execution framework

Database Health Components

• Index Health: Invalid, duplicate, bloated, and unused index detection
• Connection Health: Connection utilization and capacity analysis
• Vacuum Health: Transaction wraparound and maintenance monitoring
• Sequence Health: Sequence exhaustion and overflow protection
• Replication Health: Lag monitoring and slot management
• Buffer Health: Cache hit rate optimization for tables and indexes
• Constraint Health: Invalid constraint detection and remediation

🤖 AI Integration Features

Database Tuning Advisor (DTA):

• Pareto-optimal index selection algorithm
• Multi-query workload optimization
• Budget-constrained recommendation engine
• Time-bounded analysis with anytime approach

LLM-Powered Analysis:

• Natural language query pattern understanding
• Contextual optimization recommendations
• Human-readable explanations and guidance
• Advanced pattern recognition capabilities

📈 Recent Enhancements

Latest Features (Recent Commits)

• ✅ Comprehensive Tool Analysis: Detailed analysis document with improvement recommendations
• ✅ Enhanced Readability: Streamlined code formatting across all modules
• ✅ Robust Error Handling: Improved None value handling in vacuum analysis
• ✅ Advanced Visualizations: Enhanced blocking queries analysis with detailed recommendations
• ✅ Human-Readable Outputs: Refactored analysis tools for better text presentation
• ✅ Schema Relationship Mapping: New schema dependency analysis and visualization
• ✅ Docker Integration: Complete containerization with Docker Compose support
• ✅ Vacuum Analysis Tool: Comprehensive maintenance recommendations and bloat detection

Architecture Improvements

• Modular Design: Enhanced component separation and reusability
• Async Optimization: Improved performance with better async patterns
• Safety Framework: Comprehensive SQL execution safety controls
• Error Recovery: Robust error handling and graceful degradation
• Performance Scaling: Optimized for large database analysis
• Enhanced Startup Scripts: Flexible configuration with comprehensive validation and help system

📚 Documentation & Development

Advanced Documentation

• Database Tools Analysis: Comprehensive analysis of all tools with improvement recommendations
• Tool Improvements Roadmap: Priority-based enhancement roadmap (if available)
• Technical Implementation: Detailed code documentation and API references

Extension Points

• Custom Health Checks: Add domain-specific health monitoring
• Plugin Architecture: Extend with custom analysis tools
• Integration APIs: Connect with external monitoring systems
• Custom Visualizations: Add specialized reporting and dashboards

🔒 Security & Best Practices

Security Features

• SQL Injection Protection: Comprehensive input sanitization
• Access Mode Controls: Restricted/unrestricted operation modes
• Timeout Enforcement: Configurable query timeout protection
• Parameter Validation: Robust input validation and sanitization
• Error Handling: Secure error reporting without information leakage

Production Guidelines

• Use restricted mode for production analysis
• Configure appropriate timeout values for large operations
• Monitor resource usage during analysis operations
• Implement regular health checks for proactive monitoring
• Review security configurations and user permissions regularly

📄 License

MIT License

---

🚀 Postgres MCP Pro Plus - Advanced Database Intelligence
Empowering database professionals with AI-driven insights and optimization