# PostgreSQL Performance (read-only) [Health: Active]

**Category:** 🗄️ Databases  
**Repository:** https://github.com/gouranshul/pg-perf-mcp  
**GitHub Stars:** 0  
**Views:** 0  
**Installs:** 0  
**Upvotes:** 0  
**Directory Page:** https://allmcps.com/mcp/postgresql-performance-read-only

## Description
Read-only PostgreSQL performance diagnostics: slow queries, EXPLAIN, index advice, locks, functions

## Claude Desktop Quick Installation
Remote MCP endpoint (confidence: high). Install path detected from listing signals. Add as a URL/SSE server in your client:

```json
"mcpServers": {
  "postgresql-performance-read-only": {
    "url": "http://localhost:8080/mcp"
  }
}
```

## Documentation

## What PostgreSQL Performance (read-only) does

PostgreSQL Performance (read-only) MCP server gives AI assistants a controlled interface for investigating PostgreSQL performance. It gathers data from `pg_stat_statements`, PostgreSQL catalogs, execution plans, lock views, and function statistics. The server is intended for diagnosis rather than remediation: it can recommend an index or report a blocking session, but it does not apply changes, drop indexes, cancel sessions, or terminate backends.

The server also exposes two schema resources. `pg://schema/overview` reports table estimates, sizes, and indexes, while `pg://schema/{table}` returns columns, indexes with scan counts, and foreign keys that lack indexes. The `diagnose_slow_database` prompt provides a staged investigation covering queries, plans, indexes, table condition, functions, and a prioritized conclusion.

## How it works

Requests arrive over MCP using streamable HTTP and bearer-key authorization. Tool execution passes through an SQL guard, an audited runner, and a read-only PostgreSQL executor. The guard accepts one plain `SELECT` and rejects writes, DDL, multiple statements, data-changing CTEs, locking clauses, dangerous functions, and other unsafe constructs.

Each operation runs in a read-only transaction with statement and lock timeouts, then rolls back. Results are limited to a default maximum of 200 rows. The connection role is expected to have monitoring and statistics access, including `pg_read_all_stats` or `pg_monitor` as described by the project. Audit records store a hash and short preview of SQL rather than the complete statement, and errors omit stack traces and connection details.

For function analysis, the server can enable `auto_explain` only within the current transaction. It combines transaction-level function statistics with statement statistics to show time spent inside nested function statements and identify patterns such as a function executed once per result row.

## Tools and capabilities

The PostgreSQL Performance (read-only) MCP server provides these tools:

- `top_slow_queries` ranks statements by total time, mean time, calls, or rows and includes execution, cache, and workload-share metrics.
- `explain_query` summarizes costly plan nodes, large sequential scans, row-estimate errors, disk spills, and expensive nested loops. Optional analysis adds measured timings.
- `suggest_indexes` proposes index candidates from scans, joins, and ordering patterns. When `hypopg` is available, hypothetical indexes can be used to compare planner estimates; no index is created.
- `unused_indexes` lists zero-scan indexes while excluding primary, unique, and constraint indexes, and supplies a drop statement for review only.
- `table_health` reports tuple condition, vacuum and analyze history, table sizes, and sequential versus index scans.
- `blocking_sessions` identifies blocked and blocking pairs, wait information, lock details, blocker state, and root blockers.
- `slow_functions` ranks user-defined functions using PostgreSQL function statistics and includes language and volatility.
- `explain_function` inspects statements executed within called functions, their plans, timing, and related findings.

## Setup and configuration

The documented architecture connects the MCP service to PostgreSQL through a read-only role and expects PostgreSQL statistics features such as `pg_stat_statements`. Function diagnostics require `track_functions`. Hypothetical index validation requires the optional `hypopg` extension. The project identifies PostgreSQL 18, Java 25, and Spring Boot 4.1 in its repository badges.

The service is shown communicating with clients through streamable HTTP and a bearer API key. The supplied material does not specify installation commands, connection-string variables, or the exact configuration names, so those details should be taken from the repository before deployment.

## Limitations and notes

Results depend on the statistics and extensions available in the target database. `explain_query` uses generic plans for `$1` placeholders, and analyzed plans run under the same timeout and rollback protections. Function analysis cannot successfully inspect functions that write because execution remains read-only. Recommendations are review material: index creation, index removal, vacuum changes, and session termination must be performed separately by an authorized operator.

_Full upstream README: https://allmcps.com/mcp/postgresql-performance-read-only/readme_