pgtuner_mcp

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

• Get top resource-consuming queries from pg_stat_statements
• Analyze query execution plans with EXPLAIN and EXPLAIN ANALYZE
• Identify slow queries and bottlenecks

Index Tuning

• Smart index recommendations based on query workload
• Hypothetical index testing with HypoPG extension
• Index health analysis (duplicate, unused, bloated indexes)
• Estimate index size before creation

Database Health

• Connection utilization monitoring
• Vacuum health and transaction ID wraparound checks
• Replication lag monitoring
• Buffer cache hit rate analysis
• Sequence limit warnings

HypoPG Integration

When the HypoPG extension is available, the server can:

• Create hypothetical indexes without actual disk usage
• Test how PostgreSQL would use potential indexes
• Compare query plans with and without proposed indexes
• Hide existing indexes to test removal impact

Installation

Standard Installation (for MCP clients like Claude Desktop)

pip install pgtuner_mcp

Manual Installation

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

Configuration

Environment Variables

• DATABASE_URI: PostgreSQL connection string (required)
  • Format: postgresql://user:password@host:port/database

MCP Client Configuration

Add to your cline_mcp_settings.json:

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

Server Modes

1. Standard MCP Mode (Default)

# Default mode (stdio)
python -m pgtuner_mcp

# Explicitly specify stdio mode
python -m pgtuner_mcp --mode stdio

2. HTTP SSE Mode (Legacy Web Applications)

# Start SSE server on default host/port (0.0.0.0:8080)
python -m pgtuner_mcp --mode sse

# Specify custom host and port
python -m pgtuner_mcp --mode sse --host localhost --port 3000

# Enable debug mode
python -m pgtuner_mcp --mode sse --debug

3. Streamable HTTP Mode (Modern MCP Protocol - Recommended)

The streamable-http mode implements the modern MCP Streamable HTTP protocol with a single /mcp endpoint. It supports both stateful (session-based) and stateless modes.

# Start Streamable HTTP server in stateful mode (default)
python -m pgtuner_mcp --mode streamable-http

# Start in stateless mode (fresh transport per request)
python -m pgtuner_mcp --mode streamable-http --stateless

# Specify custom host and port
python -m pgtuner_mcp --mode streamable-http --host localhost --port 8080

# Enable debug mode
python -m pgtuner_mcp --mode streamable-http --debug

Stateful vs Stateless:

• Stateful (default): Maintains session state across requests using mcp-session-id header. Ideal for long-running interactions.
• Stateless: Creates a fresh transport for each request with no session tracking. Ideal for serverless deployments or simple request/response patterns.

Endpoint: http://{host}:{port}/mcp

Available Tools

Query Analysis Tools

1. get_top_queries - Get the slowest or most resource-intensive queries

   • Parameters: sort_by (total_time, mean_time, resources), limit

2. explain_query - Explain the execution plan for a SQL query

   • Parameters: sql, analyze (boolean), hypothetical_indexes (optional)

Index Tuning Tools

3. analyze_workload_indexes - Analyze frequently executed queries and recommend optimal indexes

   • Parameters: max_index_size_mb, method (dta, greedy)

4. analyze_query_indexes - Analyze specific SQL queries and recommend indexes

   • Parameters: queries (list), max_index_size_mb

5. get_index_recommendations - Get index recommendations for a single query

   • Parameters: query, max_recommendations

6. test_hypothetical_index - Test how a hypothetical index would affect query performance

   • Parameters: table, columns, query, using (btree, hash, etc.)

7. list_hypothetical_indexes - List all current hypothetical indexes

8. reset_hypothetical_indexes - Remove all hypothetical indexes

Database Health Tools

9. analyze_db_health - Comprehensive database health analysis

   • Parameters: health_type (index, connection, vacuum, sequence, replication, buffer, constraint, all)

10. get_index_health - Analyze index health (duplicate, unused, bloated)

Utility Tools

11. execute_sql - Execute a SQL query (respects access mode)

    • Parameters: sql

12. list_schemas - List all schemas in the database

13. get_table_info - Get detailed information about a table

    • Parameters: schema, table

HypoPG Extension

Enable in Database

CREATE EXTENSION hypopg;

Example Usage

Find Slow Queries

# Get top 10 resource-consuming queries
result = await get_top_queries(sort_by="resources", limit=10)

Analyze and Optimize a Query

# Get explain plan
plan = await explain_query(
    sql="SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'"
)

# Get index recommendations
recommendations = await analyze_query_indexes(
    queries=["SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'"]
)

# Test hypothetical index
test_result = await test_hypothetical_index(
    table="orders",
    columns=["user_id", "status"],
    query="SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'"
)

Database Health Check

# Run all health checks
health = await analyze_db_health(health_type="all")

# Check specific areas
index_health = await analyze_db_health(health_type="index")
vacuum_health = await analyze_db_health(health_type="vacuum")

Docker

Build

docker build -t pgtuner_mcp .

Run

# Streamable HTTP mode (recommended)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  pgtuner_mcp --mode streamable-http

# Streamable HTTP stateless mode
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  pgtuner_mcp --mode streamable-http --stateless

# SSE mode (legacy)
docker run -p 8080:8080 \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  pgtuner_mcp --mode sse

# stdio mode (for MCP clients)
docker run \
  -e DATABASE_URI=postgresql://user:pass@host:5432/db \
  pgtuner_mcp

Requirements

• Python 3.10+
• PostgreSQL 12+ (recommended: 14+)
• pg_stat_statements extension (for query analysis)
• hypopg extension (optional, for hypothetical index testing)

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.