PostgreSQL 多级依赖血缘系统的设计与落地

一、业务背景：三类指标与四种状态

约束

1. 指标不能重复（通过唯一键 (name, version) 保证）。

2. 已发布/已落库 的指标不允许任何变更；一旦变更，必须级联同步所有下游。

二、存储模型：全量 vs 依赖 ID 的抉择

最终采用“只存依赖 ID + 运行时动态拼装 SQL”的方案。

三、表结构：指标 & 血缘两张核心表

1）指标主表 t_indicator

CREATE TABLE t_indicator (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    version     INT  NOT NULL,
    type        VARCHAR(16) CHECK (type IN ('ATOMIC','DERIVED','COMPOSITE')),
    status      VARCHAR(16) CHECK (status IN ('SAVED','PUBLISHED','LOADED')),
    definition  JSONB,          -- 原子：SQL片段；派生/复合：依赖列表+运算
    created_at  TIMESTAMPTZ DEFAULT now(),
    UNIQUE(name, version)
);

2）血缘关系表 t_indicator_dependency

CREATE TABLE t_indicator_dependency (
    id           BIGSERIAL PRIMARY KEY,
    indicator_id BIGINT NOT NULL REFERENCES t_indicator(id),
    parent_id    BIGINT NOT NULL REFERENCES t_indicator(id),
    depth        INT    NOT NULL,          -- 当前节点到父节点的深度
    UNIQUE(indicator_id, parent_id)
);

-- 常用索引
CREATE INDEX idx_dep_parent ON t_indicator_dependency(parent_id);
CREATE INDEX idx_dep_child  ON t_indicator_dependency(indicator_id);

四、多级依赖查询：四种实战方案

方案 A：PostgreSQL 递归 CTE（开发阶段首选）

-- 查询节点 100 的所有下游（包括多级）
WITH RECURSIVE down AS (
    SELECT indicator_id, 1 AS lvl
    FROM t_indicator_dependency
    WHERE parent_id = 100
    UNION ALL
    SELECT d.indicator_id, lvl + 1
    FROM t_indicator_dependency d
    JOIN down ON d.parent_id = down.indicator_id
)
SELECT * FROM down;

如何快速查询全线依赖链（A ← B ← C ← D ← E）

🔍 场景1：查某个指标的所有上游依赖（如A依赖了谁）

-- 查询A的所有上游依赖（包括多级）
WITH RECURSIVE upstream AS (
    SELECT parent_id, 1 AS level
    FROM indicator_dependency
    WHERE indicator_id = ? -- A的ID
    UNION ALL
    SELECT d.parent_id, u.level + 1
    FROM indicator_dependency d
    JOIN upstream u ON d.indicator_id = u.parent_id
)
SELECT * FROM upstream;

🔍 场景2：查某个指标的所有下游影响（如E被谁依赖）

-- 查询E的所有下游影响（包括多级）
WITH RECURSIVE downstream AS (
    SELECT indicator_id, 1 AS level
    FROM indicator_dependency
    WHERE parent_id = ? -- E的ID
    UNION ALL
    SELECT d.indicator_id, dw.level + 1
    FROM indicator_dependency d
    JOIN downstream dw ON d.parent_id = dw.indicator_id
)
SELECT * FROM downstream;

方案 B：封装成函数，一行调用

CREATE OR REPLACE FUNCTION f_get_rel(
    node      BIGINT,
    dir       INT DEFAULT 1,   -- 1 下游；-1 上游；0 双向
    max_depth INT DEFAULT 10
)
RETURNS TABLE(direction INT, depth INT, node_id BIGINT) AS $$
BEGIN
    IF dir = 1 OR dir = 0 THEN
        RETURN QUERY
        WITH RECURSIVE down AS (
            SELECT 1, 1, indicator_id
            FROM t_indicator_dependency WHERE parent_id = node
            UNION ALL
            SELECT 1, d.depth + 1, t.indicator_id
            FROM t_indicator_dependency t
            JOIN down d ON t.parent_id = d.node_id
            WHERE d.depth < max_depth
        ) SELECT * FROM down;
    END IF;
    IF dir = -1 OR dir = 0 THEN
        RETURN QUERY
        WITH RECURSIVE up AS (
            SELECT -1, 1, parent_id
            FROM t_indicator_dependency WHERE indicator_id = node
            UNION ALL
            SELECT -1, u.depth + 1, t.parent_id
            FROM t_indicator_dependency t
            JOIN up u ON t.indicator_id = u.node_id
            WHERE u.depth < max_depth
        ) SELECT * FROM up;
    END IF;
END;
$$ LANGUAGE plpgsql;

使用示例

SELECT * FROM f_get_rel(100, 1, 5);  -- 下游5层
SELECT * FROM f_get_rel(100, -1, 5); -- 上游5层

方案 C：图数据库 Neo4j（可视化 & 超深层级）

MATCH p = (a:Indicator {id: 100})<-[:DEPENDS_ON*]-(b)
RETURN nodes(p) AS chain;

方案 D：闭包表（写入重、查询快）

