Provides read-only querying, schema exploration, execution plan inspection, and SQL diagnostics for PostgreSQL databases.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@PGScope MCP diagnose why this query on orders is slow and suggest an index"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
中文简介 · 面向 Agent 的 PostgreSQL 只读查询与诊断服务。通过本地 stdio MCP 查看表结构、查询数据、分析执行计划，以数据库证据支撑优化建议，并用可重复实验验证效果。
English · A read-only PostgreSQL MCP server for AI agents. Explore schemas, query data, inspect execution plans, and diagnose SQL with database evidence. Includes reproducible optimization benchmarks. Runs locally over stdio; no model API key required.
快速启动 · 工具接口 · 优化实验 · 架构设计 · 验收记录 · 参与开发
flowchart LR
Agent[Agent / MCP 客户端] <-->|本地 stdio| MCP[PGScope MCP]
MCP --> Guard[SQL AST 检查]
Guard <-->|只读事务| PG[(PostgreSQL 17)]
PG --> Evidence[表结构 + 执行计划]
Evidence --> Diagnosis[带证据的诊断与建议]
Diagnosis --> MCP服务内部不调用大模型，由接入的 Agent 组织工具调用。演示环境包含三张电商表、10 万条订单、两个优化实验和独立 MCP 测试客户端。
通过五个 MCP 工具完成「表结构 → 查询 → 执行计划 → 诊断」。
用 SQL AST 检查、只读事务、最小权限账号共同限制查询。
解释扫描、索引、基数估算和排序落盘，区分估算成本与实际耗时。
在 10 万订单上实测缺失索引和日期过滤改写，保存每次原始计划并核对查询结果一致。
依赖 官方 MCP Python SDK 和 SQLGlot，参考 DBHub 的数据库工具设计。原创边界与许可证见 THIRD_PARTY.md。
Related MCP server: General-Purpose MCP Database Server
实验 | 优化前中位数 | 优化后中位数 | 验证方式 |
高选择性 customer_id 查询：新增索引 | 4.788 ms | 0.069 ms | 完整结果一致，保留原始执行计划 |
日期过滤：函数改写为半开范围 | 5.751 ms | 0.307 ms | 完整结果一致，保留原始执行计划 |
每种查询预热 1 次、测量 5 次。以上为 PostgreSQL 17.11 的一次本地观测，性能取决于硬件、缓存与数据分布。查看 完整实验报告 或 自行复现。
需要 uv 和已启动的 Docker Desktop / Docker Engine。命令均在项目根目录执行。演示容器只绑定本机 127.0.0.1:55432，固定密码仅用于本地演示。
# 把运行时和缓存放在项目目录；不改变系统 Python。
$env:UV_CACHE_DIR = "$PWD/.cache/uv"
$env:UV_PYTHON_INSTALL_DIR = "$PWD/.tools/python"
uv sync --frozen
docker compose up -d --wait
$env:PGSCOPE_DSN = 'postgresql://pgscope_reader:pgscope_demo_readonly@127.0.0.1:55432/pgscope_demo'
uv run --frozen python -m scripts.smoke_mcp --output-json artifacts/mcp-smoke.jsonuv sync --frozen
docker compose up -d --wait
export PGSCOPE_DSN='postgresql://pgscope_reader:pgscope_demo_readonly@127.0.0.1:55432/pgscope_demo'
uv run --frozen python -m scripts.smoke_mcp --output-json artifacts/mcp-smoke.json看到五行 PASS 即表示独立客户端已通过 真实 stdio 子进程 调用了全部工具；不是函数内直接调用，也不是模拟数据库。该演示依赖内置 orders 表。
初始化得到 customers（5,000 行）、orders（100,000 行）、order_items（200,000 行）。初始化脚本仅在新数据卷首次启动时执行；普通 docker compose down 保留数据。
复制 客户端配置示例，把 /ABSOLUTE/PATH/TO/pgscope-mcp 改为本项目绝对路径，并按客户端支持的位置导入。Windows JSON 中可使用 F:/... 形式的路径。客户端需要能找到 uv；也可以把 command 改为 .venv 中 Python 的绝对路径，args 改为 ["-m", "pgscope_mcp"]。
配置只给出只读连接，不要放入演示管理员连接。示例采用常见的 mcpServers JSON 形态，具体导入位置依客户端而定。
使用 MCP Inspector 手动查看五个工具：
npx @modelcontextprotocol/inspector uv run --frozen pgscope-mcpInspector 需要 Node.js，首次使用会下载 Inspector。确保启动它的终端已设置 PGSCOPE_DSN。连接成功后可以用 Agent 演示提示词 展示真实工作流。
直接执行 uv run --frozen pgscope-mcp 会等待 MCP 输入，不会打开聊天界面。日志走 stderr，stdout 专用于协议通信。
工具 | 参数 | 主要返回 |
|
| 可访问表、注释、估算行数、大小、分页标记 |
|
| 字段与类型、主外键、索引及索引属性 |
|
| 列定义、行数组、耗时、截断标记 |
|
| 原始 JSON 计划、节点路径、估算或实测摘要 |
|
| 计划、六类诊断、证据、建议及可选索引 SQL |
输出使用 MCP structuredContent，同时附简短文字摘要。失败使用 isError=true 和 error.code/message/retryable；常见错误包括 QUERY_REJECTED、SCHEMA_DENIED、TABLE_NOT_FOUND、QUERY_TIMEOUT、ANALYZE_DISABLED 和 CONNECTION_FAILED。
结果保留重复列名，因此 rows 是数组而不是按列名覆盖的字典。日期为 ISO 字符串，Decimal 为精确字符串，二进制为 { "base64": "..." }。row_count 是已返回行数；看到 truncated=true 时不能把结果当完整数据集。无 ORDER BY 的行顺序不保证稳定。
规则 | 依据 |
| 投影包含 |
| SQL 中没有有效匹配条件的连接 |
| 大表、选择性过滤与实际规划的顺序扫描相结合 |
| 过滤条件对已有索引列使用函数或转换 |
| 实测每循环行数与估计显著偏离；避免提前停止带来的误报 |
| 实测排序节点明确使用磁盘 |
规则是待验证的诊断线索。小表顺序扫描可能是合理选择；cost 不是毫秒；估算计划无法证明实际耗时。候选索引只针对可以确定真实字段的简单单表过滤，复杂查询提供检查建议。
服务从环境变量读取配置，默认不自动加载 .env。如需配置文件，复制 .env.example 为 .env，用 uv run --env-file .env --frozen pgscope-mcp 显式加载。
环境变量 | 默认值 / 含义 |
| 必填，只读数据库连接串 |
|
|
|
|
|
|
|
|
|
|
未限定 schema 的表解析为允许列表中的第一个 schema；工具元数据参数默认仍为 public，其他 schema 应显式传入。查询限定为受支持的单条 SELECT 子集：JOIN、只读 CTE、集合查询、聚合和常见日期/数值/文本函数；函数允许清单位于 validation.py。
多语句、DML/DDL、写入 CTE、行锁、SELECT INTO、系统 schema、任意自定义函数/类型/操作符均被拒绝。首版只支持普通表和分区表，不支持视图及外部表。每次操作使用只读事务、独立连接和固定 search_path=pg_catalog，并在完成、错误或取消后关闭；最多同时处理四个数据库操作。查询默认 100 行，最大 1,000 行，JSON 响应上限 1 MiB，SQL 输入上限 64 KiB。
连接真实库时，请由 DBA 提供仅有目标业务表 SELECT 和对应 schema USAGE 的账号；服务拒绝超级用户及常见管理角色，但这不代替数据库授权。账号与允许 schema 都应最小化。连接具有恶意扩展、自定义类型或恶意 DBA 的数据库不属于本项目的隔离保证。
查询结果、计划及元数据会传给 MCP 客户端，其后是否发送模型由客户端控制。数据库文本和注释都是数据，不能当作 Agent 指令。服务不持久化真实查询结果，也不打印原始 SQL、连接凭据或数据到日志。
实验脚本仅接受 loopback 上、名称为 pgscope_demo 且具有专用 marker 的演示库。它使用独立管理员连接管理固定实验索引；MCP 不拥有写工具。
$env:PGSCOPE_DEMO_ADMIN_DSN = 'postgresql://postgres:pgscope_demo_admin@127.0.0.1:55432/pgscope_demo'
uv run --frozen python -m scripts.benchmark
Remove-Item Env:PGSCOPE_DEMO_ADMIN_DSNLinux/macOS：
PGSCOPE_DEMO_ADMIN_DSN='postgresql://postgres:pgscope_demo_admin@127.0.0.1:55432/pgscope_demo' \
uv run --frozen python -m scripts.benchmark两个实验分别是 customer_id 索引前后对比，以及已有日期索引时 date(created_at) 与半开区间过滤的对比。每组预热 1 次、实测 5 次，比较完整结果、耗时中位数和根节点缓冲区指标。脚本可重复执行，会重建固定实验场景的索引，不删除业务数据；实验结束后两个优化索引保留在演示库。
交付时已在本机真实运行并保存 中文实验报告 和 所有原始计划。这些是录制的本机测量结果，不能当作当前数据库状态或固定性能保证。
$env:PGSCOPE_TEST_DSN = $env:PGSCOPE_DSN
uv run --frozen pytest -q
uv run --frozen ruff check src tests scriptsLinux/macOS 使用 export PGSCOPE_TEST_DSN="$PGSCOPE_DSN"。不设置测试连接时，真实数据库测试会明确跳过；不能据此声称真实集成验收通过。离线逻辑测试可用 uv run --frozen pytest -q -m "not integration"，但其中 stdio 错误链路仍会尝试连接本机未开放端口。
CI 包含 Linux PostgreSQL + 五工具 stdio + 基准实验，以及 Windows 无数据库测试。配置位于 ci.yml，运行状态见 GitHub Actions。
src/pgscope_mcp/ 配置、SQL 检查、只读执行、诊断、MCP 协议边界
tests/ 单元测试、真实 PostgreSQL 测试、stdio 端到端测试
demo/ 确定性数据初始化及权限
scripts/ 独立 MCP 客户端与基准实验
artifacts/ 可阅读的真实演示结果
docs/ 架构、实现约定和面试讲解进一步阅读：架构与数据流、实现约定、面试讲解提纲、演示环境细节。
Docker 无法连接：先启动 Docker Desktop，并确认 Linux 容器引擎就绪；docker context ls 可检查当前上下文。
55432 被占用：修改 Compose 主机端口，并同步修改 DSN。
Windows 异步驱动报 Proactor 错误：使用项目提供的 pgscope-mcp 入口；它已选择兼容 Psycopg 的 Selector 事件循环。
无法访问 uv 缓存：使用 Windows 快速启动段中的项目内缓存设置。
实测被拒绝：重启服务时设置 PGSCOPE_ALLOW_ANALYZE=true，并在工具参数中显式传入 analyze=true。
停止演示：退出 Agent/Inspector，再执行 docker compose down，保留数据卷供下次使用。
项目采用 MIT 许可证。
查看业务表字段、类型、主外键、索引；不读取样本数据。
| Name | Required | Description | Default |
|---|---|---|---|
| table | Yes | ||
| schema | No | public |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
注解已声明readOnlyHint和destructiveHint均为假，描述在此基础上补充了'不读取样本数据'的具体限制，并列出所查看的元数据类型，提供了注解之外的上下文，且无矛盾。
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
描述仅两句，先说明核心功能，再补充限制，无冗余信息，结构紧凑且信息密度高。
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
对于简单只读工具，描述覆盖了主要功能与限制，但未提及返回格式或可选schema参数的使用，整体基本完整但仍有小缺口。
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
输入模式中参数无描述，且schema描述覆盖率为0%。描述未提及table和schema参数的含义或用法，未能补偿参数文档缺失，仅参数名可提供有限提示。
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
描述具体指明工具用于查看业务表字段、类型、主外键和索引，并明确不读取样本数据，与query等数据查询工具形成清晰区分。动词'查看'和资源列表具体，能准确传达工具功能。
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
描述通过'不读取样本数据'暗示了与查询工具的差异，但未明确说明何时应使用本工具而非替代工具（如query或explain），缺乏显式的使用条件或排除指引。
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
结合 SQL、真实表索引和执行计划诊断；建议包含证据，不自动修改数据库。
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| analyze | No |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint=true and destructiveHint=false, and the description adds that it does not automatically modify the database and that suggestions include evidence. This adds useful behavioral context beyond the annotations, though the analyze parameter's behavior is not disclosed.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single compact sentence that front-loads the tool's purpose and then states its non-destructive, evidence-based behavior. Every clause contributes value with no redundancy.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a diagnostic tool with two parameters and no output schema, the description covers the core intent and safety posture but omits the meaning of 'analyze', the nature of the diagnostic output, and any qualifying conditions. It is minimally adequate but leaves meaningful gaps.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, so the description must carry the semantic weight. It mentions 'SQL' as the core input, but provides no added meaning for the 'sql' parameter and completely omits the 'analyze' boolean parameter, leaving its purpose ambiguous.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific verb ('diagnose') and resource (SQL combined with real table indexes and execution plans), and signals evidence-based suggestions. It is distinguishable from siblings like explain, though it does not name the distinction explicitly.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The context is implied: use this when you need to diagnose SQL using indexes and execution plans. However, there are no explicit when-to-use, when-not-to-use, or alternative tool comparisons, leaving the agent to infer routing from the sibling names.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
获取 JSON 执行计划。analyze=false 只估算，true 会执行且需服务端许可。
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| analyze | No |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
The description usefully discloses that analyze=false only estimates while analyze=true executes and requires server-side permission. However, this contradicts the readOnlyHint=true annotation: if analyze=true actually executes the provided SQL and the tool does not restrict sql to SELECT statements, it may modify state. This is an annotation contradiction.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
Two short sentences with no filler. The core function is front-loaded, and the second sentence efficiently adds the parameter behavior and permission caveat. Every clause earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
The description covers the critical execute/permission caveat and the JSON output type, and annotations address read-only/destructive concerns. However, with no output schema and no description of sql semantics, an agent lacks enough detail to confidently call the tool, especially for analyze=true cases.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, so the description must compensate. It explains the analyze parameter well (false=estimate, true=execute+permission) but says nothing about the sql parameter—its format, constraints, or whether only SELECT statements are allowed. Compensation is only partial.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific function—getting a JSON execution plan—using a clear verb and resource. It is distinguishable from siblings like query or diagnose, though the phrase 'true 会执行' introduces some ambiguity about whether the tool actually runs the SQL statement.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use explain instead of query, diagnose, list_tables, or describe_table. The description explains analyze modes but does not state prerequisites, exclusions, or decision criteria for selecting this tool.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
分页列出允许 schema 中可 SELECT 的普通业务表及统计信息。
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | ||
| offset | No | ||
| schema | No | public |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint=true and destructiveHint=false, covering the safety profile. The description adds useful context that only SELECT-able business tables in the permitted schema are shown, and mentions statistics. However, it doesn't disclose what the statistics contain or pagination behavior beyond the parameters.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
A single, front-loaded sentence with no filler. It efficiently packs the verb, resource, scope constraint, and pagination cue, though it is somewhat dense in Chinese.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Adequate for a list tool whose read-only nature is covered by annotations. However, with no output schema, the description omits what the statistics (统计信息) include and the shape of the returned items, leaving an agent guessing about the return format.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, so the description carries the burden. It adds meaning by mapping 分页列出 (paged listing) to limit/offset and 允许 schema to the schema parameter, which helps an agent infer parameter purpose beyond bare titles. Yet it doesn't elaborate on each parameter's role individually.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
States a specific verb (列出/list) and resource (普通业务表/ordinary business tables), and adds the scope constraint that only SELECT-able tables in the allowed schema are returned, plus statistics. This distinguishes it from describe_table (single table detail) and query (data retrieval), though it doesn't explicitly name siblings.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance on when to use this tool versus describe_table, query, or explain. The use case (discovering available tables before querying) is only implied by the description's mention of listing tables; no exclusions or alternatives are stated.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
执行受限的单条只读 SELECT；最多 1000 行 / 1 MiB，结果可能截断。
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| max_rows | No |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint=true and destructiveHint=false, so the description doesn't need to restate that. It adds concrete behavioral details: a row limit of 1000 and a size limit of 1 MiB, with potential truncation. This is valuable context for agents deciding whether results may be incomplete. Does not contradict annotations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single concise sentence, front-loaded with the core purpose ('执行受限的单条只读 SELECT') followed by the key constraints. No wasted words; it efficiently conveys the essential behavioral limits. Slightly terse but appropriate for its scope.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a tool with 2 simple parameters and annotations covering read-only/destructive hints, the description is fairly complete in covering its own behavior with limits and truncation. However, it lacks usage guidance and parameter-level semantics, and since there is no output schema, agents are left to infer the exact return format. Given the complexity, more detail on parameters and expected results would improve completeness.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, so the description must compensate. It does not explain the 'sql' parameter or the 'max_rows' parameter beyond hinting at a row limit indirectly. The description's limit reference could be tied to max_rows, but it doesn't explicitly state the purpose of each parameter. With simple parameter names, some meaning is implicit, but the description adds little value beyond schema types and defaults.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states it executes a single read-only SELECT query, giving a specific verb and resource. It distinguishes itself from sibling tools like list_tables and describe_table by focusing on query execution, though it doesn't explicitly mention alternatives. The purpose is unambiguous and not a tautology.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use this tool versus alternatives. Sibling tools exist (list_tables, describe_table, explain, diagnose), but the description gives no context for selection. It implies usage for running read-only queries, but there is no explicit when-to-use or when-not-to-use guidance.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
v0.1.0describe_tablediagnoseexplainlist_tablesqueryScored across 5 tools
Each tool serves a distinct purpose: listing tables, describing schema, querying data, explaining execution plans, and diagnosing issues. There is no overlap or ambiguity between them.
All tools use lowercase snake_case, and most follow a verb_noun pattern (list_tables, describe_table). The remaining tools (query, explain, diagnose) are single verbs, which is slightly less consistent but still clear and predictable.
With 5 tools, the server is well-scoped for its purpose of read-only PostgreSQL inspection and diagnosis. Each tool contributes to a complete workflow without redundancy.
The surface covers the full lifecycle of read-only inspection: discovering tables, understanding schema, querying data, analyzing execution plans, and diagnosing performance issues. No obvious gaps for the stated domain.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Generate, fix, explain and run read-only SQL on PostgreSQL, MySQL and SQL Server
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
