SkillHub

Skills clickhouse agent-skills

clickhouse-managed-postgres-rca

Use it when:
A Postgres instance hosted on ClickHouse Cloud is slow, CPU-bound, low on throughput or thrashing its cache.
It does:
Scrapes the instance's Prometheus metrics once, pulls per-query-pattern runtime, IO and call counts, and matches the signal to a known problem shape.
You get:
A write-up of symptom, evidence, hypothesis with any alternative cause it can't rule out, a short-term fix and long-term follow-ups.
Doesn't:
Run DDL or cancel or terminate backends; you apply the fix.
Can't see:
Query plans, per-table scan counters or vacuum and ANALYZE times, so it reasons from IO and timing.
Needs:
A ClickHouse Cloud API key and secret, plus the organization and service IDs.

Needs a ClickHouse Cloud API key and secret, plus the organization and service IDs of the Postgres instance.

TL;DR

ClickHouse's own root-cause workflow for Postgres on ClickHouse Cloud, in six fixed steps. Because the two APIs it uses are in beta, it first fetches the OpenAPI spec and maps the field names it will cite instead of trusting memory. It scrapes the Prometheus endpoint once for current values: a cache hit ratio well below about 95%, or connections climbing toward the pool limit, is a signal on its own, and a second scrape for deadlock and rollback rates happens only when writes look congested. In parallel it pulls the Slow Query Patterns API, which totals calls, runtime, blocks and rows per normalized query. The combined signal is matched to one of three shapes, each with its own heuristic: a read-path full scan, an N+1 hot loop from the app, or write congestion. If nothing matches, it shows the top patterns and asks which workload you recognize rather than inventing a cause; if no pattern carries real duration, it stops and says what it looked at.

Summarizes the skill at d3b2b5b.

npx skills add https://github.com/clickhouse/agent-skills --skill clickhouse-managed-postgres-rca

skills.sh pageSource on GitHubAll skills from clickhouse/agent-skills

Total Downloads
6,700 on skills.sh, Nov 9, 2026
Repo stars
564 clickhouse/agent-skills
First seen
Jun 8, 2026 on skills.sh
Weekly Downloads
83
-24% over the 8 weeks to Nov 9
Weekly values
Week toInstalls
Sep 21109
Sep 2897
Oct 594
Oct 1276
Oct 19122
Oct 2681
Nov 253
Nov 983
Category
category:debuggingcategory:cloudcategory:software-engineering

Find root causes, review code and test what you built.

Deploy, provision, observe and debug cloud apps.

Everything for building software: planning, coding, debugging, design, frameworks, cloud, agents and skills.
Type
type:workflow

A multi-step process the agent follows: interview, debug, run a TDD loop, review.
Effect
effect:none

Adds no side effects of its own. It shapes how the agent thinks, writes or reviews. Guidelines and routers are always effect:none, even when the task they support edits code.
Needs
needs:account

An account, API key or paid plan with an outside service.
Publisher
by:official

A verified publisher on skills.sh, or the company or maintainer behind the product the skill operates.
Topics
databasesperformancedebuggingobservability
Stack
stack:clickhousestack:postgresstack:prometheus

SKILL.md

From clickhouse/agent-skills at d3b2b5b. Checked daily, last changed Nov 6, 2026.

Frontmatter
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.