gem-hunter

In-database observability for PostgreSQL.
One static binary connects read-only, reads Postgres's own statistics views, and prints a findings-first health report — plus what changed since last time.
No agent, no external service, no write privilege anywhere in the path.

Quickstart · Install · Setup · Commands · CI · MCP · JSON contract · Troubleshooting · Provider notes

Status: beta. The --json contract is versioned (currently 1.5.0, JSON Schema published in schema/) and breaking changes to it are treated as breaking changes to the tool. The human-readable report is not a stable interface — parse --json, not the terminal output.

curl -fsSL https://pgbot.dev/install | sh
pgbot inspect "postgres://pgbot_ro@host:5432/db"

Or set the connection once in the environment and drop the argument — handy for CI and shells, and it keeps the password out of your history and ps:

export DATABASE_URL="postgres://pgbot_ro@host:5432/db"
pgbot inspect

pgbot reads the argument first, then $DATABASE_URL, then $PGBOT_DATABASE_URL, then $PGSERVICE (if you keep your connections in a connection service file, just export PGSERVICE=mydb and drop the argument too). (Shell note: export DATABASE_URL="…" — no $ on the left, no spaces around =.)

Everything pgbot takes from the environment fits in one block — the connection, and (only if you want the optional ask/explain AI layer) one model key:

export DATABASE_URL="postgres://pgbot_ro:…@host:5432/db?sslmode=require"

# optional, for `pgbot ask` / `pgbot explain` — one of:
export OPENAI_API_KEY=sk-…        # → OpenAI (gpt-5.6-terra by default)
export GEMINI_API_KEY=…           # → Google Gemini (AI Studio key)
export ANTHROPIC_API_KEY=…        # → Anthropic (claude-opus-5 by default)
export XAI_API_KEY=…              # → xAI (grok-4.6 by default)
# …or any OpenAI-compatible endpoint, local ones included — see "explain — optional AI layer"

Everything else — inspect, queries, indexes, MCP, CI — is fully deterministic and needs no key; nothing leaves your machine. Provider pinning and model/endpoint overrides: the AI layer.

connected · db.example.com · postgres 17.4 · read-only · 6h20m window

  cache hit  [████████████████████]  99.4%     ok
  lock wait  [████████████░░░░░░░░]  61.0%     query 4f2a
  rollbacks  [██░░░░░░░░░░░░░░░░░░]  12.0%     watch
  idle idx   [██████░░░░░░░░░░░░░░]  18.0 GiB  review

checked · queries · vacuum · replication · checkpoints
        · connections · settings · deadlocks

Database health: 82/100

CRITICAL
● transaction-id age 1.8B — 84% toward wraparound

WARNING
● orders queries 3.2× slower (8 → 26 ms mean)
● 3 unused indexes consume 18 GB
● connection usage reached 87%

Details: pgbot inspect --full   ·   Machine-readable: --json
Ask it: pgbot ask "why is it slow?"

The default report is a graded read: a four-row gauge strip of vital signs (cache hit, lock wait with the culprit query, rollbacks, idle index bytes as a share of the database), a checked line naming the subsystems that came back clean (a tool that names what it verified reads like a colleague who looked, not an alarm), a health score, then findings bucketed CRITICAL / WARNING / NOTE. pgbot inspect --full adds a subsystem status board plus the section tables and per-finding caveats; focused commands (indexes, queries, tables, vacuum) each drill into one signal; pgbot ask "…" and pgbot explain put a plain-language AI reading on top of the same findings. --json is the complete, versioned contract for agents and scripts.

$ pgbot ask "what's wrong?"

Your database is mostly healthy.

1 critical issue:
orders queries became 3.2× slower in the last 6 hours.

Likely cause:
sequential scans increased after the orders table grew 18%.

Recommended:
review an index on customer_id + created_at.

pgbot is a point-in-time diagnostic you run, not a monitoring platform you operate. If you want dashboards, alerting, long retention, and multi-host rollups, run pganalyze / Percona PMM / pgwatch — pgbot doesn't replace them. Reach for pgbot when you want an answer in ten seconds without deploying anything, when you're triaging a database you don't own, or when an AI agent needs structured Postgres findings it can reason over.

