Skip to main content
Glama
pg-perf-mcp

pg-perf-mcp

by dixitgurv

pg-perf-mcp

CI

An MCP server that lets an AI agent (Claude, or any MCP client) diagnose PostgreSQL performance the way a DBA would: read the plan, find where the time actually went, and propose a specific fix.

It is read-only by design. The agent can look at anything and change nothing.

You:     orders lookups by status got slow, can you check?
Agent:   → explain_query("SELECT id, customer_id, total_cents FROM orders WHERE status = 'disputed'")

         The query takes 21 ms because a sequential scan reads 1,000,000 rows and throws
         away 995,001 of them. An index on status fixes it; since 'shipped' is 90% of the
         table, a partial index keeps it small:

           CREATE INDEX CONCURRENTLY ON orders (status) WHERE status <> 'shipped';

What the agent gets

Tool

What it answers

explain_query

Runs EXPLAIN (ANALYZE, BUFFERS) on one query and returns the nodes where time was spent plus rule-based findings with fixes

slow_queries

Top queries from pg_stat_statements by total time, mean time, calls or disk reads

table_health

Seq-scan vs index-scan ratio, dead tuples, last vacuum/analyze per table

unused_indexes

Indexes never scanned, largest first (excludes unique and constraint indexes)

redundant_indexes

Indexes whose columns are a prefix of another index on the same table

active_queries

What is running now, for how long, and what it is waiting on

database_overview

Cache hit rate, temp file usage, deadlocks, memory settings, connection count

Related MCP server: MCP-PostgreSQL-Ops

The plan analyzer

The interesting part is plan_analyzer.py. It walks the JSON plan and applies rules that encode what an experienced DBA looks for:

Rule

Fires when

Suggests

seq_scan_filter_waste

A sequential scan discards 10k+ rows and 10x more than it keeps

An index on the filter columns, written out as DDL

join_scans_whole_table

A hash/merge join reads 100k+ rows of a table to keep under 1%

An index on that table's join key (usually an unindexed foreign key)

row_misestimate

Actual rows differ from the estimate by 10x or more

ANALYZE, then CREATE STATISTICS on the correlated columns

sort_spilled_to_disk

A sort used disk (summed across parallel workers)

A sized work_mem, or an index / LIMIT when the sort is too big for memory

hash_multi_batch

A hash table split into batches

work_mem / hash_mem_multiplier, or a smaller build side

nested_loop_repeated_scan

The inner side of a nested loop is a scan repeated 1000+ times

An index on the inner join column

index_only_scan_heap_fetches

An index-only scan still visits the heap for 20%+ of rows

VACUUM to refresh the visibility map

cold_cache_reads

Most blocks came from disk rather than shared buffers

Re-run warm; check working set vs shared_buffers

The rules make the decisions, and the model on the other end explains them and talks them through with you. The model never decides what is wrong on its own, so the same plan always gives the same findings, and every rule has a unit test.

It also reports exclusive time per node (a node's time minus its children's), which is what tells you where the time went. Under a Gather, worker loops run concurrently, so their time is not multiplied by loop count. Getting that wrong is a common mistake and makes parallel scans look several times slower than they are.

Results on the demo database

demo/seed.sql builds 1M orders, 2M order items and 100k customers, with problems planted on purpose. I ran each query in demo/queries.sql, applied the fix the tool suggested, and ran it again (median of 5, PostgreSQL 14, M1 laptop):

Query

Finding

Fix applied

Before

After

Orders by status

seq_scan_filter_waste

Partial index on status

21.4 ms

3.4 ms

Customer's orders

join_scans_whole_table

Index on orders.customer_id

34.1 ms

0.7 ms

Country + city count

row_misestimate (20x under)

CREATE STATISTICS ON country, city

estimate 249 rows

estimate matches

Sort 2M rows

sort_spilled_to_disk

See note

541 ms

n/a

The sort is the honest case. It spills 73 MB, and holding it in memory took 205 MB of work_mem, which only brought it to 591 ms. The tool says so: past a point, the fix is to stop sorting that much (index or LIMIT), not to throw memory at it.

Safety

An agent with database access needs guard rails that don't depend on the agent behaving:

  1. Every session sets default_transaction_read_only=on, so Postgres itself rejects writes, including data-modifying CTEs.

  2. EXPLAIN ANALYZE runs inside a transaction that is always rolled back.

  3. statement_timeout (default 15 s) bounds every statement.

  4. A static check rejects multi-statement input and functions that have side effects even in a read-only transaction (pg_terminate_backend, set_config, pg_sleep, dblink, and so on). String literals and comments are stripped first, so SELECT ';' is still allowed.

  5. For production, connect as a role that has only pg_monitor and pg_read_all_data.

The integration tests run these refusals against a real server.

Setup

Requires Python 3.12+ and uv. slow_queries needs the pg_stat_statements extension.

git clone https://github.com/dixitgurv/pg-perf-mcp && cd pg-perf-mcp
uv sync

Claude Code

