pg-perf-mcp
An MCP server that lets AI assistants diagnose PostgreSQL performance problems safely: slow queries, EXPLAIN plans, index suggestions, table health and lock contention. It is read-only by design.
接入你的智能体
發布者自己的指令。不是 PlugMCP 端點。
用我們的金鑰與日誌託管一份副本。
docker run -i --rm ghcr.io/gouranshul/pg-perf-mcp:0.1.0Claude Code 沒有本機指令的安裝連結。複製下方指令並在終端機中執行。
PGPERF_DB_URL=<value> PGPERF_DB_USER=<value> PGPERF_DB_PASSWORD=<secret> MCP_API_KEY=<secret> claude mcp add io.github.gouranshul-pg-perf-mcp -- docker run -i --rm ghcr.io/gouranshul/pg-perf-mcp:0.1.0在終端機中執行。
{ "mcpServers": { "io.github.gouranshul-pg-perf-mcp": { "command": "docker", "args": [ "run", "-i", "--rm", "ghcr.io/gouranshul/pg-perf-mcp:0.1.0" ], "env": { "PGPERF_DB_URL": "<value>", "PGPERF_DB_USER": "<value>", "PGPERF_DB_PASSWORD": "<secret>", "MCP_API_KEY": "<secret>" } } }}claude_desktop_config.json
遠端 URL 還是本機指令? 兩種安裝方式的差異
來自發布者
An MCP server that lets AI assistants diagnose PostgreSQL performance problems safely: slow queries, EXPLAIN plans, index suggestions, table health and lock contention. It is read-only by design.
Read-only PostgreSQL performance diagnostics: slow queries, EXPLAIN, index advice, locks, functions

