本文字数：11517；估计阅读时间：29 分钟

作者：Kaushik Iska

本文在公众号【ClickHouseInc】首发

原标题：pg_stat_ch：一个将每次查询执行导出到 ClickHouse 的 PostgreSQL 扩展

我们正式开源 pg_stat_ch：这是一个 PostgreSQL 扩展，它会把每一次查询执行转换为一个固定大小约 4.6KB 的事件，并将这些事件流式写入 ClickHouse。

当这些事件进入 ClickHouse 之后，你就可以像使用 APM 一样，对查询行为进行切片分析和深度钻取：查看随时间变化的 p50 到 p99 延迟、按运行时间排序的 Top 查询、按应用划分的错误情况，以及对比“下午 2 点到 3 点之间发生了什么变化”，无论是跨越几天还是几个月的历史数据。

该项目采用 Apache 2.0 开源协议，支持 PostgreSQL 16 到 18。如果你想快速上手，可以直接试试 quickstart。

在这篇文章中，我会介绍它的底层实现原理、我们在设计中做出的权衡，以及它与现有扩展之间的对比。

为什么要构建 pg_stat_ch？

在 1 月，我们发布了由 ClickHouse 管理的 Postgres。我们需要深入了解所管理集群的运行情况，同时也希望为客户提供同样级别的可观测能力。

ClickHouse——我们最为人熟知的分析型数据库——内置了系统表，用于记录服务器内部发生的所有事情。同时，它本身就是为分析而设计的，因此你可以直接在 ClickHouse 中分析自身的使用情况。我们在 ClickHouse Cloud 的托管 ClickHouse 服务中依赖这一能力，客户也是如此。

而 Postgres 默认并不具备这种级别的内部可观测能力，也不是为分析场景而设计的。因此，我们希望构建一种机制，既能捕获 Postgres 内部运行的细节，又能提供同等级别的分析能力来处理这些数据。下面会展示一些使用 pg_stat_ch 可以获得的洞察示例。

我们经常使用 pg_stat_statements、pg_stat_monitor 和 pg_tracing 等扩展。虽然它们覆盖了部分需求，但我们有 3 个核心目标是它们无法满足的：

• 捕获 PostgreSQL 集群中的一切行为：每一条 SELECT、INSERT、DDL，甚至是执行失败的查询。

• 将事件发送到外部系统进行分析。

• 对 PostgreSQL 本身带来尽可能低的开销。

我们目前还没有将 pg_stat_ch 投入生产环境，但作为 ClickHouse 托管 Postgres 计划的一部分，我们正在积极推进这一步。当前版本会将每条查询的事件流式写入 ClickHouse，查询文本上限为 2KB，暂不包含执行计划的捕获。

如果你在大规模运行 PostgreSQL，我们非常欢迎你的反馈。随着系统逐步成熟，我们也会持续分享实践经验。

30 秒看懂整体架构

在实现层面，pg_stat_ch 尽量减少对主路径的影响：在热路径上，它只做一件事——通过一次 memcpy 将数据写入共享内存环形缓冲区。一个后台 worker 每秒唤醒一次，从缓冲区中批量取出数据，并通过 ClickHouse 原生二进制协议发送，同时使用 LZ4 进行压缩。

每当 PostgreSQL 执行一条语句——无论是 SELECT、INSERT、DDL，还是因为语法错误而失败的查询——pg_stat_ch 都会将其记录为一个固定大小的事件。每个事件包含 45 个字段，覆盖执行耗时、缓冲区 I/O、WAL、CPU 与 JIT 统计信息、错误详情以及基础的客户端上下文信息。

随后，后端进程只需进行一次快速内存拷贝，将数据写入共享内存环形缓冲区，然后立即继续执行后续逻辑。后台 worker 每秒最多提取 10,000 条事件，将其打包成列式数据块，使用 LZ4 压缩后，通过原生二进制协议发送到 ClickHouse。

在 ClickHouse 侧，原始事件首先写入 events_raw 表，同时有四个物化视图会对其进行预聚合，生成可直接查询的仪表盘数据：

这里的关键在于，所有聚合计算都发生在 ClickHouse 中，而不是在 PostgreSQL 中。Postgres 只负责捕获事件并将其推送出去；ClickHouse 负责压缩、存储，并响应各类分析查询。

