ltree dans PostgreSQL avec JPA : stockage d'arbres

Stocker des données hiérarchiques dans PostgreSQL avec ltree et JPA pour les microservices

Les développeurs ont souvent besoin de stocker des données hiérarchiques : structures organisationnelles, catalogues produits, généalogies. PostgreSQL propose ltree — un type spécialisé pour les chemins matérialisés qui simplifie les opérations sur les arbres. Cet article explore la combinaison de ltree avec des listes d'adjacence dans une seule table et son intégration avec JPA dans une architecture microservices.

Les approches clés pour stocker des arbres incluent les listes d'adjacence (références parentes) et les chemins matérialisés (chemins complets depuis la racine). ltree implémente cette dernière avec des séparateurs par points et fournit des index GIST, des opérateurs comme @> (contient), <@ (contenu par), et des fonctions telles que nlevel().

Structure de table et entité

La table combine les deux approches :

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

Entité JPA avec un type personnalisé pour 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;
}

Fonctions et prédicats personnalisés

Enregistrement d'une fonction pour les vérifications d'ancêtres dans un dialecte 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();
    }
}

Prédicat pour les ancêtres :

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

Méthodes de repository

Repository avec requêtes natives et support de 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);
}

Comparaison de performances

Tests sur 1 000 nœuds aléatoires ont montré :

• SQL récursif (liste d'adjacence) : 1118 ms
• Opérateur ltree @> : 820 ms
• Specification JPA : 1083 ms

ltree l'emporte grâce aux index GIST et aux opérateurs optimisés. Specification nécessite de récupérer d'abord le chemin, ajoutant une surcharge.

Code de test :

@Test
@DisplayName("Testing performance of retrieving 'ancestors' using different methods")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... génération de 1000 IDs
    // Mesure du temps pour chaque méthode
}

Application dans les microservices

Dans un système distribué, la structure est stockée dans un service organisation, tandis que les actualités sont dans un service de contenu. Un utilisateur demande des actualités de son département et des niveaux supérieurs.

Options :

• Service d'actualités sans chemin : Requêtes multiples au service de structure pour chaque actualité.
• Avec chemin dans le service d'actualités : Une requête pour le chemin de l'utilisateur, filtrage avec @> côté base de données.

Avantages de ltree :

• Réduit les appels réseau entre services.
• Filtrage hiérarchique rapide.
• La dénormalisation du chemin minimise le trafic.

Points clés à retenir

• ltree se combine avec les listes d'adjacence dans une table sans conflits.
• L'index GIST sur le chemin garantit les performances pour les opérations @>, <@.
• Dans les microservices, dupliquer le chemin réduit les appels inter-services.
• Les CTE récursifs sont plus lents que ltree sur les grands arbres.
• JPA nécessite un Type et un Dialect personnalisés pour ltree.

— Editorial Team