Pgtuner MCP Server for AI PostgreSQL Performance Tuning

Overview

Pgtuner MCP Server is a self-hosted service that integrates AI-driven insights into PostgreSQL performance tuning workflows. It implements the Model Context Protocol (MCP) interface to accept structured context about a PostgreSQL instance (configuration, metrics, slow queries) and return actionable tuning recommendations such as parameter changes, indexing suggestions, and diagnostic findings. The server acts as a bridge between observability data and an LLM or other model backend that generates tuning advice.

This server is useful when you want consistent, repeatable, and automated performance guidance for PostgreSQL without manually interpreting logs and metrics. It helps teams speed up slow queries, identify misconfigurations, and prioritize tuning actions while keeping auditability through suggested diffs and dry-run outputs.

Features

• MCP-compatible API for requesting model-driven tuning recommendations
• Accepts PostgreSQL context: configuration, metrics, query samples, and explain plans
• Returns recommended parameter adjustments, index suggestions, and diagnostics
• Dry-run mode that outputs configuration diffs without applying changes
• Integrates with LLM providers (cloud-hosted or self-hosted) to generate suggestions
• Audit logs and change reasoning to support safe operations and compliance
• Lightweight server designed to run as a container or standalone binary

Installation / Configuration

Clone the repository, build a container, or run a prebuilt image. Below are example steps for common deployment styles.

Clone and run locally

git clone https://github.com/isdaniel/pgtuner_mcp.git
cd pgtuner_mcp
# build a binary or container according to repo instructions
# Example: build Docker image if a Dockerfile is present
docker build -t pgtuner-mcp:local .

Run with Docker (example)

docker run -d \
  --name pgtuner-mcp \
  -p 8080:8080 \
  -e DATABASE_URL="postgres://user:pass@db:5432/mydb" \
  -e MODEL_PROVIDER_URL="https://api.example-llm.com/v1" \
  -e MODEL_API_KEY="sk-xxxx" \
  -e MCP_PORT=8080 \
  -e LOG_LEVEL=info \
  pgtuner-mcp:local

Environment variables (common)

Configuration notes

• Ensure the service account for DATABASE_URL has enough privileges to fetch pg_settings, pg_stat_activity, and to run EXPLAIN (read-only is preferred for diagnostics).
• If using a hosted LLM, secure credentials and restrict network access to the model provider.
• For production, run behind a reverse proxy and enable TLS.

Available Resources

The server exposes MCP-compatible endpoints that accept JSON payloads describing database context and return structured suggestions. Typical resource categories you will interact with:

• /mcp/v1/tune (POST) — Submit context and receive tuning recommendations (config diffs, index suggestions).
• /mcp/v1/diagnostics (POST) — Request diagnostic analysis for slow queries or resource hotspots.
• /mcp/v1/dryrun (POST) — Generate a recommendation and preview how to apply changes safely.
• Metrics collector — Optional agent/collector to feed continuous metrics and slow query samples to the server.
• Audit logs — Stores reasoning and recommended changes for review and compliance.

Example request shape (illustrative)

{
  "context": {
    "pg_settings": { "work_mem": "4MB", "max_parallel_workers_per_gather": "2" },
    "recent_slow_queries": [ "SELECT ...", "UPDATE ..." ],
    "metrics": { "cpu_usage": 0.8, "active_connections": 120 }
  },
  "policy": { "safety": "conservative", "apply_changes": false }
}

Example response (illustrative)

{
  "recommendations": [
    { "type": "parameter", "name": "work_mem", "current": "4MB", "suggested": "16MB", "reason": "large sorts in reported queries" },
    { "type": "index", "query": "SELECT ...", "suggestion": "CREATE INDEX ON ...", "benefit_estimate": "3x speedup" }
  ],
  "diff": "ALTER SYSTEM SET work_mem='16MB';"
}

Use Cases

1. Tuning for a bulk import job

   • Problem: Batch import is CPU- and I/O-bound with frequent sorts.
   • Workflow: Collect current pg_settings and example slow queries, POST to /mcp/v1/tune in dry-run mode, review parameter changes (work_mem, maintenance_work_mem) and follow recommended apply steps (ALTER SYSTEM / reload).

2. Resolving a recurring slow JOIN

   • Problem: A reported JOIN is consistently slow.
   • Workflow: Submit the SQL and EXPLAIN output to /mcp/v1/diagnostics, receive index recommendations or query rewrite suggestions, test the suggested index in a staging environment.

3. Continuous baseline and alerting

   • Problem: Performance regresses after configuration changes.
   • Workflow: Use the metrics collector to feed periodic snapshots to the MCP server; trigger automated tune requests when CPU or query latency spikes. Use the audit log to trace recommended changes and decisions.

4. Safe automation in CI/CD

   • Problem: Apply safe tuning in deployment pipelines.
   • Workflow: During CI for database migrations, call /mcp/v1/dryrun to surface any recommended database-level changes, gate deployments on human review when recommendations exceed thresholds.

Getting started checklist

• Clone and build or pull a container image