# crystaldba/postgres-mcp architecture

> Architecture diagram of the GitHub repository crystaldba/postgres-mcp, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-28). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/crystaldba/postgres-mcp
- Repository: https://github.com/crystaldba/postgres-mcp

## Overview

##### 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

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% CLI & Environment
    subgraph "CLI & Environment"
        CLI["Docker & CLI Wrapper<br/>(env vars: DATABASE_URI, access-mode, transport)"]:::infra
    end

    %% AI Client
    subgraph "AI Agent / Client"
        Client["AI Agent / MCP Client"]:::external
    end

    %% Transport Layer
    subgraph "Transport Layer"
        StdIO["STDIO Adapter"]:::infra
        SSE["SSE HTTP Adapter<br/>(port 8000)"]:::infra
    end

    %% Server Dispatcher
    subgraph "Postgres-MCP Server"
        Dispatcher["server.py<br/>Dispatcher"]:::internal
        Artifacts["artifacts.py"]:::internal
    end

    %% Tool Modules
    subgraph "Tool Modules"
        subgraph "Database Health"
            DH_Init["__init__.py"]:::internal
            DH_Buffer["buffer_health_calc.py"]:::internal
            DH_Conn["connection_health_calc.py"]:::internal
            DH_Constraint["constraint_health_calc.py"]:::internal
            DH_DB["database_health.py"]:::internal
            DH_Index["index_health_calc.py"]:::internal
            DH_Rep["replication_calc.py"]:::internal
            DH_Seq["sequence_health_calc.py"]:::internal
            DH_Vac["vacuum_health_calc.py"]:::internal
        end
        subgraph "Index Tuning"
            IT_DTA["dta_calc.py"]:::internal
            IT_Base["index_opt_base.py"]:::internal
            IT_LLM["llm_opt.py"]:::internal
            IT_Pres["presentation.py"]:::internal
        end
        subgraph "Explain Plan"
            EXP_Init["__init__.py"]:::internal
            EXP_EP["explain_plan.py"]:::internal
        end
        subgraph "Top Queries"
            TQ_Init["__init__.py"]:::internal
            TQ_Calc["top_queries_calc.py"]:::internal
        end
    end

    %% SQL Layer
    subgraph "SQL Layer"
        SQL_Init["__init__.py"]:::internal
        SQL_Safe["safe_sql.py"]:::internal
        SQL_Bind["bind_params.py"]:::internal
        SQL_Ext["extension_utils.py"]:::internal
        SQL_Driver["sql_driver.py"]:::internal
        SQL_Idx["index.py"]:::internal
    end

    %% Database
    DB["Postgres DB<br/>(v15–17)<br/>Extensions: pg_stat_statements, hypopg"]:::db

    %% Flows
    Client -->|"MCP JSON request"| StdIO
    Client -->|"MCP JSON request"| SSE
    CLI -->|"startup (env, flags)"| Dispatcher
    StdIO -->|"JSON stream"| Dispatcher
    SSE -->|"SSE events"| Dispatcher
    Dispatcher -->|"dispatch tool call"| DH_Init
    Dispatcher -->|"dispatch tool call"| IT_DTA
    Dispatcher -->|"dispatch tool call"| EXP_Init
    Dispatcher -->|"dispatch tool call"| TQ_Init
    Dispatcher -->|"uses constants"| Artifacts

    %% Module to SQL Layer
    DH_Init --> SQL_Driver
    DH_Buffer --> SQL_Driver
    DH_Conn --> SQL_Driver
    DH_Constraint --> SQL_Driver
    DH_DB --> SQL_Driver
    DH_Index --> SQL_Driver
    DH_Rep --> SQL_Driver
    DH_Seq --> SQL_Driver
    DH_Vac --> SQL_Driver

    IT_DTA --> SQL_Driver
    IT_Base --> SQL_Driver
    IT_LLM --> SQL_Driver
    IT_Pres --> SQL_Driver

    EXP_EP --> SQL_Driver

    TQ_Calc --> SQL_Driver

    %% SQL to DB
    SQL_Driver -->|"SQL query"| DB
    DB -->|"query result"| SQL_Driver
    SQL_Driver -->|"result data"| Dispatcher
    Dispatcher -->|"MCP JSON response"| StdIO
    Dispatcher -->|"SSE event"| SSE

    %% Click Events
    click CLI "https://github.com/crystaldba/postgres-mcp/tree/main/Dockerfile"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/docker-entrypoint.sh"
    click CLI "https://github.com/crystaldba/postgres-mcp/tree/main/justfile"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/smithery.yaml"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/pyproject.toml"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/devenv.nix"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/devenv.yaml"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/devenv.lock"
    click CLI "https://github.com/crystaldba/postgres-mcp/blob/main/uv.lock"

    click Dispatcher "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/server.py"
    click Artifacts "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/artifacts.py"

    click DH_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/__init__.py"
    click DH_Buffer "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/buffer_health_calc.py"
    click DH_Conn "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/connection_health_calc.py"
    click DH_Constraint "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/constraint_health_calc.py"
    click DH_DB "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/database_health.py"
    click DH_Index "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/index_health_calc.py"
    click DH_Rep "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/replication_calc.py"
    click DH_Seq "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/sequence_health_calc.py"
    click DH_Vac "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/vacuum_health_calc.py"
    click DH_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/database_health/init.sql"

    click IT_DTA "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/index/dta_calc.py"
    click IT_Base "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/index/index_opt_base.py"
    click IT_LLM "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/index/llm_opt.py"
    click IT_Pres "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/index/presentation.py"

    click EXP_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/explain/__init__.py"
    click EXP_EP "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/explain/explain_plan.py"
    click EXP_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/explain/README.md"

    click TQ_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/top_queries/__init__.py"
    click TQ_Calc "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/top_queries/top_queries_calc.py"

    click SQL_Init "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/__init__.py"
    click SQL_Safe "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/safe_sql.py"
    click SQL_Bind "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/bind_params.py"
    click SQL_Ext "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/extension_utils.py"
    click SQL_Driver "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/sql_driver.py"
    click SQL_Idx "https://github.com/crystaldba/postgres-mcp/blob/main/src/postgres_mcp/sql/index.py"

    %% Styles
    classDef external fill:#bbdefb,stroke:#333,stroke-width:1px
    classDef infra fill:#ffe0b2,stroke:#333,stroke-width:1px
    classDef internal fill:#c8e6c9,stroke:#333,stroke-width:1px
    classDef db fill:#b3e5fc,stroke:#333,stroke-width:1px
```
