PostgreSQL Performance (read Only)

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.