# PostgreSQL MCP Server [Health: Active]

**Category:** 🗄️ Databases  
**Repository:** https://github.com/YawLabs/postgres-mcp  
**GitHub Stars:** 10  
**npm Downloads (last month):** 6988  
**Views:** 5  
**Installs:** 0  
**Upvotes:** 0  
**Directory Page:** https://allmcps.com/mcp/postgresql-mcp-server-4

## Description
PostgreSQL MCP server - query, schema introspection, explain, and health checks for AI assistants

## Tools
Capabilities this server exposes over MCP:

- **pg_readonly** — Run 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_query** — Run 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_schemas** — List non-system schemas in the database. Excludes `pg_catalog`, `information_schema`, and other `pg_*` internals.
- **pg_list_tables** — List 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_table** — Describe 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_views** — List 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_functions** — List functions, procedures, and aggregates in a schema. Returns name, arguments, return type, kind (function/procedure/aggregate/window), and implementation language.
- **pg_list_extensions** — List 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_columns** — Search 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_explain** — Get 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_advisor** — Recommend 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_health** — Quick 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.
- **pg_top_queries** — Top 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_tables** — Tables 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_indexes** — Indexes 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_stats** — I/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_locks** — Show 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_roles** — List 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_privileges** — Show 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_kill** — Cancel 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_status** — Replication 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_advisor** — Rolled-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_bloat** — Estimate 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).

## Claude Desktop Quick Installation
Install path detected from listing signals. Uses `npx` (confidence: high):

```json
"mcpServers": {
  "postgresql-mcp-server": {
    "command": "npx",
    "args": ["-y","@yawlabs/postgres-mcp@latest"],
    "env": {
      "DATABASE_URL": "",
      "ALLOW_WRITES": "",
      "POSTGRES_MAX_ROWS": ""
    }
  }
}
```

**Requires environment variables:** `DATABASE_URL`, `ALLOW_WRITES`, `POSTGRES_MAX_ROWS` — the values above are empty placeholders; fill in real credentials before running (see the repository for what each one is for).

## Documentation

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

```json
{
  "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.

_Full upstream README: https://allmcps.com/mcp/postgresql-mcp-server-4/readme_