yugabyte/yugabytedb-skills/yb-model

YugabyteDB schema review

This skill reviews an existing schema, completely, from as much evidence as the user can give: the more of the bundle in step 1 exists, the more of the engine runs. The review runs offline, on files; nobody needs access to the customer's cluster. If the user has only DDL, review it and the report labels the result a structural review. If the user has no schema, only a slow query or pg_stat_statements output, this is not a schema review: see Other skills.

Scope guard. YugabyteDB YSQL only. Do not apply these rules to plain PostgreSQL work.

How this skill works

The findings come from a deterministic engine, scripts/yb-model.py. Its rules live in rules/rules.json. A rule that claims planner or execution behaviour cites the yugabyte-db regress test that pins it; statistics, workload and configuration rules are arithmetic on the customer's own data, and their findings say which inputs they were computed from. The same inputs give the same findings, severities, ranking, DDL and chat message, whichever model runs the skill.

Your job is to collect the inputs, run the engine, add only the context the engine cannot know, suggest a direction per area from the options the findings offer (step 4b), and say what was not verified. Do not add, drop, re-rank, re-word or re-grade findings yourself. If you believe a finding is wrong or something is missing, write it under Reviewer notes with the finding ID, or "not in engine". Never edit the findings table by hand. The direction is yours and is labelled as such; the findings, their facts and their options are the engine's.

Procedure

Copy this checklist and tick it off.

- [ ] 0. references/pitfalls.md and your own list read (`yb-model.py pitfall list`)
- [ ] 1. Inputs gathered into one directory
- [ ] 1b. Preflight ran; if it asked, the user said yes or no
- [ ] 1c. Any input the engine could not read converted (a copy) and named in NOTES
- [ ] 2. Engine ran (review.md, review.json and chat.md exist)
- [ ] 3. Replay ran, or the reason it did not is recorded
- [ ] 4. User context added to review.md, or the NOTES line deleted
- [ ] 4b. Decisions consolidated per area in plan.json and checked (`yb-model.py plan`), at most one revision; the user decides
- [ ] 5. Limitations stated
- [ ] 6. (optional) Second pass recorded under Reviewer notes
- [ ] 7. chat.md pasted as the chat message
- [ ] 8. Anything that did not work as expected added with `yb-model.py pitfall add`

1. Gather

A complete review needs the complete bundle, so ask for it before running anything. Whoever can connect to the database (the user, or the customer through them) runs the two capture commands in references/intake.md §1 (ysql_dump --include-yb-metadata and scripts/collect.sql): together they write every file the engine reads, namely statistics, workload, row counts, planner settings, index usage, tablet counts and the release. Take whatever they can give; preflight (step 1b) names what is still missing.

Make one directory (the bundle) and put the DDL in schema.sql. Copy every file the user gave you into it unchanged:

• The ybm_*.csv files from scripts/collect.sql.
• A pg_stat_statements export, saved as ybm_pss.csv.
• A pg_stats export, saved as ybm_pg_stats.csv.
• Captures from several nodes, one subfolder each (node1/, node2/, ...). Usage counters

are per node; the engine adds the folders up.

Exports often arrive in another shape (tab-separated, document or spreadsheet tables, aggregates over nodes). Look at what you were given first, then write a one-off converter into the layout above. Keep the originals, change no values, and say in the NOTES line what you mapped and what you could not. Keep every row: if an aggregated pg_stats export lists a column more than once (per-node variants), the engine keeps one row by a stated rule and names the columns in its open items.

Write any query list the user gives to queries.sql, one statement per ;, whether or not there is a pg_stat_statements export. The engine uses both: a listed statement found in pg_stat_statements is ranked from it, and one it lacks is added unranked. Keep parameters typed the way the application sends them (for example $2::boolean where a parameter appears only in $2 IS NULL), so replay can plan them. If, and only if, the user or their document states that no other statement touches these tables, make the first line -- ybm: workload-complete. Unused-index checks (IDX001) then run on the list, marked as resting on that statement. Never add it on your own judgement.

The release is read from SELECT version() (ybm_meta.csv), pg_settings (server_version) or the ysql_dump header. If the user states it, or it is only in some other file, pass --release <a.b.c.d> to preflight and review.

1b. Preflight: confirm before reviewing with missing inputs

python3 <skill-dir>/scripts/yb-model.py preflight <bundle>

• Exit 0: nothing that changes the findings is missing. Go to step 2. If it printed minor

gaps, mention them in one line.

• Exit 3: inputs that change the findings are missing. Show the printed text to the user

verbatim (it names each missing input, what it mutes or weakens, and how to collect it) and ask one yes / no question: proceed with an incomplete review? Use the question tool if you have one. Then stop and wait. Never answer it yourself, never assume yes from earlier messages, and do not summarise or soften the list.

• Yes: go to step 2 and add --accept-missing.
• No: give the user the collect commands from the printed text (and

references/intake.md §1 for the full capture), then stop. Run preflight again when they come back with the files.

review refuses to run (exit 3) without --accept-missing while a major input is missing. Never pass that flag unless the user said yes in this conversation to this bundle's list.

What muting means: a rule whose inputs are missing is not evaluated, because its result would rest on a guess (for example, no primary-key re-key from an unranked query list). The report names the candidates it withheld. A weakened rule still runs, and each finding it makes carries a caveat such as sharding inferred. The mapping lives in rules/inputs.json; do not re-derive it.

1c. When the engine cannot read an input

If preflight or the engine exits 2 naming a file (UTF-16, another delimiter, a missing header, no table read from the schema), or a review comes back with far fewer tables than the dump holds, find the cause before anything else: look at the file's first bytes and lines. Then write a one-off converter that writes a corrected copy into a new bundle directory, run again on that copy, and say in the NOTES line what you converted and why. Never change values or the customer's originals. references/pitfalls.md lists the causes seen so far. If yours is new, record it (step 8).

2. Run the engine

python3 <skill-dir>/scripts/yb-model.py review <bundle>

Add no flags other than --accept-missing (step 1b) and --release (step 1). The engine runs replay by itself when Docker has a yugabytedb/yugabyte image for the bundle's release, and prints why when it cannot. Replay does four things:

1. Starts that version in a scratch container.
2. Injects the customer's row counts and column statistics, the way the TAQO planner tests

do.

1. Runs EXPLAIN on every pattern.
2. Runs the probe of every fired rule that has one: a few thousand generated rows and

EXPLAIN (ANALYZE, DIST), checking that the rule's mechanism holds on that release. A refuted rule's finding is withdrawn, and the report says which, unless the image is another release with unknown or changed behaviour for that rule, or the planner settings were assumed: then the finding stays, marked unverified or disputed. Probes never use customer data and store nothing.

Never docker pull without asking: the image is a download of about 1 GB. If the user agrees to the pull, run the same command again afterwards.

The engine writes review.md, review.json, chat.md (the chat message) and, with replay, plans.json into the bundle. Exit codes:

• 0: no findings at high or above.
• 1: findings at high or above. This is not a failure.
• 2: the engine failed. Show the error. If it names an input file, go to step 1c.
• 3: inputs are missing and were not accepted. Go back to step 1b.

If the engine cannot run on this surface, say so and review by hand with references/checks.md. Label the result manual review, engine not run.

What the report contains

review.md always has these sections, in this order. Do not reorder or drop them: