### Install and Start Postgres MCP with uvx (stdio)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Installs and starts the postgres-mcp server using the uvx tool. Supports both unrestricted (development) and restricted (production) access modes. The database connection URI can be provided directly or via the DATABASE_URI environment variable.

```bash
# Install and start in unrestricted mode (development)
uvx postgres-mcp "postgresql://user:password@localhost:5432/mydb" --access-mode=unrestricted

# Install and start in restricted (read-only) mode (production)
uvx postgres-mcp "postgresql://user:password@localhost:5432/mydb" --access-mode=restricted

# Connection URI can also be supplied via environment variable
export DATABASE_URI="postgresql://user:password@localhost:5432/mydb"
uvx postgres-mcp --access-mode=restricted
```

--------------------------------

### Install Project Dependencies with uv

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Installs project dependencies using uv, including editable installs for development. 'uv sync' ensures all dependencies are correctly set up.

```bash
uv pip install -e .
uv sync
```

--------------------------------

### Install Postgres MCP Pro with uv

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Install Postgres MCP Pro using uv, a fast Python package installer. Ensure uv is installed first by following its installation instructions.

```bash
uv pip install postgres-mcp
```

--------------------------------

### Basic EXPLAIN Query Plan

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Use `tool.explain` to get the query plan without executing the query. The output can be converted to a human-readable text format.

```python
artifact = await tool.explain("SELECT * FROM orders WHERE customer_id = 42")
print(artifact.to_text())
```

--------------------------------

### Install uv Package Manager

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Installs the uv package manager using a curl script. This is the first step in setting up the local development environment.

```bash
curl -sSL https://astral.sh/uv/install.sh | sh
```

--------------------------------

### Docker Installation and Startup (stdio)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Pulls the postgres-mcp Docker image and runs it interactively using the stdio transport. The DATABASE_URI environment variable must be set for the connection.

```bash
docker pull crystaldba/postgres-mcp

# Run interactively on stdio
docker run -i --rm \
  -e DATABASE_URI="postgresql://user:password@host:5432/mydb" \
  crystaldba/postgres-mcp \
  --access-mode=unrestricted
```

--------------------------------

### Install Postgres Extensions

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Use these commands to install `pg_stat_statements` and `hypopg` extensions. Ensure you have sufficient privileges. For self-managed instances, `pg_stat_statements` may require configuration in `shared_preload_libraries`.

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS hypopg;
```

--------------------------------

### Extension Utility Functions: Check hypopg Installation

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Checks if the 'hypopg' extension is installed and provides installation instructions if it is not. Useful for ensuring prerequisites for hypothetical index testing.

```python
from postgres_mcp.sql import (
    SqlDriver, DbConnPool,
    check_extension,
    check_hypopg_installation_status,
    check_postgres_version_requirement,
    get_postgres_version,
    reset_postgres_version_cache,
)

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

# Check hypopg with installation instructions
is_installed, message = await check_hypopg_installation_status(driver, message_type="markdown")
if not is_installed:
    print(message)
# "The **hypopg** extension is required to test hypothetical indexes...
#  You can ask me to install 'hypopg' using the 'execute_query' tool."
```

--------------------------------

### Install Postgres MCP Pro with pipx

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Install Postgres MCP Pro using pipx, a tool for installing and running Python applications in isolated environments.

```bash
pipx install postgres-mcp
```

--------------------------------

### Docker Server Startup (SSE Transport)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Starts a postgres-mcp server using Docker with the Server-Sent Events (SSE) transport. This is suitable for multi-client remote server scenarios. Configuration includes port mapping, database URI, access mode, and SSE host/port settings.

```bash
# Start an SSE server on port 8000
docker run -p 8000:8000 \
  -e DATABASE_URI="postgresql://user:password@host:5432/mydb" \
  crystaldba/postgres-mcp \
  --access-mode=unrestricted \
  --transport=sse \
  --sse-host=0.0.0.0 \
  --sse-port=8000
```

--------------------------------

### Install Postgres MCP Pro with Docker

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Pull the Docker image for Postgres MCP Pro. This image includes all necessary dependencies for a reliable environment.

```bash
docker pull crystaldba/postgres-mcp
```

--------------------------------

### Start Postgres MCP with Docker and SSE Transport

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This command starts the Postgres MCP Pro server using Docker with the SSE transport enabled. It maps port 8000 and sets the DATABASE_URI.

```bash
docker run -p 8000:8000 \
  -e DATABASE_URI=postgresql://username:password@localhost:5432/dbname \
  crystaldba/postgres-mcp --access-mode=unrestricted --transport=sse