A real Claude Code session against the demo shop, sped up. First it finds which database functions are slow and why. Then it explains a slow catalog query and estimates the fix with a hypothetical index. Asked to create the index, it says it can't: every tool is read-only.
When a page is slow, developers paste a query into a chat and ask "why is this slow?". The assistant then guesses, because it cannot see the plan, the table sizes, the existing indexes or which queries actually dominate the load.
pg-perf-mcp gives the assistant real numbers from pg_stat_statements, EXPLAIN and the
catalogs, through a narrow, audited, read-only interface. It cannot change data, cannot run DDL,
and never creates the indexes it recommends.
A condensed, illustrative session against the demo shop (docker compose up, then
./demo/workload.sh). Exact numbers vary from run to run:
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")]
Resources: pg://schema/overview (tables, row estimates, sizes, indexes) and
pg://schema/{table} (columns, indexes with scan counts, foreign keys flagged when unindexed).
Prompt: diagnose_slow_database, a guided workflow: slow queries, then explain, suggest
indexes, table health, functions, then a prioritized summary.
EXPLAIN shows a function call as one opaque expression, and charges its time to whichever plan
node evaluates it. On the demo catalog page (20 products, each calling shop.product_rating),
explain_query blames the index scan on products (184 ms self time) and finds nothing else
serious. explain_function on the same query, against the full demo data:
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)
How it works (ADR 0006): the query runs once
with auto_explain enabled for that transaction only, so the plan of each nested statement comes
back as a notice; per-function time comes from pg_stat_xact_user_functions and per-statement
calls from pg_stat_statements, both as before/after differences.
Five independent layers stand between the assistant and a write (details in ADR 0001):
SELECT. It is checked by a PostgreSQL-faithful lexer and
JSqlParser, and both must agree. Blocks DML/DDL, multiple statements, data-modifying CTEs,
SELECT INTO, FOR UPDATE/SHARE, COPY, DO, and dangerous functions (pg_sleep*, *_file,
lo_*, dblink*, set_config, pg_terminate_backend, query_to_xml, ...). It fails closed.mcp_readonly has SELECT plus pg_read_all_stats/pg_monitor, with
default_transaction_read_only = on and statement_timeout = 5s set on the role.readOnly=true and a read-only session default.statement_timeout/lock_timeout set
locally, standard_conforming_strings forced on, always rolled back.Around those layers: a bearer API key (constant-time comparison; only /actuator/health is
open), a JSON audit line per call (SQL stored as a SHA-256 hash plus an 80-character preview),
error messages with no stack traces or connection details, and metrics mcp.tool.duration and
mcp.guard.rejections.
Requirements: Docker. There are two ways to start:
Either way, finish with connecting your AI client.
1. Pull the image (public on GHCR, built for amd64 and arm64):
docker pull ghcr.io/gouranshul/pg-perf-mcp:0.1.0
2. Prepare the database (once). Create a read-only role like mcp_readonly (see
docker/init.sql) and make sure pg_stat_statements is in
shared_preload_libraries. Prefer a replica or staging database: analyze=true and
explain_function execute the query. For the function tools, also
enable function tracking.
3. Run it. Put the settings in a file so secrets stay out of your shell history:
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 reaches a database on your machine (Docker Desktop; on Linux add
--add-host=host.docker.internal:host-gateway). The MCP endpoint is now
http://localhost:8080/mcp.
4. Connect your AI client: see below.
To build and test from source you also need 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
Generate some slow-query statistics (optional, but makes top_slow_queries interesting):
./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 mcp add --transport http pg-perf http://localhost:8080/mcp --header "Authorization: Bearer $MCP_API_KEY"
In PowerShell, set the key first, or the header goes out empty and the server answers 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"
Or install it as a Claude Code plugin. Claude Code asks for the endpoint (default
http://localhost:8080/mcp) and your MCP_API_KEY, which it keeps in secure storage:
/plugin install pg-perf --marketplace gouranshul/pg-perf-mcp
On Claude Code versions before 2.1.275, add the marketplace first:
/plugin marketplace add gouranshul/pg-perf-mcp, then /plugin install pg-perf@pg-perf-mcp.
Then ask: "Use pg-perf to find out why the shop database is slow", or run the
diagnose_slow_database prompt.
Claude Desktop launches stdio servers, so use the mcp-remote bridge
(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/mcp.json (or .cursor/mcp.json in a project):
{
"mcpServers": {
"pg-perf": {
"url": "http://localhost:8080/mcp",
"headers": { "Authorization": "Bearer <your MCP_API_KEY>" }
}
}
}
.vscode/mcp.json. VS Code prompts for the key once and stores it securely:
{
"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 and explain_function need two extra settings on your own database. The demo
shop already has them, and every other tool works without them:
-- 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;
Without these, explain_function still reports what it can and says in notes what is missing.
Properties can also be set as environment variables (PGPERF_QUERY_STATEMENT_TIMEOUT=3s, ...).
Metrics are at /actuator/prometheus (requires the API key).
./mvnw verify
This runs unit tests, Testcontainers integration tests against PostgreSQL 18 (same image, init
scripts and read-only role as compose; needs Docker), an end-to-end test with the MCP Java SDK
client over HTTP, and a JaCoCo gate (80% lines on the guard and analysis packages).
auto_explain log / pg_stat_kcache ingestion for plans of queries that already ran in productionanalyze=true on expensive queriesMIT © 2026 Anshul Gour
Read-only PostgreSQL performance diagnostics: slow queries, EXPLAIN, index advice, locks, functions
{ "mcpServers": { "io.github.gouranshul-pg-perf-mcp": { "command": "docker", "args": [ "run", "-i", "--rm", "ghcr.io/gouranshul/pg-perf-mcp:0.1.0" ], "env": { "PGPERF_DB_URL": "<value>", "PGPERF_DB_USER": "<value>", "PGPERF_DB_PASSWORD": "<secret>", "MCP_API_KEY": "<secret>" } } }}工具
尚無工具清單。遠端伺服器會在首次健康檢查後取得工具清單。
常見問題
在本頁複製適用於 Cursor、Claude Desktop、Claude Code 或 VS Code 的 stdio 設定。它使用發布者的安裝方式,而不是 PlugMCP 代理伺服器。
發布者列出了以下環境變數:PGPERF_DB_URL, PGPERF_DB_USER, PGPERF_DB_PASSWORD, MCP_API_KEY。
PostgreSQL Performance (read Only) 透過 stdio 在本機執行。
上次檢查時間:2026-11-09。 Read-only PostgreSQL performance diagnostics: slow queries, EXPLAIN, index advice, locks, functions