PostgreSQL 与 JPA 中的 ltree：存储树

在微服务架构中使用 PostgreSQL ltree 与 JPA 存储层次化数据

开发者常需存储层次化数据：组织结构、产品目录、族谱等。PostgreSQL 提供了 ltree——一种专门用于物化路径的数据类型，可简化树形操作。本文探讨如何将 ltree 与邻接表结合于单表中，并在微服务架构中与 JPA 集成。

存储树形数据的关键方法包括邻接表（父节点引用）和物化路径（从根节点开始的完整路径）。ltree 通过点分隔符实现后者，并提供 GIST 索引、@>（包含）、<@（被包含）等运算符，以及 nlevel() 等函数。

表结构与实体

该表结合了两种方法：

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE nodes (
    id        bigint     NOT NULL,
    parent_id bigint,
    code      varchar(6) NOT NULL,
    path      ltree      NOT NULL,
    depth     integer    NOT NULL GENERATED ALWAYS AS (nlevel(path)) STORED,
    CONSTRAINT nodes_pk PRIMARY KEY (id),
    CONSTRAINT nodes_parent_id_fk FOREIGN KEY (parent_id) REFERENCES nodes (id),
    CONSTRAINT nodes_code_unq UNIQUE (code)
);
CREATE INDEX nodes_parent_id_idx ON nodes (parent_id);
CREATE INDEX nodes_path_idx ON nodes USING GIST (path);

JPA 实体，包含 ltree 的自定义类型：

@Accessors(chain = true)
@Getter
@Setter
@Entity
@Table(name = "nodes")
public class Node {
    @Id
    @Column(name = "id", nullable = false, updatable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private Node parent;

    @Size(max = 6)
    @NotBlank
    @Column(name = "code", nullable = false, length = 6, updatable = false)
    private String code;

    @Type(PostgreSQLLTreeType.class)
    @NotBlank
    @Column(name = "path", columnDefinition = "ltree")
    private String path;

    @Generated(event = {EventType.INSERT, EventType.UPDATE})
    @Column(name = "depth", insertable = false, updatable = false)
    private Integer depth;
}

自定义函数与谓词

在 PostgreSQL 方言中注册用于祖先检查的函数：

public class PGDialect extends PostgresPlusDialect {
    public static final String LTREE_ANCESTORS = "ancestors";

    @Override
    public void initializeFunctionRegistry(FunctionContributions functionContributions) {
        super.initializeFunctionRegistry(functionContributions);
        SqmFunctionRegistry functionRegistry = functionContributions.getFunctionRegistry();
        BasicTypeRegistry basicTypeRegistry = functionContributions.getTypeConfiguration().getBasicTypeRegistry();
        BasicType<Boolean> booleanType = basicTypeRegistry.resolve(StandardBasicTypes.BOOLEAN);

        functionRegistry.patternDescriptorBuilder(
                LTREE_ANCESTORS,
                "(?1::ltree @> ?2::ltree)"
        )
        .setExactArgumentCount(2)
        .setArgumentTypeResolver(StandardFunctionArgumentTypeResolvers.ARGUMENT_OR_IMPLIED_RESULT_TYPE)
        .setInvariantType(booleanType)
        .register();
    }
}

祖先谓词：

public class NodePredicateUtils {
    public static Predicate ancestors(From<?, Node> from, CriteriaBuilder builder, String path) {
        return builder.isTrue(
                builder.function(LTREE_ANCESTORS, Boolean.class, from.get("path"), builder.literal(path))
        );
    }
}

仓库方法

支持原生查询和 Specification 的仓库：

public interface NodeRepository extends JpaRepository<Node, Long>, JpaSpecificationExecutor<Node> {

    @Query(nativeQuery = true, value = """
            WITH RECURSIVE tree AS (
                SELECT n.* FROM nodes n WHERE n.id = :nodeId
                UNION ALL
                SELECT n.* FROM nodes n, tree WHERE n.id = tree.parent_id
            )
            SELECT * FROM tree
            """)
    Collection<Node> getAncestors(Long nodeId);

    @Query(nativeQuery = true, value = "select * from nodes n " +
            "where n.path @> (select path from nodes where id = :nodeId)")
    Collection<Node> getAncestorsByPath(Long nodeId);
}

性能对比

在 1,000 个随机节点上的测试显示：

• 递归 SQL（邻接表）：1118 毫秒
• ltree @> 运算符：820 毫秒
• JPA Specification：1083 毫秒

ltree 凭借 GIST 索引和优化运算符胜出。Specification 需要先获取路径，增加了开销。

测试代码：

@Test
@DisplayName("测试使用不同方法检索‘祖先’的性能")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... 生成 1000 个 ID
    // 测量每种方法的时间
}

在微服务中的应用

在分布式系统中，结构存储在组织服务中，而新闻存储在内容服务中。用户请求其部门及更高层级的新闻。

选项：

• 新闻服务无路径：为每条新闻向结构服务发起多次查询。
• 新闻服务包含路径：一次查询获取用户路径，在数据库端使用 @> 过滤。

ltree 的优势：

• 减少服务间的网络调用。
• 快速层次化过滤。
• 路径反规范化最小化流量。

关键要点

• ltree 可与邻接表结合于单表中，无冲突。
• 路径上的 GIST 索引确保 @>、<@ 操作的性能。
• 在微服务中，复制路径可减少服务间调用。
• 递归 CTE 在大树上比 ltree 慢。
• JPA 需要自定义 Type 和 Dialect 以支持 ltree。

— Editorial Team