```

--------------------------------

### Run Postgres MCP Pro Server Locally

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Starts the Postgres MCP Pro server using uv, specifying the database connection string. This command is used for running the server during local development.

```bash
uv run postgres-mcp "postgres://user:password@localhost:5432/dbname"
```

--------------------------------

### Get Top Resource-Intensive Queries

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Reports the most resource-intensive SQL queries. Supports sorting by 'resources', 'mean_time', or 'total_time'. Returns installation instructions if pg_stat_statements is not installed.

```python
# Resource-intensive queries (default) — queries consuming >5% of any resource
result = await get_top_queries(sort_by="resources")
# Returns a string with rows containing:
# query, calls, total_exec_time, mean_exec_time, stddev_exec_time,
# total_exec_time_frac, shared_blks_accessed_frac, shared_blks_read_frac,
# shared_blks_dirtied_frac, total_wal_bytes_frac, wal_bytes
```

```python
# Top 5 slowest queries by mean execution time per call
result = await get_top_queries(sort_by="mean_time", limit=5)
# Returns: "Top 5 slowest queries by mean execution time per call:
# [{'query': 'SELECT * FROM orders WHERE ...', 'calls': 18423,
#   'total_exec_time': 94201.2, 'mean_exec_time': 5.11, 'rows': 18423}, ...]"
```

```python
# Top 10 queries by total cumulative time
result = await get_top_queries(sort_by="total_time", limit=10)
```

--------------------------------

### Get Database Object Details

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieves comprehensive metadata for a single database object, such as columns, constraints, and indexes for tables/views, or start value and increment for sequences. Ensure the correct schema, object name, and type are provided.

```python
result = await get_object_details(schema_name="public", object_name="orders", object_type="table")
```

```python
result = await get_object_details(schema_name="public", object_name="orders_id_seq", object_type="sequence")
```

--------------------------------

### Configure Postgres MCP with pipx

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Use this JSON configuration to set up Postgres MCP Pro when installed via pipx. The command directly invokes 'postgres-mcp'.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "postgres-mcp",
      "args": [
        "--access-mode=unrestricted"
      ],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### explain_query

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Gets the execution plan for a SQL query describing how PostgreSQL will process it and exposing the query planner's cost model. Can be invoked with hypothetical indexes to simulate the behavior after adding indexes.

```APIDOC
## explain_query

### Description
Gets the execution plan for a SQL query, detailing how PostgreSQL will process it and exposing the query planner's cost model. This tool can be invoked with hypothetical indexes to simulate the behavior after adding indexes.

### Parameters
#### Request Body
- **sql_query** (string) - Required - The SQL query to analyze.
- **hypothetical_indexes** (array) - Optional - An array of hypothetical indexes to simulate.
  - **index_definition** (string) - Required - The SQL definition of the hypothetical index.

### Tool
`explain_query`
```

--------------------------------

### Get Top Resource Consuming Queries

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Identify queries consuming a significant fraction of resources (CPU, I/O, WAL) using `calc.get_top_resource_queries`. Set `frac_threshold` to filter by percentage.

```python
result = await calc.get_top_resource_queries(frac_threshold=0.05)
```

--------------------------------

### get_object_details

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Provides information about a specific database object, for example, a table's columns, constraints, and indexes.

```APIDOC
## get_object_details

### Description
Provides information about a specific database object, for example, a table's columns, constraints, and indexes.

### Parameters
#### Query Parameters
- **schema_name** (string) - Required - The name of the schema containing the object.
- **object_name** (string) - Required - The name of the database object.
- **object_type** (string) - Optional - The type of the object (e.g., 'table', 'view').

### Tool
`get_object_details`
```

--------------------------------

### Extension Utility Functions: Check Extension Status

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Checks the installation and availability status of a PostgreSQL extension. Optionally includes human-readable messages in markdown format.

```python
from postgres_mcp.sql import (
    SqlDriver, DbConnPool,
    check_extension,
    check_hypopg_installation_status,
    check_postgres_version_requirement,
    get_postgres_version,
    reset_postgres_version_cache,
)

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

# Check any extension
status = await check_extension(driver, "pg_stat_statements", include_messages=True, message_type="markdown")
print(status.is_installed)    # True / False
print(status.is_available)    # True / False
print(status.default_version) # "1.10"
print(status.message)         # Markdown-formatted human-readable status
```

--------------------------------

### Extension Utility Functions: Get PostgreSQL Version

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieves the major PostgreSQL version. The result is cached globally after the first call.

```python
from postgres_mcp.sql import (
    SqlDriver, DbConnPool,
    check_extension,
    check_hypopg_installation_status,
    check_postgres_version_requirement,
    get_postgres_version,
    reset_postgres_version_cache,
)

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

