ltree v PostgreSQL s JPA: ukládání stromů

Ukládání hierarchických dat v PostgreSQL pomocí ltree a JPA pro mikroslužby

Vývojáři často potřebují ukládat hierarchická data: organizační struktury, katalogy produktů, rodokmeny. PostgreSQL nabízí ltree – specializovaný typ pro materializovanou cestu, který zjednodušuje operace se stromy. V článku je popsána kombinace ltree s adjacency list v jedné tabulce a integrace s JPA v architektuře mikroslužeb.

Hlavní přístupy k ukládání stromů zahrnují adjacency list (odkaz na rodiče) a materializovanou cestu (celá cesta od kořene). ltree implementuje druhý přístup s tečkovým oddělovačem a poskytuje GIST-indexy, operátory @> (obsahuje), <@ (je obsažen) a funkce jako nlevel().

Struktura tabulky a entity

Tabulka kombinuje oba přístupy:

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 entita s vlastním typem pro 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;
}

Vlastní funkce a predikáty

Registrace funkce pro kontrolu předků v dialektu 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();
    }
}

Predikát pro předky:

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 repozitáře

Repozitář s nativními dotazy a podporou 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);
}

Porovnání výkonnosti

Testování na 1000 náhodných uzlech ukázalo:

• Rekurzivní SQL (adjacency list): 1118 ms
• ltree @> operátor: 820 ms
• JPA Specification: 1083 ms

ltree vítězí díky GIST-indexu a optimalizovaným operátorům. Specification vyžaduje předchozí získání path, což přidává režii.

Testovací kód:

@Test
@DisplayName("Kontrolujeme výkonnost získávání 'předků' pro různé způsoby")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... generování 1000 ID
    // Měření času pro každou metodu
}

Použití v mikroslužbách

V distribuovaném systému je struktura uložena ve službě organizace, novinky – ve službě obsahu. Uživatel žádá o novinky svého oddělení a nadřízených.

Možnosti:

• Služba novinek bez path: Více dotazů na službu struktury pro každou položku novinek.
• S path ve službě novinek: Jeden dotaz na path uživatele, filtrování @> na straně databáze.

Výhody ltree:

• Snížení síťových volání mezi službami.
• Rychlé filtrování hierarchií.
• Denormalizace path minimalizuje provoz.

Co je důležité

• ltree se kombinuje s adjacency list v jedné tabulce bez konfliktů.
• GIST-index na path zajišťuje výkonnost operací @>, <@.
• V mikroslužbách duplikace path snižuje volání mezi službami.
• Rekurzivní CTE zaostává za ltree v rychlosti na velkých stromech.
• JPA vyžaduje vlastní Type a Dialect pro ltree.

— Editorial Team