PostgreSQL Analyzer MCP

Name: PostgreSQL Analyzer MCP
Author: rameshv29

@rameshv29

リポジトリ

PostgreSQL Analyzer MCP について

PostgreSQL performance analysis and optimization MCP server

基本情報

カテゴリ

データベース

ライセンス

MIT

ランタイム

python

トランスポート

stdio

公開者

rameshv29

設定

以下の設定を使って、このサーバーを MCP 対応クライアントに追加してください。

{
  "mcpServers": {
    "postgreSQL-analyzer-mcp": {
      "command": "python",
      "args": [
        "-m",
        "venv",
        "venv"
      ]
    }
  }
}

ツール

Analyze database schema and provide optimization recommendations

Identify slow-running queries in the database

Analyze a SQL query and provide optimization recommendations

Recommend indexes for a given SQL query

Suggest optimized rewrites for a SQL query

Generate a comprehensive health dashboard for the database

Interactive wizard to optimize a SQL query step by step

Analyze index usage patterns and identify unused or inefficient indexes

Execute a read-only SQL query and return the results

Show PostgreSQL configuration settings with optional filtering

Check if the server is running and responsive

概要

What is PostgreSQL Analyzer MCP?

PostgreSQL Analyzer MCP is a Model Context Protocol (MCP) server for PostgreSQL database performance analysis and optimization. It leverages AI to help database administrators and developers analyze database structure, query performance, index usage, and configuration, providing actionable recommendations. It runs as a remote MCP server using Streamable HTTP transport, allowing centralized deployment and access by any MCP-compatible client (e.g., Amazon Q Developer CLI, Claude).

How to use PostgreSQL Analyzer MCP?

Install locally (Python 3.12+, pip) or via Docker. Start the server with python src/main.py or by running the Docker container. Point your MCP client to http://your-server:8000/mcp using Streamable HTTP transport. Database credentials can be provided via AWS Secrets Manager (recommended) or directly when calling tools.

Key features of PostgreSQL Analyzer MCP

Database structure analysis (tables, columns, indexes, foreign keys)
Query performance analysis with execution plans and bottleneck identification
Index recommendations based on query patterns
Query optimization suggestions and rewrites
Slow query identification and analysis
Database health dashboard with comprehensive metrics
Index usage analysis (unused, duplicate, bloated indexes)
Read-only query execution (SELECT, EXPLAIN, SHOW only)

Use cases of PostgreSQL Analyzer MCP

Analyze entire database schema and get optimization recommendations
Identify and analyze slow-running queries for performance tuning
Get index recommendations for a specific SQL query
Generate a comprehensive health dashboard for a PostgreSQL database
Execute safe read-only queries to inspect database statistics

FAQ from PostgreSQL Analyzer MCP

Is this tool safe to use on production databases?

Yes, it operates in read-only mode by default. All database connections use SET TRANSACTION READ ONLY to prevent accidental modifications, and query execution is limited to SELECT, EXPLAIN, and SHOW commands.

What are the prerequisites for running PostgreSQL Analyzer MCP?

Python 3.12+ (or Docker), a PostgreSQL database (Amazon Aurora or RDS supported), and optionally an AWS account with Secrets Manager credentials.

How can I provide database credentials?

You can store credentials in AWS Secrets Manager (recommended) and pass the secret name when calling tools, or provide host, port, dbname, username, and password directly in tool arguments.

Can I deploy this MCP server on a remote machine?

Yes, use Docker to build and run the container on any remote server. For secure access, consider setting up a reverse proxy with SSL/TLS and authentication middleware.

What are the recommended PostgreSQL extensions for best analysis?

Enable pg_stat_statements and pg_buffercache extensions, and set shared_preload_libraries = 'pg_stat_statements' with pg_stat_statements.track = all in postgresql.conf.