# AI use cases for Pgbot (pgrundev/pgbot) Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Use cases: 5 | Page: https://dedupedai.com/use-cases/pgrundev-pgbot/ pgbot is a read-only Go command-line tool that reads PostgreSQL's own statistics views and prints a findings-first health report with changes since the last run. This file holds every use case of this repo. Each one starts with a "## " heading and ends with its URL. - Triage a slow PostgreSQL database in ten seconds: https://dedupedai.com/use-cases/pgrundev-pgbot/triage-slow-postgres-in-ten-seconds/ - Find the queries that eat the most database time: https://dedupedai.com/use-cases/pgrundev-pgbot/find-queries-eating-database-time/ - Spot unused indexes and likely missing indexes: https://dedupedai.com/use-cases/pgrundev-pgbot/spot-unused-and-missing-indexes/ - Gate schema migrations in CI with a Postgres lint: https://dedupedai.com/use-cases/pgrundev-pgbot/gate-schema-migrations-in-ci/ - Let Claude Code read PostgreSQL findings over MCP: https://dedupedai.com/use-cases/pgrundev-pgbot/let-claude-code-read-postgres-findings/ ## How to triage a slow PostgreSQL database in ten seconds with Pgbot Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Difficulty: Intermediate | Time: 15 minutes | Basis: From the README Run one read-only health check and get ranked findings for a database you do not own. **Who it is for:** An on-call engineer or database owner who needs to know what is wrong with a PostgreSQL database right now, without deploying monitoring. **The problem:** Digging through pg_stat views by hand is slow, and dashboards need setup before they help during an incident. **The outcome:** A findings-first report that grades cache hit, lock wait, rollbacks, and idle index bytes, then lists CRITICAL, WARNING, and NOTE findings. ### Steps 1. Install Pgbot with `curl -fsSL https://pgbot.dev/install | sh`. 2. Generate the read-only role setup SQL with `pgbot init`, review it, and run it yourself, since Pgbot executes nothing. (inferred, check the README) 3. Set the connection once with `export DATABASE_URL="postgres://pgbot_ro@host:5432/db"`. 4. Run `pgbot inspect` for the graded summary. 5. Run `pgbot inspect --full` for the subsystem status board and section tables. ### Prompt to paste into your agent ```text Using the pgbot tool from the pgrundev/pgbot repository, run `pgbot inspect --json` against the database whose connection string is in . Report the CRITICAL and WARNING findings and the health score. Verify that the JSON contract version reported is 1.5.0 before you trust the findings. ``` ### Caveat The human-readable report is not a stable interface, so scripts and agents should parse --json instead of the terminal output. Risk, credentials: Connection strings contain the role password, so use the environment variable rather than putting the password in shell history or the ps output. Disclaimer: Third-party repos we don't own or vouch for. AI-generated use cases can be wrong: review everything and run at your own risk. Full text: https://dedupedai.com/disclaimer/ URL: https://dedupedai.com/use-cases/pgrundev-pgbot/triage-slow-postgres-in-ten-seconds/ ## How to find the queries that eat the most database time with Pgbot Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Difficulty: Intermediate | Time: 30 minutes | Basis: From the README Rank statements by total time or call count to pick the first query to tune. **Who it is for:** A developer or DBA who knows the app is slow and needs to see which SQL statements take the most database time. **The problem:** A cheap query run a million times can cost more than an expensive query run twice, and the two are hard to compare by hand. **The outcome:** A ranked list of statements with total time, share of total time, call count, and mean time, ready to hand to a tuning pass. ### Steps 1. Confirm the pg_stat_statements extension is installed on the target, since Pgbot prints install steps if it is missing. 2. Set the connection with `export DATABASE_URL="postgres://pgbot_ro@host:5432/db"`. 3. Run `pgbot queries` to rank statements by total execution time. 4. Run `pgbot queries --by-calls` to rank by call count and compare the two lists. (inferred, check the README) 5. Run `pgbot ask "why is it slow?"` for a plain-language reading of the same findings, if you have a model key set. ### Prompt to paste into your agent ```text Use the pgbot tool from the pgrundev/pgbot repository to run `pgbot queries --json` against . Return the top five statements by total time with their share and call count. Verify that the response has enabled true and available true before you report an empty list as a real empty workload. ``` ### Caveat Query statistics only exist when pg_stat_statements is enabled, and Pgbot reports an extension it cannot read as unavailable rather than as an empty workload. Disclaimer: Third-party repos we don't own or vouch for. AI-generated use cases can be wrong: review everything and run at your own risk. Full text: https://dedupedai.com/disclaimer/ URL: https://dedupedai.com/use-cases/pgrundev-pgbot/find-queries-eating-database-time/ ## How to spot unused indexes and likely missing indexes with Pgbot Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Difficulty: Intermediate | Time: 30 minutes | Basis: From the README Find zero-scan indexes and large tables with heavy sequential scans before dropping or adding anything. **Who it is for:** A database engineer tuning schema and indexes who must avoid dropping an index that a replica still uses. **The problem:** Index drops and additions are risky when scan counts are misread, and manual checks across tables take time. **The outcome:** A list of zero-scan indexes with sizes, a list of large tables with heavy sequential scans, and a caveat about which indexes not to drop. ### Steps 1. Set the connection with `export DATABASE_URL="postgres://pgbot_ro@host:5432/db"`. 2. Run `pgbot indexes` to list zero-scan indexes with sizes. 3. Read the caveat on the output: scan counts are per node, so check replicas before you drop anything. 4. Run `pgbot tables` and look for large tables with many seq scans and few idx scans as index candidates. 5. On PostgreSQL 16 or later with hypopg installed, run `pgbot advise` for planner-validated missing-index suggestions. (inferred, check the README) ### Prompt to paste into your agent ```text Use the pgbot tool from the pgrundev/pgbot repository to run `pgbot indexes --json` and `pgbot tables --json` against . List the zero-scan indexes with their sizes and the large tables with heavy sequential scans. Verify that you do not recommend dropping any index while replication is active without stating the per-node scan caveat. ``` ### Caveat The advise command needs the hypopg extension and PostgreSQL 16 or later, and its planner checks run against the connected database only. Disclaimer: Third-party repos we don't own or vouch for. AI-generated use cases can be wrong: review everything and run at your own risk. Full text: https://dedupedai.com/disclaimer/ URL: https://dedupedai.com/use-cases/pgrundev-pgbot/spot-unused-and-missing-indexes/ ## How to gate schema migrations in CI with a Postgres lint with Pgbot Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Difficulty: Advanced | Time: 1 hour | Basis: From the README Fail a pull request when a schema change adds a critical finding, without a live production database. **Who it is for:** A platform or DevOps engineer who wants database checks on every migration pull request. **The problem:** Schema problems often reach production because nothing checks the migration before merge. **The outcome:** A CI job that exits non-zero on critical schema findings and can emit SARIF for the GitHub Security tab. ### Steps 1. Create a disposable Postgres database for the CI job and give the job a read-only role connection string through a CI secret. 2. Run `pgbot lint` to check the schema with no writes to the database. (inferred, check the README) 3. Run `pgbot inspect --profile=schema --fail-on=critical` to make the exit code the CI gate. (inferred, check the README) 4. Add `--format=sarif` to the inspect command to produce output for the GitHub Security tab. 5. Read the exit codes in CI: 0 clean, 1 warn, 2 critical, 3 connection or execution failure, 64 usage error. ### Prompt to paste into your agent ```text Use the pgbot tool from the pgrundev/pgbot repository in CI by running `pgbot inspect --profile=schema --fail-on=critical --format=sarif` against . Report the exit code and the number of findings in the SARIF output. Verify that exit code 3 is reported as a connection failure rather than as a finding. ``` ### Caveat The schema profile only runs catalog-derived findings, so it does not replace checks that need live workload statistics. Disclaimer: Third-party repos we don't own or vouch for. AI-generated use cases can be wrong: review everything and run at your own risk. Full text: https://dedupedai.com/disclaimer/ URL: https://dedupedai.com/use-cases/pgrundev-pgbot/gate-schema-migrations-in-ci/ ## How to let Claude Code read PostgreSQL findings over MCP with Pgbot Repo: [pgrundev/pgbot](https://github.com/pgrundev/pgbot) | Difficulty: Intermediate | Time: 30 minutes | Basis: From the README Connect an AI coding agent to structured database findings through the Pgbot MCP server. **Who it is for:** A developer using Claude Code who wants the agent to reason over live PostgreSQL health findings during a debugging session. **The problem:** An agent guessing at database state from application code gives weak answers, and pasting terminal output is fragile. **The outcome:** The agent queries the same versioned findings the CLI produces and cites specific tables, queries, and indexes. ### Steps 1. Install Pgbot with `curl -fsSL https://pgbot.dev/install | sh`. 2. Set the read-only connection with `export DATABASE_URL="postgres://pgbot_ro@host:5432/db"`. 3. Start the MCP server with `pgbot mcp`. 4. Register the server with Claude Code, or install the Pgbot Claude Code plugin, as described in the README MCP section. 5. Ask the agent to run the inspect findings and summarize the CRITICAL and WARNING items with their sources. ### Prompt to paste into your agent ```text Using the pgrundev/pgbot MCP server running against , fetch the current health findings and list each CRITICAL and WARNING finding with the table, query, or index it names. Verify that the response includes the contract version 1.5.0 before you act on any finding. ``` ### Caveat The MCP surface exposes the same findings as the CLI, so an agent should not treat the terminal report as the source of truth. Disclaimer: Third-party repos we don't own or vouch for. AI-generated use cases can be wrong: review everything and run at your own risk. Full text: https://dedupedai.com/disclaimer/ URL: https://dedupedai.com/use-cases/pgrundev-pgbot/let-claude-code-read-postgres-findings/