PostgreSQL Performance (read-only)

pg-perf-mcp

AI アシスタントが PostgreSQL のパフォーマンス問題を安全に診断できる MCP サーバーです：遅いクエリ、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	生存タプル／削除済みタプル、削除済みタプルの比率、最後の（自動）VACUUM／ANALYZE、ANALYZE 以降に変更された行数、シーケンシャルスキャンとインデックススキャン、サイズを、平易な警告とともに表示します。	カタログの読み取りのみです。
blocking_sessions	pg_blocking_pids を使ってブロックされているセッションとブロックしているセッションのペアを表示します。クエリテキスト（省略あり）、待機時間、ロックの種類／モード／テーブル、ブロッカーの状態、ルートブロッカーが含まれます。	何もキャンセルまたは終了しません。
slow_functions	pg_stat_user_functions からユーザー定義関数（PL/pgSQL、SQL など）を、合計時間、自己時間、平均時間または呼び出し回数で並べて表示します。言語と揮発性も含まれます。	カタログの読み取りのみです。track_functions が必要です。
explain_function	クエリが呼び出す関数の内部を調べます。関数ごとの時間、関数内で実行されたすべてのステートメント（呼び出し回数、時間、独自の分析済み実行計画を含む）、関数内のシーケンシャルスキャン、行ごとに 1 回呼び出される関数、読み取り専用なのに VOLATILE のままになっている関数などの検出結果を表示します。関数のソースも含まれます。	最初に SQL ガードを実行します。explain_query と同じロールバックされる読み取り専用トランザクション内で実行されるため、書き込みを行う関数は失敗します。auto_explain はそのトランザクション内でのみ有効になり、データ値が表示されないように汎用プランが使われます。

リソース： pg://schema/overview（テーブル、行数の見積もり、サイズ、インデックス）と pg://schema/{table}（列、スキャン回数付きのインデックス、インデックスのない外部キーを明示）。 プロンプト： diagnose_slow_database。遅いクエリ、実行計画の確認、インデックスの提案、 テーブルの状態、関数の順に進み、優先順位を付けた要約を作成するガイド付きワークフローです。

データベース関数に専用ツールが必要な理由

EXPLAIN では関数呼び出しが不透明な単一の式として表示され、その時間は評価する実行計画ノードに計上されます。デモ用カタログページ（20 個の商品がそれぞれ shop.product_rating を呼び出す場合）では、explain_query は products のインデックススキャン（自己時間 184 ms）が原因だと判断し、ほかに深刻な問題は見つけません。同じクエリを、デモデータ全体に対して 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 を有効にして 1 回実行されます。そのため、各ネストされたステートメントの実行計画が NOTICE として返されます。関数ごとの時間は pg_stat_xact_user_functions から、ステートメントごとの呼び出し回数は pg_stat_statements から取得し、どちらも実行前後の差分として算出します。

セキュリティモデル

アシスタントが書き込みを行うまでには、互いに独立した5つのレイヤーがあります（詳細は 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. 呼び出しごとに1つのロールバックされる読み取り専用トランザクション： statement_timeout／lock_timeoutを ローカルに設定し、standard_conforming_stringsを強制的にオンにして、必ずロールバックします。
5. 行数上限： すべての結果に上限を設けます（デフォルトは200行）。

これらのレイヤーに加えて、ベアラー API キー（定時間比較を使用し、/actuator/healthのみ公開）、呼び出しごとの JSON 監査ログ（SQL は SHA-256 ハッシュと80文字のプレビューとして保存）、スタックトレースや接続情報を含まない エラーメッセージ、メトリクスmcp.tool.durationとmcp.guard.rejectionsを備えています。

クイックスタート

必要なもの：Docker。起動方法は2つあります。

• 自分のデータベースで使う： 公開済みイメージを取得します。クローンもビルドも不要です。
• デモショップを試す： リポジトリをクローンし、診断に使える現実的な問題を含むシード済みデータベースを用意します。

どちらの場合も、最後に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. 起動します。 シークレットがシェルの履歴に残らないよう、設定をファイルに記述します。

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

Claude Code のバージョンが2.1.275より前の場合は、先にマーケットプレイスを追加してください： /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 エージェントモード）

.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 には、ご自身のデータベースに追加の設定が2つ必要です。デモ用ショップではすでに設定済みで、その他のすべてのツールはこれらの設定なしで動作します。

-- 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)	クライアントが送信する必要があるベアラートークンです。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が必要です）、MCP Java SDKクライアントを使用したHTTP経由のエンドツーエンドテスト、およびJaCoCoのゲート（guard パッケージと analysis パッケージで行カバレッジ80％）が実行されます。

設計上の決定

• 0001: 設計上読み取り専用とし、多層的に強制する
• 0002: ストリーミング可能なHTTPトランスポート
• 0003: 生のEXPLAINではなく、要約した実行計画
• 0004: インデックスを提案し、実行はしない
• 0005: プラットフォームのバージョンとAPIの違い
• 0006: データベース関数の内部を調べる

ロードマップ

• MCP認可仕様に準拠したOAuth 2.1リソースサーバー（共有キーではなく、ユーザーごとのIDを使用）
• 名前付きデータベースターゲットの複数設定（読み取りレプリカを優先）
• ワークロードレベルのインデックスアドバイス（候補を個別ではなく、すべての主要クエリにわたって評価）
• 本番環境ですでに実行されたクエリの実行計画を対象とする、auto_explain ログ／pg_stat_kcache の取り込み
• 生の実行計画の出力と、ツールごとのレート制限（いずれも任意）
• 高コストなクエリで analyze=true を確認するためのMCPエリシテーション

ライセンス

MIT © 2026 Anshul Gour