PostgreSQL Performance (read-only)

pg-perf-mcp

一个 MCP 服务，让 AI 助手能够安全地诊断 PostgreSQL 性能问题：慢查询、EXPLAIN 计划、索引建议、表健康状况和锁争用。它在设计上是只读的。

这是 Claude Code 在演示商店中的真实会话，播放速度经过加快。首先，它找出哪些数据库函数运行缓慢以及原因。接着，它解释一条缓慢的目录查询，并通过假设索引估算修复效果。当被要求创建索引时，它表示无法执行：所有工具都是只读的。

为什么选择它

页面运行缓慢时，开发者会把查询粘贴到聊天中并问“为什么这么慢？”。由于看不到执行计划、表大小、现有索引，也不知道哪些查询实际上占据了主要负载，助手只能猜测。

pg-perf-mcp 通过一个范围有限、经过审计且只读的接口，为助手提供来自 pg_stat_statements、EXPLAIN 和系统目录的实际数据。它无法更改数据、无法执行 DDL，也绝不会创建它所建议的索引。

演示

下面是针对演示商店的一段精简示例会话（运行 docker compose up，然后运行 ./demo/workload.sh）。每次运行的具体数值会有所不同：

You:    The shop database feels slow. What's going on?

Claude: [top_slow_queries orderBy=total_time]
        The top statement by total time is
          SELECT id, status, total, created_at FROM shop.orders WHERE customer_id = $1 ORDER BY created_at DESC
        (thousands of calls, the largest share of total execution time).

        [explain_query sql="... WHERE customer_id = 42 ORDER BY created_at DESC"]
        HIGH  SEQ_SCAN_ON_LARGE_TABLE  Seq Scan on shop.orders
              reads all ~1.0M rows of shop.orders to apply filter (customer_id = 42); only a handful match.

        [suggest_indexes]
        CREATE INDEX CONCURRENTLY idx_orders_customer_id ON shop.orders (customer_id);
        hypopg: the planner would use it; estimated cost drops by well over 90%.
        Not executed. Review it and run it yourself, ideally off-peak.

        [table_health table=shop.orders]
        Autovacuum is disabled on shop.orders and ~20% of rows are dead. Re-enable autovacuum and VACUUM.

        Prioritized actions: 1) add the index above (orders.customer_id is a foreign key with no index),
        2) re-enable autovacuum on shop.orders, 3) ...

架构

flowchart LR
    A["AI assistant<br/>(Claude Code, Claude Desktop, ...)"] -- "MCP over streamable HTTP<br/>Authorization: Bearer key" --> F["API key filter<br/>(constant-time)"]
    F --> M["MCP tools / resources / prompt<br/>Spring AI 2.0 annotations"]
    M --> R["ToolRunner<br/>audit log · metrics · safe errors"]
    R --> G["SqlGuard<br/>PG lexer + JSqlParser"]
    G --> E["ReadOnlyExecutor<br/>READ ONLY tx · always rollback<br/>statement/lock timeout · row cap"]
    R --> AN["analysis<br/>PlanAnalyzer · IndexAdvisor<br/>(pure functions)"]
    E --> P[("PostgreSQL 18<br/>role mcp_readonly<br/>pg_stat_statements · hypopg")]

工具

