ltree w PostgreSQL z JPA: przechowywanie drzew

Przechowywanie danych hierarchicznych w PostgreSQL z użyciem ltree i JPA dla architektury mikroserwisowej

Programiści często potrzebują przechowywać dane hierarchiczne: struktury organizacyjne, katalogi produktów, drzewa genealogiczne. PostgreSQL oferuje ltree — wyspecjalizowany typ danych dla materialized path, który upraszcza operacje na drzewach. W artykule omówiono połączenie ltree z adjacency list w jednej tabeli oraz integrację z JPA w architekturze mikroserwisowej.

Główne podejścia do przechowywania drzew obejmują adjacency list (odwołanie do rodzica) i materialized path (pełna ścieżka od korzenia). ltree implementuje drugie podejście z separatorem kropkowym i dostarcza indeksy GIST, operatory @> (zawiera), <@ (zawarty w) oraz funkcje takie jak nlevel().

Struktura tabeli i encji

Tabela łączy oba podejścia:

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

Encja JPA z niestandardowym typem dla 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;
}

Niestandardowe funkcje i predykaty

Rejestracja funkcji do sprawdzania przodków w dialekcie 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();
    }
}

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

Metody repozytorium

Repozytorium z natywnymi zapytaniami i wsparciem dla 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);
}

Porównanie wydajności

Testowanie na 1000 losowych węzłach wykazało:

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

ltree wygrywa dzięki indeksowi GIST i zoptymalizowanym operatorom. Specification wymaga wcześniejszego pobrania path, co dodaje narzut.

Kod testowy:

@Test
@DisplayName("Sprawdzamy wydajność pobierania 'przodków' dla różnych metod")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... generacja 1000 ID
    // Pomiar czasu dla każdej metody
}

Zastosowanie w mikroserwisach

W systemie rozproszonym struktura jest przechowywana w serwisie organizacji, a wiadomości — w serwisie treści. Użytkownik żąda wiadomości swojego działu i jednostek nadrzędnych.

Opcje:

• Serwis wiadomości bez path: Wielokrotne zapytania do serwisu struktury dla każdego news item.
• Z path w serwisie wiadomości: Jedno zapytanie o path użytkownika, filtrowanie @> po stronie bazy danych.

Zalety ltree:

• Zmniejszenie liczby wywołań sieciowych między serwisami.
• Szybkie filtrowanie hierarchii.
• Denormalizacja path minimalizuje ruch sieciowy.

Co jest ważne

• ltree współpracuje z adjacency list w jednej tabeli bez konfliktów.
• Indeks GIST na path zapewnia wydajność operacji @>, <@.
• W mikroserwisach duplikowanie path redukuje wywołania między serwisami.
• Rekurencyjny CTE ustępuje ltree pod względem szybkości na dużych drzewach.
• JPA wymaga niestandardowego Type i Dialect dla ltree.

— Editorial Team