pgtuner_mcp is an AI-powered PostgreSQL performance tuning assistant that analyzes database workloads and provides actionable optimization recommendations. It integrates with PostgreSQL's system statistics and extensions to identify bottlenecks, suggest improvements, and test optimizations without impacting production systems. Key features: - Query performance analysis with slow query identification from pg_stat_statements and execution plan examination using EXPLAIN and EXPLAIN ANALYZE - AI-powered index recommendations with hypothetical index testing via the HypoPG extension to validate improvements before implementation - Database health monitoring including connection utilization, buffer and index cache hit ratios, lock contention detection, and replication lag tracking - Real-time vacuum monitoring with tracking of VACUUM and autovacuum operations, progress monitoring, and transaction ID wraparound alerts - I/O performance analysis with disk read/write pattern identification across tables and indexes, temporary file usage tracking, and PostgreSQL 16+ pg_stat_io metrics support - Configuration analysis with categorized setting reviews and recommendations for memory, checkpoint, WAL, autovacuum, and connection parameters

PostgreSQL Performance Tuning MCP

[](https://pypi.org/project/pgtuner-mcp/) [](https://pypi.org/project/pgtuner-mcp/) [](https://www.python.org/downloads/) [](https://pypi.org/project/pgtuner-mcp/) [](https://hub.docker.com/r/dog830228/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

Index Tuning

Database Health

Vacuum Monitoring

I/O Performance Analysis

Configuration Analysis

MCP Prompts & Resources

Installation

Standard Installation (for MCP clients like Claude Desktop)

pip install pgtuner_mcp

Or using uv:

uv pip install pgtuner_mcp

Manual Installation

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

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 |

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)

-- Create a dedicated monitoring user
CREATE USER pgtuner_monitor WITH PASSWORD 'secure_password';
-- Grant connection to the target database
GRANT CONNECT ON DATABASE your_database TO pgtuner_monitor;
-- Grant usage on schemas
GRANT USAGE ON SCHEMA public TO pgtuner_monitor;
GRANT USAGE ON SCHEMA pg_catalog TO pgtuner_monitor;
-- Grant SELECT on user tables and indexes (for table stats and analysis)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgtuner_monitor;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pgtuner_monitor;
-- Grant access to system catalog views (read-only)
GRANT pg_read_all_stats TO pgtuner_monitor;  -- PostgreSQL 10+

#### Extension-Specific Permissions

For pgstattuple (Bloat Detection):

```sql -- Create the extension (requires superuser or appropriate privileges) CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Grant execution on pgstattuple functions GRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION pgstattuple_approx(regclass) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION pgsta