工程决策 

决策 1：固定大小事件 

在事件数据结构上，我们有两个选择：变长事件（根据查询文本大小为每个事件动态分配空间，例如 pg_stat_monitor 使用 PostgreSQL 的 DSA 分配器所做的那样），或者固定大小事件。

我们最终选择了固定大小。memcpy 本身非常快，但真正的开销来自 LWLock 的获取、名称解析（get_database_name、GetUserNameFromId、GetClientAddress），以及用于 CPU 计时的 getrusage()。关于实际测得的性能开销，我们会在下文详细说明。

固定大小带来的更大优势，是内存占用完全可预测，可以在启动阶段精确规划：

queue_capacity = 65,536 events (default)
event_size     = ~4.6 KB
total memory   = 65,536 × 4.6 KB = 294.4 MB

在 shared_preload_libraries 阶段，你就能准确计算所需的共享内存大小。队列容量和事件大小都是可配置参数，可以根据实际工作负载进行调优。

当然，这也存在权衡：超过 2KB（或配置的事件大小）的查询文本会被截断。不过，你仍然可以获得查询指纹（query_id）以及全部 45 个指标。如果该查询仍在执行，可以通过 pg_stat_statements 中的 query_id 查到完整文本。

决策 2：确保对 Postgres 的影响微乎其微 #

环形缓冲区是整个扩展中最核心、也是最热的数据结构。所有后端进程都会向其中写入数据（生产者），而一个后台 worker 负责读取数据（消费者）。在一个繁忙的 32 核系统上，可能会有数十个并发写入者。

其内存布局如下：

┌───────────────────────────────────────────┐
│ [Rarely changed]                          │
│   LWLock* lock                            │
│   uint32  capacity                        │
│                                           │
│ ─── CACHE LINE BOUNDARY (64 bytes) ───────│
│                                           │
│ [Producer-hot: many backends write]       │
│   atomic_uint64 head                      │
│   atomic_uint64 enqueued                  │
│   atomic_uint64 dropped                   │
│   atomic_flag   overflow_logged           │
│                                           │
│ ─── CACHE LINE BOUNDARY ──────────────────│
│                                           │
│ [Consumer-hot: bgworker reads/writes]     │
│   atomic_uint64 tail                      │
│   atomic_uint64 exported                  │
│                                           │
│ ─── CACHE LINE BOUNDARY ──────────────────│
│                                           │
│ [Stats: bgworker writes, anyone reads]    │
│   atomic_uint64 send_failures             │
│   TimestampTz   last_success_ts           │
│   TimestampTz   last_error_ts             |
│   char          last_error_text[256]      │
│   atomic_uint32 bgworker_pid              │
└───────────────────────────────────────────┘

各区域之间通过 PG_CACHE_LINE_SIZE 进行填充，这是一个至关重要的细节。如果没有这种隔离，每当后台 worker 更新 tail 时，都会使所有生产者 CPU 核心上包含 head 的缓存行失效。这种现象称为伪共享，在多路 CPU 系统上，每次访问可能会带来 50–100ns 的额外开销。通过缓存行隔离，生产者核心和消费者核心分别操作不同的缓存行，互不干扰。

队列容量始终是 2 的幂（1,024 到 65,536），因此槽位寻址可以使用位运算（index & (capacity - 1)）而不是取模运算，从而在热路径上省去一条除法指令。

决策 3：不引入背压机制

事件丢失只会发生在两种情况下，而且都是经过设计的结果：

1. 队列溢出。当环形缓冲区的填充速度超过后台 worker 的消费速度时，新事件会被丢弃——生产者通过原子操作递增 dropped 计数器，然后在纳秒级时间内返回。

2. 导出失败。后台 worker 已经出队一批事件，但在向 ClickHouse 插入时失败。这批事件将直接丢失，我们不会将其重新入队。

另一种设计是引入背压机制。这意味着一旦 ClickHouse 变慢或不可用，PostgreSQL 也会受到影响，查询延迟随之上升。对我们而言，这是无法接受的。

