clickhouse-managed-postgres-rca
MUST USE when investigating performance issues on a ClickHouse-managed Postgres instance. Provides an evidence-based RCA workflow that scrapes the Prometheus endpoint for system signal, pulls per-digest evidence from the Slow Query Patterns API, and recommends (does not apply) a fix.
- License 许可
- Apache-2.0
- 最近更新
- 2026-07-18
- Stars
- ★ 0
- Forks
- 1
01 前言 Front Matter
- name
- clickhouse-managed-postgres-rca
- license
- Apache-2.0
- metadata
- { "author": "ClickHouse Inc", "version": "0.1.0" }
- description
- MUST USE when investigating performance issues on a ClickHouse-managed Postgres instance. Provides an evidence-based RCA workflow that scrapes the Prometheus endpoint for system signal, pulls per-digest evidence from the Slow Query Patterns API, and recommends (does not apply) a fix.
03 SKILL.md 正文
ClickHouse Managed Postgres RCA
When to use
Trigger whenever a user reports slowness, high CPU, low throughput, cache thrash, or any unexplained pain on a ClickHouse-managed Postgres instance.
What you have access to
Two APIs on https://api.clickhouse.cloud (HTTP Basic auth
using a ClickHouse Cloud API key/secret pair):
- Prometheus metrics — operation
postgresInstancePrometheusGetunder the Prometheus tag. Returns Prometheus exposition format. System and workload metrics for one Postgres service. - Slow Query Patterns — operation
slowQueryPatternsGetListunder the Postgres tag. Returns per-digest latency, IO, and call statistics for normalized query patterns. Beta.
Both endpoints require an organizationId and a serviceId as
path parameters. The user must supply both, plus the API
key/secret pair.
What you do NOT have
- Query plans / EXPLAIN output.
- Per-table scan-type counters (
seq_scan/idx_scan). - Autovacuum or last-ANALYZE timestamps.
Reason from IO and timing signals, not from a plan tree.
Workflow
Six steps, in order. Do not skip ahead.
Steps 2 and 3 only share auth — no data dependency between
them. Run them in parallel (background curls, & + wait) to
cut wall time from sequential ~2s to ~1s.
1. Discover the live API shape
These endpoints are Beta — paths, params, and JSON field names
can shift. Follow rules/openapi-discovery.md to:
- Fetch the OpenAPI spec from
https://api.clickhouse.cloud/v1. - Locate the two operations by
operationId:postgresInstancePrometheusGet(Prometheus tag)slowQueryPatternsGetList(Postgres tag)
- Resolve their path templates, required query parameters, and (for the slow-query endpoint) the response schema.
- Build a session-scoped role map from the schema property
descriptions:
{ semantic role → actual field name }.
Use the resolved names in every subsequent request and citation. Never hardcode field names from memory.
2. Scrape Prom once for system gauges
Follow rules/prometheus-scrape.md. One scrape, no wait.
You're after gauges (current values) that don't need a delta:
CacheHitRatio, ActiveConnections, MemoryUsedPercent,
FilesystemUsedPercent.
A CacheHitRatio well below ~95% on a workload that should
fit in cache is a real signal on its own. Climbing
ActiveConnections toward the pool ceiling is a real signal
on its own. These don't need rate-of-change.
A second scrape for counter deltas is opt-in, used only when Step 4 triage points at write-congestion (where deadlock and rollback rates matter and the Slow Query Patterns API can't substitute). For the read-path case (the most common RCA shape) the single scrape is enough.
3. Pull top slow query patterns
Request the slow query patterns. Follow
rules/slow-query-patterns-fields.md for the fields that
matter and how to read them. This is the primary diagnostic —
it returns per-pattern accumulated totals (call count, runtime,
blocks, rows) over the window you request, which is the
"rate-of-change" data you'd otherwise derive from two Prom
scrapes — but per query and without waiting.
If no patterns return a meaningful totalDurationUs, the
report may be overstated or the issue isn't query-shaped.
Stop and tell the user what you looked at.
4. Triage: pick the right heuristic
Follow rules/triage.md. Match the combined Prom + slow-query
signal to one of the heuristic shapes. Each shape points to a
specific heuristic file:
rules/heuristic-full-scan.md— read-path full scan.rules/heuristic-hot-loop.md— N+1 / hot loop from the app.rules/heuristic-write-congestion.md— deadlocks, slow writes, high rollback rate.
If the signal does not match any shape cleanly, do not invent a hypothesis. Surface the top patterns and ask the user which workload they recognize. New heuristics are welcome as PRs.
5. Reason, then recommend
Use the format in rules/output-template.md. Always include:
symptom, evidence, hypothesis (noting any alternative cause
you cannot rule out from this surface alone), short-term fix,
and long-term follow-ups.
6. Do not apply the fix
Follow rules/recommend-only.md. Never run DDL. Never call
pg_cancel_backend or pg_terminate_backend. Write the
recommendation, explain why, and let the human apply it.
Full Compiled Document
For the complete guide with every rule expanded in a single
context load: AGENTS.md.
04 同仓库更多 Skills
arxiv-search
Search arXiv physics, math, and computer science preprints using natural language queries. Powered by Valyu semantic search.
arxiv-search
Search arXiv physics, math, and computer science preprints using natural language queries. Powered by Valyu semantic search.
working-with-legacy-code
Safely change and test untested codebases using Feathers' "Working Effectively with Legacy Code". Use when the user mentions "legacy code", "no tests", "untested codebase", "how do I test this", "seams", "characterization tests", "golden master", "sprout method", "afraid to change this code", "monster method", "dependency breaking", or "inherited a messy codebase". Also trigger when changing code without tests safely, getting a class under test when constructors, statics, or singletons block it, adding features to tangled modules, or planning incremental test coverage for an old codebase. Covers the legacy-code change algorithm, seams, characterization tests, sprout/wrap, and dependency-breaking techniques. For refactoring code that already has tests, see refactoring-patterns. For day-to-day code quality, see clean-code.
working-with-legacy-code
Safely change and test untested codebases using Feathers' "Working Effectively with Legacy Code". Use when the user mentions "legacy code", "no tests", "untested codebase", "how do I test this", "seams", "characterization tests", "golden master", "sprout method", "afraid to change this code", "monster method", "dependency breaking", or "inherited a messy codebase". Also trigger when changing code without tests safely, getting a class under test when constructors, statics, or singletons block it, adding features to tangled modules, or planning incremental test coverage for an old codebase. Covers the legacy-code change algorithm, seams, characterization tests, sprout/wrap, and dependency-breaking techniques. For refactoring code that already has tests, see refactoring-patterns. For day-to-day code quality, see clean-code.
web-typography
Select, pair, and implement typefaces for web projects. Use when the user mentions "font pairing", "which typeface", "line height", "responsive typography", "web font loading", "type hierarchy", "variable fonts", "FOUT/FOIT", "typographic scale", or "the text is hard to read". Also trigger when choosing between system fonts and web fonts, optimizing font-loading performance, or designing readable long-form content. Covers readability evaluation, CSS implementation, and performance optimization. For overall UI design systems, see refactoring-ui. For dramatic typographic experiences, see top-design.
web-typography
Select, pair, and implement typefaces for web projects. Use when the user mentions "font pairing", "which typeface", "line height", "responsive typography", "web font loading", "type hierarchy", "variable fonts", "FOUT/FOIT", "typographic scale", or "the text is hard to read". Also trigger when choosing between system fonts and web fonts, optimizing font-loading performance, or designing readable long-form content. Covers readability evaluation, CSS implementation, and performance optimization. For overall UI design systems, see refactoring-ui. For dramatic typographic experiences, see top-design.