PostgreSQL Performance (read-only) MCP server

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.

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.

Why

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 pgstatstatements, 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.

Demo

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) ...

Architecture

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")]

Tools

Shortened. The full README is on GitHub.