ClickHouse物化视图竞态风险：PostgreSQL CDC事务同步关联表问询

场景背景 #

PostgreSQL源端配置 #

两张关联业务表及CDC发布订阅配置：

-- 订单表
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID,
  amount NUMERIC,
  created_at TIMESTAMP DEFAULT now()
);

-- 支付表
CREATE TABLE payments (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id),
  payment_method TEXT,
  status TEXT,
  paid_at TIMESTAMP DEFAULT now()
);

-- 创建CDC发布，包含两张表
CREATE PUBLICATION my_pub FOR TABLE orders, payments;

ClickHouse消费端配置 #

通过Kafka消费PostgreSQL CDC流，创建物化视图实现订单与支付数据的实时关联：

CREATE MATERIALIZED VIEW orders_mv TO enriched_orders AS
SELECT
  o.id AS order_id,
  o.customer_id,
  o.amount,
  p.payment_method,
  p.status
FROM orders AS o
LEFT JOIN payments AS p ON o.id = p.order_id;

核心问题 #

同一PostgreSQL事务中插入的orders和payments记录，其CDC消息是否会出现物化视图处理orders记录时，对应的payments记录还未写入ClickHouse的情况？是否会引发竞态条件，导致关联结果不完整？

问题答案及解决方案 #

会出现竞态条件及不完整关联 #

原因如下：

1. PostgreSQL原生publication发送CDC消息的顺序不严格保证事务内操作的执行顺序，消息可能乱序到达Kafka。
2. ClickHouse消费Kafka时，若消息分布在不同分区，消费顺序无法保证；即使在同一分区，也可能因消费延迟导致orders先被写入并触发物化视图，而payments记录尚未落库。
3. 物化视图基于orders的插入事件触发计算，一旦生成关联结果写入enriched_orders，后续payments记录到达时不会自动更新已生成的结果，最终导致部分订单的支付信息缺失。

最佳实践 #

• 延迟关联，避免实时物化视图JOIN
  先将orders和payments的CDC数据分别写入ClickHouse的基础表（如MergeTree引擎），再通过定时刷新的物化视图执行关联：

  -- 定时刷新的物化视图，每5分钟执行一次关联
  CREATE MATERIALIZED VIEW enriched_orders_mv TO enriched_orders AS
  SELECT
    o.id AS order_id,
    o.customer_id,
    o.amount,
    p.payment_method,
    p.status
  FROM orders AS o
  LEFT JOIN payments AS p ON o.id = p.order_id
  REFRESH EVERY 5 MINUTE;
  

  这种方式能确保关联时，同一事务的订单和支付数据已全部落库。

• 借助CDC工具实现事务级消息打包
  使用Debezium等第三方CDC工具替代PostgreSQL原生publication，配置将同一事务内的多表操作打包为单条消息。在ClickHouse中解析该消息时，一次性插入orders和payments记录，再触发关联逻辑，从根源上避免消息乱序。

• 基于事务ID分组等待
  在PostgreSQL表中新增事务ID字段（可通过txid_current()获取当前事务ID），CDC消息携带该字段。在ClickHouse中通过窗口函数或状态表，等待同一事务ID的所有表记录都到达后，再执行关联操作。

• 查询时实时JOIN
  如果业务对实时性要求不高，直接在查询时执行JOIN而非预物化：

  SELECT
    o.id AS order_id,
    o.customer_id,
    o.amount,
    p.payment_method,
    p.status
  FROM orders AS o
  LEFT JOIN payments AS p ON o.id = p.order_id;
  

  每次查询都能获取最新的完整关联数据，无需担心数据延迟问题。

内容的提问来源于stack exchange，提问作者jasraj bedi