Postgres中共享子树存储方案咨询：替代ltree的最优实现

你需要在Postgres中存储用户树形数据，支持任意子树共享，且原树变更需同步给所有共享用户。ltree的全局路径特性导致需要复制节点来适配不同用户的视图，同步成本高，因此需要更合适的方案。

快速分析常见树形存储方式的适配性：

• 邻接表：存储每个节点的父ID，结构简单，但查询子树/路径需要递归，性能随树深度下降，权限控制需额外关联表，仅适合小规模树。
• 嵌套集：用左右值表示节点范围，查询子树快，但插入/删除节点时需更新大量节点的左右值，不适合频繁变更场景，共享权限适配复杂。
• 闭包表：存储所有节点的祖先-后代关系，查询子树、路径、层级都高效，且天然支持权限与共享场景，是解决你问题的最优选择。

通过三张核心表实现，无需复制节点，原树变更自动同步给共享用户，不同用户的子树路径动态生成。

1. 核心表结构 #

用户表（基础依赖） #

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

节点表（存储原始节点数据） #

CREATE TABLE nodes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT REFERENCES nodes(id) ON DELETE CASCADE, -- 根节点parent_id为NULL
    owner_id INT REFERENCES users(id) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

闭包表（维护原始树的祖先-后代关系） #

CREATE TABLE node_closure (
    ancestor_id INT REFERENCES nodes(id) ON DELETE CASCADE,
    descendant_id INT REFERENCES nodes(id) ON DELETE CASCADE,
    depth INT NOT NULL, -- 祖先到后代的层级差
    PRIMARY KEY (ancestor_id, descendant_id)
);

闭包表记录所有节点对的祖先-后代关系（包括节点自身），比如根节点A的闭包记录是(A,A,0)，子节点B的记录是(A,B,1)、(B,B,0)，以此类推。

共享权限表（记录子树共享关系） #

CREATE TABLE node_shares (
    id SERIAL PRIMARY KEY,
    shared_by_id INT REFERENCES users(id) NOT NULL,
    shared_to_id INT REFERENCES users(id) NOT NULL,
    root_node_id INT REFERENCES nodes(id) ON DELETE CASCADE NOT NULL, -- 共享的子树根节点
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(shared_to_id, root_node_id) -- 避免同一用户重复接收同一子树的共享
);

2. 维护闭包表的触发器 #

为自动维护闭包表关系，添加触发器处理节点增删：

插入节点时更新闭包表 #

CREATE OR REPLACE FUNCTION add_node_closure()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入节点自身的祖先-后代关系
    INSERT INTO node_closure (ancestor_id, descendant_id, depth)
    VALUES (NEW.id, NEW.id, 0);

    -- 插入所有父节点的祖先与新节点的关系
    IF NEW.parent_id IS NOT NULL THEN
        INSERT INTO node_closure (ancestor_id, descendant_id, depth)
        SELECT ancestor_id, NEW.id, depth + 1
        FROM node_closure
        WHERE descendant_id = NEW.parent_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_add_node_closure
AFTER INSERT ON nodes
FOR EACH ROW
EXECUTE FUNCTION add_node_closure();

删除节点的关联处理 #

由于闭包表设置了ON DELETE CASCADE，当节点被删除时，对应的所有祖先-后代记录会自动删除，无需额外触发器。

3. 查询用户可见的树形结构（含动态路径） #

以用户Z为例，查询其可见的所有节点及对应路径（基于共享根节点）：

WITH RECURSIVE user_accessible_nodes AS (
    -- 用户自己创建的所有节点
    SELECT
        n.id,
        n.name,
        n.id AS view_root_id, -- 自己的节点以自身为视图根
        0 AS level
    FROM nodes n
    WHERE n.owner_id = (SELECT id FROM users WHERE username = 'Z')

    UNION ALL

    -- 共享给用户的子树节点
    SELECT
        n.id,
        n.name,
        s.root_node_id AS view_root_id,
        nc.depth AS level
    FROM node_shares s
    JOIN node_closure nc ON nc.ancestor_id = s.root_node_id
    JOIN nodes n ON nc.descendant_id = n.id
    WHERE s.shared_to_id = (SELECT id FROM users WHERE username = 'Z')
),
node_view_paths AS (
    -- 生成每个节点在用户视图中的路径
    SELECT
        uan.id,
        uan.name,
        uan.view_root_id,
        array_to_string(array_agg(n.name ORDER BY nc.depth DESC), ' -> ') AS path
    FROM user_accessible_nodes uan
    JOIN node_closure nc ON nc.descendant_id = uan.id
    JOIN nodes n ON nc.ancestor_id = n.id
    -- 仅保留从用户视图根节点到当前节点的路径节点
    WHERE nc.ancestor_id IN (SELECT view_root_id FROM user_accessible_nodes)
    GROUP BY uan.id, uan.name, uan.view_root_id
)
SELECT id, name, path
FROM node_view_paths
ORDER BY view_root_id, level;

该查询会返回用户Z可见的所有节点，路径基于其共享的根节点（比如B -> C -> D），而非原始树的全局路径。

4. 方案优势 #

• 无节点复制：所有节点仅存储一份，共享仅通过权限表记录根节点，避免同步成本。
• 自动同步变更：原始树的节点修改、增删会直接反映在闭包表中，共享用户查询时自动获取最新数据。
• 灵活的权限控制：通过共享表可轻松添加/移除子树共享，支持多用户共享同一子树。
• 高效查询：闭包表的结构让子树查询、路径生成的性能远优于递归邻接表。

内容的提问来源于stack exchange，提问作者Chris Gonzalez