pgtuner_mcp

smithery badge

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

get_top_queries - Get the slowest or most resource-intensive queries
- Parameters: sort_by (total_time, mean_time, resources), limit
explain_query - Explain the execution plan for a SQL query
- Parameters: sql, analyze (boolean), hypothetical_indexes (optional)

Index Tuning Tools

analyze_workload_indexes - Analyze frequently executed queries and recommend optimal indexes
- Parameters: max_index_size_mb, method (dta, greedy)
analyze_query_indexes - Analyze specific SQL queries and recommend indexes
- Parameters: queries (list), max_index_size_mb
get_index_recommendations - Get index recommendations for a single query
- Parameters: query, max_recommendations
test_hypothetical_index - Test how a hypothetical index would affect query performance
- Parameters: table, columns, query, using (btree, hash, etc.)
list_hypothetical_indexes - List all current hypothetical indexes
reset_hypothetical_indexes - Remove all hypothetical indexes

Database Health Tools

analyze_db_health - Comprehensive database health analysis
- Parameters: health_type (index, connection, vacuum, sequence, replication, buffer, constraint, all)
get_index_health - Analyze index health (duplicate, unused, bloated)

Utility Tools

execute_sql - Execute a SQL query (respects access mode)
- Parameters: sql
list_schemas - List all schemas in the database
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.

PostgreSQL-Performance-Tuner-Mcp