PostgreSQL Analyzer MCP
About
PostgreSQL performance analysis and optimization MCP server
Details
- License
- MIT
Explore
- Database Structure Analysis: Analyze tables, columns, indexes, and foreign keys
- Query Performance Analysis: Analyze execution plans and identify bottlenecks
- Index Recommendations: Get suggestions for new indexes based on query patterns
- Query Optimization: Receive suggestions for query rewrites to improve performance
- Slow Query Identification: Find and analyze slow-running queries
- Database Health Dashboard: Get a comprehensive overview of database health metrics
- Index Usage Analysis: Identify unused, duplicate, or bloated indexes
- Read-Only Query Execution: Safely execute read-only queries for verification
Setting up with Highlight
This MCP is not yet compatible with Highlight’s one-click setup. However, you can still use it with Highlight by following these steps:
- Download and install Highlight from highlightai.com/download
- Navigate to the plugins tab and select "Add Custom Plugin"
-
Configure the plugin with the settings below
Plugin Name
PostgreSQL Analyzer MCPCommand (node, npx, python, etc.)Please refer to the README for specific instructions on how to obtain API keys or other required environment variables.
- Enable "Start Automatically" if you want the plugin to start when Highlight launches
From the repository
- Python 3.12+ (or Docker)
- Amazon Aurora or RDS PostgreSQL database
- AWS account (for Secrets Manager, optional)
1. Clone the repository:
git clone https://github.com/yourusername/postgres-performance-mcp.git
cd postgres-performance-mcp
2. Create virtual environment and Install dependencies:
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt
1. Clone the repository:
git clone https://github.com/yourusername/postgres-performance-mcp.git
cd postgres-performance-mcp
2. Build the Docker image:
docker build -t postgres-analyzer-mcp -f Dockerfile .
3. Run the Docker container:
docker run -p 8000:8000 postgres-analyzer-mcp
For AWS credentials (if using Secrets Manager):
docker run -p 8000:8000 \
-e AWS_ACCESS_KEY_ID=your_access_key \
-e AWS_SECRET_ACCESS_KEY=your_secret_key \
-e AWS_DEFAULT_REGION=your_region \
postgres-analyzer-mcp
3. Configure your database credentials:
- Option 1: Store credentials in AWS Secrets Manager (recommended)
- Option 2: Provide credentials directly when using the tools
docker run -p 8000:8000 postgres-analyzer-mcp
To connect to your remote MCP server, configure your MCP client with:
Server URL: http://your-server-address:8000/mcp
Transport: Streamable HTTP
To use AWS Secrets Manager for storing database credentials:
1. Create a secret in AWS Secrets Manager with the following keys:
- host: Database hostname
- port: Database port (usually 5432)
- dbname: Database name
- username: Database username
- password: Database password
2. Ensure your AWS credentials are configured with appropriate permissions to access the secret.
3. Use the secret name when calling the tools:
analyze_query(query="SELECT FROM users WHERE user_id = 123", secret_name="my-postgres-db-credentials")
For optimal performance analysis, we recommend enabling the following extensions:
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_buffercache;
And adding these settings to your postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
recommend_indexes(
query="SELECT FROM products WHERE category = 'electronics' AND price < 100",
secret_name="my-postgres-db-credentials"
)
To deploy the MCP server to a remote machine:
1. Install Docker on your remote server
2. Copy the project files to the server or clone from your repository
3. Build and run the Docker container:
docker build -t postgres-analyzer-mcp -f Dockerfile .
docker run -d -p 8000:8000 postgres-analyzer-mcp
``
4. Consider using a process manager like or systemd` to ensure the container restarts if the server reboots
For secure access, consider setting up:
- A reverse proxy with SSL/TLS (like Nginx or Traefik)
- Authentication middleware
- Firewall rules to restrict access
analyze_database_structure
Analyze database schema and provide optimization recommendations
get_slow_queries
Identify slow-running queries in the database
analyze_query
Analyze a SQL query and provide optimization recommendations
recommend_indexes
Recommend indexes for a given SQL query
suggest_query_rewrite
Suggest optimized rewrites for a SQL query
database_health_dashboard
Generate a comprehensive health dashboard for the database
query_optimization_wizard
Interactive wizard to optimize a SQL query step by step
analyze_index_usage
Analyze index usage patterns and identify unused or inefficient indexes
execute_read_only_query
Execute a read-only SQL query and return the results
show_postgresql_settings
Show PostgreSQL configuration settings with optional filtering
health_check
Check if the server is running and responsive
- analyze_database_structure: Analyze database schema and provide optimization recommendations
- get_slow_queries: Identify slow-running queries in the database
- analyze_query: Analyze a SQL query and provide optimization recommendations
- recommend_indexes: Recommend indexes for a given SQL query
- suggest_query_rewrite: Suggest optimized rewrites for a SQL query
- database_health_dashboard: Generate a comprehensive health dashboard for the database
- query_optimization_wizard: Interactive wizard to optimize a SQL query step by step
- analyze_index_usage: Analyze index usage patterns and identify unused or inefficient indexes
- execute_read_only_query: Execute a read-only SQL query and return the results
- show_postgresql_settings: Show PostgreSQL configuration settings with optional filtering
- health_check: Check if the server is running and responsive
Claude Desktop / Cursor
Paste into your MCP client config file to install this server.
{
"mcpServers": {
"postgresql analyzer mcp": {
"postgreSQL-analyzer-mcp": {
"command": "python",
"args": [
"-m",
"venv",
"venv"
]
}
}
}
}McpServers
{
"postgreSQL-analyzer-mcp": {
"command": "python",
"args": [
"-m",
"venv",
"venv"
]
}
}A Model Context Protocol (MCP) server for PostgreSQL database performance analysis and optimization.
Overview
PostgreSQL Analyzer MCP is a powerful tool that leverages AI to help database administrators and developers optimize their PostgreSQL databases. It provides comprehensive analysis of database structure, query performance, index usage, and configuration settings, along with actionable recommendations for improvement.
This tool runs as a remote MCP server using Streamable HTTP transport, allowing it to be deployed centrally and accessed by any MCP-compatible client, including Amazon Q Developer CLI, Claude and other AI assistants that support the MCP protocol.
⚠️ Disclaimer
EXPERIMENTAL: This project is experimental and provided as a demonstration of what's possible with MCP and PostgreSQL. All recommendations and code should be carefully reviewed before implementation in any production environment.
NOT OFFICIAL: This is a personal project and not affiliated with, endorsed by, or representative of any organization I work for or contribute to. All opinions and approaches are my own.
NO LIABILITY: This tool is provided "as is" without warranty of any kind. Use at your own risk. The author is not liable for any damages or issues arising from the use of this software.
Features
- Database Structure Analysis: Analyze tables, columns, indexes, and foreign keys
- Query Performance Analysis: Analyze execution plans and identify bottlenecks
- Index Recommendations: Get suggestions for new indexes based on query patterns
- Query Optimization: Receive suggestions for query rewrites to improve performance
- Slow Query Identification: Find and analyze slow-running queries
- Database Health Dashboard: Get a comprehensive overview of database health metrics
- Index Usage Analysis: Identify unused, duplicate, or bloated indexes
- Read-Only Query Execution: Safely execute read-only queries for verification
Security
This tool operates in read-only mode by default. All database connections are established with SET TRANSACTION READ ONLY to prevent any accidental modifications to your database. The query execution functionality is strictly limited to SELECT, EXPLAIN, and SHOW commands.
Installation
Prerequisites
- Python 3.12+ (or Docker)
- Amazon Aurora or RDS PostgreSQL database
- AWS account (for Secrets Manager, optional)
Setup
Option 1: Local Setup
1. Clone the repository:
git clone https://github.com/yourusername/postgres-performance-mcp.git
cd postgres-performance-mcp
2. Create virtual environment and Install dependencies:
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt
Option 2: Docker Setup
1. Clone the repository:
git clone https://github.com/yourusername/postgres-performance-mcp.git
cd postgres-performance-mcp
2. Build the Docker image:
docker build -t postgres-analyzer-mcp -f Dockerfile .
3. Run the Docker container:
docker run -p 8000:8000 postgres-analyzer-mcp
For AWS credentials (if using Secrets Manager):
docker run -p 8000:8000 \
-e AWS_ACCESS_KEY_ID=your_access_key \
-e AWS_SECRET_ACCESS_KEY=your_secret_key \
-e AWS_DEFAULT_REGION=your_region \
postgres-analyzer-mcp
3. Configure your database credentials:
- Option 1: Store credentials in AWS Secrets Manager (recommended)
- Option 2: Provide credentials directly when using the tools
Usage
Starting the Server
Local:
python src/main.py --host 0.0.0.0 --port 8000
Docker:
```bashSign in to leave a review
Use Google, GitHub, or an email account so ratings stay tied to real people.
No reviews posted yet.
