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

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

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

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.

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.

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.

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.

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.

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

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