You run a powerful MCP server that analyzes PostgreSQL workloads, suggests index strategies, and tests hypothetical indexes without touching real data. This server guides you toward faster queries, healthier databases, and better resource usage by combining statistics, execution plans, and AI-driven recommendations.

How to use

You interact with the server through an MCP client. Start the server in stdio mode for local development or use the HTTP streamable mode to connect from web apps. Typical tasks include finding slow queries, analyzing execution plans, and testing proposed indexes with HypoPG before applying them.

How to install

pip install pgtuner_mcp

Or, in environments that require a different Python launcher, install with uvx.

uv pip install pgtuner_mcp

For manual installation, clone the project and install in editable mode.

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

Configuration and run modes

The MCP server requires a PostgreSQL connection string and optional exclusions for specific user IDs. You typically set DATABASE_URI in your environment when starting the server.

Two common ways to run the MCP server: standard stdio mode for local usage and streamable HTTP mode for web integrations.

Server how-to for local development (stdio mode): Start the MCP server with Python and load the module.

python -m pgtuner_mcp

If you prefer explicit control over the mode, you can also run in stdio mode with the explicit flag.

python -m pgtuner_mcp --mode stdio

Streamable HTTP mode exposes a single endpoint for HTTP transport. Start the server in streamable-http mode to enable modern, web-friendly communication.

python -m pgtuner_mcp --mode streamable-http

Optional: run in stateless mode for serverless deployments

python -m pgtuner_mcp --mode streamable-http --stateless

## Security and access considerations

Protect the connection string you provide via DATABASE\_URI. Use restricted roles for the PostgreSQL user and consider restricting network access to the MCP endpoint. If you exclude certain user IDs from monitoring, set the PGTUNER\_EXCLUDE\_USERIDS environment variable to keep noise down and protect sensitive accounts.

## Examples and quick checks

Quickly verify access and basic capabilities by starting the server and performing a couple of basic queries once connected to PostgreSQL.

## Troubleshooting tips

If you encounter connection errors, double-check DATABASE\_URI formatting, ensure the PostgreSQL user has the necessary read permissions on statistics tables, and confirm the extensions pg\_stat\_statements and, if used, hypopg are installed and accessible to the server.

## Additional sections

Configuration details, security notes, and practical usage patterns are included below to help you tailor the MCP server to your environment and needs.

## Available tools

### get\_slow\_queries

Retrieve slow queries from pg\_stat\_statements with detailed statistics such as total time, mean time, calls, and 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 IO bottlenecks, and highlight hot tables, buffers, and temp file usage.

### 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 and manage their visibility.

### find\_unused\_indexes

Identify unused and duplicate indexes that can be safely dropped.

### check\_database\_health

Full health check with scoring across connections, cache, locks, replication, wraparound, and disk I/O.

### get\_active\_queries

Monitor active queries and long-running transactions, with optional exclusions for system processes.

### analyze\_wait\_events

Analyze wait events to identify I/O, lock, or CPU bottlenecks in client backends.

### review\_settings

Review PostgreSQL settings by category and provide optimization recommendations.

### analyze\_table\_bloat

Analyze table bloat using pgstattuple, showing dead tuples, free space, and wasted space percentage.

### analyze\_index\_bloat

Analyze B-tree/Gin/Hash index bloat using pgstatindex and related statistics.

### get\_bloat\_summary

Provide a comprehensive overview of database object bloat with top bloated objects.

### monitor\_vacuum\_progress

Track manual and autovacuum operations, showing progress and maintenance status.