ltree in PostgreSQL with JPA: storing trees

Storing Hierarchical Data in PostgreSQL with ltree and JPA for Microservices

Developers often need to store hierarchical data: organizational structures, product catalogs, genealogies. PostgreSQL offers ltree—a specialized type for materialized paths that simplifies tree operations. This article explores combining ltree with adjacency lists in a single table and integrating it with JPA in a microservices architecture.

Key approaches to storing trees include adjacency lists (parent references) and materialized paths (full paths from the root). ltree implements the latter with dot separators and provides GIST indexes, operators like @> (contains), <@ (contained by), and functions such as nlevel().

Table Structure and Entity

The table combines both approaches:

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 entity with a custom type for 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;
}

Custom Functions and Predicates

Registering a function for ancestor checks in a PostgreSQL dialect:

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();
    }
}

Predicate for ancestors:

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))
        );
    }
}

Repository Methods

Repository with native queries and Specification support:

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);
}

Performance Comparison

Testing on 1,000 random nodes showed:

• Recursive SQL (adjacency list): 1118 ms
• ltree @> operator: 820 ms
• JPA Specification: 1083 ms

ltree wins due to GIST indexes and optimized operators. Specification requires fetching the path first, adding overhead.

Test code:

@Test
@DisplayName("Testing performance of retrieving 'ancestors' using different methods")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... generating 1000 IDs
    // Measuring time for each method
}

Application in Microservices

In a distributed system, the structure is stored in an organization service, while news is in a content service. A user requests news from their department and higher levels.

Options:

• News service without path: Multiple queries to the structure service for each news item.
• With path in the news service: One query for the user's path, filtering with @> on the database side.

Advantages of ltree:

• Reduces network calls between services.
• Fast hierarchical filtering.
• Denormalizing the path minimizes traffic.

Key Takeaways

• ltree combines with adjacency lists in one table without conflicts.
• GIST index on path ensures performance for @>, <@ operations.
• In microservices, duplicating the path reduces inter-service calls.
• Recursive CTEs are slower than ltree on large trees.
• JPA requires custom Type and Dialect for ltree.

— Editorial Team