工具	功能	安全说明
top_slow_queries	从 pg_stat_statements 获取开销最高的语句：规范化文本、调用次数、总计/平均/最大毫秒数、行数、缓存命中率、占总耗时的比例。按 total_time、mean_time、calls 或 rows 排序。	使用固定 SQL；排序列从枚举中选择。排除服务自身的语句。
explain_query	汇总执行计划：开销最高的 5 个节点、大型表上的顺序扫描、行数估算误差达 10 倍的情况、磁盘排序/哈希溢出，以及迭代次数很多的嵌套循环。analyze=true 会添加实际耗时。$1 占位符会使用通用计划。	首先进行 SQL 防护。ANALYZE 在只读事务中运行，之后事务会回滚，并在超时后取消。
suggest_indexes	根据经过筛选的顺序扫描、连接键和 ORDER BY ... LIMIT，提供 CREATE INDEX CONCURRENTLY 候选项及各自的理由；安装了 hypopg 时，会使用假设索引进行验证。	绝不执行。 假设索引会在保存点中重置，因此不会泄漏到其他调用中。
unused_indexes	列出 idx_scan = 0 的索引，按大小从大到小排列，排除主键、唯一索引和约束索引；包含大小、定义以及供审核的 DROP INDEX CONCURRENTLY 语句。	不会删除任何内容。报告统计信息上次重置的时间。
table_health	提供实时/死元组、死元组比例、上次（自动）清理/分析的时间、分析后更改的行数、顺序扫描与索引扫描次数、大小，并附带通俗易懂的警告。	仅读取系统目录。
blocking_sessions	通过 pg_blocking_pids 获取被阻塞/阻塞会话对，包括截断后的查询文本、等待时间、锁类型/模式/表、阻塞者状态和根阻塞者。	绝不取消或终止任何操作。
slow_functions	从 pg_stat_user_functions 获取用户定义的函数（PL/pgSQL、SQL 等），按总耗时、自身耗时、平均耗时或调用次数排序，并附带语言和易变性信息。	仅读取系统目录。需要启用 track_functions。
explain_function	查看查询调用的函数内部：每个函数的耗时、函数内部执行的所有语句及其调用次数、耗时和自身的分析计划，以及诸如函数内部存在顺序扫描、每行都调用一次函数，或只读函数被标记为 VOLATILE 等发现。包含函数源代码。	首先进行 SQL 防护；在与 explain_query 相同的只读事务中运行并回滚，因此写入数据的函数会执行失败。仅对此事务启用 auto_explain，并使用通用计划，以免显示数据值。

资源： pg://schema/overview（表、行数估算、大小、索引）和 pg://schema/{table}（列、带扫描次数的索引、标记出缺少索引的外键）。 提示词： diagnose_slow_database，一个引导式工作流：依次检查慢查询、解释执行计划、建议索引、检查表健康状况和函数，最后生成按优先级排序的摘要。

为什么要为数据库函数单独提供工具

EXPLAIN 会将函数调用显示为一个不透明表达式，并把耗时计入执行该表达式的计划节点。在演示目录页面中（包含 20 种产品，每种都调用 shop.product_rating），explain_query 会将 184 ms 的自身耗时归咎于 products 上的索引扫描，并且未发现其他严重问题。对同一查询使用完整演示数据运行 explain_function：

shop.product_rating          plpgsql  20 calls  99% of execution time
  SELECT avg(rating) FROM shop.reviews WHERE product_id = p_product_id
                             20 calls  175 ms  96% of execution time
HIGH    FUNCTION_DOMINATES_QUERY  shop.product_rating takes 99% of the query's execution time
HIGH    FUNCTION_CALLED_PER_ROW   ran 20 times for one query (9.0 ms per call)
HIGH    SEQ_SCAN_ON_LARGE_TABLE   inside shop.product_rating: Seq Scan on shop.reviews
                                  reads all ~250k rows to apply filter (reviews.product_id = $1)

工作原理（ADR 0006）：查询会运行一次，仅对此事务启用 auto_explain，因此每条嵌套语句的计划都会作为通知返回；每个函数的耗时来自 pg_stat_xact_user_functions，每条语句的调用次数来自 pg_stat_statements，两者都通过运行前后的差值计算。

安全模型

助手与写操作之间有五层彼此独立的防护（详情见 ADR 0001）：

1. SQL 防护： 必须且只能是一条普通的 SELECT。它会经过符合 PostgreSQL 语法的词法分析器 和 JSqlParser 检查，并且两者的判断必须一致。会拦截 DML/DDL、多条语句、修改数据的 CTE、 SELECT INTO、FOR UPDATE/SHARE、COPY、DO 和危险函数（pg_sleep*、*_file、 lo_*、dblink*、set_config、pg_terminate_backend、query_to_xml 等）。遇到异常时会拒绝执行。
2. 只读角色： mcp_readonly 拥有 SELECT 以及 pg_read_all_stats/pg_monitor 权限，并在角色上设置 default_transaction_read_only = on 和 statement_timeout = 5s。
3. 只读连接： Hikari 设置 readOnly=true，并使用只读会话默认设置。
4. 每次调用使用一个回滚的只读事务： 在本地设置 statement_timeout/lock_timeout， 强制启用 standard_conforming_strings，并始终回滚。
5. 行数上限： 限制每个结果的行数（默认 200 行）。

此外，还包括：Bearer API 密钥（使用恒定时间比较；只有 /actuator/health 公开），每次调用写入一条 JSON 审计日志（SQL 以 SHA-256 哈希值和 80 个字符的预览形式存储），不包含堆栈跟踪或连接详情的错误消息，以及指标 mcp.tool.duration 和 mcp.guard.rejections。

快速开始

要求：Docker。启动方式有两种：

• 在你的数据库上使用：拉取已发布的镜像。无需克隆或构建。
• 试用演示商店：克隆仓库，获取一个包含真实问题的预置数据库，用于诊断。

无论选择哪种方式，最后都要连接你的 AI 客户端。

在你的数据库上使用

1. 拉取镜像（镜像在 GHCR 上公开发布，支持 amd64 和 arm64）：

docker pull ghcr.io/gouranshul/pg-perf-mcp:0.1.0

2. 准备数据库（仅需一次）。 创建一个类似 mcp_readonly 的只读角色（参见 docker/init.sql），并确保 pg_stat_statements 已加入 shared_preload_libraries。建议使用副本或预发布数据库：analyze=true 和 explain_function 会执行查询。对于函数工具，还需要 启用函数跟踪。

3. 运行服务。 将设置放入文件中，避免密钥留在 Shell 历史记录里：

cat > pg-perf.env <<'EOF'
PGPERF_DB_URL=jdbc:postgresql://host.docker.internal:5432/mydb
PGPERF_DB_USER=mcp_readonly
PGPERF_DB_PASSWORD=<the role's password>
MCP_API_KEY=<32+ random characters>
EOF

docker run --rm --env-file pg-perf.env -p 127.0.0.1:8080:8080 ghcr.io/gouranshul/pg-perf-mcp:0.1.0

host.docker.internal 可连接到你机器上的数据库（Docker Desktop；在 Linux 上添加 --add-host=host.docker.internal:host-gateway）。现在 MCP 端点为 http://localhost:8080/mcp。

4. 连接你的 AI 客户端： 参见下文。

试用演示商店

要从源代码构建和测试，还需要 Java 25。

git clone https://github.com/gouranshul/pg-perf-mcp && cd pg-perf-mcp
cp .env.example .env        # then replace the placeholder secrets
docker compose up --build   # Postgres 18 + seeded demo shop (~4.5M rows) + the MCP server on :8080

生成一些慢查询统计信息（可选，但这样 top_slow_queries 会更有用）：

./demo/workload.sh          # 60s of deliberately bad queries via pgbench
./demo/lock-scenario.sh     # holds a row lock for 2 minutes so blocking_sessions has something to show
./demo/smoke-test.sh        # curl-based end-to-end check of health, auth and two tool calls

从 Claude Code 连接

claude mcp add --transport http pg-perf http://localhost:8080/mcp --header "Authorization: Bearer $MCP_API_KEY"

在 PowerShell 中，先设置密钥，否则请求发出的请求头会为空，服务将返回 401：

$env:MCP_API_KEY = "<your MCP_API_KEY>"
claude mcp add --transport http pg-perf http://localhost:8080/mcp --header "Authorization: Bearer $env:MCP_API_KEY"

也可以将其安装为 Claude Code 插件。Claude Code 会询问端点（默认值为 http://localhost:8080/mcp）和你的 MCP_API_KEY，并将其保存在安全存储中：

/plugin install pg-perf --marketplace gouranshul/pg-perf-mcp

在 2.1.275 之前的 Claude Code 版本中，请先添加市场： /plugin marketplace add gouranshul/pg-perf-mcp，然后运行 /plugin install pg-perf@pg-perf-mcp。

然后询问：“使用 pg-perf 找出商店数据库变慢的原因”，或运行 diagnose_slow_database 提示词。

从 Claude Desktop 连接

Claude Desktop 会启动 stdio 服务，因此请使用 mcp-remote 桥接程序 （claude_desktop_config.json）：

{
  "mcpServers": {
    "pg-perf": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "http://localhost:8080/mcp", "--header", "Authorization:${AUTH_HEADER}"],
      "env": { "AUTH_HEADER": "Bearer <your MCP_API_KEY>" }
    }
  }
}

从 Cursor 连接

~/.cursor/mcp.json（或项目中的 .cursor/mcp.json）：

