The postgres-mcp server enables Claude Desktop to interact with PostgreSQL databases, offering both direct query capabilities and an advanced AI-driven SQL optimization pipeline.
Execute SQL queries: Run any SQL statement against the database; SELECT statements return JSON rows, while DML operations return the affected row count.
Inspect execution plans: Retrieve the EXPLAIN plan for a query, with an option for EXPLAIN ANALYZE to include real runtime statistics.
List tables: Get a list of all tables within a specified schema (defaults to public).
Get table schema: Obtain detailed information about a table, including columns, types, nullability, default values, and indexes.
Identify slow queries: Fetch the top slowest queries from pg_stat_statements based on mean execution time (configurable limit).
Analyze and optimize queries (SQL-Surgeon pipeline): A multi-step AI pipeline that:
Runs EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) on the query.
Identifies bottlenecks (e.g., missing indexes, sequential scans) using Gemini 2.5 Pro.
Generates optimization advice and a complete optimized SQL script (including index DDL and rewritten query).
Self-reviews the generated advice with up to two retries if improvement is suggested.
Optionally benchmarks the optimized query by cloning the target table into a temporary schema, applying suggested DDL, and comparing execution plans.
Provides tools for executing SQL queries, obtaining query execution plans, and inspecting table schemas in a PostgreSQL database.
Click on "Install 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., "@postgres-mcp show me the schema of the users table"
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.
An MCP server that connects Claude Desktop to a PostgreSQL database, exposing both direct query tools and an AI-powered query optimization pipeline built on SQL-Surgeon.
This project wraps two layers of capability into a single MCP server:
Layer 1 — Direct database tools: Claude can execute SQL, inspect execution plans, and query table schemas against a live PostgreSQL database.
Layer 2 — AI query optimization pipeline: Claude can invoke a multi-step LangGraph agent (SQL-Surgeon) that analyzes a slow query, identifies performance bottlenecks, generates optimization advice, self-reviews the advice for quality, and optionally benchmarks the result in a sandbox schema.
The MCP interface means Claude decides which tool to use based on the user's question — no manual tool selection needed.
Related MCP server: @yawlabs/postgres-mcp
Claude Desktop
│
│ MCP protocol
▼
server.py ← tool registration + MCP entry point
│
tools.py ← tool logic
│
┌───┴──────────────────────────────────┐
│ │
db.py agent/graph.py
(Layer 1: direct tools) (Layer 2: LangGraph pipeline)
│ │
├── execute_query ┌───────────┼───────────┐
├── explain_query ▼ ▼ ▼
├── list_tables run_explain identify_issues generate_advice
├── get_table_schema │ │ │
└── get_slow_queries └───────────┴───────────┘
│
review_advice ←── retry loop (max 2x)
│
generate_benchmark_schema (optional)Tool | Parameters | Description |
|
| Run any SQL; SELECT returns JSON rows, DML returns affected row count |
|
| Get query execution plan; |
|
| List all tables in a schema |
|
| List columns, types, nullability, defaults, and indexes |
|
| Return the slowest queries by mean execution time from |
|
| Run full SQL-Surgeon optimization pipeline; returns issues, advice, optimized SQL, and optional benchmark |
analyze_query invokes a 5-node LangGraph graph:
run_explain — executes EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) against the real database
identify_issues — sends the execution plan + DDL to Gemini 2.5 Pro; returns a JSON array of identified bottlenecks (missing indexes, sequential scans, row count misestimation, etc.)
generate_advice — generates specific optimization recommendations and a complete optimized SQL script (index DDL + rewritten query)
review_advice — a second LLM call acting as a senior DBA reviewer; returns pass or retry with feedback; retries up to 2 times
generate_benchmark_schema (optional) — clones the target table into a temporary schema, applies the suggested DDL, and re-runs EXPLAIN to compare plans
src/postgres_mcp/
server.py # MCP entry point, tool registrations
tools.py # Tool logic; calls db.py and agent/
db.py # Connection helper (reads DATABASE_URL)
db_client.py # DBClient used by the agent pipeline
agent/
graph.py # LangGraph graph definition
nodes.py # 5 node functions
state.py # AgentState TypedDict
prompts.py # System prompts for each LLM node
tests/
test_tools.py # Unit tests with mocked DB connections
examples/
claude_desktop_config.jsonPython 3.10+
uv — brew install uv
A running PostgreSQL instance
A Google API key (Gemini 2.5 Pro) for analyze_query
git clone https://github.com/RachelHuangZW/postgres-mcp
cd postgres-mcp
uv syncCreate .env in the project root:
DATABASE_URL=postgresql://user:password@localhost:5432/dbname
GOOGLE_API_KEY=your-google-api-keyEdit ~/Library/Application Support/Claude/claude_desktop_config.json:
{
"mcpServers": {
"postgres-mcp": {
"command": "uv",
"args": [
"run",
"--directory", "/path/to/postgres-mcp",
"--env-file", "/path/to/postgres-mcp/.env",
"python", "-m", "postgres_mcp.server"
]
}
}
}Fully quit and reopen Claude Desktop after saving.
Open Claude Desktop and ask:
"What MCP tools do you have available?"
Claude should list all six tools.
uv sync --group dev
uv run pytestTests use mocked database connections and do not require a live PostgreSQL instance.
MCP framework: FastMCP
Agent framework: LangGraph
LLM: Gemini 2.5 Pro via langchain-google-genai
Database: PostgreSQL via psycopg2
Package manager: uv
This project was built with Claude Code as a pair-programming partner. I designed the architecture (two-layer tool exposure, separation of db.py vs db_client.py), made all technical decisions (MCP framework choice, LangGraph integration approach, security boundaries), and iterated on implementation with AI assistance. Every design decision documented in this README reflects my own thinking about MCP server design and enterprise database tool exposure.
execute_query runs arbitrary SQL. Use a read-only database role in production or restrict access to trusted users only.
Execute a SQL query against the connected PostgreSQL database and return results as JSON.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations exist, so the description must disclose behavioral traits. It mentions JSON output but fails to indicate whether the query can modify data (read-only vs write), what side effects occur, or any authorization requirements. This is insufficient for a potentially destructive operation.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is concise (one sentence) and front-loaded with the core action. However, it lacks structure (e.g., no sections or warnings) and could be slightly more informative without losing brevity.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the presence of an output schema and the potential risks of SQL execution, the description is incomplete. It does not clarify read-only status, transaction behavior, error handling, or limitations. A more thorough description is needed for safe and correct agent use.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, yet the description adds no meaning beyond the parameter name and type. There is no guidance on SQL format, statement constraints, or parameterization, leaving the agent to infer from the name alone.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the tool executes a SQL query against a PostgreSQL database and returns JSON results. It uses specific verbs and resources, and the sibling tools (explain_query, get_table_schema) suggest distinct purposes.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No explicit when-to-use or when-not-to-use guidance is provided. The description implies usage for executing SQL queries, but does not contrast with siblings like explain_query or get_table_schema.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Return the EXPLAIN execution plan for a SQL query. Set analyze=True to run EXPLAIN ANALYZE (this actually executes the query).
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| analyze | No |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description carries the full burden. It discloses the critical behavioral trait that setting analyze=True actually executes the query, but does not discuss other traits like idempotency or potential side effects.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is extremely concise with two sentences containing no fluff. Every word adds value.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the simple tool with only two parameters and existence of an output schema, the description is largely complete. It could optionally mention what the plan looks like or any limitations, but the output schema likely covers that.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
With 0% schema description coverage, the description compensates well by explaining the 'sql' parameter implicitly and the 'analyze' parameter explicitly ('Set analyze=True to run EXPLAIN ANALYZE'). For two parameters, this adds meaningful context beyond the schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states it returns the EXPLAIN execution plan for a SQL query, which distinguishes it from sibling tools like execute_query (executes the query) and get_table_schema (retrieves schema).
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies usage by mentioning the analyze flag, but it does not explicitly state when to use this tool versus execute_query or provide exclusions. It could add guidance like 'Use when you need the query plan instead of actual results.'
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Get columns and indexes for a PostgreSQL table.
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | public | |
| table_name | Yes |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the full burden of behavioral disclosure. It only states what is retrieved (columns and indexes) but does not mention safety (read-only), required permissions, or limitations (e.g., no constraints or foreign keys).
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single, efficient sentence—'Get columns and indexes for a PostgreSQL table.'—with no filler or redundancy. It is front-loaded and earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the tool's simplicity, the output schema exists (so return values need not be described), but the description omits important context like default behavior of the 'schema' parameter and the requirement that the table must exist. It is minimally complete.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, and the description adds no information beyond the parameter names. It does not explain that 'schema' defaults to 'public' or that 'table_name' is case-sensitive, providing no semantic value over the input schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the verb 'Get' and the resource 'columns and indexes for a PostgreSQL table'. It distinguishes from siblings 'execute_query' (execute SQL) and 'explain_query' (show query plan) by specifying exactly what artifact is retrieved.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use this tool versus alternatives like 'execute_query' or 'explain_query'. There is no mention of prerequisites (e.g., table must exist) or context in which this tool is preferred.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Each tool serves a distinct purpose: executing queries, explaining plans, and retrieving table schema. No overlap exists.
All tools follow a consistent verb_noun snake_case pattern (execute_query, explain_query, get_table_schema) with clear, descriptive names.
Three tools is slightly low for a database server, but the scope is focused on query execution and schema inspection, which is reasonable. Could benefit from additional tools like listing tables.
Core query execution and schema retrieval are covered, but missing tools to list tables or databases, manage transactions, or modify data directly. Agents would need to rely on raw SQL for many operations.
Unclaimed servers have limited discoverability.
If you are the server author, to access and configure the admin panel.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Query your warehouse or a CSV with Claude/ChatGPT over MCP, governed by table-level ACL + audit.
Analytical memory for AI agents: a real Postgres queried in plain English over MCP. One command.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
We provide all the information about MCP servers via our MCP API.
curl -X GET 'https://glama.ai/api/mcp/v1/servers/RachelHuangZW/postgres-mcp'
If you have feedback or need assistance with the MCP directory API, please join our Discord server