举例来说，对于一个每秒处理 50,000 次查询的 OLTP 系统，如果在 ClickHouse 短暂故障期间，每个查询因为背压额外增加 10ms 延迟，那么 p99 延迟会从 5ms 上升到 15ms。这将显著影响 SLO，用户也会明显感知到。为了监控而牺牲业务性能，是不可接受的权衡。

这与基于 UDP 的指标系统（StatsD、DogStatsD）所遵循的理念一致：宁可丢失少量数据点，也不要影响被观测的系统。

可观测性应该是观察者，而不是阻碍者。

dropped 计数器可以通过内置的 pg_stat_ch_stats() 函数查看，从而监控溢出情况并调整 queue_capacity。实际运行中，在 1 秒刷新间隔、10K 批量大小的配置下，只有在持续 65K QPS 且 ClickHouse 完全停滞的情况下，才会开始出现丢失。

决策 4：最小化锁竞争 

入队路径采用三层机制，以尽可能减少锁竞争：

• 第 1 步是安全阀机制。我们仅使用原子操作检查是否溢出。如果环形缓冲区已满，直接丢弃事件并立即返回。整个溢出处理过程不会获取任何锁。

• 第 2 步是正常路径。我们以非阻塞方式尝试获取 LWLock。在没有竞争的情况下，入队流程与最简单的实现一致：获取锁，将数据 memcpy 写入对应槽位，递增 head 指针，然后释放锁。

• 第 3 步则用于在大量后端并发运行（例如 32 个以上）时提升扩展性。与其让每个后端为每条查询都争夺同一个独占锁，不如让每个后端先将事件暂存在一个小型进程本地缓冲区中，并在事务结束时统一刷新。在 TPC-B 测试中，每个事务大约包含 5 条查询，这种设计将每秒锁获取次数从约 150K 降低到约 30K，实现了大约 5 倍的下降。

这个进程本地缓冲区位于静态 BSS 段中，每个后端约占用 37KB（8 个事件 × 每个约 4.6KB）。此外，pg_stat_ch 还注册了 on_shmem_exit 回调，在后端退出时刷新剩余数据，即使后端在事务中途退出，也能将缓冲的事件导出。

决策 5：使用带 LZ4 压缩的原生协议 

我们通过 clickhouse-cpp（以静态方式链接进扩展 .so 文件）使用 ClickHouse 的原生二进制协议，而不是 HTTP。主要有三个原因：

1. LZ4 块压缩——当每秒需要通过网络发送数千条事件时，带宽优化至关重要。

2. 列式编码——数据以列主序格式发送，ClickHouse 可以直接写入，无需进行行到列的转换。

3. 类型安全的二进制编码——避免了 JSON 解析带来的额外开销。

我们将 clickhouse-cpp 客户端作为静态库编译进扩展中，这让部署变得非常简单：除了 PostgreSQL（以及可选的 OpenSSL）之外，没有额外的运行时依赖。代价是体积更大——静态链接后扩展大小超过 20 MB。不过，只要能减少“在我机器上没问题”这类环境差异带来的问题，我们认为这个权衡是值得的。

我们特意将 socket 超时设置为 30 秒。后台 worker 仍然需要响应 PostgreSQL 的信号，尤其是在执行 DROP DATABASE 等操作期间（通过 procsignal_sigusr1_handler）。如果使用无限超时，而到 ClickHouse 的网络发生阻塞，就可能导致 DROP DATABASE 一直挂起，直到 socket 恢复，这显然不可接受。

我们如何接入 PostgreSQL 的钩子机制 

PostgreSQL 为扩展提供了若干 hook。pg_stat_ch 会注册到这些 hook 上，从而在 Postgres 清理相关状态之前，在恰当的时机捕获所需数据。下面是我们使用的 hook、在各个 hook 中执行的操作，以及它们为何重要。

所有 hook 都会按照链式方式调用之前的 hook，因此 pg_stat_ch 可以与 pg_stat_statements、auto_explain、pg_tracing 以及其他扩展和平共存。链式调用的顺序遵循 shared_preload_libraries 的加载顺序。

对 PostgreSQL 的性能影响 

我们在 AMD Ryzen AI MAX+ 395（16c/32t，64MB L3，128GB RAM）上运行 pgbench（TPC-B，scale factor 10，32 个客户端，8 个线程，30 秒），分别测试启用和未启用 pg_stat_ch 的 PostgreSQL 18。ClickHouse 运行在本地 Docker 中。