• PostgreSQL 14–18 (16–18 fully supported, 14–15 best-effort); collectors degrade rather than fail on older feature sets — see Version support.
• A login role holding pg_monitor — see Setup.
• pg_stat_statements for the queries section (optional; pgbot prints the provider-specific install steps when it's missing).
• advise additionally needs the hypopg extension and PostgreSQL 16+.
• Linux, macOS, Windows — amd64 and arm64.

pgbot inspect --full — a subsystem status board (one row per subsystem, colored ok / warn / fail), followed by the detailed section tables.

pgbot indexes — zero-scan indexes with sizes, and the caveat that matters: on a primary those scan counts are per-node, so a replica may still be using an index that looks unused here. It tells you what not to drop.

pgbot queries — the top statements from pg_stat_statements, ranked by total execution time (the query quietly eating your database) with a share column for each query's slice of total time. Add --by-calls to rank by call count instead — a cheap query run a million times can outweigh an expensive one run twice. Transaction-control and session-SET noise is filtered out. An enabled extension whose statistics could not be read is reported as unavailable, not as an empty workload. The MCP top_queries response preserves enabled and adds available to distinguish those states; a successful empty workload has enabled: true, available: true, and an empty queries array.

$ pgbot queries "$DATABASE_URL"
  total  share  calls  mean       query
  4h11m  61.0%  812.4k 18.55 ms   SELECT * FROM orders WHERE user_id = $1 AND …
  22m3s  17.8%  1.3k   1.02 s     SELECT count(*) FROM events WHERE created_at …
  15m2s  12.0%  99.8k  9.04 ms    INSERT INTO audit_log (actor, action, …) VAL …

pgbot vacuum — autovacuum health per table: dead tuples, dead-tuple ratio, when autovacuum last ran, and a computed due? — whether the table's dead tuples have passed Postgres' default autovacuum trigger (50 + 20% of live rows). Rising dead tuples with due? yes and no recent run is autovacuum falling behind, the early signal for bloat and, eventually, wraparound risk.

$ pgbot vacuum "$DATABASE_URL"
  table               live   dead   dead%  last autovacuum  due?
  public.demo_events  42.9k  33.8k  44.1%  4m ago           yes
  public.churny       5.0k   10.0k  66.7%  never            yes

pgbot tables — the largest tables by total size (heap + indexes + TOAST), each with row count, dead-tuple ratio, and sequential-vs-index scan counts. It's storage accounting and a missing-index radar: a large table with heavy seq scans and few idx scans is a likely index candidate.

$ pgbot tables "$DATABASE_URL"
  size      rows   dead%  seq scans  idx scans  table
  38.7 GiB  19.7M  8.3%   1.5k       112.3M     public.performance_events
  20.0 GiB  1.3M   10.8%  2.5M       121.6M     public.events        ← 2.5M seq scans
  7.1 GiB   5.6M   0.0%   5.0k       46.6M      public.log_entries

pgbot ask "why is it slow?" — a plain-language reading of the same deterministic findings. It leads with the lock contention and refuses to recommend dropping the indexes because replication is active — the caveat is carried into the advice, not lost.

Every command takes the connection the same way — an argument, $DATABASE_URL, $PGBOT_DATABASE_URL, or $PGSERVICE.

Key inspect flags:

Exit codes are a scriptable contract: 0 clean · 1 warn · 2 critical · 3 connection/execution failure · 64 usage error. Suppressed and pre-existing findings never move them.

npx @pgbot/cli fetches the prebuilt binary for your platform from npm (shipped as an optionalDependency, so only the matching one installs) and runs it — nothing to install, works with npm ci --ignore-scripts. It installs the pgbot command (npm i -g @pgbot/cli). The package is scoped: the bare name pgbot is blocked by npm's package-name-similarity rule (too close to got), so npx pgbot returns E404 — use npx @pgbot/cli.

Any Node package manager works. npm, pnpm, Yarn (Classic and Berry) and bun all install it, and each downloads only your platform's binary — the other five are skipped by the packages' os/cpu fields:

The wrapper locates the binary at run time and pgbot ships no install scripts, so it works wherever lifecycle scripts are disabled — npm ci --ignore-scripts, pnpm's blocked-by-default builds, bun. Two Yarn notes: yarn dlx is Berry-only (Classic uses yarn global add), and under Berry's default Plug'n'Play linker there is no node_modules/.bin, so invoke it as yarn pgbot.

Homebrew installs from the pgrundev/homebrew-tap tap; the formula is regenerated by every release and pins the SHA-256 of each platform's release archive. macOS (Intel/Apple Silicon) and Linux (x86_64/arm64).

Against a remote/managed database (RDS, Neon, Supabase, …) the container needs no special networking — it reaches the host directly. Pass the DSN by environment, not as an argument, so the password stays out of ps and your shell history:

export DATABASE_URL="postgres://pgbot_ro:…@yourdb.example.com:5432/db?sslmode=require"
docker run --rm -e DATABASE_URL ghcr.io/pgrundev/pgbot inspect

Use a pg_monitor role, not a superuser. (For a database in a local container, see Postgres in Docker — the networking differs.)

What each path verifies. npm is the convenient path: the packages carry registry integrity hashes and npm provenance, a verifiable link to the GitHub Actions workflow that built them — that attests where the package came from, not that the artifact was signed. install.sh is the verified path: releases ship SHA256 checksums signed with cosign (keyless, via GitHub Actions OIDC), and the script verifies that signature when cosign is on your PATH and always verifies the checksum. For the strongest guarantee, require the signature:

PGBOT_REQUIRE_SIGNATURE=1 curl -fsSL https://pgbot.dev/install | sh

PGBOT_REQUIRE_SIGNATURE=1 hard-fails if cosign is missing or the check doesn't pass. To verify a release by hand:

cosign verify-blob --bundle checksums.txt.cosign.bundle \
  --certificate-identity-regexp '^https://github.com/pgrundev/pgbot/\.github/workflows/release\.yml@' \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com checksums.txt

rm "$(command -v pgbot)"
rm -rf "${XDG_STATE_HOME:-$HOME/.local/state}/pgbot"   # local baseline store

pgbot writes nothing else on your machine — no daemons, no launch agents — and nothing in the database: no extensions, no tables, no roles.

The read-only guarantee is the role, not a flag. Create a login role that holds pg_monitor (so it can see the full statistics views) and has no write grants:

CREATE ROLE pgbot_ro LOGIN PASSWORD '...';
GRANT pg_monitor TO pgbot_ro;
GRANT CONNECT ON DATABASE yourdb TO pgbot_ro;

Or have pgbot init write exactly this for you — tailored to your provider and database name, including the provider-specific pg_stat_statements step. pgbot itself executes none of it (pgbot never writes); you review and run it:

pgbot init "postgres://admin@host:5432/db" | psql "postgres://admin@host:5432/db"
pgbot init --verify "postgres://pgbot_ro:…@host:5432/db"   # confirm the role works

Without pg_monitor, a non-superuser sees only its own sessions in pg_stat_activity and can't read several views fully — pgbot detects this at connect time and tells you exactly which GRANT to run rather than silently reporting partial data.

pgbot additionally pins every session read-only (default_transaction_read_only, statement_timeout=15s, lock_timeout=2s) and wraps each query in its own BEGIN READ ONLY … COMMIT. It commits those read-only probes rather than rolling them back — a read-only transaction writes nothing either way, but a rollback would inflate the xact_rollback counter pgbot itself reports. Those are defence in depth; the role is the boundary.

A run opens one connection pool capped at 4 connections, holds no long transactions (every probe is its own BEGIN READ ONLY … COMMIT under the pinned statement_timeout=15s / lock_timeout=2s), and takes no locks beyond the shared catalog access any SELECT takes. Counters are sampled twice across the --interval gap (default 1s), so a full inspect finishes in a few seconds of wall clock. It is safe to run against a busy primary — pgbot even excludes its own sessions, transactions, and temp usage from what it reports, so it never measures its own footprint as the database's. Run it against a replica if you prefer, noting the per-node index-scan caveat in pgbot indexes.

Pass the connection string as an argument — a URL or a libpq DSN:

pgbot inspect "postgres://pgbot_ro:secret@host:5432/db?sslmode=require"

# the libpq keyword/value DSN form works too:
pgbot inspect "host=host port=5432 dbname=db user=pgbot_ro sslmode=require"

Or set it once in the environment and omit the argument — convenient for a shell session or CI, and it keeps the password out of your shell history and ps output:

export DATABASE_URL="postgres://pgbot_ro:secret@host:5432/db?sslmode=require"

pgbot inspect
pgbot queries     # every command takes the connection the same way
pgbot diff --since 24h

pgbot resolves the connection in this order: the argument first, then $DATABASE_URL, then $PGBOT_DATABASE_URL. Add ?sslmode=require (or stricter) for any database reached over a network.

A database on a private network — RDS/Aurora inside a VPC, or a Postgres behind a bastion — is reached through an SSH jump host:

pgbot inspect "postgres://pgbot_ro@db.internal:5432/appdb?sslmode=verify-full" \
  --ssh-tunnel bastion.example.com    # or user@host:port, or a ~/.ssh/config alias

--ssh-tunnel is global — every command that opens a connection takes it — and $PGBOT_SSH_TUNNEL sets it for a whole session.

The tunnel is a dialer, not an ssh -L forward, so the DSN keeps naming the real host: sslmode=verify-full still validates against that hostname, .pgpass still matches on it, and no local port is left open to everyone else on your machine.

How the jump host is reached comes from your own ssh_config — HostName, Port, User, IdentityFile, IdentitiesOnly, IdentityAgent, StrictHostKeyChecking, UserKnownHostsFile — so a bare alias works and the host key is verified exactly the way your ssh verifies it: a host seen for the first time is accepted under your StrictHostKeyChecking setting and recorded in your known_hosts, and a key that later changes is refused. Your agent is offered before any key on disk, and one SSH connection serves the whole run. Raise --timeout if the link is slow.

pgbot is a client — it connects over the Postgres wire protocol like psql. You never install anything on the database; run pgbot from your laptop, a bastion, CI, or an instance in the same network. Grant pg_monitor to your role (above) and connect. Provider-specific notes:

You can't install on the RDS/Aurora instance itself — it's managed, no OS access. Run pgbot from a client that can reach it:

• Private RDS (recommended for prod): run pgbot from a small EC2 in the same VPC. It reaches the private endpoint over AWS's internal network — no public access, no SSH tunnel, no IP allow-listing. The only rule is the RDS security group allowing 5432 from the EC2's security group.
• Publicly accessible RDS: allow your IP in the RDS security group and connect straight from your laptop.

# on the EC2 (or your laptop for a public instance):
curl -fsSL https://pgbot.dev/install | sh
pgbot inspect "postgres://pgbot_ro@mydb.abc123.us-east-1.rds.amazonaws.com:5432/appdb?sslmode=require"

Grant pg_monitor as the master (rds_superuser) role. Caveat: host metrics (CPU / memory / disk IOPS) live in CloudWatch, not Postgres, so they're out of reach over a connection string — everything else works.

pgbot inspect "postgres://user:pass@ep-xxx.region.aws.neon.tech/dbname?sslmode=require"

• The pooled endpoint has a -pooler host suffix (transaction mode). pgbot detects it and proceeds — rates stay correct — or use the direct (non-pooler) host for session-scoped certainty.
• Neon's default string ships channel_binding=require; pgbot ignores it automatically (the driver can't do channel binding; TLS from sslmode still applies) instead of erroring.
• pg_stat_statements is preloaded — just CREATE EXTENSION pg_stat_statements;.
• Scale-to-zero: after idle, Neon suspends the compute and discards stats. The first run after a wake is a cold window — pgbot suppresses counter-based findings until the window is old enough, so a reset never reads as a −99% regression.

