io.github.YawLabs/postgres-mcp

Rolled-up DBA lint pass. One call returns three categories of findings: - sequence_exhaustion: SERIAL / BIGSERIAL / IDENTITY sequences whose `last_value` is above `seqExhaustionThreshold` of `max_value`. The classic incident class. - 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. 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.

Describe a relation: kind (table / view / materialized_view / partitioned_table / foreign_table), columns (name, type, nullable, default), 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.

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 always rolled back, so you can inspect a plan for an INSERT/UPDATE/DELETE without persisting the mutation. Format is `text` (default) or `json`. Pass the raw SQL (not an EXPLAIN-prefixed statement). 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.

Quick health snapshot: server version, database size, connection count, active queries, and table count. Useful as a connection sanity check and to spot runaway queries.

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. Caveat on `relation`: for non-relation waits (transactionid/virtualxid, where the wait is on the blocker's xid rather than a table) `relation` is a best-effort hint -- an alphabetical guess among the blocker's held write-intent locks -- not authoritative. Use the blocked/blocking query text to disambiguate which table is actually contested.

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. When `signaled=false`, the `note` field surfaces postgres's NOTICE explaining why (e.g. 'not a PostgreSQL backend process' for a non-pg PID, 'must be a member of...' for permission denial) so an agent can act on the specific cause rather than guess from a three-way list.

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.

List functions, procedures, and aggregates in a schema. Returns name, arguments, return type, kind (function/procedure/aggregate/window), and implementation language.

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.

List non-system schemas in the database. Excludes `pg_catalog`, `information_schema`, and other `pg_*` internals.

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.

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.

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.

Run a SQL statement guaranteed read-only. Always executes inside a `BEGIN READ ONLY` transaction regardless of `ALLOW_WRITES`, so postgres itself rejects any write attempt. Use this whenever the goal is to read - SELECT, EXPLAIN, SHOW, VALUES, WITH ... SELECT, etc. Hosts that gate tools individually (Claude Code permissions, mcp.hosting) can safely auto-allow this one. 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.

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.

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'.

Tables with high sequential-scan counts relative to index scans - the first place to look for missing-index candidates. Returns 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.

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. 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).

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`.

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 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. 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).

Indexes that have never been scanned or have very low usage. Each unused index costs write amplification (every INSERT/UPDATE maintains it) and disk space. Excludes primary keys and unique constraints (which are load-bearing even with zero scans). Use this before adding new indexes - sometimes the fix is to drop a dead one.