clickhouse-managed-postgres-rca

What the source does

• FilesystemNone observedNo reads or writes in the source
• NetworkNone observedNo outbound calls in the source
• ShellNone observedNo commands run in the source
• DependenciesPinnedNothing floats
• SecretsNone foundNo keys in source

Five audit layers

Gates applied: no behavioural pass.

Audit 0.4.1 · source sha 26841bf3d95f · 27 Oct 2026Report an issue or request a re-scan

• Publisherclickhouse GitHub organizationOwner of the repository this skill is read from
• Source folderclickhouse/agent-skills/skills/clickhouse-managed-postgres-rcaThe exact folder we audited
• LicenceApache-2.0As the repository states it
• Commit pinned for this verdict26841bf3d95fA new audit runs when the source changes
• Repository statusNot archivedLast push 2 days ago
• Sandboxed runNot runSkills are read, not executed; layer L4 is skipped

Its 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…

---
name: clickhouse-managed-postgres-rca
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.
license: Apache-2.0
metadata:
  author: ClickHouse Inc
  version: "0.1.0"
---

# 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 `postgresInstancePrometheusGet`
  under the Prometheus tag. Returns Prometheus exposition format.
  System and workload metrics for one Postgres service.
- **Slow Query Patterns** — operation `slowQueryPatternsGetList`
  under 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:

1. Fetch the OpenAPI spec from `https://api.clickhouse.cloud/v1`.
2. Locate the two operations by `operationId`:
   - `postgresInstancePrometheusGet` (Prometheus tag)
   - `slowQueryPatternsGetList` (Postgres tag)
3. Resolve their path templates, required query parameters,
   and (for the slow-query endpoint) the response schema.
4. 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`.

Excerpt, first 4,893 characters of SKILL.md, verbatim from the audited commit. It is text from someone else’s repository, quoted so you can judge it before your agent reads it.

git clone https://github.com/clickhouse/agent-skills
git -C agent-skills checkout 26841bf3d95f

Works in

1. 2026-10-27
   ✓SAFE B 89 / 100

   first audit · source 26841bf3d95f

Questions about clickhouse-managed-postgres-rca

• github.com/clickhouse/agent-skills/tree/main/skills/clickhouse-m — the audited source at commit 26841bf3d95f and every finding aboveObserved · 27 Oct 2026

Spotted something wrong? Report a correction · Methodology