Allows AI assistants to query databases, perform schema introspection, analyze query performance, and run health diagnostics.
Also known as YawLabs/postgres-mcp, postgres-mcp, @yawlabs/postgres-mcp
- Stars
- 10
- Tools
- 23
- Downloads, 30 days
- —
- Latest version
- 0.13.13
- Last commit
- Nov 12, 2026
- License
- MIT
What is PostgreSQL MCP?
PostgreSQL allows AI assistants to query databases, perform schema introspection, analyze query performance, and run health diagnostics. It exposes 23 tools, such as pg_readonly, pg_query, pg_list_schemas.
Summary generated from the project's README
About PostgreSQL MCP server
Developers can manage and analyze PostgreSQL databases directly through AI clients like Claude Code and Cursor using this MCP server. It provides a suite of tools for schema introspection, query performance analysis, and database health diagnostics. The server is designed with a security-first approach, operating in read-only mode by default to prevent accidental data loss or schema changes.
Users connect to their databases by providing a DATABASE_URL in their local configuration. While the server is read-only by default, write access can be enabled via the ALLOW_WRITES environment variable. To ensure safety, the server uses extended query protocols and parameterized queries to prevent SQL injection. It also includes specialized tools like pg_index_advisor for workload optimization and pg_health for quick snapshots of database status, connection counts, and long-running queries.
What you can do with it
- 1Optimize database performanceAsk the AI to recommend new indexes based on your current workload to reduce query costs.
- 2Investigate slow queriesRequest a diagnostic report to identify why specific database operations are performing poorly.
- 3Monitor database healthGet a quick summary of database size, version, and active connections.
Written from the project's README. Every use case is checked against a quote from it.
How to install PostgreSQL MCP
Formats verified 10 Nov 2026Add to CursorAdd to GooseInstall PostgreSQL MCP in Claude Code
$ claude mcp add postgres-mcp -- npx -y @yawlabs/postgres-mcpInstall PostgreSQL MCP in Cursor
~/.cursor/mcp.json
{
"mcpServers": {
"postgres-mcp": {
"command": "npx",
"args": [
"-y",
"@yawlabs/postgres-mcp"
]
}
}
}Install PostgreSQL MCP in VS Code
.vscode/mcp.json
{
"servers": {
"postgres-mcp": {
"type": "stdio",
"command": "npx",
"args": [
"-y",
"@yawlabs/postgres-mcp"
]
}
}
}Install PostgreSQL MCP in VS Code CLI
$ code --add-mcp '{"name":"postgres-mcp","type":"stdio","command":"npx","args":["-y","@yawlabs/postgres-mcp"]}'Install PostgreSQL MCP in Claude Desktop
claude_desktop_config.json
{
"mcpServers": {
"postgres-mcp": {
"command": "npx",
"args": [
"-y",
"@yawlabs/postgres-mcp"
]
}
}
}Install PostgreSQL MCP in Codex
$ codex mcp add postgres-mcp -- npx -y @yawlabs/postgres-mcpInstall PostgreSQL MCP in Codex config.toml
~/.codex/config.toml
[mcp_servers.postgres-mcp] command = "npx" args = ["-y", "@yawlabs/postgres-mcp"]
Install PostgreSQL MCP in Gemini CLI
$ gemini mcp add -s user postgres-mcp npx -- -y @yawlabs/postgres-mcpInstall PostgreSQL MCP in Gemini settings.json
~/.gemini/settings.json
{
"mcpServers": {
"postgres-mcp": {
"command": "npx",
"args": [
"-y",
"@yawlabs/postgres-mcp"
]
}
}
}Started by HostedMCP on 16 Nov 2026
We ran this command for @yawlabs/postgres-mcp 0.13.13 in an isolated container with no network and no keys. It answered and listed 23 tools.
PostgreSQL MCP tools 23
Read from the server with tools/listpg_readonlyRead-onlyRun a SQL statement with no persistent data changes. Always executes inside a `BEGIN READ ONLY` transaction regardless of `ALLOW_WRITES`, so postgres itself rejects any INSERT/UPDATE/DELETE/DDL and the transaction is always rolled back. Use this whenever the goal is to read - SELECT, EXPLAIN, SHOW, VALUES, WITH ... SELECT, etc. Scope caveat for hosts that auto-allow this tool: `READ ONLY` constrains writes to the DATABASE, not every side effect. Functions whose effect is outside the table data - `pg_cancel_backend` / `pg_terminate_backend`, `pg_read_file`, `lo_export`, `COPY ... TO PROGRAM` - are NOT blocked here and are NOT behind the `ALLOW_WRITES` gate that `pg_kill` sits behind. They still require the privileges the `DATABASE_URL` role holds, so a least-privileged role (e.g. `pg_read_all_data`) is what actually bounds this tool. Use `params` for parameterized queries to avoid SQL injection. Params can be strings, numbers, booleans, null, arrays (for postgres arrays / ANY), or objects (for json/jsonb columns). Large result sets are truncated to POSTGRES_MAX_ROWS (default 1000) with a `truncated: true` flag.
pg_queryActionRun a SQL query against the configured PostgreSQL database. Postgres itself is the primary safety gate: the role in `DATABASE_URL` enforces what queries can succeed. The recommended posture is a least-privileged role (e.g. one granted `pg_read_all_data`), which makes writes server-rejected regardless of any env var. `ALLOW_WRITES=1` is a secondary belt-and-braces gate - it lifts the in-server `BEGIN READ ONLY` wrapper, but it cannot grant privileges the role lacks. Useful for managed databases where creating a second role is awkward. For read-only access where you want the guarantee in the tool name, prefer `pg_readonly`. Use `params` for parameterized queries to avoid SQL injection. Params can be strings, numbers, booleans, null, arrays (for postgres arrays / ANY), or objects (for json/jsonb columns). Dates and UUIDs can be passed as ISO strings. Large result sets are truncated to POSTGRES_MAX_ROWS (default 1000) with a `truncated: true` flag.
pg_list_schemasRead-onlyList non-system schemas in the database. Excludes `pg_catalog`, `information_schema`, and other `pg_*` internals.
pg_list_tablesRead-onlyList tables (and optionally views) in a schema. Returns name, type (table/view/materialized view/foreign), and estimated row count (from `reltuples`; null = no ANALYZE yet on PG 14+; 0 may mean empty or unanalyzed on PG <= 13). Paginate via `limit`/`offset` on very large schemas.
pg_describe_tableRead-onlyDescribe a relation: kind (table / view / materialized_view / partitioned_table / foreign_table), columns (name, type, nullable, default, `generated`, `identity`), primary key, foreign keys (outgoing), `referenced_by` (other tables whose FKs point at this one), `constraints` (CHECK / UNIQUE non-PK / EXCLUDE), indexes, and partition info (`partition_of` parent, `partitions` children). Works on views and materialized views too -- PK/FK/constraint/index lists will simply be empty for a plain view. Use `kind` to disambiguate before assuming you can write to the relation. Generated columns (`generated`: 'stored' / 'virtual') and `identity`: 'always' columns are NOT writable -- omit them from INSERT/UPDATE column lists; a generated column's expression is reported as `generation_expression`, never as `default_value`. On PostgreSQL 18+ constraints also report `validated` / `enforced` / `has_period`, and columns report `not_null_validated` -- a NOT VALID not-null constraint means `nullable: false` can still hide NULLs.
pg_list_viewsRead-onlyList views and materialized views in a schema with their SQL definitions. Use this over `pg_list_tables` with `includeViews: true` when you want the view body, not just names.
pg_list_functionsRead-onlyList functions, procedures, and aggregates in a schema. Returns name, arguments, return type, kind (function/procedure/aggregate/window), and implementation language.
pg_list_extensionsRead-onlyList installed PostgreSQL extensions. Returns name, version, schema, and description. Useful to check for pgvector, postgis, pg_stat_statements, uuid-ossp, etc. before writing queries that rely on them.
pg_search_columnsRead-onlySearch for columns by name across all user schemas. Supports SQL LIKE patterns (`%` matches any substring, `_` matches one character). Case-insensitive. Use this instead of iterating `pg_describe_table` when the user asks 'which tables have X'.
pg_explainActionGet the query plan for a SQL statement. By default, this uses plain EXPLAIN (no execution). Set `analyze: true` to run the query with EXPLAIN ANALYZE - for non-SELECT statements, ALLOW_WRITES=1 is required (since ANALYZE actually executes the statement). Writes executed during EXPLAIN ANALYZE are rolled back, so you can inspect a plan for an INSERT/UPDATE/DELETE without persisting the rows -- but only what Postgres rolls back is undone: a sequence the statement advanced (nextval, a serial or identity column) stays advanced, and any side effect of a function the statement called that lands outside table data (pg_terminate_backend, an advisory lock) persists. Format is `text` (default) or `json`. Pass the raw SQL (not an EXPLAIN-prefixed statement). Planner options (all optional): `buffers` reports shared/local/temp block hits and is the fastest way to tell a bad plan from a cold cache - it defaults to TRUE whenever `analyze` is true (matching PostgreSQL 18, which turns it on for you), pass `buffers: false` to suppress it; requesting it WITHOUT `analyze` needs PostgreSQL 13+. `verbose` adds output columns and schema-qualified names. `settings` (PostgreSQL 12+) lists planner GUCs set away from their defaults - the usual explanation for a plan that looks impossible. `wal` (PostgreSQL 13+) reports WAL generated and `serialize` (`none`|`text`|`binary`, PostgreSQL 17+) charges the cost of building the result rows; both require `analyze`. `memory` (PostgreSQL 17+) reports memory used by the PLANNER, so it works with or without `analyze` - use it alone to ask why planning a statement is expensive. `generic_plan` (PostgreSQL 16+) plans a parameterized statement WITHOUT values for its $1/$2 placeholders and cannot be combined with `analyze` or `params`. `costs` and `timing` default to true (as in postgres); set either to false to drop those columns, and note `timing` only applies with `analyze`. Options that need a newer server than the one connected are rejected with an explicit error naming the required version instead of a confusing parse failure. Set `hypothetical_indexes` to a list of `{table, columns, using?}` to ask the planner 'what would the plan be if these indexes existed?' -- requires the HypoPG extension (`CREATE EXTENSION hypopg`). The hypothetical indexes are torn down at the end of the call, never touching real disk.
pg_index_advisorRead-onlyRecommend indexes for a workload, and prove each one pays for itself before recommending it. Give it `statements` (the SQL you care about) or let it take the top N from `pg_stat_statements`; it plans each statement, generates candidate indexes, costs them with HypoPG hypothetical indexes, and returns only the ones that measurably cut estimated cost. How candidates are generated, and the honest limit: this tool has NO SQL parser and does not read your SQL text. It EXPLAINs each statement and harvests the columns the PLANNER reports as filters, join keys, and sort keys, then intersects those tokens with the real column list from `pg_attribute` -- so a candidate can never name a column that does not exist. The extraction is deliberately loose (a token matching a real column name on a different table can slip through); HypoPG is the arbiter, and anything that does not lower cost is discarded. Column ORDER within each candidate is equality columns first (most selective first, from `pg_stats`), then at most one range column, then sort columns. The search is greedy and BOUNDED. Each accepted index stays in place while the rest are re-costed on top of it, so later picks account for what earlier ones already fixed. `max_candidates` caps how many candidates are considered and `max_explains` caps total EXPLAIN round trips; when a cap stops the search early, `budget_exhausted` is true and the result is a truncated search, not a converged one. PostgreSQL 18 note, and it reverses a rule you have probably internalized: PG18 added B-tree SKIP SCAN, so a multi-column index whose LEADING column the query never constrains CAN now be used. The classic 'leading column never filtered means the index is useless' heuristic is wrong on PG18+. This tool gates that prune on the server version -- on PG18+ such candidates are kept and costed (`skip_scan_available: true`, and an accepted one carries `requires_skip_scan`), below PG18 they are pruned as unusable and counted in `candidates_pruned_leading_column`. Requires the HypoPG extension (`CREATE EXTENSION hypopg;`). Hypothetical indexes are session-scoped and are reset before the call returns, on the success and the failure path alike, so they never touch disk and never leak into a later query plan. Statements are only ever EXPLAINed, never executed, inside a `BEGIN READ ONLY` transaction. Costs are PLANNER ESTIMATES, not measurements: they are the right way to compare two plans for the same statement and the wrong way to predict wall-clock time. They are weighted by `calls` when the workload came from pg_stat_statements, so a query run a million times outranks an identical one run twice. Validate a recommendation with `pg_explain` before creating it, and create it with CONCURRENTLY in production (`create_statement_concurrently`).
pg_healthRead-onlyQuick health snapshot: server version, database size, connection counts measured against `max_connections`, active queries with their wait events, a pg_stat_database rollup, and table count. Useful as a connection sanity check and to spot runaway queries, connection-cap pressure, and lock/IO waits. - connections: `total` for the CURRENT database, broken down into `active` / `idle` / `idle_in_transaction` / `idle_in_transaction_aborted` / `other` (starting, fastpath function call, disabled) / `state_unavailable` -- those six sum to `total`. `idle_in_transaction_aborted` is called out separately because it holds locks and blocks vacuum while doing no work and will never commit. `state_unavailable` counts sessions whose `state` reads NULL because the role lacks pg_read_all_stats / pg_monitor membership; a non-zero value means every other bucket is under-counted by at least that much, so do NOT read `active: 0` next to it as an idle database. Plus `cluster_client_backends` (client backends across ALL databases -- those are what actually consume connection slots), `max_connections`, `superuser_reserved_connections`, and `used_fraction` (cluster_client_backends / max_connections). A raw connection count means nothing without the cap; read `used_fraction` first. - active_queries: `pid`, `state`, `query`, `application_name`, `backend_type`, `wait_event_type` / `wait_event` (both NULL when the backend is running rather than waiting -- the single most diagnostic pair in pg_stat_activity). Both are reported verbatim as the server spells them, and that spelling changes between majors: a backend waiting on a buffer pin reports `wait_event_type` 'BufferPin' through PostgreSQL 18 and 'Buffer' from 19 on, with the `wait_event` names beneath it changing to match. Read them against the reported `version` rather than hard-coding a literal. `duration_seconds` (since query_start) and `transaction_age_seconds` (since xact_start). A large transaction_age_seconds next to a small duration_seconds is a long-open transaction, the usual root cause behind lock waits, bloat, and stalled autovacuum. - database_stats: pg_stat_database for the current database -- `deadlocks`, `temp_files` / `temp_bytes` (work_mem spills), `conflicts` (recovery conflicts, only ever non-zero on a replica), `blks_hit` / `blks_read` / `cache_hit_ratio`, and `stats_reset`. Every counter is CUMULATIVE since stats_reset, not a rate -- interpret them against that timestamp. Sub-queries that fail (several of these are permission-gated on managed providers) append to `_warnings` and leave their field null; the rest of the snapshot still returns.
Show all 23 toolsShow fewer tools
pg_top_queriesRead-onlyTop N queries by total or mean execution time. Requires the `pg_stat_statements` extension to be installed and enabled (most managed Postgres providers have it on by default). Returns `{rows, stats_reset, stats_reset_age_seconds, dealloc}`: each row has normalized query text (constants replaced with `?`), call count, total/mean/min/max time in ms, rows returned, and cache hit ratio. Use this to find slow queries worth optimizing. `calls` and `total_time_ms` are cumulative since the last `pg_stat_statements_reset()`, so this ranking only describes the window that started at the top-level `stats_reset` (with `stats_reset_age_seconds` beside it). This is pg_stat_statements' OWN reset clock, read from `pg_stat_statements_info` -- it is independent of the `stats_reset` reported by `pg_seq_scan_tables` / `pg_unused_indexes`, which comes from pg_stat_database, so do not compare the two timestamps or assume one implies the other. `stats_reset: null` means the start of the window is unknown, not that it covers all time. READ `dealloc` BEFORE TRUSTING THE RANKING: it counts how many times entries for the LEAST-EXECUTED statements were evicted because more distinct statements were seen than `pg_stat_statements.max` allows. A non-zero `dealloc` means this ranking is drawn from an INCOMPLETE population -- queries may be missing from these results entirely, and an evicted query's counters restart from zero if it runs again, understating it. The larger `dealloc` is, the more churn, so 'not in the top N' stops being evidence that a query is cheap. Raise `pg_stat_statements.max` to get a complete picture. On pg_stat_statements < 1.9 (before Postgres 14) `pg_stat_statements_info` does not exist, so `stats_reset`, `stats_reset_age_seconds` and `dealloc` are omitted entirely rather than returned as nulls, and a `_warnings` entry says so. On pg_stat_statements >= 1.10 (Postgres 15+), also returns `io_read_time_ms` and `io_write_time_ms` to separate IO-bound from CPU-bound queries (null when track_io_timing = off or the query did no measurable IO -- enable track_io_timing in postgresql.conf to get non-null values). Scoped to the database in DATABASE_URL: pg_stat_statements is cluster-wide, so results are filtered by `dbid` to match every other tool here rather than leaking query text from unrelated databases sharing the cluster.
pg_seq_scan_tablesRead-onlyTables with high sequential-scan counts relative to index scans - the first place to look for missing-index candidates. Returns `{rows, stats_reset, stats_reset_age_seconds}`: each row has seq_scans, idx_scans, live tuples, and the ratio. A high ratio on a large table usually means a query is reading the whole table where an index would suffice. Pair with `pg_top_queries` to find which query is doing it. These counters are cumulative since the last statistics reset, so every ratio here is only meaningful relative to the top-level `stats_reset` (and `stats_reset_age_seconds`). A ratio measured over a window that was reset minutes ago describes that window, not the workload; `stats_reset: null` means the start of the window is unknown. On PostgreSQL 16+ each row also carries `last_seq_scan` and `last_idx_scan` timestamps (null = no such scan since the reset), which separate 'scanned hard months ago' from 'being scanned right now' in a way the raw counts cannot.
pg_unused_indexesRead-onlyIndexes that have never been scanned or have very low usage, largest first. Each unused index costs write amplification (every INSERT/UPDATE maintains it) and disk space, so before adding a new index, check whether the fix is to drop a dead one. Returns `{rows, stats_reset, stats_reset_age_seconds}`. READ THIS BEFORE RECOMMENDING A DROP: `scans` is a counter, not a verdict. It only counts since the last statistics reset, which is why the top-level `stats_reset` and `stats_reset_age_seconds` are part of the answer. If the counters were reset an hour ago, EVERY index looks unused; if `stats_reset` is null, the start of the window is unknown and the counts prove nothing. This list is only trustworthy once the reset age comfortably exceeds the slowest cycle that could use the index - a monthly report, a quarterly close, a yearly job, a failover-only query path. PRIMARY KEY and UNIQUE indexes are already excluded from these results: they enforce a constraint and stay load-bearing at zero scans, so they never appear here and their absence is not evidence of anything. On PostgreSQL 16+ each row also carries `last_idx_scan`, the timestamp of the most recent scan (null = never scanned since the reset). 'Not scanned since 2026-02-14' is a far better basis for a decision than a bare count. On PostgreSQL 18+, do not fall back on the old 'the leading column is never filtered, so this index is dead weight' reasoning. Skip scan lets the planner use a multi-column btree whose leading column is unconstrained, so such an index can now be doing real work.
pg_io_statsRead-onlyI/O observability: cumulative per-backend-type I/O from `pg_stat_io` (PostgreSQL 16+), plus in-flight asynchronous I/O handles from `pg_aios` (PostgreSQL 18+). This is the layer underneath `pg_top_queries` and `pg_health` -- it says WHICH subsystem is doing the I/O (client backends vs autovacuum vs checkpointer vs walwriter) and through which path, which a per-query or per-table view cannot. - io: one row per (`backend_type`, `io_object`, `io_context`) combination. Counters `reads` / `writes` / `extends` / `writebacks` / `hits` / `evictions` / `reuses` / `fsyncs` are bigints returned as decimal strings; `read_time_ms` / `write_time_ms` / `writeback_time_ms` / `extend_time_ms` / `fsync_time_ms` are float8 milliseconds. A timing of 0 next to a non-zero op count means `track_io_timing` is off, NOT that the I/O was free -- turn it on to get real numbers. A NULL counter means the operation is not possible for that combination, which is different from 0. - io[].read_bytes / write_bytes / extend_bytes: a normalized byte figure that means the same thing on every supported server. On PG16-17 it is computed as `op_bytes * <op count>`; on PG18 `op_bytes` was removed and the server reports bytes directly. The top-level `byte_accounting` field says which source produced the numbers. - io[].stats_reset: these are CUMULATIVE counters, so a row is only interpretable next to its reset point. Reported per row because that is how the view reports it; `pg_stat_reset_shared('io')` resets them together in practice, but this tool does not assert that. - Rows whose counters are all zero are omitted by default (`pg_stat_io` is mostly zeros on a quiet system, and the noise buries the handful of rows that matter). Pass `includeZeroRows: true` for the full matrix. - in_flight + io_method: PostgreSQL 18+ ONLY, and both keys are ABSENT on older servers rather than empty/null -- an empty `in_flight` array would read as 'nothing is stalled' when the truth is 'this server cannot tell you'. `in_flight` is live, currently-outstanding async I/O (`pid`, `io_id`, `op`, `state`, `off`, `length`, `target_desc`), which is what you want while a stall is happening rather than after it. `io_method` (`worker` / `io_uring` / `sync`) explains what `in_flight` can contain: with `io_method = sync` there is no asynchronous submission, so the array is legitimately empty no matter how much I/O is running. Requires PostgreSQL 16+. Sub-queries that fail (`pg_stat_io` and `pg_aios` are permission-gated on some managed providers) append to `_warnings` and set their field to null; the rest of the response still returns.
pg_inspect_locksRead-onlyShow current lock contention: which sessions are blocked and who is blocking them. Returns blocked PID, blocking PID, lock types, relation being contested, and the queries involved. Use this first when a tool call hangs or the app feels stuck - it's the fastest way to identify a long-held transaction holding a lock. Row shape: one row per (blocked_pid, blocking_pid) pair. A session waiting on multiple blockers appears on multiple rows -- group/deduplicate by `blocked_pid` if you want a per-blocked-session count. Scope: CLUSTER-WIDE, unlike `pg_health` active_queries, which is filtered to the database in DATABASE_URL. Lock waits cross databases (a shared catalog, a long transaction in a sibling database), so the blocker is not always somewhere this connection could see -- but it does mean a row here may name a pid in another database entirely. `relation` is only resolved for locks in the CURRENT database or on a cluster-shared catalog (pg_authid, pg_database, ...); it is NULL for a lock held in another database, because a pg_class OID is only meaningful within its own database. A NULL `relation` has TWO unrelated causes, and `lock_type` is what tells them apart. When the wait is ON a relation (`lock_type` relation / extend / page / tuple), NULL means the lock is held in another database and cannot be named from here. When the wait is on something else (`transactionid` / `virtualxid` / `advisory`), `relation` was only ever a best-effort hint -- an alphabetical guess among the blocker's held write-intent locks, not authoritative -- so NULL there means the guess found no candidate, and the blocker is very likely LOCAL. Do not read that second case as 'somewhere else': use `blocking_pid` and `blocking_query`, which are populated either way. Note also that pg_cancel_backend / pg_terminate_backend signal by PID across the whole cluster, so even a genuinely cross-database blocker is actionable via `pg_kill`. Use the blocked/blocking query text to disambiguate which table is actually contested.
pg_list_rolesRead-onlyList database roles (users and groups) with their login/superuser/createdb/createrole attributes and inherited role memberships. Use this to answer 'who has access to this database?' without needing to read `pg_authid` directly.
pg_table_privilegesRead-onlyShow which roles have which privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER) on a table or on every table in a schema. If `table` is omitted, the result spans every table in `schema`, ordered by table then grantee. Use this to answer 'who can write to this table?' or to audit schema-wide access before a migration. Visibility caveat: backed by `information_schema.table_privileges`, which postgres filters by what the calling role can see. A least-privileged role may not see grants involving unrelated third-party roles. For a complete picture, run as a superuser or a member of `pg_read_all_data`.
pg_killActionCancel a running query (SIGINT-equivalent) or terminate a backend connection (SIGTERM-equivalent) by PID. Find the PID via `pg_health` active_queries or `pg_inspect_locks`. Requires ALLOW_WRITES=1 since this changes database session state. The role in DATABASE_URL must have permission - cancelling another user's query needs the `pg_signal_backend` role or superuser. Note: `pg_signal_backend` does NOT cover superuser-owned backends - only a superuser can signal another superuser's session. Cancel is graceful; terminate is forceful. `signaled=true` means postgres SENT the signal, not that the target stopped: a session that is idle (or idle in a transaction) ignores a cancel, so re-check with `pg_health` and use `terminate` for an idle session. `signaled=false` means postgres found no backend with that PID; the `note` field carries its own warning (e.g. 'PID 123 is not a PostgreSQL backend process'). A permission denial is NOT a `false`: postgres raises, so the call returns an error with SQLSTATE 42501 that says what the role lacks.
pg_replication_statusRead-onlyReplication overview: configured replication slots, connected replicas (from `pg_stat_replication`), and current WAL position. Use on primary to spot lagging or disconnected replicas, on replicas to see upstream status. Returns empty arrays on a standalone (non-replicated) database rather than erroring.
pg_advisorRead-onlyRolled-up DBA lint pass. One call returns four categories of findings: - sequence_exhaustion: SERIAL / BIGSERIAL / IDENTITY sequences whose `last_value` is above `seqExhaustionThreshold` of `max_value`. The classic incident class. - wraparound_risk: transaction-ID AND multixact wraparound pressure, the classic pageable incident. `{autovacuum_freeze_max_age, autovacuum_multixact_freeze_max_age, databases[], tables[]}`. Those two cluster GUCs are the divisors both lists are measured against (null if unreadable). Multixact IDs are a SEPARATE 32-bit counter, consumed by row-level locking (`SELECT ... FOR SHARE/UPDATE`, FK checks), so a lock-heavy workload can exhaust them while relfrozenxid stays perfectly healthy -- both counters are checked here. `databases` rows: `{database, xid_age (age(datfrozenxid)), mxid_age (mxid_age(datminmxid)), pct_of_freeze_max_age, pct_of_multixact_freeze_max_age, triggered_by}` -- template databases included, since template0 ages like any other and the cluster horizon is the minimum across all of them. `tables` rows: `{schema, table, relkind, xid_age (age(relfrozenxid)), freeze_max_age, pct_of_freeze_max_age, mxid_age (mxid_age(relminmxid)), multixact_freeze_max_age, pct_of_multixact_freeze_max_age, triggered_by}`, where `freeze_max_age` / `multixact_freeze_max_age` are the EFFECTIVE limits -- a per-table `autovacuum_freeze_max_age` / `autovacuum_multixact_freeze_max_age` storage parameter wins over the GUC. A row is returned when EITHER ratio is at or above `wraparoundThreshold`, and `triggered_by` ('xid' | 'multixact' | 'both') says which one did it: 'xid' means chase freezing/autovacuum, 'multixact' means chase the lock-heavy workload burning members. `mxid_age` and `pct_of_multixact_freeze_max_age` are null on rows whose minmxid is InvalidMultiXactId (no multixact ever recorded); such rows can only be xid-triggered. At pct_of_freeze_max_age 1.0 autovacuum forces an anti-wraparound VACUUM, and near 2.1 billion xids (or 4.2 billion multixacts) the server stops accepting writes. `tables` deliberately includes pg_catalog and pg_toast relations -- the culprit is more often a TOAST table or a system catalog than a user table. On PG18+ table rows also carry `pages` / `all_frozen_pages` / `frozen_page_fraction` from `pg_class.relallfrozen` (visibility-map freeze coverage); those three keys are ABSENT on older servers rather than null. - tables_without_primary_key: user tables (plain and partitioned) with no PK defined. Bloat candidates and a sign of design drift; some replication setups also need PKs. Foreign tables are excluded -- PostgreSQL forbids declaring PKs on foreign tables. - public_tables_without_rls: tables in `public` (or any schema in `rlsSchemas`) with row-level security disabled. Useful as a security baseline check. Any category whose query fails (permission-gated catalogs on managed providers) appends to `_warnings` and returns empty; the other categories still return. Use this as the 'what should I be looking at?' starting point, then drill into `pg_unused_indexes`, `pg_table_bloat`, `pg_seq_scan_tables` for the perf side.
pg_table_bloatRead-onlyEstimate table bloat (dead tuples + free space) for tables in a schema. Returns live tuples, dead tuples, dead-tuple ratio, last_vacuum / last_autovacuum timestamps, and total relation size. A high dead_ratio with a stale last_autovacuum is a sign a table needs VACUUM. On PostgreSQL 19+ every row also carries `stats_reset`: the last time THAT relation's counters were reset via `pg_stat_reset_single_table_counters()`. Read it before trusting anything else in the row -- a reset zeroes live_tuples and dead_tuples AND clears last_vacuum / last_autovacuum / last_analyze together, so a table reset a minute ago is indistinguishable from a pristine one without it. The key is ABSENT on older servers rather than null; null on PG19+ means this relation's counters have never been reset. Three methods are available via the `method` parameter: - `estimate` (default): reads pg_stat_user_tables -- fast, no extensions, ANALYZE-driven approximations. Use this first. - `approx`: uses pgstattuple_approx() -- fast sampling pass, more accurate than estimates, requires the pgstattuple extension. - `exact`: uses pgstattuple() -- full table scan, exact counts, slow on large tables, requires the pgstattuple extension. Always pass `schema` with method='exact' -- scanning all user tables in one statement will hit statement_timeout on non-trivial databases. Install pgstattuple with `CREATE EXTENSION pgstattuple` (requires superuser).
Configuration
ALLOW_WRITESDATABASE_URLOAM_BINPOSTGRES_AUDIT_LOG_FILEPOSTGRES_AUDIT_REDACTPOSTGRES_MCP_RUNTIMEPOSTGRES_MCP_SANDBOXPOSTGRES_SSL_REJECT_UNAUTHORIZED
Automated checks
Ran Nov 16, 2026Full report →Passed our automated checks. That is not a security audit: read the code and give the server the narrowest access it needs. How checks work
- Source repository linked and reachableyawlabs/postgres-mcp is public and reachablegithub.com/yawlabs/postgres-mcp ↗Pass
- Package names this repository as its source@yawlabs/postgres-mcp names yawlabs/postgres-mcp as sourcenpmjs.com/package/@yawlabs/postgres-mcp ↗Pass
- Build provenance (Sigstore attestation)No build attestation published for this releaseNot checked
- Open source license (OSI)MITspdx.org/licenses/MIT.html ↗Pass
- Commit or release in the last yearLast commit or release 2026-11-16github.com/yawlabs/postgres-mcp ↗Pass
- OpenSSF ScorecardNot checked
- Package name not close to a popular packageNo popular package one edit away from @yawlabs/postgres-mcpPass
- No critical advisory in the package or its dependenciesthe package checked; no critical advisoryosv.dev ↗Pass
- No hidden instructions to the model23 tool descriptions and the README read; no override phrases, credential paths or hidden charactersPass
- Destructive tools are labelledNo tool named like delete, drop or execPass
- Remote endpoint answers the MCP handshakeNot checked
README
View on GitHub@yawlabs/postgres-mcp
One click adds this to your local Yaw MCP config so it's available in every Yaw Terminal session. Or install manually below.
Query a PostgreSQL database from Claude Code, Cursor, and any MCP client. Read-only by default - writes opt in via a single env var - so an agent can't silently drop your tables.
Built and maintained by Yaw Labs.
What's new in 0.12
An index advisor, opt-in audit logging, structured tool output, and support for current-revision MCP clients. Full detail in the CHANGELOG.
pg_index_advisorrecommends indexes for a workload and keeps only the ones that measurably lower estimated cost. Candidates are costed with HypoPG hypothetical indexes, never created on disk, and the search knows that PostgreSQL 18's skip scan changes which multi-column indexes are useful.- Opt-in audit logging of the SQL tools send: one JSON line per audited statement, to stderr or a file. Bound parameter values are never recorded. Off by default, and not a complete record -- see Audit logging.
- Structured tool output. Every tool declares an
outputSchemaand returnsstructuredContentalongside the unchanged text block, so anything reading the text today keeps working. - Both MCP protocol eras. The server used to speak only the legacy (2025) protocol revisions, which a client on the current 2026-07-28 revision fails against. It now serves both.
On 0.12.0? Upgrade. 0.12.1 closes a stacked-query hole in pg_index_advisor: SQL passed in its statements argument ran on a protocol that accepts several commands in one string, so SELECT 1; COMMIT; DROP SCHEMA public CASCADE; escaped the read-only transaction. The tool is annotated read-only, so hosts often auto-allow it. The same release stops pg_inspect_locks attributing a lock held in another database to whichever local table shares its OID, fixes the advisor's greedy search, and makes audit lines carry the tool field they were missing.
Coming from 0.10.x? 0.11.0 has three breaking changes: pg_seq_scan_tables, pg_unused_indexes and pg_top_queries return an envelope (read data.rows where you used to read data), pg_explain with analyze: true emits BUFFERS (pass buffers: false for the old output), and Node 22 is the floor. Details in the 0.11.0 changelog entry.
Backstory
Anthropic's reference Postgres MCP server, @modelcontextprotocol/server-postgres, was archived in May 2025 and marked deprecated on npm in July 2025. Anthropic has not shipped a replacement. Despite the deprecation, the last published version (v0.6.2) is still pulled ~20,000 times per week - a lot of agents are pointed at an unmaintained package.
That unmaintained package also has a known, publicly documented stacked-query SQL injection (Datadog Security Labs) that bypasses its BEGIN READ ONLY wrapper with input like COMMIT; DROP SCHEMA public CASCADE;. It has never been patched at npm.
A handful of community forks have appeared, but each fills a narrow slice:
@zeddotdev/postgres-context-server- Zed's fork, primarily a security patch on the original shape.- Postgres MCP Pro (Crystal DBA) - focused on index tuning and hypothetical-index / buffer-cache diagnostics.
- AWS Labs Postgres MCP - tied to Aurora / RDS Data API + Secrets Manager.
None of them position themselves as a general-purpose daily driver you'd hand to Claude Code or Cursor against an arbitrary Postgres: modern introspection, perf helpers, role/privilege awareness, and a write-safety posture out of the box. That's the gap @yawlabs/postgres-mcp fills.
Why this one?
- Read-only by default, with an unconditional read-only tool too -
pg_queryruns user SQL in aBEGIN READ ONLYtransaction, so postgres itself (not string parsing) blocks writes; opt in to writes withALLOW_WRITES=1.pg_readonlyis a separate tool that stays read-only regardless ofALLOW_WRITES, so hosts that gate tools individually (Claude Code permissions, mcp.hosting) can auto-allow it -- paired with a least-privileged role, sinceREAD ONLYbounds writes to the database rather than every side effect (details). - Role-based access as the primary control - the recommended posture is to use a least-privileged postgres role in
DATABASE_URL(e.g. one withGRANT pg_read_all_data); postgres itself then enforces the boundary, no env var needed. See Configuring access. - Extended query protocol for all user SQL -
pg_querysends user input withqueryMode: 'extended', which restricts each request to a single statement. This closes the stacked-query injection class (COMMIT; DROP SCHEMA x CASCADE;) that defeated the reference server'sBEGIN READ ONLYwrapper. Integration test asserts the rejection. - Parameterized queries -
pg_querytakes aparamsarray for$1,$2, etc. No string-interpolated SQL in our code path. - Written from scratch, actively maintained - not a fork of the deprecated code. Unit + integration tests (
npm test,npm run test:integration) run against a real Postgres; releases cut viarelease.sh. - Schema introspection built in -
pg_list_schemas,pg_list_tables,pg_describe_tablereturn columns, primary keys, foreign keys, and indexes without the agent having to rememberpg_catalogjoins. EXPLAINas a first-class tool - text or JSON format, with optionalANALYZE. ANALYZE for non-SELECT statements requiresALLOW_WRITES=1and always rolls back, so the plan is real but the written rows don't persist. (What Postgres never rolls back still sticks: a sequence the statement advanced stays advanced.)- Perf diagnostics the deprecated server never had -
pg_top_queries(frompg_stat_statements),pg_seq_scan_tables,pg_unused_indexes,pg_table_bloat,pg_inspect_locks,pg_replication_status. Answer "why is this slow?" in one tool call. - Health snapshot -
pg_healthreturns version, db size, connection counts, and the 10 longest-running active queries in one call. - Role and privilege awareness -
pg_list_rolesandpg_table_privilegesfor the common "who can touch what?" questions. - Instant startup - ships as a single bundled file with zero runtime dependencies. No multi-minute
node_modulesinstall on everynpxcold start. - Result truncation - large result sets are capped at
POSTGRES_MAX_ROWS(default 1000) with atruncated: trueflag, so a straySELECT * FROM eventsdoesn't blow out the model context.
Quick start
1. Create .mcp.json in your project root
macOS / Linux / WSL:
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": ["-y", "@yawlabs/postgres-mcp@latest"],
"env": {
"DATABASE_URL": "postgres://user:password@host:5432/dbname"
}
}
}
}
Windows:
{
"mcpServers": {
"postgres": {
"command": "cmd",
"args": ["/c", "npx", "-y", "@yawlabs/postgres-mcp@latest"],
"env": {
"DATABASE_URL": "postgres://user:password@host:5432/dbname"
}
}
}
}
Why the extra step on Windows? Since Node 20,
child_process.spawncannot directly execute.cmdfiles (that's whatnpxis on Windows). Wrapping withcmd /cis the standard workaround.
2. Restart and approve
Restart Claude Code (or your MCP client) and approve the postgres MCP server when prompted.
3. (Optional) Enable writes
Read-only is the default. If you want the agent to be able to INSERT, UPDATE, DELETE, or run DDL, add ALLOW_WRITES=1 to the env block:
"env": {
"DATABASE_URL": "postgres://...",
"ALLOW_WRITES": "1"
}
Prefer scoping this to dev/test databases - for production, leave writes off and use migration tools out-of-band.
Configuring access
The role in DATABASE_URL is the primary access control. Postgres has had a battle-tested permission system for 30 years; lean on it instead of relying on ALLOW_WRITES alone. A least-privileged role makes writes server-rejected no matter what tools or env vars are configured.
Read-only agent (recommended default):
CREATE ROLE mcp_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_db TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT pg_read_all_data TO mcp_reader;
Point DATABASE_URL at mcp_reader. Postgres rejects every write, every DDL, every privilege change - regardless of ALLOW_WRITES. No app-level guard to bypass; the database is the boundary.
Scoped write agent (dev/test or narrow production use):
CREATE ROLE mcp_writer LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_db TO mcp_writer;
GRANT USAGE ON SCHEMA public TO mcp_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mcp_writer;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO mcp_writer;
-- DDL not granted -- the agent can change data but not schema.
Set ALLOW_WRITES=1 so pg_query will issue writes, and rely on the role to keep the agent away from DDL and other schemas.
Per-tool gating in the host:
Tools split cleanly across two authority classes:
- Auto-allow:
pg_readonly(server-sideBEGIN READ ONLY, unconditional),pg_index_advisor(only ever EXPLAINs the statements it is given, never with ANALYZE, insideBEGIN READ ONLY), plus the introspection and diagnostics tools (pg_list_*,pg_describe_table,pg_search_columns,pg_health,pg_inspect_locks,pg_table_bloat,pg_unused_indexes,pg_seq_scan_tables,pg_top_queries,pg_io_stats,pg_replication_status,pg_advisor,pg_table_privileges). - Always prompt:
pg_query(writes whenALLOW_WRITES=1is set and the role allows it),pg_explain(analyze: trueexecutes the statement; withALLOW_WRITES=1that includes writes, which are rolled back, and without it the tool has the same reach aspg_readonly),pg_kill(changes session state; requiresALLOW_WRITES=1).
The split follows each tool's readOnlyHint annotation, and Claude Code's permissions block and mcp.hosting's per-tool toggle both honor it.
What
READ ONLYdoes and does not cover. ABEGIN READ ONLYtransaction blocks writes to the database -- INSERT/UPDATE/DELETE, DDL,nextval/setval. It does not block functions whose effect lands outside the table data.SELECT pg_terminate_backend(...),pg_cancel_backend,pg_read_file,lo_export, andCOPY ... TO PROGRAMall run to completion insidepg_readonly, which means auto-allowingpg_readonlyreaches the same capability thatpg_killputs behindALLOW_WRITES=1. Every one of them still requires a privilege theDATABASE_URLrole must actually hold (pg_signal_backend,pg_read_server_files, superuser), so the role is the control that bounds this tool, not the transaction mode. If you auto-allowpg_readonly, use a least-privileged role -- see Configuring access.
ALLOW_WRITES as defense-in-depth:
ALLOW_WRITES is a secondary belt-and-braces gate. Useful when:
- You're on a managed database where creating a second role is awkward (some Supabase/Neon plans).
- You want a single role that can write, but want the MCP server to refuse writes anyway during normal operation.
Otherwise, configure the role and stop relying on ALLOW_WRITES.
What can an agent do with this?
Once connected, the agent picks tools automatically based on what you ask. A few single-tool examples:
- "Describe the users table" ->
pg_describe_table-> returns kind, columns, PK, FKs, indexes. - "Which tables have a
user_idcolumn?" ->pg_search_columnswith patternuser_id-> one call instead of iterating every table. - "This query is slow, why?" ->
pg_explainwithanalyze: true-> returns the plan with actual row counts and timing. - "What's the slowest query we run?" ->
pg_top_queries-> returns the top N frompg_stat_statementswith mean/total/min/max times. - "Do we have any unused indexes?" ->
pg_unused_indexes-> returns non-unique, non-primary indexes with zero or low scan counts + their size. - "Is
pgvectorinstalled?" ->pg_list_extensions-> yes/no with version.
The bigger leverage is multi-tool reasoning. A few real workflows:
- Unstick a hung app.
pg_inspect_locksreturns blocked PID + blocking PID + the offending query, thenpg_kill(ALLOW_WRITES=1required) cancels the blocker. The agent can run both in one turn - it's the fastest path from "the app is frozen" to "back up." - Chase a slow page.
pg_top_queriesranks the worst queries,pg_explainwithanalyze: trueshows the plan for the top hit,pg_seq_scan_tablesandpg_unused_indexessay whether the answer is "add an index here" or "drop a dead one there." - Oncall triage.
pg_healthchecks connectivity + active-query count + database size;pg_inspect_locksandpg_replication_statusconfirm whether contention or replication lag is in play before paging the on-call DBA.
Tools
| Tool | Description |
|---|---|
pg_readonly |
Run a SQL statement with no persistent data changes - always inside BEGIN READ ONLY, regardless of ALLOW_WRITES. The recommended tool for read access, and the one to auto-allow for ad-hoc SQL; pair it with a least-privileged role (why). |
pg_query |
Run a SQL query. Writes gated by the role in DATABASE_URL first, ALLOW_WRITES second. Supports parameterized queries via params. Result fields include dataTypeName (e.g. int4, jsonb) alongside dataTypeID. |
pg_list_schemas |
List non-system schemas. |
pg_list_tables |
List tables (and optionally views) in a schema with estimated row counts. Paginated via limit/offset. |
pg_describe_table |
Kind, columns, PK, outgoing FKs, incoming FKs (referenced_by), CHECK / UNIQUE / EXCLUDE constraints, indexes, and partition parent/children for a relation. Generated and identity columns are flagged (generated, identity, generation_expression) so an agent doesn't try to write to them. Constraints carry validated, plus enforced / has_period on PG18+. |
pg_list_views |
List views and materialized views in a schema, including their SQL definitions. |
pg_list_functions |
List functions, procedures, and aggregates in a schema with signatures and return types. |
pg_list_extensions |
List installed extensions (pgvector, postgis, pg_stat_statements, etc.) with versions. |
pg_search_columns |
Find columns by name pattern across all user schemas. Case-insensitive, supports SQL LIKE wildcards. |
pg_explain |
EXPLAIN or EXPLAIN ANALYZE for a SQL statement. Text or JSON output. Planner options: buffers (on by default with analyze), settings, verbose, wal, costs, timing, plus generic_plan (PG16+, plan a parameterized query with no values) and memory / serialize (PG17+). Optional hypothetical_indexes (requires the HypoPG extension) lets you ask "what would the plan be with these indexes?" without creating them on disk. |
pg_index_advisor |
Recommend indexes for a workload and prove each one pays for itself first. Takes statements you pass or the top N from pg_stat_statements, harvests candidate columns from what the planner reports as filters / join keys / sort keys (no SQL parser - every token is intersected with the real pg_attribute column list), then costs each candidate with HypoPG hypothetical indexes and keeps only what measurably lowers estimated cost. Greedy and bounded via max_candidates / max_explains, so a big workload cannot run away; budget_exhausted flags a truncated search. Returns the CREATE INDEX (plus a CONCURRENTLY form), cost before/after, which statements each index helps, and the estimated size. PG18-aware: PG18 added B-tree skip scan, so a multi-column index whose leading column is never filtered is no longer useless - that classic prune is gated on the server version rather than applied blindly. Requires HypoPG; indexes are session-scoped and reset on every exit path. |
pg_health |
Server version, database size, connections against max_connections, active queries with wait events and transaction age, pg_stat_database rollup (deadlocks, temp files, cache hit ratio), table count. |
pg_top_queries |
Top N queries by total/mean execution time. Requires the pg_stat_statements extension. Returns stats_reset (from pg_stat_statements_info, a different clock from the other stats tools) and dealloc on extension 1.9+ - a non-zero dealloc means entries were evicted past pg_stat_statements.max, so the ranking is drawn from an incomplete population. |
pg_seq_scan_tables |
Tables with heavy sequential scans - missing-index candidates. Returns the stats_reset window alongside the rows, since the counters mean nothing without it. last_seq_scan / last_idx_scan on PG16+. |
pg_unused_indexes |
Non-unique, non-primary indexes with low scan counts - drop candidates. Also returns stats_reset: a recently reset counter makes every index look unused, which is how a load-bearing index gets dropped. last_idx_scan on PG16+. |
pg_io_stats |
I/O observability: pg_stat_io read/write/extend/fsync counts, bytes and times per backend type and context (PG16+), plus in-flight async I/O handles from pg_aios and the active io_method (PG18+). |
pg_inspect_locks |
Who is blocking whom right now (blocked PID, blocker PID, lock type, queries). |
pg_list_roles |
Database roles with login/superuser/createdb flags and group memberships. |
pg_table_privileges |
Who has SELECT/INSERT/UPDATE/DELETE/etc. on a table or whole schema. |
pg_table_bloat |
Tables with high dead-tuple ratios - VACUUM candidates. |
pg_replication_status |
Replication slots, connected replicas, and current WAL position. |
pg_advisor |
Rolled-up DBA lints in one call: sequence-exhaustion candidates, wraparound risk for both counters (per-database and per-table age(relfrozenxid) against autovacuum_freeze_max_age, and mxid_age(relminmxid) against autovacuum_multixact_freeze_max_age -- a lock-heavy workload can exhaust multixacts while xids look healthy; triggered_by says which), tables without a primary key, and (configurable) public tables with RLS disabled. The "what should I be looking at?" starting point. |
pg_kill |
Cancel a running query or terminate a backend connection. Requires ALLOW_WRITES=1. |
PostgreSQL MCP questions
How do I add PostgreSQL MCP to Claude Code?
Run claude mcp add postgres-mcp -- npx -y @yawlabs/postgres-mcp in your terminal. Add --scope user to make it available in every project.
How do I add PostgreSQL MCP to Cursor?
Open ~/.cursor/mcp.json and add the server under mcpServers, using the Cursor config shown in the install section above.
What tools does PostgreSQL MCP provide?
It exposes 23 tools, including pg_readonly, pg_query, pg_list_schemas, pg_list_tables, pg_describe_table.
Is the server read-only by default?
Yes, the server is read-only by default to prevent accidental data loss. You must explicitly opt in to writes using the ALLOW_WRITES environment variable.
How can I enable write access for the AI agent?
To allow the agent to perform INSERT, UPDATE, DELETE, or DDL operations, add ALLOW_WRITES=1 to your environment configuration.
What performance diagnostic tools are included?
The server includes tools for analyzing top queries, sequential scans, unused indexes, table bloat, locks, replication status, and more.
Does it support schema introspection?
Yes, it includes built-in tools to list schemas, tables, and describe tables including columns, primary keys, foreign keys, and indexes.