# Postgres MCP Pro Plus

<p align="center">
<strong>Advanced PostgreSQL Database Analysis & Optimization Suite</strong><br>
<em>Extended version based on <a href="https://github.com/crystaldba/postgres-mcp">crystaldba/postgres-mcp</a></em>
</p>

## 🚀 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

| Tool Name            | Description                                                              |
| -------------------- | ------------------------------------------------------------------------ |
| `list_schemas`       | List all schemas with ownership and type classification                  |
| `list_objects`       | Browse database objects (tables, views, sequences, extensions) by schema |
| `get_object_details` | Detailed object analysis including columns, constraints, and indexes     |
| `execute_sql`        | Execute SQL with safety controls (restricted/unrestricted modes)         |

### Performance & Optimization

| Tool Name                  | Description                                                                |
| -------------------------- | -------------------------------------------------------------------------- |
| `explain_query`            | Advanced execution plan analysis with HypoPG hypothetical index simulation |
| `get_top_queries`          | Identify slow and resource-intensive queries with performance metrics      |
| `analyze_workload_indexes` | AI-powered index recommendations from workload analysis (DTA/LLM)          |
| `analyze_query_indexes`    | Targeted index optimization for specific query sets (up to 10 queries)     |

### Health & Monitoring

| Tool Name                     | Description                                                                                                  |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------ |
| `analyze_db_health`           | Comprehensive health checks: indexes, connections, vacuum, sequences, replication, buffer cache, constraints |
| `get_blocking_queries`        | Advanced blocking analysis with lock hierarchy visualization and resolution recommendations                  |
| `analyze_vacuum_requirements` | Comprehensive vacuum analysis with bloat detection and maintenance recommendations                           |

### Advanced Analysis

| Tool Name                      | Description                                                                                |
| ------------------------------ | ------------------------------------------------------------------------------------------ |
| `get_database_overview`        | Enterprise-grade database assessment with performance, security, and relationship analysis |
| `analyze_schema_relationships` | Schema dependency mapping with visual relationship analysis and coupling metrics           |

## 🔧 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:

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

#### 2. Native Deployment

```bash
# 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

```bash
# Start with Docker Compose
docker-compose up -d

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

#### 4. Interactive Testing (MCP Inspector)

```bash
# 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

```bash
# 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

```bash
# 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](plan/database-tools-analysis.md)**: Comprehensive analysis of all tools with improvement recommendations
- **[Tool Improvements Roadmap](todos/tool_improvements.md)**: 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

---

<p align="center">
<strong>🚀 Postgres MCP Pro Plus - Advanced Database Intelligence</strong><br>
<em>Empowering database professionals with AI-driven insights and optimization</em>
</p>