在 36.6K TPS 下，pg_stat_ch 在 30 秒内捕获了 770 万条事件，且没有任何丢失（queue_capacity=4M，flush_interval=100ms，batch_max=100K）。所有数据均为连续两次运行的平均值。

下面是 flamegraph：

我们还使用 perf record（启用 DWARF 调用图）对 8 个后端进程进行了 10 秒的性能分析：

Overhead Component                    % of CPU
──────────────────────────────────────────────────
Enqueue (try-lock fast path)            0.94%    ← was 2.32% before batching
  Batch flush (XactCallback → batch)    0.54%    ← new: amortized lock path
BuildEvent (QueryDesc → struct)         0.33%
ProcessUtility overhead                 0.08%
InstrAlloc (enable instrumentation)     0.05%
Nesting tracking (Run + Finish)         0.05%
──────────────────────────────────────────────────
TOTAL pg_stat_ch CPU overhead          ~2.0%

结果显示，pg_stat_ch 带来的 CPU 开销约为 ~2%。而基准测试中观察到的 ~11% TPS/延迟下降，主要来自入队锁竞争被放大的效应。在该 pgbench TPC-B 场景中，事务极短（亚毫秒级）且并发度很高，因此即便是一个很小的串行临界区，也可能导致明显的 TPS 下降。

在引入 try-lock + 本地批处理优化之前，pg_stat_ch 会在每个查询上获取一次入队 LWLock。在该负载下，这把锁成为明显的热点，TPS 下降约为 24%。引入 try-lock + 本地批处理后，下降幅度降低到约 11%：后端会优先尝试非阻塞入队；如果检测到锁竞争，则将事件缓存在本地，并在事务结束时统一刷新。对于每个 TPC-B 事务约 5 条查询的场景，这将锁获取次数从约 150K/s 降低到约 30K/s（减少约 5 倍），显著缓解了锁竞争。

这也解释了为什么会出现 ~2% CPU 开销与 ~11% TPS 开销之间的差异。~2% 指的是实际花在 pg_stat_ch 代码中的 CPU 时间（构建事件、快速入队路径、批量刷新），不包括锁竞争带来的连带影响。剩余差距来自共享入队路径上的锁竞争和调度开销，这些表现为吞吐量下降，而不是扩展本身消耗更多 CPU。

在 32 个客户端的 TPC-B 场景下，这可能接近锁竞争影响的最坏情况。我们预计大多数真实业务负载受到的影响会更小。产品发布后不久，我们会在更具代表性的工作负载上公布更多测试数据，以量化在锁压力较低情况下的实际开销。

ClickHouse 中的存储与压缩 

在 PostgreSQL 共享内存中，每个 PschEvent 都是固定大小。我们将 770 万条 pgbench 事件写入 ClickHouse，并测量了实际的磁盘占用情况：

7.7M events × ~4.6KB ≈ 35 GB as raw structs
ClickHouse compressed:     426 MB
compression ratio:         ~83:1 (from raw event size)
bytes per row:             ~36 bytes compressed

按列拆分后，可以清楚看到压缩收益主要来自哪里：

低基数字符串列（db、username、app）几乎被压缩到接近于零——通过字典编码，每行只需占用几个比特。query 列占用了 119MB，是最大的部分，但在 pgbench 场景中只有少量不同的查询文本被重复了数百万次，因此这一列仍然实现了 5.6:1 的压缩比。

在真实业务负载中，如果查询类型更加多样，压缩结果会有所不同——query 列体积会更大，但低基数列依然接近于零。核心结论不变：即使是数月的查询遥测数据，占用的存储空间也出乎意料地小。

对于一个 10K QPS 的真实业务场景，我们预计每月存储成本低于 100 美元：

events per day:       10,000 × 86,400 = 864M
raw event size:       864M × 4.6KB ≈ 4 TB/day
at 36 bytes/row:      864M × 36 = ~31 GB/day
monthly storage:      31 × 30 = ~930 GB/month
cloud block storage:  ~$0.08/GB/month = ~$74/month
S3-backed cold tiers: ~$0.02/GB/month = ~$19/month

对于一个 10K QPS 的系统来说，考虑到它带来的事件级可观测能力，这样的成本是非常合理的。

