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

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

-- 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"
    }
  }
}

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)

The SSE (Server-Sent Events) mode provides a web-based transport for MCP communication. It's useful for web applications and clients that need HTTP-based communication.

# 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

SSE Endpoints:

Endpoint	Method	Description
/sse	GET	SSE connection endpoint - clients connect here to receive server events
/messages	POST	Send messages/requests to the server

MCP Client Configuration for SSE:

For MCP clients that support SSE transport (like Claude Desktop or custom clients):

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

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

Note: All tools focus exclusively on user/application tables and indexes. System catalog tables (pg_catalog, information_schema, pg_toast) are automatically excluded from all analyses.

Performance Analysis Tools

Tool	Description
get_slow_queries	Retrieve slow queries from pg_stat_statements with detailed stats (total time, mean time, calls, cache hit ratio). Excludes system catalog queries.
analyze_query	Analyze a query's execution plan with EXPLAIN ANALYZE, including automated issue detection
get_table_stats	Get detailed table statistics including size, row counts, dead tuples, and access patterns
analyze_disk_io_patterns	Analyze disk I/O read/write patterns, identify hot tables, buffer cache efficiency, and I/O bottlenecks. Supports filtering by analysis type (all, buffer_pool, tables, indexes, temp_files, checkpoints).

Index Tuning Tools

Tool	Description
get_index_recommendations	AI-powered index recommendations based on query workload analysis
explain_with_indexes	Run EXPLAIN with hypothetical indexes to test improvements without creating real indexes
manage_hypothetical_indexes	Create, list, drop, or reset HypoPG hypothetical indexes. Supports hide/unhide existing indexes.
find_unused_indexes	Find unused and duplicate indexes that can be safely dropped

Database Health Tools

Tool	Description
check_database_health	Comprehensive health check with scoring (connections, cache, locks, replication, wraparound, disk, checkpoints)
get_active_queries	Monitor active queries, find long-running transactions and blocked queries. By default excludes system processes.
analyze_wait_events	Analyze wait events to identify I/O, lock, or CPU bottlenecks. Focuses on client backend processes.
review_settings	Review PostgreSQL settings by category with optimization recommendations

Bloat Detection Tools (pgstattuple)

Tool	Description
analyze_table_bloat	Analyze table bloat using pgstattuple extension. Shows dead tuple counts, free space, and wasted space percentage.
analyze_index_bloat	Analyze B-tree index bloat using pgstatindex. Shows leaf density, fragmentation, and empty/deleted pages. Also supports GIN and Hash indexes.
get_bloat_summary	Get a comprehensive overview of database bloat with top bloated tables/indexes, total reclaimable space, and priority maintenance actions.

Vacuum Monitoring Tools

Tool	Description
monitor_vacuum_progress	Track manual VACUUM, VACUUM FULL, and autovacuum operations. Monitor progress percentage, dead tuples collected, index vacuum rounds, and estimated time remaining. Includes autovacuum configuration review and tables needing maintenance.

Tool Parameters

get_slow_queries

• limit: Maximum queries to return (default: 10)
• min_calls: Minimum call count filter (default: 1)
• min_mean_time_ms: Minimum mean (average) execution time in milliseconds filter
• order_by: Sort by mean_time, calls, or rows

analyze_query

• query (required): SQL query to analyze
• analyze: Execute query with EXPLAIN ANALYZE (default: true)
• buffers: Include buffer statistics (default: true)
• format: Output format - json, text, yaml, xml

get_index_recommendations

• workload_queries: Optional list of specific queries to analyze
• max_recommendations: Maximum recommendations (default: 10)
• min_improvement_percent: Minimum improvement threshold (default: 10%)
• include_hypothetical_testing: Test with HypoPG (default: true)
• target_tables: Focus on specific tables

check_database_health

• include_recommendations: Include actionable recommendations (default: true)
• verbose: Include detailed statistics (default: false)

analyze_table_bloat

• table_name: Name of a specific table to analyze (optional)
• schema_name: Schema name (default: public)
• use_approx: Use pgstattuple_approx for faster analysis on large tables (default: false)
• min_table_size_gb: Minimum table size in GB to include in schema-wide scan (default: 5)
• include_toast: Include TOAST table analysis (default: false)

analyze_index_bloat

• index_name: Name of a specific index to analyze (optional)
• table_name: Analyze all indexes on this table (optional)
• schema_name: Schema name (default: public)
• min_index_size_gb: Minimum index size in GB to include (default: 5)
• min_bloat_percent: Only show indexes with bloat above this percentage (default: 20)

get_bloat_summary

• schema_name: Schema to analyze (default: public)
• top_n: Number of top bloated objects to show (default: 10)
• min_size_gb: Minimum object size in GB to include (default: 5)

monitor_vacuum_progress

• action: Action to perform - progress (monitor active vacuum operations), needs_vacuum (find tables needing vacuum), autovacuum_status (review autovacuum configuration), or recent_activity (view recent vacuum history)
• schema_name: Schema to analyze (default: public, used with needs_vacuum action)
• top_n: Number of results to return (default: 20)

analyze_disk_io_patterns