# Get major PostgreSQL version (result cached globally after first call)
version = await get_postgres_version(driver)
print(version)  # 16
```

--------------------------------

### Get Top Queries by Mean Execution Time

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieve the top N queries sorted by their mean execution time using `calc.get_top_queries_by_time`. Specify the `limit` and `sort_by="mean"`.

```python
result = await calc.get_top_queries_by_time(limit=5, sort_by="mean")
print(result)
```

--------------------------------

### Get Top Queries by Total Execution Time

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieve the top N queries sorted by their total cumulative execution time using `calc.get_top_queries_by_time`. Specify the `limit` and `sort_by="total"`.

```python
result = await calc.get_top_queries_by_time(limit=10, sort_by="total")
```

--------------------------------

### Preventing SQL Injection with Rollback/Commit

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This example demonstrates how an LLM could attempt to circumvent read-only transaction modes by issuing a ROLLBACK statement followed by a data modification. The pglast library is used to parse and reject such statements.

```sql
ROLLBACK; DROP TABLE users;
```

--------------------------------

### Initialize ExplainPlanTool

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Initializes the ExplainPlanTool for programmatic EXPLAIN plan analysis. Requires an initialized SqlDriver.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.explain import ExplainPlanTool
from postgres_mcp.sql.index import IndexDefinition

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)
tool = ExplainPlanTool(sql_driver=driver)
```

--------------------------------

### EXPLAIN ANALYZE Query Execution and Plan

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Use `tool.explain_analyze` to execute a query and retrieve its execution plan along with timing information. Useful for understanding actual performance.

```python
artifact = await tool.explain_analyze("SELECT COUNT(*) FROM orders")
print(f"Planning: {artifact.planning_time:.1f}ms, Execution: {artifact.execution_time:.1f}ms")
print(artifact.plan_tree.node_type)
```

--------------------------------

### Generate PostgreSQL EXPLAIN Plan

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Generates a PostgreSQL EXPLAIN plan for any SQL query. Supports basic cost estimates, actual execution stats with `analyze=True`, and hypothetical-index simulation using the `hypopg` extension. Bind variables are handled automatically.

```python
result = await explain_query(
    sql="SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.status = 'pending'",
    analyze=False,
    hypothetical_indexes=[]
)
```

```python
result = await explain_query(
    sql="SELECT COUNT(*) FROM orders WHERE customer_id = 42",
    analyze=True
)
```

```python
result = await explain_query(
    sql="SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day'",
    analyze=False,
    hypothetical_indexes=[
        {"table": "orders", "columns": ["status", "created_at"], "using": "btree"}
    ]
)
```

--------------------------------

### Initialize TopQueriesCalc

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Set up the `TopQueriesCalc` by creating a `DbConnPool` and `SqlDriver`. This tool analyzes query performance statistics from `pg_stat_statements`.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.top_queries import TopQueriesCalc

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)
calc = TopQueriesCalc(sql_driver=driver)
```

--------------------------------

### Generate EXPLAIN Plan Difference

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Use the static utility `ExplainPlanArtifact.create_plan_diff` to compare two query plans and highlight the differences. Requires plans in dictionary format.

```python
from postgres_mcp.artifacts import ExplainPlanArtifact
diff = ExplainPlanArtifact.create_plan_diff(before_plan_dict, after_plan_dict)
print(diff)
```

--------------------------------

### Configure Postgres MCP with uvx

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This JSON configuration is for setting up Postgres MCP Pro when using the uvx command-line tool. The DATABASE_URI should be specified in the environment.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": [
        "postgres-mcp",
        "--access-mode=unrestricted"
      ],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### EXPLAIN with Hypothetical Indexes

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Simulate the effect of new indexes on a query plan using `tool.explain_with_hypothetical_indexes`. This helps in deciding which indexes to create.

```python
artifact = await tool.explain_with_hypothetical_indexes(
    sql_query="SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending'",
    hypothetical_indexes=[
        {"table": "orders", "columns": ["customer_id", "status"], "using": "btree"}
    ]
)
print(artifact.to_text())
```

--------------------------------

### Analyze Workload for Index Recommendations (LLM)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Experimental LLM-based optimizer for index recommendations. Requires the OPENAI_API_KEY environment variable to be set.

```python
# Experimental LLM-based optimizer (requires OPENAI_API_KEY env var)
result = await analyze_workload_indexes(
    max_index_size_mb=1000,
    method="llm"
)
```

--------------------------------

### Basic EXPLAIN

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Performs a basic EXPLAIN on a given SQL query to show the query plan without executing it.

```APIDOC
## tool.explain

### Description
Performs a basic EXPLAIN on a given SQL query to show the query plan without executing it.

### Method
`await tool.explain(sql_query: str)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
artifact = await tool.explain("SELECT * FROM orders WHERE customer_id = 42")
print(artifact.to_text())
```

### Response
#### Success Response (200)
Returns an artifact object containing the EXPLAIN plan.

#### Response Example
```text
Seq Scan on orders (Cost: 0.00..4200.00) [Rows: 5000]
    Filter: (customer_id = 42)
```
```

--------------------------------

### Initialize DatabaseHealthTool

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Set up the `DatabaseHealthTool` by creating a `DbConnPool` and `SqlDriver`. The tool orchestrates various database health checks.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.database_health import DatabaseHealthTool, HealthType

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)
tool = DatabaseHealthTool(sql_driver=driver)
```

--------------------------------

### ExplainPlanTool - Programmatic EXPLAIN

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Provides programmatic access to EXPLAIN functionality with three different modes, returning structured ExplainPlanArtifact objects.

```APIDOC
## ExplainPlanTool

### Description
`ExplainPlanTool` provides the three explain modes as directly callable async methods, returning structured `ExplainPlanArtifact` objects (or `ErrorResult` on failure). This is the internal engine behind the `explain_query` MCP tool.

### Initialization
```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.explain import ExplainPlanTool
from postgres_mcp.sql.index import IndexDefinition

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)
tool = ExplainPlanTool(sql_driver=driver)
```

### Methods
`ExplainPlanTool` exposes methods for different EXPLAIN modes. (Specific methods and their return types are not detailed in the provided text, but the description indicates they return `ExplainPlanArtifact` or `ErrorResult`.)
```

--------------------------------

### Configure Postgres MCP with Docker

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Use this JSON configuration to set up Postgres MCP Pro when running via Docker. Ensure the DATABASE_URI is correctly set.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "-e",
        "DATABASE_URI",
        "crystaldba/postgres-mcp",
        "--access-mode=unrestricted"
      ],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### Claude Desktop Configuration (uvx)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Configuration for Claude Desktop to connect to a postgres-mcp server managed by uvx. Defines the command, arguments, and environment variables required for the connection.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "uvx",
      "args": ["postgres-mcp", "--access-mode=unrestricted"],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### Configure Postgres MCP with uv

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This JSON configuration is for setting up Postgres MCP Pro when using the uv command-line tool. It specifies the command and arguments for running the MCP server.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "uv",
      "args": [
        "run",
        "postgres-mcp",
        "--access-mode=unrestricted"
      ],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### EXPLAIN with Hypothetical Indexes

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Simulates the execution plan of an SQL query with specified hypothetical indexes to assess their potential impact.

```APIDOC
## tool.explain_with_hypothetical_indexes

### Description
Simulates the execution plan of an SQL query with specified hypothetical indexes to assess their potential impact.

### Method
`await tool.explain_with_hypothetical_indexes(sql_query: str, hypothetical_indexes: list)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
hypothetical_indexes = [
    {"table": "orders", "columns": ["customer_id", "status"], "using": "btree"}
]
artifact = await tool.explain_with_hypothetical_indexes(
    sql_query="SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending'",
    hypothetical_indexes=hypothetical_indexes
)
print(artifact.to_text())
```

### Response
#### Success Response (200)
Returns an artifact object showing the query plan with the hypothetical indexes applied.

#### Response Example
```text
Plan will show Index Scan instead of Seq Scan if the index would be used.
```
```

--------------------------------

### Initialize and Connect DbConnPool

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Initializes an asynchronous connection pool and connects to the database. Raises ValueError on connection failure, with the password obfuscated in the error message.

```python
from postgres_mcp.sql import DbConnPool

pool = DbConnPool()

# Connect (also validates the connection with SELECT 1)
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
# Raises ValueError if connection fails, with the password obfuscated in the message.

print(pool.is_valid)   # True
print(pool.last_error) # None

# Close gracefully
await pool.close()
print(pool.is_valid)   # False
```

--------------------------------

### ExplainPlanArtifact.create_plan_diff

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

A static utility method to generate a difference report between two EXPLAIN plan dictionaries.

```APIDOC
## ExplainPlanArtifact.create_plan_diff

### Description
A static utility method to generate a difference report between two EXPLAIN plan dictionaries.

### Method
`ExplainPlanArtifact.create_plan_diff(before_plan_dict: dict, after_plan_dict: dict)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
from postgres_mcp.artifacts import ExplainPlanArtifact
# Assume before_plan_dict and after_plan_dict are populated EXPLAIN plan dictionaries
diff = ExplainPlanArtifact.create_plan_diff(before_plan_dict, after_plan_dict)
print(diff)
```

### Response
#### Success Response (200)
Returns a string representing the differences between the two plans.

#### Response Example
```text
PLAN CHANGES:
Cost: 8430.00 → 2010.00 (4.2x improvement)
Operation Changes:
 -→ Seq Scan on orders
 +→ Index Scan on orders
```
```

--------------------------------

### DatabaseTuningAdvisor: Analyze Workload

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Analyzes a database workload using pg_stat_statements to recommend indexes. Configure budget, runtime, and index width for the search.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.index.dta_calc import DatabaseTuningAdvisor
from postgres_mcp.index.presentation import TextPresentation

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

dta = DatabaseTuningAdvisor(
    sql_driver=driver,
    budget_mb=500,            # max total index storage (default: unlimited)
    max_runtime_seconds=30,   # anytime cutoff (default: 30s)
    max_index_width=3,        # max columns per index (default: 3)
    min_column_usage=1,       # skip columns used in fewer queries
    pareto_alpha=2.0,         # performance/space tradeoff weight
    min_time_improvement=0.1  # stop below 10% improvement
)

# High-level API via TextPresentation (used by the MCP tools)
presenter = TextPresentation(driver, dta)

# Analyze workload from pg_stat_statements
report = await presenter.analyze_workload(max_index_size_mb=500)
print(report)
```

--------------------------------

### Claude Desktop Configuration (Docker)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Configuration for Claude Desktop to connect to a postgres-mcp server running via Docker. Specifies the command to run, arguments, and environment variables including the DATABASE_URI.

```json
{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-e", "DATABASE_URI",
        "crystaldba/postgres-mcp",
        "--access-mode=unrestricted"
      ],
      "env": {
        "DATABASE_URI": "postgresql://username:password@localhost:5432/dbname"
      }
    }
  }
}
```

--------------------------------

### Analyze Workload for Index Recommendations (DTA)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Analyzes the live query workload to recommend optimal indexes using the DTA method. Balances execution time improvement against storage cost. Stops when no candidate yields >=10% time improvement.

```python
# Analyze workload with default DTA algorithm (recommended)
result = await analyze_workload_indexes(
    max_index_size_mb=500,   # total index storage budget in MB
    method="dta"             # "dta" (default) or "llm"
)
# Returns a formatted text report like:
# "Workload Analysis Complete
#  Analyzed 47 unique queries from pg_stat_statements
#
#  Recommended Indexes (3):
#  1. CREATE INDEX ON orders (status, created_at) -- btree
#     Estimated improvement: 4.2x  |  Size: ~12 MB
#     Affected queries: 8
#     Before: Seq Scan (cost 8430) → After: Index Scan (cost 2010)
#  2. CREATE INDEX ON order_items (order_id) -- btree
#     Estimated improvement: 2.1x  |  Size: ~8 MB
#     Affected queries: 12
#  ..."
```

--------------------------------

### Run All Database Health Checks

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Execute all available health checks using `tool.health(health_type="all")`. The result is a comprehensive health report.

```python
report = await tool.health(health_type="all")
print(report)
```

--------------------------------

### list_schemas

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Lists all database schemas available in the PostgreSQL instance.

```APIDOC
## list_schemas

### Description
Lists all database schemas available in the PostgreSQL instance.

### Tool
`list_schemas`
```

--------------------------------

### Clone Postgres MCP Pro Repository

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Clones the Postgres MCP Pro repository from GitHub and navigates into the project directory. This is a standard step for setting up local development.

```bash
git clone https://github.com/crystaldba/postgres-mcp.git
cd postgres-mcp
```

--------------------------------

### DatabaseTuningAdvisor: Analyze Specific Queries

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Analyzes a provided list of SQL queries to recommend indexes. Useful for targeted optimization or when pg_stat_statements is not available.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver
from postgres_mcp.index.dta_calc import DatabaseTuningAdvisor
from postgres_mcp.index.presentation import TextPresentation

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

dta = DatabaseTuningAdvisor(
    sql_driver=driver,
    budget_mb=500,            # max total index storage (default: unlimited)
    max_runtime_seconds=30,   # anytime cutoff (default: 30s)
    max_index_width=3,        # max columns per index (default: 3)
    min_column_usage=1,       # skip columns used in fewer queries
    pareto_alpha=2.0,         # performance/space tradeoff weight
    min_time_improvement=0.1  # stop below 10% improvement
)

# High-level API via TextPresentation (used by the MCP tools)
presenter = TextPresentation(driver, dta)

# Analyze a specific list of queries
report = await presenter.analyze_queries(
    queries=[
        "SELECT * FROM events WHERE user_id = $1 AND event_type = $2",
        "SELECT * FROM events WHERE created_at > $1 ORDER BY created_at DESC LIMIT 100"
    ],
    max_index_size_mb=200
)
print(report)
```

--------------------------------

### Analyze Specific Queries for Index Recommendations

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Analyzes up to 10 explicitly provided SQL queries and recommends optimal indexes using the DTA greedy-search engine. Useful for targeted recommendations on known slow queries.

```python
# Analyze specific slow queries
result = await analyze_query_indexes(
    queries=[
        "SELECT * FROM orders WHERE customer_id = $1 AND status = $2",
        "SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at > $1",
        "SELECT product_id, SUM(quantity) FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE status = 'pending') GROUP BY product_id"
    ],
    max_index_size_mb=200,
    method="dta"
)
# Returns a report with per-query analysis and index recommendations, including
# before/after EXPLAIN plan comparisons and estimated improvement multiples.

# Error cases:
# await analyze_query_indexes(queries=[])
# → Error: Please provide a non-empty list of queries to analyze.

# await analyze_query_indexes(queries=["q1", ..., "q11"])  # > 10 queries
# → Error: Please provide a list of up to 10 queries to analyze.
```

--------------------------------

### List Schemas with list_schemas

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieves a list of all schemas in the connected PostgreSQL database, categorizing them as user, system, or information schemas. This is typically an initial step for an AI agent to understand the database structure.

```python
# Called by the MCP client automatically — shown here as the equivalent Python invocation
result = await list_schemas()
# Returns: [{"schema_name": "public", "schema_owner": "postgres", "schema_type": "User Schema"},
#           {"schema_name": "pg_catalog", "schema_owner": "pg_catalog", "schema_type": "System Schema"},
#           {"schema_name": "information_schema", "schema_owner": "pg_catalog",
#            "schema_type": "System Information Schema"}]
```

--------------------------------

### EXPLAIN ANALYZE

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Executes an SQL query and provides detailed execution statistics, including planning and execution times.

```APIDOC
## tool.explain_analyze

### Description
Executes an SQL query and provides detailed execution statistics, including planning and execution times.

### Method
`await tool.explain_analyze(sql_query: str)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
artifact = await tool.explain_analyze("SELECT COUNT(*) FROM orders")
print(f"Planning: {artifact.planning_time:.1f}ms, Execution: {artifact.execution_time:.1f}ms")
print(artifact.plan_tree.node_type)
```

### Response
#### Success Response (200)
Returns an artifact object containing the EXPLAIN ANALYZE plan and execution details.

#### Response Example
```text
Planning: 10.5ms, Execution: 25.2ms
Aggregate
```
```

--------------------------------

### Cursor/Windsurf Configuration (SSE)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Configuration for Cursor or Windsurf to connect to a postgres-mcp server using the SSE transport. Specifies the type of server and its URL.

```json
{
  "mcpServers": {
    "postgres": {
      "type": "sse",
      "url": "http://localhost:8000/sse"
    }
  }
}
```

--------------------------------

### Run All PostgreSQL MCP Tests

Source: https://github.com/crystaldba/postgres-mcp/blob/main/tests/README.md

Execute all tests within the PostgreSQL MCP package using the 'uv run pytest' command.

```bash
uv run pytest
```

--------------------------------

### Run Specific Test File in PostgreSQL MCP

Source: https://github.com/crystaldba/postgres-mcp/blob/main/tests/README.md

Execute tests from a particular file, such as 'test_obfuscate_password.py', using 'uv run pytest'.

```bash
uv run pytest tests/unit/test_obfuscate_password.py
```

--------------------------------

### Configure MCP Client for SSE Transport (Windsurf)

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This JSON configuration is for Windsurf clients to connect to a Postgres MCP Pro server via SSE transport. Note the 'serverUrl' key used instead of 'url'.

```json
{
    "mcpServers": {
        "postgres": {
            "type": "sse",
            "serverUrl": "http://localhost:8000/sse"
        }
    }
}
```

--------------------------------

### list_schemas

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Lists every schema in the connected database, classifying each as a user schema, system schema, or the ANSI information schema. This is typically the first call when an agent needs to orient itself inside an unfamiliar database.

```APIDOC
## MCP Tool: `list_schemas`

### Description
Lists every schema in the connected database, classifying each as a user schema, system schema (`pg_%`), or the ANSI information schema. This is typically the first call when an agent needs to orient itself inside an unfamiliar database.

### Parameters
None

### Response
#### Success Response (200)
- **schema_name** (string) - The name of the schema.
- **schema_owner** (string) - The owner of the schema.
- **schema_type** (string) - The type of the schema (e.g., "User Schema", "System Schema", "System Information Schema").

### Response Example
```json
[
  {"schema_name": "public", "schema_owner": "postgres", "schema_type": "User Schema"},
  {"schema_name": "pg_catalog", "schema_owner": "pg_catalog", "schema_type": "System Schema"},
  {"schema_name": "information_schema", "schema_owner": "pg_catalog", "schema_type": "System Information Schema"}
]
```
```

--------------------------------

### List Database Objects with list_objects

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Lists database objects within a specified schema and of a given type. Supported object types include 'table', 'view', 'sequence', and 'extension'. For extensions, the schema_name is ignored as they are cluster-wide.

```python
# List all tables in the public schema
result = await list_objects(schema_name="public", object_type="table")
# Returns: [{"schema": "public", "name": "orders", "type": "BASE TABLE"},
#           {"schema": "public", "name": "customers", "type": "BASE TABLE"}]

# List installed extensions (schema_name is ignored for extensions)
result = await list_objects(schema_name="public", object_type="extension")
# Returns: [{"name": "hypopg", "version": "1.4.0", "relocatable": True},
#           {"name": "pg_stat_statements", "version": "1.10", "relocatable": True}]

# List sequences
result = await list_objects(schema_name="public", object_type="sequence")
# Returns: [{"schema": "public", "name": "orders_id_seq", "data_type": "bigint"}]
```

--------------------------------

### TopQueriesCalc.get_top_resource_queries

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Identifies queries consuming a significant fraction of system resources (CPU, I/O, WAL).

```APIDOC
## TopQueriesCalc.get_top_resource_queries

### Description
Identifies queries consuming a significant fraction of system resources (CPU, I/O, WAL) based on a provided threshold.

### Method
`await calc.get_top_resource_queries(frac_threshold: float)`

### Parameters
#### Path Parameters
None

#### Query Parameters
- **frac_threshold** (float) - Required - The fractional threshold for resource consumption (e.g., 0.05 for 5%).

#### Request Body
None

### Request Example
```python
# Queries consuming >5% of any resource
result = await calc.get_top_resource_queries(frac_threshold=0.05)
print(result)
```

### Response
#### Success Response (200)
Returns a list of dictionaries, where each dictionary represents a query and its resource consumption metrics.

#### Response Example
```json
[
  {
    "query": "SELECT ...",
    "calls": 1024,
    "total_exec_time": 51200.0,
    "mean_exec_time": 50.0,
    "stddev_exec_time": 10.0,
    "total_exec_time_frac": 0.06,
    "shared_blks_accessed_frac": 0.07,
    "shared_blks_read_frac": 0.05,
    "shared_blks_dirtied_frac": 0.01,
    "total_wal_bytes_frac": 0.02,
    "shared_blks_hit": 10000,
    "shared_blks_read": 5000,
    "shared_blks_dirtied": 100,
    "wal_bytes": 204800
  }
]
```
```

--------------------------------

### Analyze Database Health (All Checks)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Runs all database health checks and returns a plain-text report. This is the default behavior when no specific health type is provided.

```python
# Run all health checks at once (default)
result = await analyze_db_health(health_type="all")
# Returns a multi-section report covering all checks below.
```

--------------------------------

### Run Specific Test Case in PostgreSQL MCP

Source: https://github.com/crystaldba/postgres-mcp/blob/main/tests/README.md

Execute a single, specific test case, like 'test_pool_connect_success' within 'test_db_conn_pool.py', using 'uv run pytest'.

```bash
uv run pytest tests/unit/test_db_conn_pool.py::test_pool_connect_success
```

--------------------------------

### obfuscate_password: None Input

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Handles None input gracefully by returning None, ensuring that the function does not raise errors when no connection string is provided.

```python
from postgres_mcp.sql import obfuscate_password

# None input is passed through
clean = obfuscate_password(None)  # None
```

--------------------------------

### analyze_query_indexes

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Analyzes a list of specific SQL queries (up to 10) and recommends optimal indexes for them.

```APIDOC
## analyze_query_indexes

### Description
Analyzes a list of specific SQL queries (up to 10) and recommends optimal indexes for them.

### Parameters
#### Request Body
- **sql_queries** (array) - Required - An array of SQL queries to analyze.
  - **query** (string) - Required - The SQL query string.

### Tool
`analyze_query_indexes`
```

--------------------------------

### Execute Arbitrary SQL with SqlDriver

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Executes arbitrary SQL queries using a DbConnPool. Suitable for DDL/DML operations and read queries. Supports forcing read-only transactions.

```python
from postgres_mcp.sql import DbConnPool, SqlDriver

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")

driver = SqlDriver(conn=pool)

# Execute a query
rows = await driver.execute_query("SELECT id, name FROM customers LIMIT 3")
# rows: [RowResult(cells={"id": 1, "name": "Alice"}),
#        RowResult(cells={"id": 2, "name": "Bob"}),
#        RowResult(cells={"id": 3, "name": "Carol"})]

for row in rows:
    print(row.cells["id"], row.cells["name"])

# Execute a write (DDL/DML) — only safe to call directly, not via SafeSqlDriver
rows = await driver.execute_query("INSERT INTO logs (msg) VALUES ('test') RETURNING id")

# Force read-only transaction
rows = await driver.execute_query("SELECT 1", force_readonly=True)
```

--------------------------------

### analyze_workload_indexes

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Analyzes the database workload to identify resource-intensive queries, then recommends optimal indexes for them.

```APIDOC
## analyze_workload_indexes

### Description
Analyzes the database workload to identify resource-intensive queries and recommends optimal indexes for them.

### Tool
`analyze_workload_indexes`
```

--------------------------------

### Run Targeted Database Health Checks

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Perform specific health checks by providing a comma-separated string of `HealthType` values to `tool.health()`. This allows for focused analysis.

```python
report = await tool.health(health_type="index,vacuum,sequence")
```

--------------------------------

### Extension Utility Functions: Check Version Requirement

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Checks if the current PostgreSQL version meets a specified minimum requirement for a feature. Returns a boolean and a message.

```python
from postgres_mcp.sql import (
    SqlDriver, DbConnPool,
    check_extension,
    check_hypopg_installation_status,
    check_postgres_version_requirement,
    get_postgres_version,
    reset_postgres_version_cache,
)

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")
driver = SqlDriver(conn=pool)

# Check version requirement
meets, msg = await check_postgres_version_requirement(driver, min_version=16, feature_name="GENERIC_PLAN")
# (True, "PostgreSQL version 16 meets the requirement for GENERIC_PLAN")
```

--------------------------------

### Configure MCP Client for SSE Transport (Cursor/Cline)

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

This JSON configuration is used by MCP clients like Cursor or Cline to connect to a Postgres MCP Pro server using the SSE transport. It specifies the server URL.

```json
{
    "mcpServers": {
        "postgres": {
            "type": "sse",
            "url": "http://localhost:8000/sse"
        }
    }
}
```

--------------------------------

### Execute Arbitrary SQL

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Executes arbitrary SQL against the database. Use 'unrestricted' mode for all statement types or 'restricted' mode for validated, read-only queries with a timeout. DDL statements only succeed in unrestricted mode.

```python
result = await execute_sql(sql="SELECT id, status, total FROM orders WHERE created_at > NOW() - INTERVAL '7 days'")
```

```python
result = await execute_sql(sql="CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status)")
```

```python
result = await execute_sql(sql="""
    SELECT date_trunc('day', created_at) AS day, COUNT(*) AS order_count
    FROM orders
    GROUP BY 1
    ORDER BY 1 DESC
    LIMIT 7
""")
```

--------------------------------

### list_objects

Source: https://github.com/crystaldba/postgres-mcp/blob/main/README.md

Lists database objects (tables, views, sequences, extensions) within a specified schema.

```APIDOC
## list_objects

### Description
Lists database objects (tables, views, sequences, extensions) within a specified schema.

### Parameters
#### Query Parameters
- **schema_name** (string) - Required - The name of the schema to list objects from.

### Tool
`list_objects`
```

--------------------------------

### TopQueriesCalc.get_top_queries_by_time

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Retrieves and formats performance data from pg_stat_statements, ordered by execution time.

```APIDOC
## TopQueriesCalc.get_top_queries_by_time

### Description
Retrieves and formats performance data from `pg_stat_statements`, ordered by execution time. Automatically adapts to different PostgreSQL versions for time-related columns.

### Method
`await calc.get_top_queries_by_time(limit: int, sort_by: str)`

### Parameters
#### Path Parameters
None

#### Query Parameters
- **limit** (int) - Required - The maximum number of queries to return.
- **sort_by** (str) - Required - Specifies the sorting criteria. Accepted values are "mean" (for mean execution time) or "total" (for total cumulative execution time).

#### Request Body
None

### Request Example
```python
# Top 5 queries by mean execution time per call
result = await calc.get_top_queries_by_time(limit=5, sort_by="mean")
print(result)

# Top 10 by total cumulative execution time
result = await calc.get_top_queries_by_time(limit=10, sort_by="total")
print(result)
```

### Response
#### Success Response (200)
Returns a list of dictionaries, where each dictionary represents a query and its performance statistics.

#### Response Example
```json
[
  {
    "query": "SELECT ...",
    "calls": 1024,
    "total_exec_time": 51200.0,
    "mean_exec_time": 50.0,
    "rows": 1024
  }
]
```
```

--------------------------------

### SqlDriver - Low-Level Query Executor

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Executes arbitrary SQL queries against a database connection pool. It returns results as a list of RowResult objects.

```APIDOC
## SqlDriver

### Description
`SqlDriver` is the base query executor. It accepts a `DbConnPool` and executes arbitrary SQL, returning a list of `RowResult` objects whose `.cells` attribute is a plain `dict[str, Any]`.

### Initialization
```python
from postgres_mcp.sql import DbConnPool, SqlDriver

pool = DbConnPool()
await pool.pool_connect("postgresql://user:pass@localhost:5432/mydb")

driver = SqlDriver(conn=pool)
```

### Execute Query
Executes a given SQL query.

```python
# Execute a query
rows = await driver.execute_query("SELECT id, name FROM customers LIMIT 3")
# rows: [RowResult(cells={"id": 1, "name": "Alice"}),
#        RowResult(cells={"id": 2, "name": "Bob"}),
#        RowResult(cells={"id": 3, "name": "Carol"})]

for row in rows:
    print(row.cells["id"], row.cells["name"])
```

### Execute Write Query
Executes a write query (DDL/DML).

```python
# Execute a write (DDL/DML) — only safe to call directly, not via SafeSqlDriver
rows = await driver.execute_query("INSERT INTO logs (msg) VALUES ('test') RETURNING id")
```

### Force Read-Only Transaction
Executes a query within a read-only transaction.

```python
# Force read-only transaction
rows = await driver.execute_query("SELECT 1", force_readonly=True)
```
```

--------------------------------

### Analyze Database Health (Specific Checks)

Source: https://context7.com/crystaldba/postgres-mcp/llms.txt

Runs specific database health checks based on the provided `health_type`. Accepts a single value or a comma-separated list. Each sub-check is independent.

```python
# Check only index and vacuum health
result = await analyze_db_health(health_type="index,vacuum")
```

```python
# Individual check examples and what they return:

# index — invalid/duplicate/bloated/unused indexes
result = await analyze_db_health(health_type="index")
# "Invalid index check: No invalid indexes found.
#  Duplicate index check: Found 2 duplicate indexes: idx_orders_cust (duplicates orders_pkey)...
#  Index bloat: 3 bloated indexes found (>50% bloat): idx_old_events...
#  Unused index check: 5 unused indexes found: idx_users_legacy..."
```

```python
# connection — pool utilization
result = await analyze_db_health(health_type="connection")
```