Pgtuner MCP

🚀 PostgreSQL Performance Tuning MCP

PostgreSQL Performance Tuning MCP 是一個基於 Model Context Protocol (MCP) 的服務器，藉助人工智能為 PostgreSQL 提供性能調優能力。它能夠識別慢查詢、推薦最優索引、分析執行計劃，還可利用 HypoPG 進行假設索引測試，有效提升數據庫性能。

🚀 快速開始

本項目提供了多種安裝方式，你可以根據自身需求選擇合適的方法進行安裝。安裝完成後，還需要進行相應的配置，包括設置環境變量、配置 MCP 客戶端等，以確保項目能夠正常運行。

✨ 主要特性

查詢分析

• 從 pg_stat_statements 中檢索慢查詢，並提供詳細統計信息。
• 使用 EXPLAIN 和 EXPLAIN ANALYZE 分析查詢執行計劃。
• 通過自動計劃分析識別性能瓶頸。
• 監控活動查詢並檢測長時間運行的事務。

索引調優

• 基於查詢工作負載分析，提供由人工智能驅動的索引建議。
• 使用 HypoPG 擴展進行假設索引測試（不佔用磁盤空間）。
• 查找未使用和重複的索引，以便進行清理。
• 在創建索引之前估算索引大小。
• 在實施之前，使用建議的索引測試查詢計劃。

數據庫健康檢查

• 通過多項檢查進行全面的健康評分。
• 監控連接利用率。
• 分析緩存命中率（緩衝區和索引）。
• 檢測鎖爭用情況。
• 監控真空操作健康狀況和事務 ID 迴繞情況。
• 監控複製延遲。
• 分析後臺寫入器和檢查點的 I/O 情況。

真空操作監控

• 即時跟蹤長時間運行的 VACUUM 和 VACUUM FULL 操作。
• 監控自動真空操作的進度和性能。
• 識別需要進行真空操作的表。
• 查看最近的真空操作活動歷史記錄。
• 分析自動真空配置的有效性。

I/O 性能分析

• 分析表和索引的磁盤讀寫模式。
• 識別 I/O 瓶頸和熱點表。
• 監控緩衝區緩存命中率。
• 跟蹤臨時文件使用情況，以發現 work_mem 問題。
• 分析檢查點和後臺寫入器的 I/O 情況。
• 支持 PostgreSQL 16+ 增強的 pg_stat_io 指標。

配置分析

• 按類別審查 PostgreSQL 設置。
• 獲取有關內存、檢查點、WAL、自動真空和連接設置的建議。
• 識別欠佳的配置。

MCP 提示與資源

• 為常見調優工作流提供預定義的提示模板。
• 提供表統計信息、索引信息和健康檢查的動態資源。
• 提供全面的文檔資源。

📦 安裝指南

標準安裝（適用於像 Claude Desktop 這樣的 MCP 客戶端）

pip install pgtuner_mcp

或者使用 uv：

uv pip install pgtuner_mcp

手動安裝

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

💻 使用示例

基礎用法

# 查找並分析慢查詢
# 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
)

高級用法

# 獲取索引建議
# 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"])

📚 詳細文檔

配置

環境變量

連接字符串格式：postgresql://user:password@host:port/database

最小用戶權限

為了運行此 MCP 服務器，PostgreSQL 用戶需要特定的權限來查詢系統目錄和擴展。以下是不同功能集所需的最小權限：

-- 完整設置腳本
-- 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;

排除特定用戶的監控

你可以將特定的 PostgreSQL 用戶排除在查詢分析和監控結果之外。這對於過濾以下用戶很有用：

• 監控或複製用戶
• 系統賬戶
• 內部應用程序服務賬戶

設置 PGTUNER_EXCLUDE_USERIDS 環境變量，以逗號分隔的用戶 OID 列表：

# Exclude user IDs 16384, 16385, and 16386
export PGTUNER_EXCLUDE_USERIDS="16384,16385,16386"

要查找特定 PostgreSQL 用戶的 OID：

SELECT usesysid, usename FROM pg_user WHERE usename = 'monitoring_user';

配置後，以下查詢將被過濾：

• pg_stat_activity 查詢（在 usesysid 列上過濾）
• pg_stat_statements 查詢（在 userid 列上過濾）

這會影響 get_slow_queries、get_active_queries、analyze_wait_events、check_database_health 和 get_index_recommendations 等工具。

MCP 客戶端配置

添加到你的 cline_mcp_settings.json 或 Claude Desktop 配置中：