• analysis_type: Type of I/O analysis - all (comprehensive), buffer_pool (cache hit ratios), tables (table I/O patterns), indexes (index I/O patterns), temp_files (temporary file usage), or checkpoints (checkpoint I/O statistics)
• schema_name: Schema to analyze (default: public)
• top_n: Number of top I/O-intensive objects to show (default: 20)
• min_size_gb: Minimum object size in GB to include (default: 1)

MCP Prompts

The server includes pre-defined prompt templates for guided tuning sessions:

Prompt	Description
diagnose_slow_queries	Systematic slow query investigation workflow
index_optimization	Comprehensive index analysis and cleanup
health_check	Full database health assessment
query_tuning	Optimize a specific SQL query
performance_baseline	Generate a baseline report for comparison

MCP Resources

Static Resources

• pgtuner://docs/tools - Complete tool documentation
• pgtuner://docs/workflows - Common tuning workflows guide
• pgtuner://docs/prompts - Prompt template documentation

Dynamic Resource Templates

• pgtuner://table/{schema}/{table_name}/stats - Table statistics
• pgtuner://table/{schema}/{table_name}/indexes - Table index information
• pgtuner://query/{query_hash}/stats - Query performance statistics
• pgtuner://settings/{category} - PostgreSQL settings (memory, checkpoint, wal, autovacuum, connections, all)
• pgtuner://health/{check_type} - Health checks (connections, cache, locks, replication, bloat, all)

PostgreSQL Extension Setup

HypoPG Extension

HypoPG enables testing indexes without actually creating them. This is extremely useful for:

• Testing if a proposed index would be used by the query planner
• Comparing execution plans with different index strategies
• Estimating storage requirements before committing

Enable HypoPG in Database

HypoPG enables testing hypothetical indexes without creating them on disk.

-- Create the extension
CREATE EXTENSION IF NOT EXISTS hypopg;

-- Verify installation
SELECT * FROM hypopg_list_indexes();

pg_stat_statements Extension

The pg_stat_statements extension is required for query performance analysis. It tracks planning and execution statistics for all SQL statements executed by a server.

Step 1: Enable the Extension in postgresql.conf

Add the following to your postgresql.conf file:

# Required: Load pg_stat_statements module
shared_preload_libraries = 'pg_stat_statements'

# Required: Enable query identifier computation
compute_query_id = on

# Maximum number of statements tracked (default: 5000)
pg_stat_statements.max = 10000

# Track all statements including nested ones (default: top)
# Options: top, all, none
pg_stat_statements.track = top

# Track utility commands like CREATE, ALTER, DROP (default: on)
pg_stat_statements.track_utility = on

Note: After modifying shared_preload_libraries, a PostgreSQL server restart is required.

Step 2: Create the Extension in Your Database

-- Connect to your database and create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify installation
SELECT * FROM pg_stat_statements LIMIT 1;

pgstattuple Extension

The pgstattuple extension is required for bloat detection tools (analyze_table_bloat, analyze_index_bloat, get_bloat_summary). It provides functions to get tuple-level statistics for tables and indexes.

-- Create the extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Verify installation
SELECT * FROM pgstattuple('pg_class') LIMIT 1;

Performance Impact Considerations

Setting	Overhead	Recommendation
pg_stat_statements	Low (~1-2%)	Always enable
track_io_timing	Low-Medium (~2-5%)	Enable in production, test first
track_functions = all	Low	Enable for function-heavy workloads
pg_stat_statements.track_planning	Medium	Enable only when investigating planning issues
log_min_duration_statement	Low	Recommended for slow query identification

Tip: Use pg_test_timing to measure the timing overhead on your specific system before enabling track_io_timing.

Example Usage

Find and Analyze Slow Queries

# Get top 10 slowest queries
slow_queries = await get_slow_queries(limit=10, order_by="total_time")

# Analyze a specific query's execution plan
analysis = await analyze_query(
    query="SELECT * FROM orders WHERE user_id = 123",
    analyze=True,
    buffers=True
)

Get Index Recommendations

# Analyze workload and get recommendations
recommendations = await get_index_recommendations(
    max_recommendations=5,
    min_improvement_percent=20,
    include_hypothetical_testing=True
)

# Recommendations include CREATE INDEX statements
for rec in recommendations["recommendations"]:
    print(rec["create_statement"])

Database Health Check

# Run comprehensive health check
health = await check_database_health(
    include_recommendations=True,
    verbose=True
)

print(f"Health Score: {health['overall_score']}/100")
print(f"Status: {health['status']}")

# Review specific areas
for issue in health["issues"]:
    print(f"{issue}")

Find Unused Indexes

# Find indexes that can be dropped
unused = await find_unused_indexes(
    schema_name="public",
    include_duplicates=True
)

# Get DROP statements
for stmt in unused["recommendations"]:
    print(stmt)

Docker

docker pull  dog830228/pgtuner_mcp

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

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

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

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

Requirements

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

Dependencies

Core dependencies:

• mcp[cli]>=1.12.0 - Model Context Protocol SDK
• psycopg[binary,pool]>=3.1.0 - PostgreSQL adapter with connection pooling
• pglast>=7.10 - PostgreSQL query parser

Optional (for HTTP modes):

• starlette>=0.27.0 - ASGI framework
• uvicorn>=0.23.0 - ASGI server

Contributing

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