{
"markdown": "# PostgreSQL Performance Tuning MCP\n\n[](https://pypi.org/project/pgtuner-mcp/)\n[](https://pypi.org/project/pgtuner-mcp/)\n[](https://www.python.org/downloads/)\n[](https://pypi.org/project/pgtuner-mcp/)\n[](https://hub.docker.com/r/dog830228/pgtuner_mcp)\n\n\n ![](\"https://glama.ai/mcp/servers/@isdaniel/pgtuner-mcp/badge\")
\n\n\nA 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.\n\n## Features\n\n### Query Analysis\n- Retrieve slow queries from `pg_stat_statements` with detailed statistics\n- Analyze query execution plans with `EXPLAIN` and `EXPLAIN ANALYZE`\n- Identify performance bottlenecks with automated plan analysis\n- Monitor active queries and detect long-running transactions\n\n### Index Tuning\n- AI-powered index recommendations based on query workload analysis\n- Hypothetical index testing with **HypoPG** extension (no disk usage)\n- Find unused and duplicate indexes for cleanup\n- Estimate index sizes before creation\n- Test query plans with proposed indexes before implementing\n\n### Database Health\n- Comprehensive health scoring with multiple checks\n- Connection utilization monitoring\n- Cache hit ratio analysis (buffer and index)\n- Lock contention detection\n- Vacuum health and transaction ID wraparound monitoring\n- Replication lag monitoring\n- Background writer and checkpoint analysis\n\n### Vacuum Monitoring\n- Track long-running VACUUM and VACUUM FULL operations in real-time\n- Monitor autovacuum progress and performance\n- Identify tables that need vacuuming\n- View recent vacuum activity history\n- Analyze autovacuum configuration effectiveness\n\n### I/O Performance Analysis\n- Analyze disk read/write patterns across tables and indexes\n- Identify I/O bottlenecks and hot tables\n- Monitor buffer cache hit ratios\n- Track temporary file usage indicating work_mem issues\n- Analyze checkpoint and background writer I/O\n- PostgreSQL 16+ enhanced pg_stat_io metrics support\n\n### Configuration Analysis\n- Review PostgreSQL settings by category\n- Get recommendations for memory, checkpoint, WAL, autovacuum, and connection settings\n- Identify suboptimal configurations\n\n### MCP Prompts & Resources\n- Pre-defined prompt templates for common tuning workflows\n- Dynamic resources for table stats, index info, and health checks\n- Comprehensive documentation resources\n\n## Installation\n\n### Standard Installation (for MCP clients like Claude Desktop)\n\n```bash\npip install pgtuner_mcp\n```\n\nOr using `uv`:\n\n```bash\nuv pip install pgtuner_mcp\n```\n\n### Manual Installation\n\n```bash\ngit clone https://github.com/isdaniel/pgtuner_mcp.git\ncd pgtuner_mcp\npip install -e .\n```\n\n## Configuration\n\n### Environment Variables\n\n| Variable | Description | Required |\n|----------|-------------|----------|\n| `DATABASE_URI` | PostgreSQL connection string | Yes |\n| `PGTUNER_EXCLUDE_USERIDS` | Comma-separated list of user IDs (OIDs) to exclude from monitoring | No |\n| `PGTUNER_STATEMENT_TIMEOUT_MS` | Per-statement timeout in ms (default 30000, 0=disable) | No |\n| `PGTUNER_IDLE_TXN_TIMEOUT_MS` | Idle-in-txn timeout in ms (default 60000) | No |\n| `PGTUNER_LOCK_TIMEOUT_MS` | Lock timeout in ms (default 5000) | No |\n| `PGTUNER_CORS_ALLOW_ORIGINS` | Comma-separated CORS allowlist; `*` for all | No |\n| `PGTUNER_LINT_DISABLED_RULES` | Comma-separated rule IDs to disable in linter | No |\n\n**Connection String Format:** `postgresql://user:password@host:port/database`\n\n### Minimal User Permissions\n\nTo 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.\n\n#### Basic Permissions (Required for Core Functionality)\n\n```sql\n-- Create a dedicated monitoring user\nCREATE USER pgtuner_monitor WITH PASSWORD 'secure_password';\n\n-- Grant connection to the target database\nGRANT CONNECT ON DATABASE your_database TO pgtuner_monitor;\n\n-- Grant usage on schemas\nGRANT USAGE ON SCHEMA public TO pgtuner_monitor;\nGRANT USAGE ON SCHEMA pg_catalog TO pgtuner_monitor;\n\n-- Grant SELECT on user tables and indexes (for table stats and analysis)\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO pgtuner_monitor;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pgtuner_monitor;\n\n-- Grant access to system catalog views (read-only)\nGRANT pg_read_all_stats TO pgtuner_monitor; -- PostgreSQL 10+\n```\n\n#### Extension-Specific Permissions\n\n**For pgstattuple (Bloat Detection):**\n\n```sql\n-- Create the extension (requires superuser or appropriate privileges)\nCREATE EXTENSION IF NOT EXISTS pgstattuple;\n\n-- Grant execution on pgstattuple functions\nGRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION pgstattuple_approx(regclass) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION pgstatginindex(regclass) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION pgstathashindex(regclass) TO pgtuner_monitor;\n\n-- Alternative: Use pg_stat_scan_tables role (PostgreSQL 14+)\nGRANT pg_stat_scan_tables TO pgtuner_monitor;\n```\n\n**For HypoPG (Hypothetical Index Testing):**\n\n```sql\n-- Create the extension (requires superuser or appropriate privileges)\nCREATE EXTENSION IF NOT EXISTS hypopg;\n\n-- Grant SELECT on HypoPG views\nGRANT SELECT ON hypopg_list_indexes TO pgtuner_monitor;\nGRANT SELECT ON hypopg_hidden_indexes TO pgtuner_monitor;\n\n-- Grant execution on HypoPG functions with proper signatures\nGRANT EXECUTE ON FUNCTION hypopg_create_index(text) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_drop_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_reset() TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_hide_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_unhide_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_relation_size(oid) TO pgtuner_monitor;\n\n-- Note: HypoPG operations are session-scoped and don't affect the actual database\n```\n\n#### Complete Setup Script\n\n```sql\n-- 1. Create the monitoring user\nCREATE USER pgtuner_monitor WITH PASSWORD 'secure_password';\n\n-- 2. Grant connection and schema access\nGRANT CONNECT ON DATABASE your_database TO pgtuner_monitor;\nGRANT USAGE ON SCHEMA public TO pgtuner_monitor;\n\n-- 3. Grant read access to user tables\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO pgtuner_monitor;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pgtuner_monitor;\n\n-- 4. Grant system statistics access\nGRANT pg_read_all_stats TO pgtuner_monitor; -- PostgreSQL 10+\n\n-- Grant access to pg_stat_statements views explicitly\nGRANT SELECT ON pg_stat_statements TO pgtuner_monitor;\nGRANT SELECT ON pg_stat_statements_info TO pgtuner_monitor;\n\n-- 5. Install and grant access to extensions (as superuser)\n-- pg_stat_statements (required)\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n\n-- pgstattuple (for bloat detection)\nCREATE EXTENSION IF NOT EXISTS pgstattuple;\nGRANT pg_stat_scan_tables TO pgtuner_monitor; -- PostgreSQL 14+\n-- OR grant individual functions:\n-- GRANT EXECUTE ON FUNCTION pgstattuple(regclass) TO pgtuner_monitor;\n-- GRANT EXECUTE ON FUNCTION pgstattuple_approx(regclass) TO pgtuner_monitor;\n-- GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO pgtuner_monitor;\n\n-- hypopg (for hypothetical index testing)\nCREATE EXTENSION IF NOT EXISTS hypopg;\nGRANT SELECT ON hypopg_list_indexes TO pgtuner_monitor;\nGRANT SELECT ON hypopg_hidden_indexes TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_create_index(text) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_drop_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_reset() TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_hide_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_unhide_index(oid) TO pgtuner_monitor;\nGRANT EXECUTE ON FUNCTION hypopg_relation_size(oid) TO pgtuner_monitor;\n\n-- 6. Verify permissions\nSET ROLE pgtuner_monitor;\nSELECT * FROM pg_stat_statements LIMIT 1;\nSELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();\nSELECT * FROM pgstattuple('pg_class') LIMIT 1;\nSELECT * FROM hypopg_list_indexes();\nRESET ROLE;\n```\n\n### Excluding Specific Users from Monitoring\n\nYou can exclude specific PostgreSQL users from being included in query analysis and monitoring results. This is useful for filtering out:\n- Monitoring or replication users\n- System accounts\n- Internal application service accounts\n\nSet the `PGTUNER_EXCLUDE_USERIDS` environment variable with a comma-separated list of user OIDs:\n\n```bash\n# Exclude user IDs 16384, 16385, and 16386\nexport PGTUNER_EXCLUDE_USERIDS=\"16384,16385,16386\"\n```\n\nTo find the OID for a specific PostgreSQL user:\n\n```sql\nSELECT usesysid, usename FROM pg_user WHERE usename = 'monitoring_user';\n```\n\nWhen configured, the following queries are filtered:\n- `pg_stat_activity` queries (filters on `usesysid` column)\n- `pg_stat_statements` queries (filters on `userid` column)\n\nThis affects tools like `get_slow_queries`, `get_active_queries`, `analyze_wait_events`, `check_database_health`, and `get_index_recommendations`.\n\n### MCP Client Configuration\n\nAdd to your `cline_mcp_settings.json` or Claude Desktop config:\n\n```json\n{\n \"mcpServers\": {\n \"pgtuner_mcp\": {\n \"command\": \"python\",\n \"args\": [\"-m\", \"pgtuner_mcp\"],\n \"env\": {\n \"DATABASE_URI\": \"postgresql://user:password@localhost:5432/mydb\"\n },\n \"disabled\": false,\n \"autoApprove\": []\n }\n }\n}\n```\n\nOr Streamable HTTP Mode\n\n```json\n{\n \"mcpServers\": {\n \"pgtuner_mcp\": {\n \"type\": \"http\",\n \"url\": \"http://localhost:8080/mcp\"\n }\n }\n}\n```\n\n## Security Hardening\n\n`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.\n\n### Connection-level safeguards (built in)\n\nEvery connection started by the pool receives session-level guards via libpq `options` at handshake time:\n\n| Env | Default | Effect |\n|---|---|---|\n| `PGTUNER_STATEMENT_TIMEOUT_MS` | `30000` | Per-statement cap. Caps `analyze_query` EXPLAIN ANALYZE. Set `0` to disable. |\n| `PGTUNER_IDLE_TXN_TIMEOUT_MS` | `60000` | Kills orphaned transactions. Set `0` to disable. |\n| `PGTUNER_LOCK_TIMEOUT_MS` | `5000` | Caps the tuning user's wait on application locks. |\n\nBelt-and-braces — also pin on the monitoring role:\n\n```sql\nALTER ROLE pgtuner_monitor SET statement_timeout = '30s';\nALTER ROLE pgtuner_monitor SET idle_in_transaction_session_timeout = '60s';\n```\n\n### CORS\n\n| Env | Default | Effect |\n|---|---|---|\n| `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`). |\n\n### Recommended reverse-proxy template (Caddy)\n\n```caddyfile\nmcp.example.com {\n basicauth {\n teamuser \n }\n reverse_proxy localhost:8080\n}\n```\n\n### What is NOT included\n\n- No Bearer-token / API-key auth (operator concern — see reverse proxy)\n- No rate limiting (operator concern)\n- No in-process TLS (use the reverse proxy)\n- No per-client tool allowlist\n\n## Server Modes\n\n### 1. Standard MCP Mode (Default)\n\n```bash\n# Default mode (stdio)\npython -m pgtuner_mcp\n\n# Explicitly specify stdio mode\npython -m pgtuner_mcp --mode stdio\n```\n\n### 2. HTTP SSE Mode (Legacy Web Applications)\n\nThe 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.\n\n```bash\n# Start SSE server on default host/port (0.0.0.0:8080)\npython -m pgtuner_mcp --mode sse\n\n# Specify custom host and port\npython -m pgtuner_mcp --mode sse --host localhost --port 3000\n\n# Enable debug mode\npython -m pgtuner_mcp --mode sse --debug\n```\n\n**SSE Endpoints:**\n\n| Endpoint | Method | Description |\n|----------|--------|-------------|\n| `/sse` | GET | SSE connection endpoint - clients connect here to receive server events |\n| `/messages` | POST | Send messages/requests to the server |\n\n**MCP Client Configuration for SSE:**\n\nFor MCP clients that support SSE transport (like Claude Desktop or custom clients):\n\n```json\n{\n \"mcpServers\": {\n \"pgtuner_mcp\": {\n \"type\": \"sse\",\n \"url\": \"http://localhost:8080/sse\"\n }\n }\n}\n```\n\n### 3. Streamable HTTP Mode (Modern MCP Protocol - Recommended)\n\nThe streamable-http mode implements the modern MCP Streamable HTTP protocol with a single `/mcp` endpoint. It supports both stateful (session-based) and stateless modes.\n\n```bash\n# Start Streamable HTTP server in stateful mode (default)\npython -m pgtuner_mcp --mode streamable-http\n\n# Start in stateless mode (fresh transport per request)\npython -m pgtuner_mcp --mode streamable-http --stateless\n\n# Specify custom host and port\npython -m pgtuner_mcp --mode streamable-http --host localhost --port 8080\n\n# Enable debug mode\npython -m pgtuner_mcp --mode streamable-http --debug\n```\n\n**Stateful vs Stateless:**\n- **Stateful (default)**: Maintains session state across requests using `mcp-session-id` header. Ideal for long-running interactions.\n- **Stateless**: Creates a fresh transport for each request with no session tracking. Ideal for serverless deployments or simple request/response patterns.\n\n**Endpoint:** `http://{host}:{port}/mcp`\n\n## Available Tools\n\n> **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.\n\n### Performance Analysis Tools\n\n| Tool | Description |\n|------|-------------|\n| `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. |\n| `analyze_query` | Analyze a query's execution plan with EXPLAIN ANALYZE, including automated issue detection |\n| `get_table_stats` | Get detailed table statistics including size, row counts, dead tuples, and access patterns |\n| `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). |\n\n### Index Tuning Tools\n\n| Tool | Description |\n|------|-------------|\n| `get_index_recommendations` | AI-powered index recommendations based on query workload analysis |\n| `explain_with_indexes` | Run EXPLAIN with hypothetical indexes to test improvements without creating real indexes |\n| `manage_hypothetical_indexes` | Create, list, drop, or reset HypoPG hypothetical indexes. Supports hide/unhide existing indexes. |\n| `find_unused_indexes` | Find unused and duplicate indexes that can be safely dropped |\n\n### Database Health Tools\n\n| Tool | Description |\n|------|-------------|\n| `check_database_health` | Comprehensive health check with scoring (connections, cache, locks, replication, wraparound, disk, checkpoints) |\n| `get_active_queries` | Monitor active queries, find long-running transactions and blocked queries. By default excludes system processes. |\n| `analyze_wait_events` | Analyze wait events to identify I/O, lock, or CPU bottlenecks. Focuses on client backend processes. |\n| `review_settings` | Review PostgreSQL settings by category with optimization recommendations |\n\n### Bloat Detection Tools (pgstattuple)\n\n| Tool | Description |\n|------|-------------|\n| `analyze_table_bloat` | Analyze table bloat using pgstattuple extension. Shows dead tuple counts, free space, and wasted space percentage. |\n| `analyze_index_bloat` | Analyze B-tree index bloat using pgstatindex. Shows leaf density, fragmentation, and empty/deleted pages. Also supports GIN and Hash indexes. |\n| `get_bloat_summary` | Get a comprehensive overview of database bloat with top bloated tables/indexes, total reclaimable space, and priority maintenance actions. |\n\n### Vacuum Monitoring Tools\n\n| Tool | Description |\n|------|-------------|\n| `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. |\n\n### Tool Parameters\n\n#### get_slow_queries\n- `limit`: Maximum queries to return (default: 10)\n- `min_calls`: Minimum call count filter (default: 1)\n- `min_mean_time_ms`: Minimum mean (average) execution time in milliseconds filter\n- `order_by`: Sort by `mean_time`, `calls`, or `rows`\n\n#### analyze_query\n- `query` (required): SQL query to analyze\n- `analyze`: Execute query with EXPLAIN ANALYZE (default: true)\n- `buffers`: Include buffer statistics (default: true)\n- `format`: Output format - `json`, `text`, `yaml`, `xml`\n\n#### get_index_recommendations\n- `workload_queries`: Optional list of specific queries to analyze\n- `max_recommendations`: Maximum recommendations (default: 10)\n- `min_improvement_percent`: Minimum improvement threshold (default: 10%)\n- `include_hypothetical_testing`: Test with HypoPG (default: true)\n- `target_tables`: Focus on specific tables\n\n#### check_database_health\n- `include_recommendations`: Include actionable recommendations (default: true)\n- `verbose`: Include detailed statistics (default: false)\n\n#### analyze_table_bloat\n- `table_name`: Name of a specific table to analyze (optional)\n- `schema_name`: Schema name (default: `public`)\n- `use_approx`: Use `pgstattuple_approx` for faster analysis on large tables (default: false)\n- `min_table_size_gb`: Minimum table size in GB to include in schema-wide scan (default: 5)\n- `include_toast`: Include TOAST table analysis (default: false)\n\n#### analyze_index_bloat\n- `index_name`: Name of a specific index to analyze (optional)\n- `table_name`: Analyze all indexes on this table (optional)\n- `schema_name`: Schema name (default: `public`)\n- `min_index_size_gb`: Minimum index size in GB to include (default: 5)\n- `min_bloat_percent`: Only show indexes with bloat above this percentage (default: 20)\n\n#### get_bloat_summary\n- `schema_name`: Schema to analyze (default: `public`)\n- `top_n`: Number of top bloated objects to show (default: 10)\n- `min_size_gb`: Minimum object size in GB to include (default: 5)\n\n#### monitor_vacuum_progress\n- `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)\n- `schema_name`: Schema to analyze (default: `public`, used with `needs_vacuum` action)\n- `top_n`: Number of results to return (default: 20)\n\n#### analyze_disk_io_patterns\n- `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)\n- `schema_name`: Schema to analyze (default: `public`)\n- `top_n`: Number of top I/O-intensive objects to show (default: 20)\n- `min_size_gb`: Minimum object size in GB to include (default: 1)\n\n## MCP Prompts\n\nThe server includes pre-defined prompt templates for guided tuning sessions:\n\n| Prompt | Description |\n|--------|-------------|\n| `diagnose_slow_queries` | Systematic slow query investigation workflow |\n| `index_optimization` | Comprehensive index analysis and cleanup |\n| `health_check` | Full database health assessment |\n| `query_tuning` | Optimize a specific SQL query |\n| `performance_baseline` | Generate a baseline report for comparison |\n\n## MCP Resources\n\n### Static Resources\n- `pgtuner://docs/tools` - Complete tool documentation\n- `pgtuner://docs/workflows` - Common tuning workflows guide\n- `pgtuner://docs/prompts` - Prompt template documentation\n\n### Dynamic Resource Templates\n- `pgtuner://table/{schema}/{table_name}/stats` - Table statistics\n- `pgtuner://table/{schema}/{table_name}/indexes` - Table index information\n- `pgtuner://query/{query_hash}/stats` - Query performance statistics\n- `pgtuner://settings/{category}` - PostgreSQL settings (memory, checkpoint, wal, autovacuum, connections, all)\n- `pgtuner://health/{check_type}` - Health checks (connections, cache, locks, replication, bloat, all)\n\n## PostgreSQL Extension Setup\n\n### HypoPG Extension\n\nHypoPG enables testing indexes without actually creating them. This is extremely useful for:\n- Testing if a proposed index would be used by the query planner\n- Comparing execution plans with different index strategies\n- Estimating storage requirements before committing\n\n#### Enable HypoPG in Database\n\nHypoPG enables testing hypothetical indexes without creating them on disk.\n\n```sql\n-- Create the extension\nCREATE EXTENSION IF NOT EXISTS hypopg;\n\n-- Verify installation\nSELECT * FROM hypopg_list_indexes();\n```\n\n### pg_stat_statements Extension\n\nThe `pg_stat_statements` extension is **required** for query performance analysis. It tracks planning and execution statistics for all SQL statements executed by a server.\n\n#### Step 1: Enable the Extension in postgresql.conf\n\nAdd the following to your `postgresql.conf` file:\n\n```ini\n# Required: Load pg_stat_statements module\nshared_preload_libraries = 'pg_stat_statements'\n\n# Required: Enable query identifier computation\ncompute_query_id = on\n\n# Maximum number of statements tracked (default: 5000)\npg_stat_statements.max = 10000\n\n# Track all statements including nested ones (default: top)\n# Options: top, all, none\npg_stat_statements.track = top\n\n# Track utility commands like CREATE, ALTER, DROP (default: on)\npg_stat_statements.track_utility = on\n```\n\n> **Note**: After modifying `shared_preload_libraries`, a PostgreSQL server **restart** is required.\n\n#### Step 2: Create the Extension in Your Database\n\n```sql\n-- Connect to your database and create the extension\nCREATE EXTENSION IF NOT EXISTS pg_stat_statements;\n\n-- Verify installation\nSELECT * FROM pg_stat_statements LIMIT 1;\n```\n\n### pgstattuple Extension\n\nThe `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.\n\n```sql\n-- Create the extension\nCREATE EXTENSION IF NOT EXISTS pgstattuple;\n\n-- Verify installation\nSELECT * FROM pgstattuple('pg_class') LIMIT 1;\n```\n\n### Performance Impact Considerations\n\n| Setting | Overhead | Recommendation |\n|---------|----------|----------------|\n| `pg_stat_statements` | Low (~1-2%) | **Always enable** |\n| `track_io_timing` | Low-Medium (~2-5%) | Enable in production, test first |\n| `track_functions = all` | Low | Enable for function-heavy workloads |\n| `pg_stat_statements.track_planning` | Medium | Enable only when investigating planning issues |\n| `log_min_duration_statement` | Low | Recommended for slow query identification |\n\n> **Tip**: Use `pg_test_timing` to measure the timing overhead on your specific system before enabling `track_io_timing`.\n\n## Example Usage\n\n### Find and Analyze Slow Queries\n\n```python\n# Get top 10 slowest queries\nslow_queries = await get_slow_queries(limit=10, order_by=\"total_time\")\n\n# Analyze a specific query's execution plan\nanalysis = await analyze_query(\n query=\"SELECT * FROM orders WHERE user_id = 123\",\n analyze=True,\n buffers=True\n)\n```\n\n### Get Index Recommendations\n\n```python\n# Analyze workload and get recommendations\nrecommendations = await get_index_recommendations(\n max_recommendations=5,\n min_improvement_percent=20,\n include_hypothetical_testing=True\n)\n\n# Recommendations include CREATE INDEX statements\nfor rec in recommendations[\"recommendations\"]:\n print(rec[\"create_statement\"])\n```\n\n### Database Health Check\n\n```python\n# Run comprehensive health check\nhealth = await check_database_health(\n include_recommendations=True,\n verbose=True\n)\n\nprint(f\"Health Score: {health['overall_score']}/100\")\nprint(f\"Status: {health['status']}\")\n\n# Review specific areas\nfor issue in health[\"issues\"]:\n print(f\"{issue}\")\n```\n\n### Find Unused Indexes\n\n```python\n# Find indexes that can be dropped\nunused = await find_unused_indexes(\n schema_name=\"public\",\n include_duplicates=True\n)\n\n# Get DROP statements\nfor stmt in unused[\"recommendations\"]:\n print(stmt)\n```\n\n## Docker\n\n```bash\ndocker pull dog830228/pgtuner_mcp\n\n# Streamable HTTP mode (recommended for web applications)\ndocker run -p 8080:8080 \\\n -e DATABASE_URI=postgresql://user:pass@host:5432/db \\\n dog830228/pgtuner_mcp --mode streamable-http\n\n# Streamable HTTP stateless mode (for serverless)\ndocker run -p 8080:8080 \\\n -e DATABASE_URI=postgresql://user:pass@host:5432/db \\\n dog830228/pgtuner_mcp --mode streamable-http --stateless\n\n# SSE mode (legacy web applications)\ndocker run -p 8080:8080 \\\n -e DATABASE_URI=postgresql://user:pass@host:5432/db \\\n dog830228/pgtuner_mcp --mode sse\n\n# stdio mode (for MCP clients like Claude Desktop)\ndocker run -i \\\n -e DATABASE_URI=postgresql://user:pass@host:5432/db \\\n dog830228/pgtuner_mcp --mode stdio\n```\n\n## Requirements\n\n- **Python**: 3.10+\n- **PostgreSQL**: 12+ (recommended: 14+)\n- **Extensions**:\n - `pg_stat_statements` (required for query analysis)\n - `hypopg` (optional, for hypothetical index testing)\n\n## Dependencies\n\nCore dependencies:\n- `mcp[cli]>=1.12.0` - Model Context Protocol SDK\n- `psycopg[binary,pool]>=3.1.0` - PostgreSQL adapter with connection pooling\n- `pglast>=7.10` - PostgreSQL query parser\n\nOptional (for HTTP modes):\n- `starlette>=0.27.0` - ASGI framework\n- `uvicorn>=0.23.0` - ASGI server\n\n## Integration Testing\n\nIntegration tests exercise every MCP tool against a live PostgreSQL via Docker.\n\n### Quickstart\n\n```bash\nmake up PG=16 # start PG16 container\nmake test-integration # run integration suite (defaults to PG=16)\nmake down # tear down\n```\n\nSupported PG versions: 14, 15, 16, 17 (e.g., `make up PG=17`).\n\nCI runs the suite on every PR across all four PG versions via `.github/workflows/integration.yml`.\n\n## Contributing\n\nContributions are welcome! Please feel free to submit a Pull Request.\n\n\n\n",
"bytes": 26807,
"sha": "8f8a1590aedce2e7d4698567c55ff2db1e1c5fbd32e8a424dd46c043015d0cd0",
"repo_slug": "isdaniel/pgtuner_mcp",
"fonte": "repo",
"truncated": false,
"api": "https://agentalog.com/api/listings/mcp_io_github_isdaniel_pgtuner_mcp_bbfd2a4e/readme"
}