PostgreSQL performance Tuner MCP Server

PostgreSQL Performance Tuning 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

• Retrieve slow queries from pg_stat_statements with detailed statistics
• Analyze query execution plans with EXPLAIN and EXPLAIN ANALYZE
• Identify performance bottlenecks with automated plan analysis
• Monitor active queries and detect long-running transactions

Index Tuning

• AI-powered index recommendations based on query workload analysis
• Hypothetical index testing with HypoPG extension (no disk usage)
• Find unused and duplicate indexes for cleanup
• Estimate index sizes before creation
• Test query plans with proposed indexes before implementing

Database Health

• Comprehensive health scoring with multiple checks
• Connection utilization monitoring
• Cache hit ratio analysis (buffer and index)
• Lock contention detection
• Vacuum health and transaction ID wraparound monitoring
• Replication lag monitoring
• Background writer and checkpoint analysis

Vacuum Monitoring

• Track long-running VACUUM and VACUUM FULL operations in real-time
• Monitor autovacuum progress and performance
• Identify tables that need vacuuming
• View recent vacuum activity history
• Analyze autovacuum configuration effectiveness

I/O Performance Analysis

• Analyze disk read/write patterns across tables and indexes
• Identify I/O bottlenecks and hot tables
• Monitor buffer cache hit ratios
• Track temporary file usage indicating work_mem issues
• Analyze checkpoint and background writer I/O
• PostgreSQL 16+ enhanced pg_stat_io metrics support

Configuration Analysis

• Review PostgreSQL settings by category
• Get recommendations for memory, checkpoint, WAL, autovacuum, and connection settings
• Identify suboptimal configurations

MCP Prompts & Resources

• Pre-defined prompt templates for common tuning workflows
• Dynamic resources for table stats, index info, and health checks
• Comprehensive documentation 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

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):

-- 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 pgstatginindex(regclass) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION pgstathashindex(regclass) TO pgtuner_monitor;

-- Alternative: Use pg_stat_scan_tables role (PostgreSQL 14+) GRANT pg_stat_scan_tables TO pgtuner_monitor;

For HypoPG (Hypothetical Index Testing):

-- Create the extension (requires superuser or appropriate privileges)

CREATE EXTENSION IF NOT EXISTS hypopg;

-- Grant SELECT on HypoPG views GRANT SELECT ON hypopg_list_indexes TO pgtuner_monitor; GRANT SELECT ON hypopg_hidden_indexes TO pgtuner_monitor;

-- Grant execution on HypoPG functions with proper signatures GRANT EXECUTE ON FUNCTION hypopg_create_index(text) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_drop_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_reset() TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_hide_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_unhide_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_relation_size(oid) TO pgtuner_monitor;

-- Note: HypoPG operations are session-scoped and don't affect the actual database

Complete Setup Script

-- 1. Create the monitoring user

CREATE USER pgtuner_monitor WITH PASSWORD 'secure_password';

-- 2. Grant connection and schema access GRANT CONNECT ON DATABASE your_database TO pgtuner_monitor; GRANT USAGE ON SCHEMA public TO pgtuner_monitor;

-- 3. Grant read access to user tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgtuner_monitor; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pgtuner_monitor;

-- 4. Grant system statistics access GRANT pg_read_all_stats TO pgtuner_monitor; -- PostgreSQL 10+

-- Grant access to pg_stat_statements views explicitly GRANT SELECT ON pg_stat_statements TO pgtuner_monitor; GRANT SELECT ON pg_stat_statements_info TO pgtuner_monitor;

-- 5. Install and grant access to extensions (as superuser) -- pg_stat_statements (required) CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- pgstattuple (for bloat detection) CREATE EXTENSION IF NOT EXISTS pgstattuple; GRANT pg_stat_scan_tables TO pgtuner_monitor; -- PostgreSQL 14+ -- OR grant individual 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;

-- hypopg (for hypothetical index testing) CREATE EXTENSION IF NOT EXISTS hypopg; GRANT SELECT ON hypopg_list_indexes TO pgtuner_monitor; GRANT SELECT ON hypopg_hidden_indexes TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_create_index(text) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_drop_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_reset() TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_hide_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_unhide_index(oid) TO pgtuner_monitor; GRANT EXECUTE ON FUNCTION hypopg_relation_size(oid) TO pgtuner_monitor;

-- 6. Verify permissions SET ROLE pgtuner_monitor; SELECT * FROM pg_stat_statements LIMIT 1; SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid(); SELECT * FROM pgstattuple('pg_class') LIMIT 1; SELECT * FROM hypopg_list_indexes(); RESET ROLE;

Excluding Specific Users from Monitoring

You can exclude specific PostgreSQL users from being included in query analysis and monitoring results. This is useful for filtering out:

• Monitoring or replication users
• System accounts
• Internal application service accounts

Set the PGTUNER_EXCLUDE_USERIDS environment variable with a comma-separated list of user OIDs:

# Exclude user IDs 16384, 16385, and 16386

export PGTUNER_EXCLUDE_USERIDS="16384,16385,16386"

To find the OID for a specific PostgreSQL user:

SELECT usesysid, usename FROM pg_user WHERE usename = 'monitoring_user';

When configured, the following queries are filtered:

• pg_stat_activity queries (filters on usesysid column)
• pg_stat_statements queries (filters on userid column)

This affects tools like get_slow_queries, get_active_queries, analyze_wait_events, check_database_health, and get_index_recommendations.

MCP Client Configuration

Add to your cline_mcp_settings.json or Claude Desktop config:

{

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

Or Streamable HTTP Mode

{

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

Security Hardening

pgtuner_mcp HTTP modes (sse, streamable-http) do not include authentication. They are safe for local-only use; for any networked deployment you MUST front them with a reverse proxy that handles auth and TLS.

Connection-level safeguards (built in)

Every connection started by the pool receives session-level guards via libpq options at handshake time:

Belt-and-braces — also pin on the monitoring role:

ALTER ROLE pgtuner_monitor SET statement_timeout = '30s';

ALTER ROLE pgtuner_monitor SET idle_in_transaction_session_timeout = '60s';

CORS

Recommended reverse-proxy template (Caddy)

mcp.example.com {

basicauth { teamuser } reverse_proxy localhost:8080 }

What is NOT included

• No Bearer-token / API-key auth (operator concern — see reverse proxy)
• No rate limiting (operator concern)
• No in-process TLS (use the reverse proxy)
• No per-client tool allowlist

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