An MCP server that provides AI-powered PostgreSQL performance tuning capabilities, helping identify slow queries, recommend optimal indexes, analyze execution plans, and use HypoPG for testing hypothetical indexes.

Installation

PyPI

pip install pgtuner_mcp

UV

uv pip install pgtuner_mcp

From Source Code

git clone https://github.com/isdaniel/pgtuner_mcp.git
cd pgtuner_mcp
pip install -e .

Docker

docker pull dog830228/pgtuner_mcp

Configuration

Claude Desktop (Standard MCP)

{
  "mcpServers": {
    "pgtuner_mcp": {
      "command": "python",
      "args": ["-m", "pgtuner_mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

HTTP Mode

{
  "mcpServers": {
    "pgtuner_mcp": {
      "type": "http",
      "url": "http://localhost:8080/mcp"
    }
  }
}

Available Tools

Tool	Description
get_slow_queries	Retrieve slow queries from pg_stat_statements with detailed statistics (total time, average time, call count...)
analyze_query	Analyze query execution plan using EXPLAIN ANALYZE, including automatic problem detection
get_table_stats	Retrieve detailed table statistics, including size, row count, dead rows, and access patterns
get_index_recommendations	AI-powered index recommendations based on query workload analysis
explain_with_indexes	Execute EXPLAIN with hypothetical indexes to test improvements without creating real indexes
manage_hypothetical_indexes	Create, view, delete, or reset HypoPG hypothetical indexes
find_unused_indexes	Find unused and duplicate indexes that can be safely dropped
check_database_health	Comprehensive health check with scoring (connections, cache, locks, replication, wraparound, disk...)
get_active_queries	Monitor active queries, find long-running transactions and blocked queries
analyze_wait_events	Analyze wait events to identify I/O, lock, or CPU bottlenecks
review_settings	Review PostgreSQL settings by category with optimization recommendations

Features

• Retrieve slow queries from pg_stat_statements with detailed statistics
• Analyze query execution plans using EXPLAIN and EXPLAIN ANALYZE
• AI-powered index recommendations based on query workload analysis
• Test hypothetical indexes using HypoPG extension (without disk usage)
• Find unused and duplicate indexes for cleanup
• Comprehensive health assessment with multiple checks
• Connection usage monitoring
• Cache hit ratio analysis (buffer and index)
• Lock contention detection
• Vacuum and transaction ID wraparound health monitoring

Environment Variables

Required

• DATABASE_URI - PostgreSQL connection string in format postgresql://user:password@host:port/database

Usage Examples

Diagnose slow queries in my database

Recommend indexes for better performance

Run a comprehensive database health check

Optimize this specific SQL query

Find unused indexes that can be dropped

Notes

Requires PostgreSQL 12+ with pg_stat_statements extension (mandatory). HypoPG extension is optional but recommended for testing hypothetical indexes. Supports multiple modes: stdio (default for MCP clients), HTTP SSE (for legacy web applications), and Streamable HTTP (modern MCP protocol). Server restart is required after enabling pg_stat_statements.