postgresai
/postgresai
Run EXPLAIN / EXPLAIN ANALYZE, test indexes, and measure query cost on PostgresAI Database Lab thin clones with the PostgresAI CLI (postgresai, pgai, Joe). Use when asked to explain or optimize a query, check a plan at production scale, try an index on realistic data, or prepare a GitLab database review.
- Author
- gitlabhq ↗
- Repository
- gitlabhq ↗
- Licence
- MIT
- Last repository change
- 23.10.2026
Assessment and security
- Files in this package
- 7
- Scripts
- 2
- HTML comments
- 0
- URLs in SKILL.md
- 1
No signals detected. Repository changed on 23.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
- 2 scripts in this package. Review them before installing, as Claude may execute them.
Scripts: scripts/joe.sh, tests/test_joe.sh
What it does and when to use it
Run EXPLAIN / EXPLAIN ANALYZE, test indexes, and measure query cost on PostgresAI Database Lab thin clones with the PostgresAI CLI (postgresai, pgai, Joe). Use when asked to explain or optimize a query, check a plan at production scale, try an index on realistic data, or prepare a GitLab database review.
Original author description
Run EXPLAIN / EXPLAIN ANALYZE, test indexes, and measure query cost on PostgresAI Database Lab thin clones with the PostgresAI CLI (postgresai, pgai, Joe). Use when asked to explain or optimize a query, check a plan at production scale, try an index on realistic data, or prepare a GitLab database review.
How it works
- When to Use
- Install
- Authenticate
- Pick the target
- Core loop
- Gotchas
- Safety rules
- GitLab-specific
- CLI reference (live)
- Contributing Improvements
SKILL.md preview
--- name: postgresai description: "Run EXPLAIN / EXPLAIN ANALYZE, test indexes, and measure query cost on PostgresAI Database Lab thin clones with the PostgresAI CLI (postgresai, pgai, Joe). Use when asked to explain or optimize a query, check a plan at production scale, try an index on realistic data, or prepare a GitLab database review." version: 1.0.3 license: MIT category: Database --- # PostgresAI CLI (Database Lab / Joe) Run `EXPLAIN` and DDL experiments on ephemeral thin clones of production-sized data from the terminal, not the Joe chat bot. ## When to Use - Checking query plans at production scale, or testing an index or migration on a thin clone. - Collecting queries and plans as evidence for a database review. Not for local development databases (GitLab: use `gitlab-psql`). ## Install Prefer npm: `npm i -g postgresai` (or `npx postgresai ...`); binaries `postgresai`, `pgai`. Don't rely on a Homebrew tap; it may no longer exist. ## Authenticate `postgresai auth show-key` shows whether a key is stored; if not, run `postgresai login` (browser OAuth, callback on `127.0.0.1`, `--port` selectable). Headless: `login --set-key <key>` or `PGAI_API_KEY`. Never commit or print the key. ## Pick the target ```shell postgresai projects # JOE column shows which projects are ready ``` Pass `--project <id|alias>` (or `--instance-id <id>`) to `joe`, or store a default with `postgresai set-default-project <alias>`; `joe --help` wrongly says `--project` is always required. Prefer JOE `ready` entries (skips stale `-deprecated`/`-new` duplicates); ask the user if ambiguous. ## Core loop Run `joe` through [scripts/joe.sh](scripts/joe.sh) (path relative to this skill): same arguments as `postgresai joe`, but it waits out the CLI's poll budget and prints only the result. ```shell J=scripts/joe.sh; P="--project <alias>" $J plan $P "SELECT ..." # estimates only, never executes: default $J explain $P "SELECT ..." # EXPLAIN + ANALYZE + buffers, executes on clone $J describe $P <table> # \d-style metadata $J exec $P "CREATE INDEX ..." # real DDL on the clone; prints only the duration $J exec $P "ANALYZE <table>" # refresh stats, then `explain` again to re-measure $J reset $P # discard experiments ``` Plans and iterating: [references/plan-analysis.md](references/plan-analysis.md). ## Gotchas - **Budget:** plain `postgresai joe` returns exit 0 after `--budget` (default 25 s) with `started <id> · pending … resume: pgai joe result <id>`; the command keeps running (`result` exits 1 while pending). `joe.sh` polls (`JOE_WAIT_SECONDS`, default 600; raise it for big tables). - **Don't `reset` to stop a slow command** (kills pending work, `conn closed`): use `joe activity` and `joe terminate <pid>`. - **`hypo` (HypoPG) didn't persist between CLI calls**: test indexes with `exec CREATE INDEX` + `exec ANALYZE` + `explain` (see plan-analysis). - `explain` and `exec` really execute: bound queries (`LIMIT`, tenant/project id). `exec` returns no rows. - **Clone creation can fail** (`failed to create a Database Lab clone`). Stop using that target for the session (`explain`/`reset` fail the same way), report it, don't retry in a loop. Switch target only if another database can answer the question. - JSON output and explicit clones: [references/advanced.md](references/advanced.md). ## Safety rules - Experiment only through the clone commands; never run writes or DDL against production or replicas by any other route. Always `joe reset` when finished. - Clone data is real production data. **Never paste row values, emails, tokens or result sets** into public MRs, issues, or repos. Share SQL, plans with literals replaced by placeholders, and timings/buffer counts only. - Clones may enforce statement timeouts; don't retry them in a loop. ## GitLab-specific GitLab monolith: target aliases in [references/gitlab.md](references/gitlab.md); review flow in [references/db-review-flow.md](references/db-review-flow.md). ## CLI reference (live) Flags change between releases; check the installed CLI: !`postgresai joe --help 2>&1 | head -30` ## Contributing Improvements This skill is maintained in [`gitlab-org/gitlab`](https://gitlab.com/gitlab-org/gitlab) at `.claude/skills/postgresai/`. If you discover that any guidance here is **inaccurate or outdated** (e.g. a command that no longer works, a wrong flag, CLI output drift that breaks `scripts/joe.sh`), confirm with the user and open an MR against `gitlab-org/gitlab` with the fix. Keep changes focused — one fix per MR.
Files in this package (7)
- SKILL.md 5 KB
- references/advanced.md 1 KB
- references/db-review-flow.md 1 KB
- references/gitlab.md 1 KB
- references/plan-analysis.md 2 KB
- scripts/joe.sh 3 KB
- tests/test_joe.sh 6 KB
Versions
The database has no other skills named postgresai.
Database snapshot: 13.11.2026 · package version ddc79fd3a51c (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100db-query-plan-analyzer
Reads `EXPLAIN` / `EXPLAIN ANALYZE` output from PostgreSQL, MySQL, or SQLite - identifies the dominant cost (sequential scan, nested loop, sort spill, missing index, type-cast preventing index use), proposes the specific index or query rewrite to fix it, and emits the candidate… - 100analyzing-database-indexes
Process use when you need to work with database indexing. This skill provides index design and optimization with comprehensive guidance and automation. Trigger with phrases like "create indexes", "optimize indexes", or "improve query performance". - 90database-query-performance
Diagnose database latency with explain plans, index ownership, and query-shape review. Use when a query is slow, an index is missing, or scans and N+1 patterns appear. - 100mariadb-explain
MariaDB-specific behavior of EXPLAIN and its relatives (ANALYZE, EXPLAIN FORMAT=JSON, SHOW EXPLAIN/ANALYZE FOR) — MariaDB has no `EXPLAIN ANALYZE`; it uses a standalone `ANALYZE <statement>` that actually executes the query (and, for UPDATE/DELETE/INSERT, actually writes); plain… - 100database-index-design
Designs relational database indexes from the actual query workload - composite column ordering (equality, sort, range), covering and INCLUDE indexes, partial and expression indexes, redundancy removal, and the write, storage, and lock cost of each index. Use when adding indexes… - 100db-index-advisor
Database index optimization advisor for PostgreSQL, MySQL, and SQLite. Analyzes slow queries, missing indexes, unused indexes, and over-indexed tables. Generates CREATE INDEX statements with EXPLAIN ANALYZE estimates. Use when queries are slow, p99 DB latency spikes, or when reviewing a new schema.