clickhouse-managed-postgres-rca

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

ClickHouse Managed Postgres RCA

ClickHouse托管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.

当用户反馈ClickHouse托管的Postgres实例出现缓慢、CPU占用过高、吞吐量低、缓存抖动或其他无法解释的异常情况时，触发此流程。

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.

https://api.clickhouse.cloud

上的两个API（使用ClickHouse Cloud API密钥/密钥对进行HTTP Basic认证）：

Prometheus指标 — Prometheus标签下的
```
postgresInstancePrometheusGet
```
操作。返回Prometheus暴露格式的数据，包含单个Postgres服务的系统和工作负载指标。
慢查询模式 — Postgres标签下的
```
slowQueryPatternsGetList
```
操作。返回标准化查询模式的摘要级延迟、IO和调用统计数据。Beta版本。

这两个端点都需要

organizationId

和

serviceId

作为路径参数。用户必须提供这两个参数以及API密钥/密钥对。

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.

查询计划/EXPLAIN输出。
每张表的扫描类型计数器（
```
seq_scan
```
/
```
idx_scan
```
）。
自动清理（Autovacuum）或最后一次ANALYZE的时间戳。

需基于IO和时序信号进行推理，而非计划树。

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.

共六个步骤，按顺序执行，不得跳过。

步骤2和步骤3仅共享认证信息——彼此无数据依赖。可并行执行（后台curl命令，使用

wait

），将总耗时从串行的约2秒缩短至约1秒。

1. Discover the live API shape

1. 探索实时API结构

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.

这些端点处于Beta阶段——路径、参数和JSON字段名称可能会变动。请遵循

rules/openapi-discovery.md

中的步骤：

从
```
https://api.clickhouse.cloud/v1
```
获取OpenAPI规范。
通过
```
operationId
```
定位两个操作：
- ```
postgresInstancePrometheusGet
```
  （Prometheus标签）
- ```
slowQueryPatternsGetList
```
  （Postgres标签）
解析它们的路径模板、必填查询参数以及（针对慢查询端点）响应 schema。
根据schema属性描述构建会话作用域的角色映射：
```
{ 语义角色 → 实际字段名 }
```
。

在后续所有请求和引用中使用解析后的名称，切勿凭记忆硬编码字段名。

2. Scrape Prom once for system gauges

2. 抓取一次Prometheus系统仪表指标

rules/prometheus-scrape.md

. One scrape, no wait. You're after gauges (current values) that don't need a delta:

CacheHitRatio

ActiveConnections

MemoryUsedPercent

FilesystemUsedPercent

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.

遵循

rules/prometheus-scrape.md

。仅抓取一次，无需等待。目标是获取无需计算增量的仪表指标（当前值）：

CacheHitRatio

、

ActiveConnections

、

MemoryUsedPercent

、

FilesystemUsedPercent

。

对于本应能装入缓存的工作负载，若

CacheHitRatio

远低于约95%，这本身就是一个明确信号。

ActiveConnections

攀升至连接池上限附近，同样是明确信号。这些指标无需计算变化率。

仅当步骤4的分类指向写入拥堵（此时死锁和回滚速率至关重要，且Slow Query Patterns API无法替代）时，才需要选择抓取第二次以获取计数器增量。对于最常见的读路径问题RCA场景，单次抓取已足够。

3. Pull top slow query patterns

3. 获取顶级慢查询模式

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.

请求慢查询模式数据。请遵循

rules/slow-query-patterns-fields.md

了解关键字段及其解读方式。这是主要诊断手段——它会返回你请求的时间窗口内，每个查询模式的累积统计数据（调用次数、运行时间、块数、行数），这相当于你通过两次Prometheus抓取计算出的“变化率”数据，但针对每个查询且无需等待。

如果没有模式返回有意义的

totalDurationUs

，则问题报告可能夸大，或者问题并非由查询导致。此时需停止流程并告知用户你的排查内容。

4. Triage: pick the right heuristic

4. 分类：选择合适的启发式规则

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.

遵循

rules/triage.md

。将Prometheus指标与慢查询信号相结合，匹配对应的启发式模式。每种模式指向一个特定的启发式规则文件：

```
rules/heuristic-full-scan.md
```
— 读路径全表扫描。
```
rules/heuristic-hot-loop.md
```
— 应用端的N+1/热循环问题。
```
rules/heuristic-write-congestion.md
```
— 死锁、写入缓慢、高回滚率。

如果信号无法清晰匹配任何模式，请勿凭空假设。列出顶级模式并询问用户识别出哪些工作负载。欢迎通过PR提交新的启发式规则。

5. Reason, then recommend

5. 推理并给出建议

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.

使用

rules/output-template.md

中的格式。内容必须包含：症状、证据、假设（注明仅通过表层信息无法排除的其他可能原因）、短期修复方案以及长期跟进措施。

6. Do not apply the fix

6. 请勿执行修复

rules/recommend-only.md

. Never run DDL. Never call

pg_cancel_backend

pg_terminate_backend

. Write the recommendation, explain why, and let the human apply it.

遵循

rules/recommend-only.md

。切勿执行DDL语句，切勿调用

pg_cancel_backend

或

pg_terminate_backend

。撰写修复建议并解释原因，由用户自行执行。

Full Compiled Document

完整编译文档

For the complete guide with every rule expanded in a single context load:

AGENTS.md

如需包含所有扩展规则的完整指南，请查看：

AGENTS.md

。