# direct endpoint (session-scoped, best for pgbot):
pgbot inspect "postgres://postgres:pass@db.<ref>.supabase.co:5432/postgres?sslmode=require"
# or the pooled endpoint (:6543, transaction mode) — pgbot notes it and proceeds:
pgbot inspect "postgres://postgres.<ref>:pass@aws-0-<region>.pooler.supabase.com:6543/postgres?sslmode=require"

• The default pooled connection string uses port :6543 (Supavisor, transaction mode). pgbot detects the pooler and proceeds with a note; prefer the direct :5432 endpoint when you can.
• pg_stat_statements is preloaded — CREATE EXTENSION pg_stat_statements;.
• Supabase doesn't hand out superuser; the built-in postgres role already has broad read access, or grant pg_monitor to a dedicated role where allowed.

The connection string depends on where pgbot runs relative to the container.

pgbot on the host, container with a published port. Read the PORTS column of docker ps — 0.0.0.0:6433->5432/tcp means host port 6433 maps to the container's 5432. Connect to the host port:

docker port mypg 5432                    # → 0.0.0.0:6433  (find the host port)
pgbot inspect "postgres://postgres:pw@127.0.0.1:6433/postgres?sslmode=disable"

Use 127.0.0.1, not localhost: localhost resolves to IPv6 (::1) first, which Docker Desktop doesn't forward, so the connect stalls ~10s before falling back to IPv4. Local containers usually have no TLS → sslmode=disable. Find the credentials with docker exec mypg env | grep POSTGRES.

