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.

Name		Name	Last commit message	Last commit date
Latest commit History 37 Commits
.github/workflows		.github/workflows
src/pgtuner_mcp		src/pgtuner_mcp
tests		tests
.gitignore		.gitignore
Dockerfile		Dockerfile
Dockerfile.streamable-http		Dockerfile.streamable-http
LICENSE		LICENSE
README.md		README.md
glama.json		glama.json
pyproject.toml		pyproject.toml
pytest.ini		pytest.ini
requirements.txt		requirements.txt
server.json		server.json
smithery.yaml		smithery.yaml

License

isdaniel/pgtuner_mcp

Folders and files

Latest commit

History

Repository files navigation