claude mcp add pg-perf --env PG_PERF_DSN="postgresql://readonly@localhost/mydb" \
  -- uv --directory /path/to/pg-perf-mcp run pg-perf-mcp

Claude Desktop (claude_desktop_config.json)

{
  "mcpServers": {
    "pg-perf": {
      "command": "uv",
      "args": ["--directory", "/path/to/pg-perf-mcp", "run", "pg-perf-mcp"],
      "env": { "PG_PERF_DSN": "postgresql://readonly@localhost/mydb" }
    }
  }
}

A production-style read-only role:

CREATE ROLE perf_reader LOGIN PASSWORD '...';
GRANT pg_monitor, pg_read_all_data TO perf_reader;   -- PostgreSQL 14+

Development

uv run pytest        # unit tests + integration tests against a throwaway cluster
uv run ruff check

The integration suite starts a temporary PostgreSQL cluster with the local initdb/pg_ctl, loads the demo data and runs every tool through a real MCP client in-process. Set PG_PERF_TEST_DSN to use an existing server instead, which is how CI runs it.

See docs/HOW_IT_WORKS.md for the design notes.

Roadmap

  • Oracle: parse AWR reports (top SQL, wait events, load profile) with the same findings model

  • hypopg support, to check that a suggested index would actually be used before anyone builds it

  • Plan diffing: compare a query's plan before and after a change

License

MIT

Available Tools

7 tools
active_queriesB

Currently running queries with run time, transaction age and wait events.

Inputs: limit. Returns: result

database_overviewC

Cache hit rate, temp file usage, deadlocks, key memory settings and connection count.

Inputs: no named parameters. Returns: result

explain_queryA

Run EXPLAIN (ANALYZE, BUFFERS) on one SELECT query and diagnose the plan.

The query runs in a read-only transaction that is rolled back. Returns total time,
the nodes where time was actually spent, and findings such as wasteful sequential
scans, row misestimates, sorts spilling to disk and repeated nested-loop scans,
each with a suggested fix.

Args:
    sql: A single SELECT / WITH query.
    analyze_query: False shows the estimated plan without executing the query.
    timeout_ms: Abort the query after this many milliseconds.

Inputs: sql (required), timeout_ms, analyze_query. Returns: result

redundant_indexesB

Indexes whose columns are a leading prefix of another index on the same table.

Inputs: no named parameters. Returns: result

slow_queriesB

Top queries from pg_stat_statements: total time, mean time, call count or disk reads.

Inputs: limit, order_by. Returns: result

table_healthC

Largest tables with seq-vs-index scan ratio, dead tuple share and last vacuum/analyze.

Inputs: limit. Returns: result

unused_indexesB

Non-unique indexes never used since stats were last reset, largest first.

Inputs: limit. Returns: result

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    B3.4/5.0

    Scored across 7 tools

    Disambiguation5/5

    Each tool targets a clearly distinct object: a single query plan (explain_query), historical stats (slow_queries), live activity (active_queries), table-level stats (table_health), index hygiene (unused_indexes vs redundant_indexes, clearly separated by 'never used' vs 'prefix duplicate'), and cluster-level stats (database_overview). No two tools can plausibly be confused for the same task.

    Naming Consistency4/5

    All names are lowercase snake_case and uniformly descriptive, with no mixed conventions or stray prefixes. The only minor deviation is verb style: explain_query is verb_noun while the rest are adjective/noun phrases, but the pattern remains predictable.

    Tool Count5/5

    Seven tools is tightly scoped to the performance-diagnostics domain, and each one covers a distinct angle (plan, history, live, table, index, cluster). Nothing looks like filler or a duplicate entry point.

    Completeness4/5

    Covers the core diagnostic loop: plan analysis, slow-query history, live activity, table health, index waste and instance overview. Minor gaps remain, such as explicit lock/blocking-chain analysis, missing-index recommendations (only unused/redundant are surfaced), and no write/DML plan analysis, but agents can work around these.

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      B
      maintenance
      Read-only PostgreSQL performance auditing via MCP. Collects metrics from PostgreSQL system views, applies deterministic checks for index, query, vacuum, configuration, and connection issues, and returns structured findings for MCP clients.
      6
      MIT
    • F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to perform read-only PostgreSQL DBA work across versions 12–18, including performance analysis, slow query identification, bloat detection, lock and deadlock monitoring, autovacuum and schema inspection, and replication/WAL checks via natural language. Works with regular user permissions on RDS/Aurora without requiring any extensions, while optionally leveraging pg_stat_statements and pg_stat_monitor for deeper query analytics.
      -
    • F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to perform read-only PostgreSQL DBA operations and monitoring through natural language, including performance and slow-query analysis, bloat detection, lock/deadlock monitoring, autovacuum and schema inspection, and maintenance recommendations across PostgreSQL 12–18.
      -
    • F
      license
      Not graded
      quality
      A
      maintenance
      Enables AI assistants to perform read-only PostgreSQL database operations, monitoring, and management through natural language queries, including performance analysis, bloat detection, lock/deadlock monitoring, autovacuum inspection, and schema discovery across PostgreSQL 12–18.
      -