每次变更指标需批量刷新闭包表，适合“读多写极少”系统。

✅ 推荐实践

• 中小型系统：优先使用 WITH RECURSIVE，简洁够用。

• 中大型系统：考虑 闭包表 + 缓存，或引入 图数据库 做血缘分析。

• 实时性要求高：用 预构建路径表 或 缓存方案。

✅ 总结对比表

五、状态级联校验：防止“已发布”被改动

1）触发器：禁止直接 UPDATE 已发布/已落库指标

CREATE OR REPLACE FUNCTION f_block_if_published()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM t_indicator WHERE id = NEW.id
        AND status IN ('PUBLISHED','LOADED')
    ) THEN
        RAISE EXCEPTION 'Cannot modify published/loaded indicator %', NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_block_update
BEFORE UPDATE ON t_indicator
FOR EACH ROW EXECUTE FUNCTION f_block_if_published();

2）级联校验：新增派生/复合指标时检查所有父级状态

public void validateParents(List<Long> parentIds) {
    String sql = """
        SELECT id FROM t_indicator
        WHERE id = ANY(?) AND status NOT IN ('PUBLISHED','LOADED')
    """;
    List<Long> bad = jdbcTemplate.queryForList(sql, Long.class, parentIds);
    if (!bad.isEmpty()) {
        throw new BizException("存在未发布父级: " + bad);
    }
}

六、线上性能优化三板斧

1. 缓存：将 f_get_rel() 结果写入 Redis，Key=rel:{id}:{dir}:{depth}，TTL=5 min。

2. 预编译：对常用 SQL 模板（原子、派生、复合）提前生成 PreparedStatement。

3. 异步刷新：指标发布后，通过 MQ 触发“血缘刷新”任务，预热缓存。

七、总结

八、提问：在面对可能存在多级依赖的场景下，针对新建的派生指标，复合指标，指标的定义是保存成全量数据【把父级关系维护一份全量再加上自己的定义整合在一起保存】合适还是只保存父级之间的依赖关系【例如父级id】？

✅ 推荐方案：只保存依赖关系（父级ID）

1. 避免数据冗余与一致性问题

• 如果每个派生或复合指标都保存全量定义（包括父级SQL、维度、过滤条件等），一旦父级发生变更（如下线、修订），子级将无法自动同步，导致逻辑漂移。

• 只保存ID可确保子级始终引用父级的最新定义，通过运行时动态拼装SQL或逻辑，天然支持依赖链同步。

2. 满足“已发布/已落库不可改”的强约束

• 全量保存模式下，若父级已发布，子级无法感知父级的“冻结”状态，可能导致子级基于过期定义构建，违反业务规则。

• 依赖ID模式下，子级创建时必须校验父级状态（如只允许引用已发布父级），系统可在元数据层面强制阻断非法引用。

3. 支持多级依赖的灵活扩展

• 复合指标可能依赖多个派生指标，派生指标又依赖原子指标，依赖链可能非常深。

• 全量保存会导致存储膨胀（如一个复合指标需冗余存储整个依赖树的定义），而ID模式只需记录有向无环图（DAG）的边关系，存储轻量且易于维护。

4. 版本控制与回滚更简单

• 依赖ID模式下，每个指标定义可独立版本化（如使用Git-style的版本号），子级通过ID+版本号锁定父级快照，支持精确回滚。

• 全量保存模式下，任何父级字段的微调都会级联影响所有子级版本，回滚复杂度呈指数级增长。

⚠️ 需注意的配套设计

• 运行时解析性能：依赖ID模式需在查询时动态拼装SQL/逻辑，可能引入延迟。可通过预编译缓存（如Flink SQL的物化视图、Presto的查询缓存）优化。

• 循环依赖检测：需在建模时通过DAG校验禁止环形引用（如A→B→A）。

• 父级下线影响：若父级被下线，需级联校验所有子级状态（如提示“依赖指标已下线，请重新编辑”）。

📌 结论

在强依赖、强状态控制、多级衍生的场景下，“只保存依赖关系（父级ID）”是更可持续的设计，配合版本化、状态校验和缓存机制，可兼顾灵活性、一致性与性能。

✅循环检测
如果 indicator_dependency 里没有循环引用（即不存在 A→B→A 这种环），查询会正常结束；
如果可能成环，建议加上防环条件，例如

WITH RECURSIVE upstream AS (
    SELECT parent_id, 1 AS level,
           ARRAY[indicator_id] AS path   -- 记录已走过的节点
    FROM indicator_dependency
    WHERE indicator_id = ?               -- A的ID

    UNION ALL
    SELECT d.parent_id, u.level + 1,
           u.path || d.indicator_id
    FROM indicator_dependency d
    JOIN upstream u ON d.indicator_id = u.parent_id
    WHERE NOT d.parent_id = ANY(u.path)  -- 避免环
)
SELECT * FROM upstream;

1. 返回列完整性
   目前只返回了 parent_id 和 level，如果后续还要用到 indicator_id 或其它字段，最好在 CTE 里一并携带。

• 生产环境若存在成环风险，务必加防环条件。