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.
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:
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:
Env
Default
Effect
PGTUNER_STATEMENT_TIMEOUT_MS
30000
Per-statement cap. Caps analyze_query EXPLAIN ANALYZE. Set 0 to disable.
PGTUNER_IDLE_TXN_TIMEOUT_MS
60000
Kills orphaned transactions. Set 0 to disable.
PGTUNER_LOCK_TIMEOUT_MS
5000
Caps the tuning user's wait on application locks.
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
Env
Default
Effect
PGTUNER_CORS_ALLOW_ORIGINS
(default: any localhost/127.0.0.1 port, http or https)
Comma-separated allowlist. Setting it switches off the localhost regex default and uses literal-origin matching. Use * to allow all (forces allow_credentials=false).
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):
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
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)
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
Integration Testing
Integration tests exercise every MCP tool against a live PostgreSQL via Docker.
Quickstart
make up PG=16 # start PG16 container
make test-integration # run integration suite (defaults to PG=16)
make down # tear down
Supported PG versions: 14, 15, 16, 17 (e.g., make up PG=17).
CI runs the suite on every PR across all four PG versions via .github/workflows/integration.yml.
Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
Ainda não há lista de ferramentas. Servidores remotos recebem uma após a primeira verificação de saúde.
Perguntas frequentes
Perguntas
Como instalo PostgreSQL Performance Tuner MCP Server?
Copie nesta página a configuração stdio para Cursor, Claude Desktop, Claude Code ou VS Code. Ela usa a instalação do publicador, não um proxy do PlugMCP.
PostgreSQL Performance Tuner MCP Server precisa de uma chave de API?
Este registro não lista nenhuma variável de ambiente obrigatória.
Qual transporte PostgreSQL Performance Tuner MCP Server usa?
PostgreSQL Performance Tuner MCP Server roda localmente via stdio.
Quando PostgreSQL Performance Tuner MCP Server foi verificado pela última vez?