pgbot as a container reaching a DB container. localhost would mean pgbot's own container — join the DB's network and use the container name + internal port 5432:

docker run --rm --network <that-network> ghcr.io/pgrundev/pgbot \
  inspect "postgres://postgres:pw@mypg:5432/postgres?sslmode=disable"

The image is multi-arch (amd64/arm64) and public — no login needed. Prefer passing the DSN by environment so it stays out of the container's argument list:

docker run --rm --network <that-network> -e DATABASE_URL ghcr.io/pgrundev/pgbot inspect

pgbot as a container reaching a DB on the host. Use host.docker.internal (add --add-host=host.docker.internal:host-gateway on Linux).

Rule of thumb: same-network containers address each other by container name + internal port 5432; the host reaches a container by 127.0.0.1 + the published host port. A container with no -> mapping in docker ps isn't reachable from the host at all — publish it with -p, or connect from inside its network.

pgbot inspect <connection-string>   # URL or libpq DSN, or set $DATABASE_URL
  --json                 emit the versioned, PII-free Context (the agent/script contract)
  --interval 1s          gap between the two counter samples (min 500ms)
  --no-store             don't read or write the local baseline
  --no-color             disable ANSI (also honors NO_COLOR and non-TTY)
  --ssh-tunnel <host>    reach the database through an SSH jump host — global, so
                         every command that connects takes it (also $PGBOT_SSH_TUNNEL)

