Postgres MCP Pro Plus

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

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

This server cannot be installed

security - not tested

license - permissive license

quality - not tested

How are these scores calculated?

An extended PostgreSQL management and analysis server that provides database professionals with tools for schema management, query optimization, performance monitoring, and health analysis through a collection of specialized functions.

Related MCP Servers

PostgreSQL MCP Server
nahmanmate
A
security
A
license
A
quality
Facilitates management and optimization of PostgreSQL databases, offering analysis, setup guidance, and debugging, while ensuring secure and efficient database operations.
Last updated -
3
1
16
TypeScript
AGPL 3.0
PostgreSQL MCP Server
vinsidious
-
security
F
license
-
quality
Provides read-only access to PostgreSQL databases, enabling users to inspect database schemas and execute read-only queries through a Model Context Protocol server.
Last updated -
3
JavaScript
PostgreSQL MCP Server
DanielRSnell
-
security
F
license
-
quality
Provides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries within a secure transaction context.
Last updated -
20,135
1
JavaScript
PostgreSQL MCP Server
HenkDz
-
security
A
license
-
quality
A Model Context Protocol server that enables powerful PostgreSQL database management capabilities including analysis, schema management, data migration, and monitoring through natural language interactions.
Last updated -
438
89
TypeScript
AGPL 3.0

View all related MCP servers