{
  "mcpServers": {
    "pg-perf": {
      "url": "http://localhost:8080/mcp",
      "headers": { "Authorization": "Bearer <your MCP_API_KEY>" }
    }
  }
}

从 VS Code 连接（Copilot Agent 模式）

.vscode/mcp.json。VS Code 会提示输入一次密钥，并将其安全保存：

{
  "inputs": [
    { "type": "promptString", "id": "pg-perf-key", "description": "pg-perf MCP_API_KEY", "password": true }
  ],
  "servers": {
    "pg-perf": {
      "type": "http",
      "url": "http://localhost:8080/mcp",
      "headers": { "Authorization": "Bearer ${input:pg-perf-key}" }
    }
  }
}

启用函数工具（可选）

slow_functions 和 explain_function 需要在你自己的数据库上额外设置两项。演示商店已完成这些设置，其他工具无需这些设置即可使用：

-- postgresql.conf: track_functions = 'pl' (or 'all' to include SQL functions), then reload.
-- Load auto_explain for this role only (no restart needed); it stays inactive until a call enables it:
ALTER ROLE mcp_readonly SET session_preload_libraries = 'auto_explain';
-- PostgreSQL 15+: let the role change only these settings, only for its own transactions:
GRANT SET ON PARAMETER auto_explain.log_min_duration, auto_explain.log_analyze,
    auto_explain.log_nested_statements, auto_explain.log_format, auto_explain.log_level,
    auto_explain.log_verbose, auto_explain.log_parameter_max_length TO mcp_readonly;

如果未完成这些设置，explain_function 仍会报告可获取的信息，并在 notes 中说明缺少什么。

配置

变量 / 属性	默认值	用途
MCP_API_KEY	(required)	客户端必须发送的 Bearer 令牌；至少 16 个字符。没有它，服务将无法启动。
PGPERF_DB_URL	jdbc:postgresql://localhost:5432/shop	JDBC URL
PGPERF_DB_USER / PGPERF_DB_PASSWORD	mcp_readonly / (empty)	数据库凭据（请使用只读角色）
pgperf.query.statement-timeout	5s	每次调用的 statement_timeout 和 lock_timeout
pgperf.query.max-rows	200	每次查询的行数上限
pgperf.guard.max-sql-length	20000	接受的 SQL 最大长度
pgperf.functions.nested-plan-threshold	1ms	仅当函数内语句的执行时间至少达到此值时，explain_function 才会保留其执行计划（但仍会统计每次执行）
OTEL_TRACING_ENABLED	false	通过 OTLP/HTTP 导出跟踪数据
OTEL_EXPORTER_OTLP_ENDPOINT	http://localhost:4318/v1/traces	OTLP 跟踪数据端点
OTEL_TRACING_SAMPLING_PROBABILITY	1.0	跟踪采样比例

也可以将属性设为环境变量（PGPERF_QUERY_STATEMENT_TIMEOUT=3s，……）。 指标位于 /actuator/prometheus（需要 API 密钥）。

构建和测试

./mvnw verify

此命令会运行单元测试、针对 PostgreSQL 18 的 Testcontainers 集成测试（使用与 compose 相同的镜像、初始化脚本和只读角色；需要 Docker）、通过 HTTP 使用 MCP Java SDK 客户端进行的端到端测试，以及 JaCoCo 门槛检查（guard 和 analysis 包的行覆盖率为 80%）。

设计决策

• 0001：从设计上实现只读，并进行纵深防御
• 0002：Streamable HTTP 传输
• 0003：使用摘要计划而非原始 EXPLAIN
• 0004：提供索引建议，但绝不执行
• 0005：平台版本和 API 差异
• 0006：检查数据库函数内部

路线图

• 根据 MCP 授权规范实现 OAuth 2.1 资源服务（使用每位用户的身份，而非共享密钥）
• 支持多个命名数据库目标，并优先使用只读副本
• 提供工作负载级索引建议（综合评估所有高频查询的候选索引，而非逐个评估）
• 接入 auto_explain 日志 / pg_stat_kcache，为已在生产环境运行过的查询获取执行计划
• 可选的原始计划输出和每个工具的速率限制
• 使用 MCP elicitation 确认对开销较大的查询设置 analyze=true

许可证

MIT © 2026 Anshul Gour