pgbot baselines list                # what's stored locally, per database
pgbot baselines prune <fingerprint> # delete a database's snapshots
pgbot baselines export <fingerprint># dump stored snapshots as JSON

pgbot indexes <connection-string>   # zero-scan indexes + what NOT to drop
  --correlate            grade each index (catalog_proven/needs_code_check/inconclusive) + what to grep in code
pgbot queries <connection-string>   # top pg_stat_statements by total time (--by-calls to re-rank)
pgbot tables  <connection-string>   # largest tables + row counts + seq-vs-index scan pattern
pgbot vacuum <connection-string>    # autovacuum health per table — dead tuples + whether it's due
pgbot tune <connection-string>      # config-tuning recommendations from the workload
pgbot explain <connection-string>   # inspect, then have an AI explain the findings
pgbot sql explain "SELECT …"        # one query's plan as a tree + plain English ($DATABASE_URL)
  --json                 the raw EXPLAIN (FORMAT JSON) instead
  --ai                   append a model's prose reading of the plan
pgbot ask "why is it slow?"         # AI answer grounded on the findings ($DATABASE_URL)
  --yes                  skip the data-disclosure confirmation prompt
pgbot mcp                           # run as an MCP server over stdio (for AI agents)

pgbot mcp speaks the Model Context Protocol on stdio, so an AI agent can call pgbot as a read-only tool. It exposes deterministic tools only and lets the connected model do the explaining:

• inspect — full findings as JSON
• unused_indexes, top_queries, vacuum_health — the CLI's focused views
• suggest_indexes — planner-validated index recommendations (hypopg)
• index_code_correlation — grades each unused/redundant/invalid index (catalog_proven / needs_code_check / inconclusive) and, for the actionable ones, the exact identifiers to grep (all case conventions) with the instruction to search filters (WHERE/JOIN/ORDER BY), never SELECT lists. pgbot never reads your repo; it says what to search for. Also pgbot indexes --correlate [--json].
• record_index_verdict — store what the repo search found, so a later run over a longer window carries strengthening evidence (no DB connection needed)
• explain_plan — the planner's plan for a SELECT (plain EXPLAIN, never executed), with the same deterministic observations that pgbot sql explain prints
• schema_of — a table's columns/indexes/constraints + row estimate, no data
• compare_to_baseline — the diff, with its interval-honesty and reset caveats
• why — the causal chains from stored history (symptom ← mechanism ← antecedent), computed offline from the local store; at least four stored snapshots are needed to produce the three interval observations required for a sustained change
• explain_finding — pgbot's catalogue page for a finding, so the agent explains a recommendation in pgbot's words instead of inventing them

Every tool is read-only, returns a stable JSON shape carrying its exactness label, honors .pgbot.toml suppression, and never exposes a raw connection string or query literals to the model. The agent reasons over the same findings the CLI computes.

In Claude Code it's one line:

claude mcp add pgbot --env DATABASE_URL="postgres://pgbot_ro:…@host:5432/db?sslmode=require" -- pgbot mcp

Or add it to any MCP client's config (Claude Desktop, Cursor, …):

{
  "mcpServers": {
    "pgbot": {
      "command": "pgbot",
      "args": ["mcp"],
      "env": { "DATABASE_URL": "postgres://pgbot_ro@host:5432/db" }
    }
  }
}

With DATABASE_URL set, the agent calls inspect with no arguments; or it can pass connection_string per call to reach several databases. pgbot never writes, so there's nothing an agent can break through it.

It also exposes a dia