ltree en PostgreSQL con JPA: almacenando árboles

Almacenamiento de Datos Jerárquicos en PostgreSQL con ltree y JPA para Microservicios

Los desarrolladores a menudo necesitan almacenar datos jerárquicos: estructuras organizativas, catálogos de productos, genealogías. PostgreSQL ofrece ltree, un tipo especializado para rutas materializadas que simplifica las operaciones con árboles. Este artículo explora cómo combinar ltree con listas de adyacencia en una sola tabla e integrarlo con JPA en una arquitectura de microservicios.

Los enfoques clave para almacenar árboles incluyen listas de adyacencia (referencias al padre) y rutas materializadas (rutas completas desde la raíz). ltree implementa este último con separadores de punto y proporciona índices GIST, operadores como @> (contiene), <@ (contenido por) y funciones como nlevel().

Estructura de la Tabla y Entidad

La tabla combina ambos enfoques:

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

Entidad JPA con un tipo personalizado para 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;
}

Funciones y Predicados Personalizados

Registro de una función para comprobaciones de ancestros en un dialecto 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();
    }
}

Predicado para ancestros:

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étodos del Repositorio

Repositorio con consultas nativas y soporte para 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);
}

Comparación de Rendimiento

Pruebas con 1,000 nodos aleatorios mostraron:

• SQL recursivo (lista de adyacencia): 1118 ms
• Operador ltree @>: 820 ms
• Specification de JPA: 1083 ms

ltree gana debido a los índices GIST y operadores optimizados. Specification requiere obtener primero la ruta, añadiendo sobrecarga.

Código de prueba:

@Test
@DisplayName("Prueba de rendimiento para recuperar 'ancestros' usando diferentes métodos")
@Transactional(readOnly = true)
public void testPerformance() {
    // ... generando 1000 IDs
    // Midiendo el tiempo para cada método
}

Aplicación en Microservicios

En un sistema distribuido, la estructura se almacena en un servicio de organización, mientras que las noticias están en un servicio de contenido. Un usuario solicita noticias de su departamento y niveles superiores.

Opciones:

• Servicio de noticias sin ruta: Múltiples consultas al servicio de estructura para cada noticia.
• Con ruta en el servicio de noticias: Una consulta para la ruta del usuario, filtrando con @> en la base de datos.

Ventajas de ltree:

• Reduce las llamadas de red entre servicios.
• Filtrado jerárquico rápido.
• Desnormalizar la ruta minimiza el tráfico.

Conclusiones Clave

• ltree se combina con listas de adyacencia en una tabla sin conflictos.
• El índice GIST en path asegura rendimiento para operaciones @>, <@.
• En microservicios, duplicar la ruta reduce las llamadas entre servicios.
• CTEs recursivos son más lentos que ltree en árboles grandes.
• JPA requiere Type y Dialect personalizados para ltree.

— Editorial Team