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.

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.

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.

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.

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.

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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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