How crystaldba/postgres-mcp works
Project type and purpose
• Postgres-MCP Pro is an open-source Python-based MCP (Model Context Protocol) server.
• Purpose: expose a suite of deterministic database-tuning and health-checking tools (index tuning, explain plans, health checks, safe SQL execution) to AI agents over standard I/O or SSE transports.
File structure
• Top-level scripts and configuration:
• Dockerfile, docker-entrypoint.sh, justfile, smithery.yaml – define container build & orchestration.
• devenv.*, pyproject.toml, uv.lock – define Python environment and dependencies.
• src/postgres_mcp/server.py – entry point for the MCP server.
• Core packages under src/postgres_mcp:
• database_health/… – health check calc modules + init.sql
• index/… – index tuning algorithms (dta_calc, LLM opt, presentation)
• /… – explain_plan, tooling to simulate explain plans
• top_queries/… – identify top queries via pg_stat_statements
• sql/… – safe_sql parsing, extension_utils, bind_params, sql_driver (psycopg3 wrapper)
• artifacts.py – shared constants & artifact definitions
• Test suites under tests/unit and tests/integration validate each module and end-to-end behavior.
Key technical insights from README
• Supports two transports: stdio and SSE (HTTP).
• Uses psycopg3 for async Postgres access; pglast to parse/reject unsafe SQL.
• Optional Postgres extensions: pg_stat_statements (workload analysis), hypopg (“what-if” indexing).
• CLI or Docker entrypoint invokes server.py with DATABASE_URI and flags (access-mode, transport).
• Implements MCP tools only (not resources), dispatching on tool calls.
Components and interactions
a. Main components
• AI Agent / MCP Client (e.g., Claude Desktop, Cursor)
• Transport Layer
‑ stdio JSON stream
‑ SSE HTTP server (port 8000)
• Postgres-MCP Server
‑ server.py (dispatch)
‑ Tool modules: database_health, index, explain, top_queries, sql
‑ SQL driver & safe-SQL layer (psycopg3 + pglast)
• Postgres Database
‑ Core Postgres (v15–17)
‑ Extensions: pg_stat_statements, hypopg
• Docker / CLI wrapper & environment (Nix/devenv, pipx, uv)
b. Relationships & data flows
1. Client sends MCP JSON tool-call via stdio or HTTP(SSE).
2. server.py routes request to the appropriate tool module.
3. Tool module invokes sql_driver → establishes connection to Postgres.
4. sql_driver may load extensions or simulate indexes via hypopg.
5. Results propagated back through server.py → marshaled as MCP JSON response.
6. Tests and examples verify these flows end-to-end.
c. Architectural patterns & principles
• Modular design: each MCP tool is a self-contained module.
• Adapter/Facade: sql_driver abstracts psycopg3 and pglast.
• Greedy search algorithm (Anytime) in index tuning.
• Safe-SQL parsing to enforce read-only “restricted” mode.
• Two transport adapters: stdio and SSE, pluggable at startup.
d. Key technologies & frameworks
• Python 3.12+
• psycopg3 (async libpq)
• pglast (SQL AST parsing)
• Docker, Nix/devEnv, uv (CLI wrapper)
• Model Context Protocol (MCP) standard