clickhouse-update
Skill24 starsChanged 5 days agoApache-2.0View on GitHub
Evaluate and ship a ClickHouse version update — restore-compat check, dashboard-query benchmark against prod version, evaluation record, prod rollout
In this repository5 other files it gives its agents.
- AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- add-dashboard-categorySkill
- add-dashboard-pageSkill
- review-ij-perfSkill
The file
We split this skill into the rules it gives an agent
6 rules. About 0 are common, 1 is distinctive, and 5 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈1
Distinctive · estimate
This author's own choices. Worth a look.
5
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 10
dashboard query workload: does not regress.
- Very specific: line 58
Read benchmark/results/report.tsv (ratio > 1 = candidate slower):
- Very specific: line 74
Write benchmark/evaluation/<version>.md (verdict, environment incl.
- Very specific: line 80
Before the first push, note the latest prod backup name (list remote) — that is the rollback pin.
- Very specific: line 82
Update the release URL in deployment/clickhouse/Dockerfile (and clickhouse-backup if bumped).
- Very specific: line 104
Stop both local servers when done (SYSTEM STOP MERGES is not persistent — no state to undo).
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Evaluate ClickHouse $0 against the current prod version and, if it passes, ship it. The evaluation proves two things on real data: the prod backup restores under the new version (that is how prod upgrades — the data volume is ephemeral, see BACKUP.md), and the dashboard query workload does not regress.
1. Resolve versions and binaries
- Current prod version: the release pinned in
deployment/clickhouse/Dockerfile. - Download missing binaries (macOS arm64) to
~/clickhouse-<version>and verify:curl -fL -o ~/clickhouse-<ver> https://github.com/ClickHouse/ClickHouse/releases/download/<tag>/clickhouse-macos-aarch64 && chmod +x ~/clickhouse-<ver> && ~/clickhouse-<ver> --version - On a major-version jump, check the pinned clickhouse-backup (same Dockerfile) against Altinity's release notes/test matrix — it may need a bump too.
2. Restore the same backup into both instances
Preconditions: MinIO running (BACKUP.md "Restore locally"), doppler available, ports 9000/9010 free.
- Baseline (exact prod version, default paths, port 9000):
RESTORE_DB=true CLICKHOUSE_BIN=~/clickhouse-<prod-ver> go run ./cmd/clickhouseNote the backup name from theDB is restored (backup=...)log line. Skip if the baseline dir already holds a restore of a recent backup made with the prod version — but then pin the candidate to that same backup. - Candidate (target version, its own config/data/port):
RESTORE_DB=true CLICKHOUSE_BIN=~/clickhouse-<target-ver> CLICKHOUSE_CONFIG=$PWD/deployment/ch-local-candidate/config.xml CLICKHOUSE_DATA_PATH=/Volumes/data/ij-perf-db/clickhouse-candidate CLICKHOUSE_PORT=9010 RESTORE_BACKUP_NAME=<same-backup> go run ./cmd/clickhouse
This candidate restore IS the backup-compat check: a prod-version-written backup must restore
under the target server binary. If it fails, the upgrade is blocked regardless of performance.
Gotchas: concurrent restores of the same backup need distinct TMPDIRs (pid file is keyed by
backup name); a failed restore leaves the temporary server running — kill it before retrying.
3. Start and verify both servers
~/clickhouse-<prod-ver> server --config-file=deployment/ch-local/config.xml # port 9000
~/clickhouse-<target-ver> server --config-file=deployment/ch-local-candidate/config.xml # port 9010On each port: SELECT version(), SELECT sum(rows) FROM system.parts WHERE active (must match
across servers within ~0.01%), and the s3-tier probe from BACKUP.md
(SELECT count() FROM ij.report WHERE generated_time < '2021-01-01' — non-zero and equal).
4. Benchmark
Corpus: reuse benchmark/corpus.jsonl. Refresh it only if dashboards changed materially:
browse the heavy dashboards against a local backend (defaults to 127.0.0.1:9000), then run
benchmark/extract-queries.sql per its header.
benchmark/run.sh benchmark/corpus.jsonl benchmark/results 16Read benchmark/results/report.tsv (ratio > 1 = candidate slower):
- Any
rows_check MISMATCHor failed query → correctness blocker, stop here. - Weighted verdict (frequency-weighted, what users feel):
clickhouse local -q "SELECT round(sum(toInt64(c.runs)*r.old_ms)/1000,2) old_s, round(sum(toInt64(c.runs)*r.new_ms)/1000,2) new_s, round(new_s/old_s,3) ratio FROM file('benchmark/results/report.tsv', TSVWithNames) r JOIN file('benchmark/corpus.jsonl', JSONEachRow) c ON r.id=c.id" - Investigate queries ≥20 ms with ratio ≥ 1.2: replay on the candidate with
--compatibility '<prod-major>'and--enable_analyzer 0(settings-default vs analyzer cause), and diffread_rows/ProfileEvents['SelectedMarks']between servers' query_logs (planner vs executor cause). Compare memory columns too — regressions can hide there (prod limit is 32Gi). - Ignore ratios on sub-10 ms queries: 1 ms rounding dominates.
Pass bar: no correctness issues, weighted ratio ≤ ~1.05, no user-facing query class with a sustained >20% median regression, no memory blowups.
5. Record the evaluation
Write benchmark/evaluation/<version>.md (verdict, environment incl. backup name, results,
diagnosis, mitigations tested) and copy report.tsv to
benchmark/evaluation/<version>-report.tsv. See 26.6.2.81.md for the shape.
6. Ship (only if the evaluation passed)
Before the first push, note the latest prod backup name (list remote) — that is the rollback pin.
- Update the release URL in
deployment/clickhouse/Dockerfile(and clickhouse-backup if bumped); commit and push. CI builds the new base image (entrypoint/usr/bin/entrypoint— a placeholder; this image is not deployable on its own). - Find the new base digest in the ghcr
clickhousepackage (upload time after your push;org.opencontainers.image.revisionlabel = your commit SHA). Update bothbaseImageOverridesentries in.ko.yamlto it; commit and push. CI now ko-builds the pod images on the 25.x base. - Find the fresh ko-built digests (upload time after the second push; entrypoint
/ko-app/clickhousein theclickhousepackage,/ko-app/clickhouse-backupin theclickhouse-backuppackage — ko images inherit the base's revision label, so match on upload time + entrypoint, not revision). Pin both inimages:injb/values.yaml, commit, push the jb repo — this triggers the real rollout. Never pin the raw base image. - Watch the rollout:
kubectl logs -n idea <new-pod> -c restore-clickhouse-backup -fmust end withDB is restored; the old pod keeps serving until the new one answers/ping. VerifySELECT version()afterwards. Then confirm the next sidecar backup succeeds (create_remotein the clickhouse-backup container logs — the rate-limit clock resets on pod restart, so it lands within ~20–40 min of the rollout). - Rollback insurance: after the new version writes its first backup, rolling back requires
RESTORE_BACKUP_NAMEpinned to the pre-upgrade backup noted above (BACKUP.md "Restore in prod").
Cleanup
Stop both local servers when done (SYSTEM STOP MERGES is not persistent — no state to undo).
The candidate data dir (/Volumes/data/ij-perf-db/clickhouse-candidate, ~12 GB) can be deleted
or left for the next evaluation.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

