Postgres Query Insights: 一屏掌握数据库健康！
Amog Iska约 8 分钟本文字数：4746；估计阅读时间：12 分钟
作者：Amog Iska
查询变慢事出有因。Postgres 提供了大量相关信号；pg_stat_ch 按语句捕获这些信号，而 Postgres Query Insights 则将它们整合呈现。
Query Insights 现已在 ClickHouse Cloud Managed Postgres 上推出预览版：它能根据影响对数据库运行的每个查询模式进行排名，并提供导致每个查询变慢的完整诊断信息。我们构建了开源扩展 pg_stat_ch，以将每条语句的遥测数据 (telemetry) 流式传输到 ClickHouse，从而实现 Insights 功能。
三大功能界面，按实际使用顺序排列。
概览
打开 Query insights 选项卡，您将看到一个一屏即可展示的数据库健康检查视图，包含：
• 查询量
• 错误率
• 缓存命中率
• 工作负载中实际包含的各类操作
• 您所选时间窗口内的延迟
一屏即可判断数据库的健康状况。无需层层深入，无需交叉比对，也无需在头脑中同时处理多个信息页。
慢查询模式
当概览显示异常时，模式表将是您着手调查的起点。数据库运行的每个查询模式占据一行，可根据您的关注点进行排序：
• 总运行时长
• 总 CPU 使用量
• 错误数量
• 最大延迟
• P95
当你按总持续时间 (total duration) 排序时，排名第一的模式 (pattern) 通常就回答了这个问题：“哪类查询的开销最大？” 它不一定是单次执行最慢的模式。一个每天运行八百万次、每次耗时十二毫秒的查询 (query) ，可能比一个只运行过一次、耗时三秒的查询更值得关注。
每一种排序方式都会提供一个不同的观察角度。总 CPU (Total CPU) 可以显示计算密集型模式。错误计数 (Error count) 会暴露反复发生的失败。P95 能捕捉最严重的异常值。结合使用这些排序方式，就能把笼统的故障信号收敛成一个明确的排查起点。
你可以按以下维度，将表格缩小到正在调查的工作负载 (workload) 范围：
数据库 (database)
应用程序名称 (application name)
操作类型 (operation type)
用户 (user)
“只显示订单服务 (orders service) 在销售数据库 (sales db) 上的操作。”
详情
点击某个模式的行，会打开浮出面板 (flyout) 。调查通常会从这里真正展开。
详情面板会汇总该模式在指定时间范围内的所有执行，并聚合各项指标，以解释其运行缓慢的原因：
• 百分位延迟 (p95/p99)
• CPU 时间消耗分布
• 缓存与磁盘读取情况
• 溢出到临时空间的数据量（已读取的块数）
• 应启动而未启动的并行工作线程信息
• WAL 写入量来源
诊断缓慢模式所需的所有信息都汇集一处，让您一目了然。
这是一个具体的演练。您运行一个托管式 Postgres 实例，为销售订单仪表板提供支持。在过去一周内，该仪表板主端点（endpoint）的 p99 延迟持续攀升，而 p50 保持正常。用户偶尔报告查询缓慢和查询超时。您打开 Query Insights，以找出导致问题的查询。
1. 打开标签页。
进入实例页面，点击 Query insights。统计信息网格显示：查询量持平，错误率持平，缓存命中率（cache hit ratio）为 99.4%。乍看之下，一切正常。
2. 切换图表指标。
默认图表显示的是 query_count。您将其切换到 p99_duration。此时，曲线在过去一周内呈上升趋势。而 p50 保持平稳。这表明延迟回归真实存在，且主要体现在长尾部分。
3. 找到缓慢模式。
您将模式列表按 Total Duration、P99 或 Avg Duration 降序排列。
排在最上面的一行是：
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = $1
ORDER BY orders.created_at DESC
LIMIT $2;
4. 打开模式详情面板。
• 平均延迟正常，保持在个位数毫秒
• p99 已达到数百毫秒（这表明长尾延迟才是实际问题）
• 缓存命中率（cache hit ratio）接近 100%，因此瓶颈不在于共享缓冲区 I/O
• WAL 字节数为零，这符合只读查询的预期
深入查看详情面板中的近期执行：
• Temp block ops 不为零（表明排序操作正在溢出到磁盘）
• Parallel workers launched 远低于 parallel workers planned
这种现象组合至关重要。这既不是写入问题，也不是缓冲池问题。查询在排序时发生溢出，而正是这种溢出导致了长尾延迟。
5. 修复。
一旦识别出磁盘溢出（spill），下一步是使用 EXPLAIN (ANALYZE, BUFFERS) 进行确认。查询计划将显示 Sort 节点被标记为溢出到磁盘，同时也会显示排序在执行期间实际消耗了多少内存。你会看到 Sort Method: external merge Disk: NkB，而一个健康的计划则会显示 Sort Method: quicksort Memory: NkB。此处的 Disk 数值表示排序操作写入了多少临时文件。将其与你配置的 work_mem 进行比较：如果只是少量超出，这通常是一个调优问题；如果超出数倍之多，则表明存在查询计划结构问题。
至此，解决方案就清晰了：可以添加一个支持过滤和排序的索引，从而让 Postgres 完全避免排序操作；或者为正确的角色或会话增加 work_mem，以确保排序有足够的内存空间来运行；亦或两者兼施。Query Insights 帮你定位到问题模式，而 EXPLAIN ANALYZE 则会指导你采取何种措施。
应用修复后，Query Insights 会立即显现出差异。磁盘溢出从问题模式的详细视图中消失，并行工作器（parallel workers）按计划启动，而 p99 延迟也回落到与 p50 延迟相近的水平。总览页面也证实了这一点：缓存命中率（cache hit ratio）稳定，没有出现新错误，整体延迟保持平稳。实例再次恢复健康。
一个健康的实例具有熟悉的特征。缓存命中率（cache hit ratio）稳定在 90% 以上。查询量随应用程序流量同步波动，而非背离。错误率保持平稳或为零。在模式表中，没有一个模式是主要的瓶颈：总耗时分散在多个模式中，没有某个模式显著突出。延迟百分位数（latency percentiles）彼此紧密相随，p99 维持在 p50 的合理倍数范围内。当所有指标都呈现出这样的状态时，Query Insights 便以一种最理想的方式保持着“安静”。
产品背后的一些设计选择：
我们使用与客户相同的引擎。 Insights 的后端是 ClickHouse Cloud —— 这是我们所知最快地存储和查询大规模、大体量数据的方式。繁忙的 Postgres 实例每天会产生数百万行的查询遥测数据。ClickHouse 能够从多个数据源（producers）摄取数据，其列式压缩（columnar compression）技术能够以低成本保留数月的执行细节数据，并支持在数十亿行数据上进行亚秒级的聚合查询。即使在对繁忙数据库上数周或数月的每条执行语句进行切片分析时，用户界面（UI）也能保持高度交互性：百分位重计算、排名重排序以及过滤器更改等操作都极为迅速。
在数据传输前，在 Postgres 内部完成标准化。 我们会在解析-分析阶段进行介入，即 Postgres 解析语句并识别查询文本中所有字面量位置的时刻。我们将每个字面量替换为占位符（如 $1、$2 等），并将生成的模式缓存在一个按 queryid 键控的、每个后端独立的 LRU (Least Recently Used) 缓存中。当执行器完成语句执行时，该缓存模式会在事件入队之前被附加到事件上。包含具体值的原始语句绝不会离开数据库。个人身份信息 (PII) 和个人健康信息 (PHI) 在设计上不会出现在遥测数据流中。
不影响数据库运行。 每条语句约 3% 的生产者开销：入队路径使用共享内存环形缓冲区上的非阻塞尝试锁。如果锁竞争激烈，生产者会在本地排队并在事务结束时刷新，而不是空转或阻塞。在压力下，该扩展会丢弃部分事件并进行计数，而不是对 Postgres 施加反压。遥测数据收集的首要原则是：绝不能成为你试图测量的瓶颈。
开源。 pg_stat_ch 采用 Apache 2.0 许可证。您可以在任何 Postgres 实例上运行它，并将数据传输到任何 ClickHouse 实例。
原始事件，而非聚合数据。 pg_stat_ch 为每个执行的语句（包括顶层和嵌套语句）发出一个原始事件，并支持采样。用户界面中显示的所有百分位数、排名和细分数据，都是通过对同一事件流执行 ClickHouse 查询获得的。
我们接下来的一些工作包括：一个开放 API (Open API)，它将暴露 UI 所使用的相同数据，并专为智能体时代 (agentic era) 而构建。让您的 AI 代理 (AI agents) 能够直接访问模式聚合数据和每次执行的计数器，从而使它们能够对数据进行推理、自主识别瓶颈，并采取行动修复应用程序中缓慢或故障的部分。
以下是开放 API 及其如何赋能代理的抢先预览。我们已在名为 HouseClick 的演示应用程序上对此进行了测试。
该演示的 PR (Pull Request) 链接：https://github.com/ClickHouse/HouseClick/pull/55
我们还在开发等待事件 (wait events) 功能，用于按每次执行对 Postgres 实际等待的原因（如 I/O、锁、缓冲区引脚、IPC、客户端）进行归因分析。这对于 UI 场景特别有用，例如当 CPU 和 I/O 计数器都很小，但查询仍然花费了数百毫秒的情况。
我们还计划提供针对慢查询的 EXPLAIN plans，以帮助用户识别执行计划中的瓶颈。这样做的目标是保持其可配置性，以便管理数据库负载，例如，允许启用不带 BUFFERS 的 ANALYZE，或者为计划收集设置查询延迟阈值。
基于这些数据，我们计划提供可操作的建议，例如索引提示、work_mem 调优建议和 autovacuum 指导，以减少用户在“下一步该做什么”上的认知负担。我们还希望为 p99 latency 翻倍、出现新的主要问题源或错误计数超出预设阈值等情况添加回归警报。
您可以通过注册 ClickHouse Cloud 并部署一个 Postgres 服务来体验 Query Insights。如果您已经拥有 Postgres 服务但未看到 Query Insights 功能，请提交支持工单，我们将为您启用。要开始使用：
• 注册 ClickHouse 托管的 Postgres 服务
• 查阅快速入门指南
• 浏览文档
驱动 Postgres Query Insights 的扩展位于 github.com/clickhouse/pg_stat_ch（基于 Apache 2.0 许可证）：欢迎提交问题、发送 PR，并可在任何 Postgres 实例上运行。
/END/
试用阿里云 ClickHouse企业版
轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]