clickhouse-managed-postgres-rca

Name: clickhouse-managed-postgres-rca
Author: ClickHouse

2.5kinstalls32forks

How to install clickhouse-managed-postgres-rca?

clickhouse/agent-skills/clickhouse-managed-postgres-rca

$npx -y skills add clickhouse/agent-skills --skill clickhouse-managed-postgres-rca

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Managed Postgres RCA
2
3	## When to use
4
5	Trigger whenever a user reports slowness, high CPU, low
6	throughput, cache thrash, or any unexplained pain on a
7	ClickHouse-managed Postgres instance.
8
9	## What you have access to
10
11	Two APIs on `https://api.clickhouse.cloud` (HTTP Basic auth
12	using a ClickHouse Cloud API key/secret pair):
13
14	- Prometheus metrics — operation `postgresInstancePrometheusGet`
15	under the Prometheus tag. Returns Prometheus exposition format.
16	System and workload metrics for one Postgres service.
17	- Slow Query Patterns — operation `slowQueryPatternsGetList`
18	under the Postgres tag. Returns per-digest latency, IO, and
19	call statistics for normalized query patterns. Beta.
20
21	Both endpoints require an `organizationId` and a `serviceId` as
22	path parameters. The user must supply both, plus the API
23	key/secret pair.
24
25	## What you do NOT have
26
27	- Query plans / EXPLAIN output.
28	- Per-table scan-type counters (`seq_scan` / `idx_scan`).
29	- Autovacuum or last-ANALYZE timestamps.
30
31	Reason from IO and timing signals, not from a plan tree.
32
33	## Workflow
34
35	Six steps, in order. Do not skip ahead.
36
37	Steps 2 and 3 only share auth — no data dependency between
38	them. Run them in parallel (background curls, `&` + `wait`) to
39	cut wall time from sequential ~2s to ~1s.
40
41	### 1. Discover the live API shape
42
43	These endpoints are Beta — paths, params, and JSON field names
44	can shift. Follow `rules/openapi-discovery.md` to:
45
46	1. Fetch the OpenAPI spec from `https://api.clickhouse.cloud/v1`.
47	2. Locate the two operations by `operationId`:
48	- `postgresInstancePrometheusGet` (Prometheus tag)
49	- `slowQueryPatternsGetList` (Postgres tag)
50	3. Resolve their path templates, required query parameters,
51	and (for the slow-query endpoint) the response schema.
52	4. Build a session-scoped role map from the schema property
53	descriptions: `{ semantic role → actual field name }`.
54
55	Use the resolved names in every subsequent request and citation.
56	Never hardcode field names from memory.
57
58	### 2. Scrape Prom once for system gauges
59
60	Follow `rules/prometheus-scrape.md`. One scrape, no wait.
61	You're after gauges (current values) that don't need a delta:
62	`CacheHitRatio`, `ActiveConnections`, `MemoryUsedPercent`,
63	`FilesystemUsedPercent`.
64
65	A `CacheHitRatio` well below ~95% on a workload that should
66	fit in cache is a real signal on its own. Climbing
67	`ActiveConnections` toward the pool ceiling is a real signal
68	on its own. These don't need rate-of-change.
69
70	A second scrape for counter deltas is opt-in, used only
71	when Step 4 triage points at write-congestion (where deadlock
72	and rollback rates matter and the Slow Query Patterns API
73	can't substitute). For the read-path case (the most common
74	RCA shape) the single scrape is enough.
75
76	### 3. Pull top slow query patterns
77
78	Request the slow query patterns. Follow
79	`rules/slow-query-patterns-fields.md` for the fields that
80	matter and how to read them. This is the primary diagnostic —
81	it returns per-pattern accumulated totals (call count, runtime,
82	blocks, rows) over the window you request, which is the
83	"rate-of-change" data you'd otherwise derive from two Prom
84	scrapes — but per query and without waiting.
85
86	If no patterns return a meaningful `totalDurationUs`, the
87	report may be overstated or the issue isn't query-shaped.
88	Stop and tell the user what you looked at.
89
90	### 4. Triage: pick the right heuristic
91
92	Follow `rules/triage.md`. Match the combined Prom + slow-query
93	signal to one of the heuristic shapes. Each shape points to a
94	specific heuristic file:
95
96	- `rules/heuristic-full-scan.md` — read-path full scan.
97	- `rules/heuristic-hot-loop.md` — N+1 / hot loop from the app.
98	- `rules/heuristic-write-congestion.md` — deadlocks, slow
99	writes, high rollback rate.
100
101	If the signal does not match any shape cleanly, do not invent
102	a hypothesis. Surface the top patterns and ask the user which
103	workload they recognize. New heuristics are welcome as PRs.
104
105	### 5. Reason, then recommend
106
107	Use the format in `rules/output-template.md`. Always include:
108	symptom, evidence, hypothesis (noting any alternative cause
109	you cannot rule out from this surface alone), short-term fix,
110	and long-term follow-ups.
111
112	### 6. Do not apply the fix
113
114	Follow `rules/recommend-only.md`. Never run DDL. Never call
115	`pg_cancel_backend` or `pg_terminate_backend`. Write the
116	recommendation, explain why, and let the human apply it.
117
118	## Full Compiled Document
119
120	For the complete guide with every rule expanded in a single
121	context load: `AGENTS.md`.

People who liked this also viewed

6 picks

Type

clickhouse-managed-postgres-rca

2.5kinstalls32forks

View on GitHub

How to install clickhouse-managed-postgres-rca?

clickhouse/agent-skills/clickhouse-managed-postgres-rca

$npx -y skills add clickhouse/agent-skills --skill clickhouse-managed-postgres-rca

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Managed Postgres RCA
2
3	## When to use
4
5	Trigger whenever a user reports slowness, high CPU, low
6	throughput, cache thrash, or any unexplained pain on a
7	ClickHouse-managed Postgres instance.
8
9	## What you have access to
10
11	Two APIs on `https://api.clickhouse.cloud` (HTTP Basic auth
12	using a ClickHouse Cloud API key/secret pair):
13
14	- Prometheus metrics — operation `postgresInstancePrometheusGet`
15	under the Prometheus tag. Returns Prometheus exposition format.
16	System and workload metrics for one Postgres service.
17	- Slow Query Patterns — operation `slowQueryPatternsGetList`
18	under the Postgres tag. Returns per-digest latency, IO, and
19	call statistics for normalized query patterns. Beta.
20
21	Both endpoints require an `organizationId` and a `serviceId` as
22	path parameters. The user must supply both, plus the API
23	key/secret pair.
24
25	## What you do NOT have
26
27	- Query plans / EXPLAIN output.
28	- Per-table scan-type counters (`seq_scan` / `idx_scan`).
29	- Autovacuum or last-ANALYZE timestamps.
30
31	Reason from IO and timing signals, not from a plan tree.
32
33	## Workflow
34
35	Six steps, in order. Do not skip ahead.
36
37	Steps 2 and 3 only share auth — no data dependency between
38	them. Run them in parallel (background curls, `&` + `wait`) to
39	cut wall time from sequential ~2s to ~1s.
40
41	### 1. Discover the live API shape
42
43	These endpoints are Beta — paths, params, and JSON field names
44	can shift. Follow `rules/openapi-discovery.md` to:
45
46	1. Fetch the OpenAPI spec from `https://api.clickhouse.cloud/v1`.
47	2. Locate the two operations by `operationId`:
48	- `postgresInstancePrometheusGet` (Prometheus tag)
49	- `slowQueryPatternsGetList` (Postgres tag)
50	3. Resolve their path templates, required query parameters,
51	and (for the slow-query endpoint) the response schema.
52	4. Build a session-scoped role map from the schema property
53	descriptions: `{ semantic role → actual field name }`.
54
55	Use the resolved names in every subsequent request and citation.
56	Never hardcode field names from memory.
57
58	### 2. Scrape Prom once for system gauges
59
60	Follow `rules/prometheus-scrape.md`. One scrape, no wait.
61	You're after gauges (current values) that don't need a delta:
62	`CacheHitRatio`, `ActiveConnections`, `MemoryUsedPercent`,
63	`FilesystemUsedPercent`.
64
65	A `CacheHitRatio` well below ~95% on a workload that should
66	fit in cache is a real signal on its own. Climbing
67	`ActiveConnections` toward the pool ceiling is a real signal
68	on its own. These don't need rate-of-change.
69
70	A second scrape for counter deltas is opt-in, used only
71	when Step 4 triage points at write-congestion (where deadlock
72	and rollback rates matter and the Slow Query Patterns API
73	can't substitute). For the read-path case (the most common
74	RCA shape) the single scrape is enough.
75
76	### 3. Pull top slow query patterns
77
78	Request the slow query patterns. Follow
79	`rules/slow-query-patterns-fields.md` for the fields that
80	matter and how to read them. This is the primary diagnostic —
81	it returns per-pattern accumulated totals (call count, runtime,
82	blocks, rows) over the window you request, which is the
83	"rate-of-change" data you'd otherwise derive from two Prom
84	scrapes — but per query and without waiting.
85
86	If no patterns return a meaningful `totalDurationUs`, the
87	report may be overstated or the issue isn't query-shaped.
88	Stop and tell the user what you looked at.
89
90	### 4. Triage: pick the right heuristic
91
92	Follow `rules/triage.md`. Match the combined Prom + slow-query
93	signal to one of the heuristic shapes. Each shape points to a
94	specific heuristic file:
95
96	- `rules/heuristic-full-scan.md` — read-path full scan.
97	- `rules/heuristic-hot-loop.md` — N+1 / hot loop from the app.
98	- `rules/heuristic-write-congestion.md` — deadlocks, slow
99	writes, high rollback rate.
100
101	If the signal does not match any shape cleanly, do not invent
102	a hypothesis. Surface the top patterns and ask the user which
103	workload they recognize. New heuristics are welcome as PRs.
104
105	### 5. Reason, then recommend
106
107	Use the format in `rules/output-template.md`. Always include:
108	symptom, evidence, hypothesis (noting any alternative cause
109	you cannot rule out from this surface alone), short-term fix,
110	and long-term follow-ups.
111
112	### 6. Do not apply the fix
113
114	Follow `rules/recommend-only.md`. Never run DDL. Never call
115	`pg_cancel_backend` or `pg_terminate_backend`. Write the
116	recommendation, explain why, and let the human apply it.
117
118	## Full Compiled Document
119
120	For the complete guide with every rule expanded in a single
121	context load: `AGENTS.md`.

People who liked this also viewed

6 picks

Type