你可以用原始事件做什么

当每条查询的原始事件数据存储在 ClickHouse 中后，你可以提出一些在仅有聚合数据系统中根本无法回答的问题。以下是一些真实查询示例：

找出具体哪些查询导致了缓存未命中：

SELECT
    query_id,
    any(query) AS sample_query,
    count() AS executions,
    avg(shared_blks_read) AS avg_physical_reads,
    sum(shared_blks_read) AS total_physical_reads
FROM events_raw
WHERE ts_start > now() - INTERVAL 24 HOUR
  AND shared_blks_read > 1000
GROUP BY query_id
ORDER BY total_physical_reads DESC
LIMIT 20;

查看最昂贵查询的百分位分布情况：

SELECT
    query_id,
    any(query) AS sample_query,
    count() AS executions,
    round(100 * sum(duration_us) / (
        SELECT sum(duration_us)
        FROM pg_stat_ch.events_raw
        WHERE ts_start > now() - INTERVAL 24 HOUR
    ), 2) AS pct_runtime,
    count() AS cnt,    round(sum(duration_us) / 1e6, 2) AS total_seconds,
    round(quantile(0.50)(duration_us) / 1000, 0) AS p50_ms,
    round(quantile(0.90)(duration_us) / 1000, 0) AS p90_ms,
    round(quantile(0.99)(duration_us) / 1000, 0) AS p99_ms
FROM pg_stat_ch.events_raw
WHERE ts_start > now() - INTERVAL 24 HOUR
GROUP BY query_id
ORDER BY sum(duration_us) DESC
LIMIT 20;

借助 ClickHouse，你可以在极长的时间窗口内分析 Postgres 数据库的健康状况，并在几秒内查询所有历史数据。

事实上，ClickHouse 还能支持一些非常有趣的分析。例如，在 pgbench 基准测试期间，我们可以精确地找出执行次数最多的那一条查询——不是查询类型，而是具体的那条 SQL：

SELECT
    query,
    cmd_type,
    count(*) AS c
FROM events_raw
WHERE (query_id > 0) AND (cmd_type != 'UTILITY') AND (cmd_type != 'SELECT')
GROUP BY
    query,
    cmd_type
ORDER BY c DESC
LIMIT 5
Query id: abfc5785-48d8-4111-bfb9-2696a010a6d3
┌─query────────────────────────────────────┬─cmd_type─┬──c─┐
│ UPDATE pgbench_branches SET bbalance...  │ UPDATE   │ 26 │
│ UPDATE pgbench_branches SET bbalance...  │ UPDATE   │ 26 │
│ UPDATE pgbench_branches SET bbalance...  │ UPDATE   │ 26 │
│ UPDATE pgbench_branches SET bbalance...  │ UPDATE   │ 26 │
│ UPDATE pgbench_branches SET bbalance...  │ UPDATE   │ 25 │
└──────────────────────────────────────────┴──────────┴────┘
5 rows in set. Elapsed: 10.268 sec. Processed 63.33 million rows, 3.43 GB (6.17 million rows/s., 333.55 MB/s.)

物化视图同样可以支持这些场景。通过 *State() / *Merge() 模式，聚合结果是增量维护的——仪表盘无需反复扫描原始数据：

CREATE MATERIALIZED VIEW query_stats_5m TO query_stats_5m_target AS
SELECT
    toStartOfFiveMinutes(ts_start) AS bucket,
    db, query_id, cmd_type,
    countState()                                    AS calls,
    quantilesTDigestState(0.95, 0.99)(duration_us)  AS duration_quantiles,
    sumState(shared_blks_hit)                       AS total_shared_blks_hit,
    sumState(shared_blks_read)                      AS total_shared_blks_read
FROM events_raw
GROUP BY bucket, db, query_id, cmd_type;

构建仪表盘时查询 query_stats_5m 表，进行深入分析时查询 events_raw 表。两者始终保持实时更新。

基于这些能力，我们可以构建监控仪表盘，持续跟踪 Postgres 的关键运行指标：

一个使用 pg_stat_ch 监控 Postgres 的 ClickHouse Cloud 仪表盘

与其他扩展的比较 

