pg-perf-mcp
Provides read-only PostgreSQL performance diagnostics, including EXPLAIN ANALYZE plan analysis, slow-query inspection via pg_stat_statements, table health checks, unused and redundant index detection, active query monitoring, and database overview metrics.
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., "@pg-perf-mcp find the slowest queries and tell me what to fix"
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.
pg-perf-mcp
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 |
| Runs |
| Top queries from |
| Seq-scan vs index-scan ratio, dead tuples, last vacuum/analyze per table |
| Indexes never scanned, largest first (excludes unique and constraint indexes) |
| Indexes whose columns are a prefix of another index on the same table |
| What is running now, for how long, and what it is waiting on |
| 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 |
| A sequential scan discards 10k+ rows and 10x more than it keeps | An index on the filter columns, written out as DDL |
| 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) |
| Actual rows differ from the estimate by 10x or more |
|
| A sort used disk (summed across parallel workers) | A sized |
| A hash table split into batches |
|
| The inner side of a nested loop is a scan repeated 1000+ times | An index on the inner join column |
| An index-only scan still visits the heap for 20%+ of rows |
|
| Most blocks came from disk rather than shared buffers | Re-run warm; check working set vs |
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 |
| Partial index on | 21.4 ms | 3.4 ms |
Customer's orders |
| Index on | 34.1 ms | 0.7 ms |
Country + city count |
|
| estimate 249 rows | estimate matches |
Sort 2M rows |
| 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:
Every session sets
default_transaction_read_only=on, so Postgres itself rejects writes, including data-modifying CTEs.EXPLAIN ANALYZEruns inside a transaction that is always rolled back.statement_timeout(default 15 s) bounds every statement.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, soSELECT ';'is still allowed.For production, connect as a role that has only
pg_monitorandpg_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 syncClaude Code
claude mcp add pg-perf --env PG_PERF_DSN="postgresql://readonly@localhost/mydb" \
-- uv --directory /path/to/pg-perf-mcp run pg-perf-mcpClaude 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 checkThe 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
hypopgsupport, to check that a suggested index would actually be used before anyone builds itPlan diffing: compare a query's plan before and after a change
License
MIT
Available Tools
7 toolsactive_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
Scored across 7 tools
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.
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.
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.
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
Hosted MCP server for PostgreSQL diagnostics: slow queries, missing indexes, connection pressure.
DBRE-grade SQL analysis inside any MCP client. No connection. No install. Paste a query.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Related MCP Servers
- AlicenseAqualityBmaintenanceRead-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.6MIT
- FlicenseNot gradedqualityAmaintenanceEnables 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.-
- FlicenseNot gradedqualityAmaintenanceEnables 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.-
- FlicenseNot gradedqualityAmaintenanceEnables 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.-