{
  "mcpServers": {
    "pgtuner_mcp": {
      "command": "python",
      "args": ["-m", "pgtuner_mcp"],
      "env": {
        "DATABASE_URI": "postgresql://user:password@localhost:5432/mydb"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

或者使用可流式傳輸的 HTTP 模式：

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

服務器模式

1. 標準 MCP 模式（默認）

# Default mode (stdio)
python -m pgtuner_mcp

# Explicitly specify stdio mode
python -m pgtuner_mcp --mode stdio

2. HTTP SSE 模式（適用於舊版 Web 應用程序）

SSE（服務器發送事件）模式為 MCP 通信提供基於 Web 的傳輸方式。這對於需要基於 HTTP 通信的 Web 應用程序和客戶端很有用。

# 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 端點：

MCP 客戶端 SSE 配置： 對於支持 SSE 傳輸的 MCP 客戶端（如 Claude Desktop 或自定義客戶端）：

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

3. 可流式傳輸的 HTTP 模式（推薦使用現代 MCP 協議）

可流式傳輸的 HTTP 模式通過單個 /mcp 端點實現現代 MCP 可流式傳輸的 HTTP 協議。它支持有狀態（基於會話）和無狀態模式。

# 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

有狀態與無狀態：

• 有狀態（默認）：使用 mcp-session-id 標頭在請求之間保持會話狀態。適用於長時間運行的交互。
• 無狀態：為每個請求創建全新的傳輸，不進行會話跟蹤。適用於無服務器部署或簡單的請求/響應模式。

端點：http://{host}:{port}/mcp

可用工具

⚠️ 重要提示

所有工具僅專注於用戶/應用程序表和索引。系統目錄表（pg_catalog、information_schema、pg_toast）會自動排除在所有分析之外。

性能分析工具

索引調優工具

數據庫健康檢查工具

膨脹檢測工具（pgstattuple）

真空操作監控工具

工具參數

MCP 提示

服務器包含預定義的提示模板，用於指導調優會話：

MCP 資源

靜態資源

• pgtuner://docs/tools - 完整的工具文檔
• pgtuner://docs/workflows - 常見調優工作流指南
• pgtuner://docs/prompts - 提示模板文檔

動態資源模板

• pgtuner://table/{schema}/{table_name}/stats - 表統計信息
• pgtuner://table/{schema}/{table_name}/indexes - 表索引信息
• pgtuner://query/{query_hash}/stats - 查詢性能統計信息
• pgtuner://settings/{category} - PostgreSQL 設置（內存、檢查點、WAL、自動真空、連接、所有）
• pgtuner://health/{check_type} - 健康檢查（連接、緩存、鎖、複製、膨脹、所有）

PostgreSQL 擴展設置

HypoPG 擴展

HypoPG 允許在不實際創建索引的情況下測試索引。這對於以下情況非常有用：

• 測試查詢規劃器是否會使用建議的索引
• 比較不同索引策略的執行計劃
• 在提交之前估算存儲需求

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

-- Verify installation
SELECT * FROM hypopg_list_indexes();

pg_stat_statements 擴展

pg_stat_statements 擴展是查詢性能分析所必需的。它跟蹤服務器執行的所有 SQL 語句的規劃和執行統計信息。

步驟 1：在 postgresql.conf 中啟用擴展

在 postgresql.conf 文件中添加以下內容：

# 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

⚠️ 重要提示

修改 shared_preload_libraries 後，需要重啟 PostgreSQL 服務器。

步驟 2：在數據庫中創建擴展

-- 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 擴展

pgstattuple 擴展是膨脹檢測工具（analyze_table_bloat、analyze_index_bloat、get_bloat_summary）所必需的。它提供獲取表和索引元組級統計信息的函數。

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

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

性能影響考慮

💡 使用建議

在啟用 track_io_timing 之前，使用 pg_test_timing 在你的特定系統上測量計時開銷。

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

要求

• Python：3.10+
• PostgreSQL：12+（推薦：14+）
• 擴展：
  • pg_stat_statements（查詢分析必需）
  • hypopg（可選，用於假設索引測試）

依賴項

核心依賴項

• mcp[cli]>=1.12.0 - 模型上下文協議 SDK
• psycopg[binary,pool]>=3.1.0 - 帶有連接池的 PostgreSQL 適配器
• pglast>=7.10 - PostgreSQL 查詢解析器

可選依賴項（用於 HTTP 模式）

• starlette>=0.27.0 - ASGI 框架
• uvicorn>=0.23.0 - ASGI 服務器

🔧 技術細節

本項目基於 Model Context Protocol (MCP) 構建，使用 Python 語言開發，依賴於多個 PostgreSQL 擴展和 Python 庫。通過對 PostgreSQL 系統目錄和擴展的查詢，實現了對數據庫性能的監控和調優。利用 HypoPG 擴展進行假設索引測試，避免了實際創建索引帶來的磁盤空間佔用和性能開銷。同時，通過對各種性能指標的分析和統計，為用戶提供了全面的數據庫健康檢查和調優建議。

📄 許可證

文檔中未提及相關內容，故跳過該章節。

貢獻

歡迎貢獻代碼！請隨時提交拉取請求。