在 Postgres 指标采集领域，已经有不少优秀工具，它们解决的是部分重叠但侧重点不同的问题。

pg_stat_statements 

pg_stat_statements 是几乎每个 DBA 都熟悉的基础工具。

它不依赖额外基础设施，保存完整的查询文本，并且经过十多年的生产环境验证。如果你的需求只是查看“按总执行时间排序的热门查询”，它已经足够。

但对我们而言，它无法满足一些关键需求，例如时间序列分析（事件带有时间戳）、百分位统计（不仅仅是 mean/min/max）、错误跟踪（SQLSTATE + message + severity），以及长期保留历史数据。如果你想回答“昨天 2pm 到 3pm 之间发生了什么变化？”，pg_stat_statements 并不适合。

两者使用相同的 query_id 指纹，因此可以自然关联，也可以同时运行。

pg_stat_monitor 

Percona 的 pg_stat_monitor 是这一领域功能最丰富的扩展之一。它在 pg_stat_statements 的基础上增加了时间分桶直方图、查询计划捕获、错误跟踪以及客户端 IP 跟踪等能力。

pg_stat_monitor 已支持查询计划捕获，而 pg_stat_ch 目前尚未实现，这是两者之间最大的功能差距。此外，pg_stat_monitor 没有外部依赖，执行 CREATE EXTENSION 即可使用。

相比之下，pg_stat_ch 提供无限期数据保留（pg_stat_monitor 只会在 N 个共享内存桶中轮转，通常最多保留数小时数据）、原始事件级粒度而非预聚合桶、更低的热路径开销（每条查询仅一次 memcpy，而不是执行哈希表查找和更新），以及借助 ClickHouse SQL 进行任意分析的能力。同时还有 ClickHouse 的高压缩率优势：一个典型事件在磁盘上压缩后仅约 100–200 字节，而在 Postgres 共享内存中为约 4.6KB。

pg_tracing

pg_tracing 会为每条查询生成 OpenTelemetry 的 span（OpenTelemetry spans），包括针对各个执行计划节点（SeqScan、HashJoin 等）、触发器以及并行 worker 的子 span。它为 PostgreSQL 提供了分布式追踪能力。

这些扩展关注的问题并不相同：pg_tracing 用来回答“为什么这条具体查询变慢了？”（是 HashJoin 的问题？IndexScan？还是某个触发器？），而 pg_stat_ch 回答的是“哪些查询变慢了？它们是否呈现出时间趋势？”

同样，你完全可以同时使用两者：用 pg_stat_ch 做全局级别的可观测与告警，当它提示某些指标异常时，再用 pg_tracing 深入分析具体的慢查询。

OpenTelemetry Collector 

OTel Collector 的 PostgreSQL Receiver 是一个外部采集器，它会以可配置的时间间隔轮询 pg_stat_* 视图。

如果你已经构建了 OTel 数据管道，它可以很方便地接入，无需大规模改造。同时，它还能采集系统级指标（连接数、锁、复制延迟等），这些并不在 pg_stat_ch 的采集范围内。

不过，采用 10 秒轮询间隔的外部采集器，永远无法捕获单条查询的执行细节。它看到的只是预聚合计数器的增量变化。如果你想知道“在 3:00:00 到 3:00:05 之间具体执行了哪些查询”，只有进程内扩展才能提供这样的答案。

接下来计划

我们正在积极推进以下工作：

• 查询计划捕获：将执行计划作为独立事件进行存储

• 采样支持：为超高吞吐场景提供采样机制，以降低开销

• ClickStack 与 Grafana 仪表盘模板：为常见使用场景提供预构建仪表盘

• 生产环境加固：压力测试、边界场景覆盖以及更多 PostgreSQL 版本支持

pg_stat_ch 还可以与 pg_clickhouse 无缝配合使用。pg_clickhouse 是我们提供的扩展，用于在 PostgreSQL 中直接查询 ClickHouse。你可以通过 pg_stat_ch 推送遥测数据，再通过外部表在 psql 中查询分析，全程无需离开 psql。

我们非常期待你的反馈。如果你在生产环境运行 PostgreSQL，并且关注可观测性，不妨试试 pg_stat_ch，告诉我们哪些功能好用，哪些还需要改进。欢迎加入我们的社区 Slack！

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com