pgtuner_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
Vacuum Monitoring
- Track long-running VACUUM and VACUUM FULL operations in real-time
- Monitor autovacuum progress and performance
- Identify tables that need vacuuming
- View recent vacuum activity history
- Analyze autovacuum configuration effectiveness
I/O Performance Analysis
- Analyze disk read/write patterns across tables and indexes
- Identify I/O bottlenecks and hot tables
- Monitor buffer cache hit ratios
- Track temporary file usage indicating work_mem issues
- Analyze checkpoint and background writer I/O
- PostgreSQL 16+ enhanced pg_stat_io metrics support
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
Installation
Standard Installation (for MCP clients like Claude Desktop)
Or using uv:
Manual Installation
Configuration
Environment Variables
| Variable | Description | Required |
|----------|-------------|----------|
| DATABASE_URI | PostgreSQL connection string | Yes |
| PGTUNER_EXCLUDE_USERIDS | Comma-separated list of user IDs (OIDs) to exclude from monitoring | No |
| PGTUNER_STATEMENT_TIMEOUT_MS | Per-statement timeout in ms (default 30000, 0=disable) | No |
| PGTUNER_IDLE_TXN_TIMEOUT_MS | Idle-in-txn timeout in ms (default 60000) | No |
| PGTUNER_LOCK_TIMEOUT_MS | Lock timeout in ms (default 5000) | No |
| PGTUNER_CORS_ALLOW_ORIGINS | Comma-separated CORS allowlist; * for all | No |
| PGTUNER_LINT_DISABLED_RULES | Comma-separated rule IDs to disable in linter | No |
**Connection String Format:** postgresql://user:password@host:port/database
Minimal User Permissions
To run this MCP server, the PostgreSQL user requires specific permissions to query system catalogs and extensions. Below are the minimal permissions needed for different feature sets.
Basic Permissions (Required for Core Functionality)
Extension-Specific Permissions
**For pgstattuple (Bloat Detection):**
**For HypoPG (Hypothetical Index Testing):**
Complete Setup Script
Excluding Specific Users from Monitoring
You can exclude specific PostgreSQL users from being included in query analysis and monitoring results. This is useful for filtering out:
- Monitoring or replication users
- System accounts
- Internal application service accounts
Set the PGTUNER_EXCLUDE_USERIDS environment variable with a comma-separated list of user OIDs:
To find the OID for a specific PostgreSQL user:
When configured, the following queries are filtered:
- pg_stat_activity queries (filters on usesysid column)
- pg_stat_statements queries (filters on userid column)
This affects tools like get_slow_queries, get_active_queries, analyze_wait_events, check_database_health, and get_index_recommendations.
MCP Client Configuration
Add to your cline_mcp_settings.json or Claude Desktop config: