PostgreSQL MCP Server

What PostgreSQL MCP Server does

PostgreSQL MCP Server exposes PostgreSQL operations as MCP tools for AI assistants. It connects to the database identified by DATABASE_URL and supports SQL execution, schema discovery, query planning, health inspection, access review, and performance analysis.

The server includes both low-level SQL tools and focused database helpers. Agents can list schemas, tables, views, functions, extensions, roles, and privileges; describe tables and relationships; search for columns; inspect locks and replication; and review database statistics. Results from large SQL queries are limited by POSTGRES_MAX_ROWS, which defaults to 1,000 and includes a truncation indicator.

How it works

The server runs as a standard MCP process, commonly launched with npx, and communicates with an MCP client over its configured process connection. User SQL is sent through PostgreSQL's extended query protocol and can use positional parameters, helping avoid string-interpolated queries and stacked statements.

pg_readonly always wraps execution in a read-only transaction and rolls it back. pg_query is also read-only by default, but ALLOW_WRITES=1 removes its in-server read-only wrapper; PostgreSQL permissions still determine what the configured role can actually do. A least-privileged database role remains the primary access boundary. The separate pg_kill tool requires writes to be enabled because it changes backend session state.

pg_explain returns text or JSON plans and can optionally run EXPLAIN ANALYZE. Analyzed statements are rolled back, although effects outside transactional table data, such as sequence advancement or certain function side effects, may remain. The index advisor uses HypoPG to cost hypothetical indexes without creating them on disk and returns only candidates that reduce estimated planner cost.

Setup and configuration

Add the server to an MCP client configuration with npx -y @yawlabs/postgres-mcp@latest and provide a PostgreSQL connection string:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@yawlabs/postgres-mcp@latest"],
      "env": {
        "DATABASE_URL": "postgres://user:password@host:5432/dbname"
      }
    }
  }
}

On Windows, launch npx through cmd /c. Restart the MCP client and approve the server when prompted. Set ALLOW_WRITES to 1 only when write operations are needed; leaving it unset keeps the normal query path read-only. POSTGRES_MAX_ROWS controls the maximum number of rows returned from large query results.

Node.js 22 or newer is required. The package is distributed as a single bundled file with no runtime dependency installation beyond the package launch.

Tools and capabilities

• Execute parameterized SQL with pg_readonly or pg_query.
• List and describe schemas, tables, views, functions, extensions, and columns.
• Generate EXPLAIN plans, including optional analysis, buffers, settings, memory, WAL, serialization, and hypothetical-index options where supported by the PostgreSQL version.
• Recommend indexes with HypoPG and inspect top queries, sequential scans, unused indexes, table bloat, and I/O activity.
• Check connection pressure, active queries, wait events, database statistics, locks, replication, and advisory findings.
• Review roles and table privileges, cancel or terminate backends, and inspect tables without primary keys or row-level security.

Several diagnostics depend on PostgreSQL features or extensions. pg_top_queries requires pg_stat_statements; index recommendations require HypoPG; approximate and exact bloat methods require pgstattuple; and I/O reporting requires PostgreSQL 16 or newer. Permission-limited diagnostic subqueries can return warnings while leaving the rest of a snapshot available.

Limitations and notes

Read-only transactions do not block every possible external side effect. Functions or commands such as backend signaling, file access, large-object export, or COPY ... TO PROGRAM still depend on the privileges of the configured PostgreSQL role. Use a least-privileged role, especially when a client automatically approves read-only tools.

Diagnostic counters are generally cumulative since their associated statistics reset and should be interpreted with their reset timestamps. Connection activity is scoped to the current database in health reports, while lock inspection is cluster-wide. Exact bloat inspection scans tables and can be slow; provide a schema and avoid using it indiscriminately on large databases. Index-advisor results are planner estimates, not measured execution times, and a bounded search can report that its budget was exhausted.