PostgreSQL 및 Spring Data JPA를 사용한 계층적 데이터-java지도 시간-php.cn

나무를 심는 사람
희망을 심습니다.
나무를 심어요 by Lucy Larcom ?

소개

이 게시물에서는 트리 데이터 구조로 표현된 계층적 데이터를 관리하기 위한 몇 가지 옵션을 보여 드리겠습니다. 이는 다음과 같은 것을 구현해야 할 때 자연스러운 접근 방식입니다.

파일 시스템 경로
조직도
토론방 댓글
더 현대적인 주제: RAG 애플리케이션을 위한 small2big 검색

그래프가 무엇인지 이미 알고 있다면 트리는 기본적으로 사이클이 없는 그래프입니다. 이렇게 시각적으로 표현할 수 있습니다.

Hierarchical data with PostgreSQL and Spring Data JPA

관계형 데이터베이스에 트리를 저장하는 데는 여러 가지 대안이 있습니다. 아래 섹션에서는 그 중 세 가지를 보여드리겠습니다.

인접 목록
구체화된 경로
중첩 세트

이 블로그 게시물은 두 부분으로 구성됩니다. 첫 번째 항목에서는 대안이 소개되었으며 데이터를 로드하고 저장하는 방법(기본 사항)을 살펴봅니다. 이를 제쳐두고 두 번째 부분에서는 비교와 절충에 더 중점을 둡니다. 예를 들어 데이터 양이 증가하면 어떤 일이 발생하는지, 그리고 적절한 인덱싱 전략은 무엇인지 살펴보고 싶습니다.

아래 섹션에서 볼 수 있는 모든 코드는 관심이 있는 경우 여기에서 찾을 수 있습니다.

실행 중인 사용 사례는 직원과 해당 관리자의 사용 사례이며, 각 ID는 위에 표시된 트리 시각화에서 본 것과 정확히 같습니다.

지역 환경

최근 출시된 Postgres 17을 Testcontainers와 함께 사용하고 있습니다. 이를 통해 작업할 수 있는 반복 가능한 설정이 제공됩니다. 예를 들어, 초기화 SQL 스크립트를 사용하여 필요한 테이블이 포함된 Postgres 데이터베이스 생성을 자동화하고 일부 테스트 데이터로 채울 수 있습니다.

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

로그인 후 복사

첫 번째 접근 방식을 살펴보겠습니다.

1. 인접 목록 모델

이것은 계층적 데이터를 관리하기 위한 최초의 솔루션이므로 코드베이스에 여전히 광범위하게 존재하므로 언젠가 접할 가능성이 높습니다. 아이디어는 관리자의, 더 일반적으로 말하면 상위 ID를 같은 행에 저장한다는 것입니다. 테이블 구조를 보면 금방 알 수 있습니다.

개요

인접 목록 옵션에 해당하는 테이블은 다음과 같습니다.

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

로그인 후 복사

위의 내용 외에도 데이터 무결성을 보장하려면 최소한 다음을 확인하는 제약 조건 검사도 작성해야 합니다.

모든 노드에는 단일 상위 노드가 있습니다
사이클 없음

테스트 데이터 생성

특히 이 시리즈의 2부에서는 스키마를 채우기 위해 원하는 만큼의 데이터를 생성할 수 있는 방법이 필요합니다. 명확성을 위해 처음에는 단계별로 수행한 다음 나중에 재귀적으로 수행해 보겠습니다.

단계별로

계층 구조에 세 가지 수준의 직원을 명시적으로 삽입하는 것부터 간단하게 시작합니다.

Postgres의 CTE에 대해 이미 알고 계실 것입니다. 이는 기본 쿼리의 컨텍스트 내에서 실행되는 보조 명명 쿼리입니다. 아래에서는 이전 레벨을 기반으로 각 레벨을 구성하는 방법을 확인할 수 있습니다.

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

로그인 후 복사

지금까지 예상대로 작동하는지 확인하고 이를 위해 몇 개의 요소가 삽입되었는지 계산해 보겠습니다. 포스팅 초반에 보여드린 트리 시각화의 노드 개수와 비교해보시면 됩니다.

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

로그인 후 복사

괜찮아 보이네요! 세 가지 레벨로 총 15개의 노드를 얻습니다.

재귀적 접근 방식으로 넘어갈 시간입니다.

재귀적

재귀 쿼리 작성은 표준 절차를 따릅니다. 기본 단계와 재귀 단계를 정의한 다음 Union All을 사용하여 서로 "연결"합니다. 런타임 시 Postgres는 이 방법을 따르고 모든 결과를 생성합니다. 한번 보세요.

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

로그인 후 복사

실행 후, 같은 개수의 요소가 삽입되었는지 다시 계산해 보겠습니다.

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

로그인 후 복사

멋지네요! 우리는 사업 중입니다. 이제 원하는 만큼의 수준과 요소로 스키마를 채울 수 있으므로 삽입된 볼륨을 완전히 제어할 수 있습니다. 지금은 재귀 쿼리가 여전히 다소 어려워 보이더라도 걱정하지 마세요. 나중에 데이터를 검색하는 쿼리를 작성할 때 재귀 쿼리를 다시 살펴보겠습니다.

지금은 테이블을 Java 클래스에 매핑하는 데 사용할 수 있는 Hibernate 엔터티를 살펴보겠습니다.

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

로그인 후 복사

특별한 것은 없으며 관리자와 직원 간의 일대다 관계입니다. 당신은 이것이 오는 것을 보았습니다. 쿼리를 시작해 보겠습니다.

자손

과장의 모든 부하직원

ID로 참조되는 특정 관리자의 부하 직원을 모두 검색하기 위해 다시 재귀 쿼리를 작성합니다. 기본 단계와 기본 단계와 연결된 재귀 단계를 다시 볼 수 있습니다. 그런 다음 Postgres는 이를 반복하고 쿼리와 관련된 모든 행을 검색합니다. 예를 들어 ID = 2인 직원을 생각해 보겠습니다. 이것은 제가 방금 설명한 내용을 더 쉽게 이해할 수 있도록 시각적으로 표현한 것입니다. 모든 결과를 포함하지 않고 처음 몇 개만 포함했습니다.

Hierarchical data with PostgreSQL and Spring Data JPA

다음은 하위 항목을 쿼리하기 위한 JPQL 쿼리입니다.

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

로그인 후 복사

위와 같은 쿼리에서 쿼리를 더 깔끔하게 만들고 결과를 기록할 레코드의 정규화된 이름을 쓸 필요가 없도록 하기 위해 hypersistence-utils 라이브러리를 사용하여 ClassImportIntegratorProvider를 작성할 수 있습니다.

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

로그인 후 복사

생성된 쿼리 검토

작동하지만 Hibernate가 생성한 내용을 더 자세히 살펴보겠습니다. 내부적으로 무슨 일이 일어나고 있는지 이해하는 것은 항상 좋은 일입니다. 그렇지 않으면 모든 사용자 요청에서 비효율성이 발생할 수 있으며 이는 더욱 커질 것입니다.

다음 설정으로 Spring Boot 앱을 시작해야 합니다.

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

로그인 후 복사

자, 한번 살펴보겠습니다. 다음은 Hibernate에서 생성된 자손에 대한 쿼리입니다.

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

로그인 후 복사

흠- 예상보다 좀 복잡해 보이네요! 기본 단계와 기본 단계에 연결된 재귀 단계에 대해 앞서 보여드린 그림을 염두에 두고 조금 단순화할 수 있는지 살펴보겠습니다. 우리는 그 이상을 할 필요가 없습니다. 다음 사항에 대해 어떻게 생각하는지 확인해 보세요.

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

로그인 후 복사

훨씬 나아졌어요! 일부 불필요한 조인을 제거했습니다. 이렇게 하면 수행할 작업이 줄어들기 때문에 쿼리 속도가 더 빨라질 것으로 예상됩니다.

최종 결과

마지막 단계로 쿼리를 정리하고 Hibernate가 추가하는 테이블 이름을 사람이 더 읽기 쉬운 이름으로 바꾸겠습니다.

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

로그인 후 복사

자, 이제 어떻게 나무 위로 올라가는지 살펴보겠습니다.

부조

모든 관리자가 상위에 위치

먼저 ID = 14인 직원의 관리자를 가져오는 개념적 단계를 적어 보겠습니다.

Hierarchical data with PostgreSQL and Spring Data JPA

후손에 대한 것과 매우 유사해 보이지만 기본 단계와 재귀 단계 사이의 연결이 다릅니다.

JPQL 쿼리는 다음과 같이 작성할 수 있습니다.

@Entity
@Table(name = "employees")
@Getter
@Setter
public class Employee {
    @Id
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "manager_id")
    private Employee manager;

    @OneToMany(
            mappedBy = "parent",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Employee> employees = new ArrayList<>();
}

로그인 후 복사

그리고 그게 다입니다! 생성된 SQL 쿼리를 살펴봤지만 삭제할 수 있는 추가 명령을 찾을 수 없습니다. 2번으로 다가갈 시간입니다.

2. 구체화된 경로

ltree는 계층적 트리 구조를 구체화된 경로(트리 상단에서 시작)로 작업하는 데 사용할 수 있는 Postgres 확장입니다. 예를 들어, 리프 노드 8의 경로를 1.2.4.8로 기록하는 방법은 다음과 같습니다. 몇 가지 유용한 기능이 함께 제공됩니다. 테이블 열로 사용할 수 있습니다:

return entityManager.createQuery("""
 with employeeRoot as (
  select
    employee.employees employee
  from
    Employee employee
  where
    employee.id = :employeeId

  union all

  select
    employee.employees employee
  from
    Employee employee
  join
    employeeRoot root ON employee = root.employee
  order by
    employee.id
  )
  select 
    new Employee(
     root.employee.id
   )
  from 
  employeeRoot root
 """, Employee.class
)
 .setParameter("employeeId", employeeId)
 .getResultList();

로그인 후 복사

위 테이블을 테스트 데이터로 채우기 위해 제가 취한 접근 방식은 기본적으로 다음 SQL 명령을 사용하여 이전에 본 인접 목록에 사용된 테이블에서 생성된 데이터를 마이그레이션하는 것입니다. 이는 모든 단계에서 요소를 누산기로 수집하는 재귀 쿼리입니다.

public class ClassImportIntegratorProvider implements IntegratorProvider {
    @Override
    public List<Integrator> getIntegrators() {
        return List.of(
                new ClassImportIntegrator(
                        singletonList(
                                Employee.class
                        )
                )
        );
    }
}

로그인 후 복사

위 명령으로 생성된 항목은 다음과 같습니다.

@TestConfiguration(proxyBeanMethods = false)
class TestcontainersConfiguration {

    private static final String POSTGRES = "postgres";

    @Bean
    @ServiceConnection
    PostgreSQLContainer<?> postgresContainer() {
        return new PostgreSQLContainer<>(DockerImageName.parse("postgres:latest"))
                .withUsername(POSTGRES)
                .withPassword(POSTGRES)
                .withDatabaseName(POSTGRES)
                .withInitScript("init-script.sql");
    }
}

로그인 후 복사

계속해서 Hibernate 엔터티 작성을 진행할 수 있습니다. ltree 유형의 열을 매핑하기 위해 UserType을 구현했습니다. 그런 다음 @Type(LTreeType.class):
를 사용하여 경로 필드를 매핑할 수 있습니다.

create table employees
(
    id           bigserial primary key,
    manager_id   bigint references employees
    name         text,
);

로그인 후 복사

몇 가지 쿼리를 작성할 준비가 되었습니다. 기본 SQL에서는 다음과 같습니다.

with root as (
  insert into 
    employees(manager_id, name)
      select 
        null, 
        'root' || md5(random()::text) 
      from  
        generate_series(1, 1) g
      returning 
        employees.id
  ),
  first_level as (
    insert into 
      employees(manager_id, name)
        select 
          root.id, 
          'first_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          root
        returning 
          employees.id
  ),
  second_level as (
    insert into 
      employees(manager_id, name)
        select 
          first_level.id, 
          'second_level' || md5(random()::text) 
        from 
          generate_series(1, 2) g, 
          first_level
        returning 
          employees.id
  )
insert into 
  employees(manager_id, name)
select 
  second_level.id, 
  'third_level' || md5(random()::text) 
from 
  generate_series(1, 2) g, 
  second_level;

로그인 후 복사

하지만 JPQL로 쿼리를 작성해 보겠습니다. 이를 위해 먼저 사용자 정의 StandardSQLFunction을 작성해야 합니다. 이를 통해 Postgres 기본 연산자에 대한 대체를 정의할 수 있습니다.

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

로그인 후 복사

다음과 같이 FunctionContributor로 등록해야 합니다.

create temporary sequence employees_id_seq;
insert into employees (id, manager_id, name)
with recursive t(id, parent_id, level, name) AS
(
  select 
    nextval('employees_id_seq')::bigint,
    null::bigint, 
    1, 
    'root' from generate_series(1,1) g

    union all

    select 
      nextval('employees_id_seq')::bigint, 
      t.id, 
      level+1, 
      'level' || level || '-' || md5(random()::text) 
    from 
      t, 
      generate_series(1,2) g
    where 
      level < 4
)
select 
  id, 
  parent_id, 
  name 
from 
  t;
drop sequence employees_id_seq;

로그인 후 복사

마지막 단계는 META-INF/services 폴더에 org.hibernate.boot.model.FunctionContributor라는 리소스 파일을 생성하는 것입니다. 여기에 위 클래스의 정규화된 이름이 포함된 한 줄을 추가할 것입니다.

좋아요, 멋지네요! 마침내 다음 쿼리를 작성할 수 있게 되었습니다.

postgres=# select count(*) from employees;
 count 
-------
 15
(1 row)

로그인 후 복사

예를 들어, 다음과 같이 이 메소드를 호출하여 ID = 2를 포함하는 모든 경로를 검색할 수 있습니다.

@Entity
@Table(name = "employees")
@Getter
@Setter
public class Employee {
    @Id
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "manager_id")
    private Employee manager;

    @OneToMany(
            mappedBy = "parent",
            cascade = CascadeType.ALL,
            orphanRemoval = true
    )
    private List<Employee> employees = new ArrayList<>();
}

로그인 후 복사

Postgres는 ltree 작업을 위한 다양한 기능 세트를 제공합니다. 공식 문서 페이지에서 찾을 수 있습니다. 유용한 치트시트도 있습니다.

데이터 일관성을 보장하려면 스키마에 제약 조건을 추가하는 것이 중요합니다. 이 주제에 대해 제가 찾은 좋은 리소스는 다음과 같습니다.

3. 중첩 세트

가장 이해하기 쉬운 것은 직관을 보여주는 이미지입니다. 트리의 모든 노드에는 해당 ID 외에 추가 "왼쪽" 및 "오른쪽" 열이 있습니다. 규칙은 모든 자식이 부모의 왼쪽 값과 오른쪽 값 사이에 왼쪽과 오른쪽을 갖는다는 것입니다.

Hierarchical data with PostgreSQL and Spring Data JPA

위의 트리를 표현하는 테이블 구조는 다음과 같습니다.

return entityManager.createQuery("""
 with employeeRoot as (
  select
    employee.employees employee
  from
    Employee employee
  where
    employee.id = :employeeId

  union all

  select
    employee.employees employee
  from
    Employee employee
  join
    employeeRoot root ON employee = root.employee
  order by
    employee.id
  )
  select 
    new Employee(
     root.employee.id
   )
  from 
  employeeRoot root
 """, Employee.class
)
 .setParameter("employeeId", employeeId)
 .getResultList();

로그인 후 복사

테이블을 채우기 위해 Joe Celko의 "SQL for smarties" 책에 있는 스크립트를 Postgres 구문으로 변환했습니다. 여기 있습니다:

public class ClassImportIntegratorProvider implements IntegratorProvider {
    @Override
    public List<Integrator> getIntegrators() {
        return List.of(
                new ClassImportIntegrator(
                        singletonList(
                                Employee.class
                        )
                )
        );
    }
}

로그인 후 복사

알겠습니다. 몇 가지 질문을 할 준비가 되었습니다. 조상을 구하는 방법은 다음과 같습니다.

@DynamicPropertySource
static void registerPgProperties(DynamicPropertyRegistry registry) {
    registry.add("spring.jpa.show_sql", () -> true);
}

로그인 후 복사

후손의 경우 먼저 왼쪽과 오른쪽을 검색해야 하며 그 후에 아래 쿼리를 사용할 수 있습니다.

with recursive employeeRoot (employee_id) as 
(
select 
  e1_0.id
from 
  employees eal1_0
join 
  employees e1_0 on eal1_0.id = e1_0.manager_id
where eal1_0.id=?

union all

(
select 
  e2_0.id
from 
  employees eal2_0
join 
  employeeRoot root1_0 on eal2_0.id = root1_0.employee_id
join 
  employees e2_0 on eal2_0.id = e2_0.manager_id
order by 
  eal2_0.id
)
)
select 
  root2_0.employee_id
from 
  employeeRoot root2_0

로그인 후 복사

그리고 그게 다입니다! 세 가지 접근 방식 모두에 대해 트리를 위아래로 이동하는 방법을 살펴보았습니다. 즐거운 여행이 되셨기를 바라며, 도움이 되셨기를 바랍니다.

Postgres와 문서/그래프 데이터베이스

위 예시에서 사용한 데이터베이스는 PostgreSQL입니다. 이것이 유일한 옵션은 아닙니다. 예를 들어 MongoDB와 같은 문서 데이터베이스나 Neo4j와 같은 그래프 데이터베이스는 실제로 이러한 유형의 워크로드를 염두에 두고 구축되었기 때문에 선택하지 않는지 궁금할 것입니다.

트랜잭션 보장을 활용하는 관계형 모델의 Postgres에 이미 진실 데이터 소스가 있을 가능성이 있습니다. 이 경우 모든 것을 한 곳에 보관하기 위해 먼저 Postgres 자체가 보조 사용 사례를 얼마나 잘 처리하는지 확인해야 합니다. 이렇게 하면 새로운 별도의 전문 데이터 저장소를 가동하고 유지/업그레이드하는 데 필요한 비용 증가와 운영 복잡성을 피할 수 있을 뿐만 아니라 익숙해져야 할 필요도 없습니다.

결론

데이터베이스 애플리케이션에서 계층적 데이터를 모델링하는 데는 몇 가지 흥미로운 옵션이 있습니다. 이번 포스팅에서는 세 가지 방법을 보여드렸습니다. 두 항목을 비교하고 더 많은 양의 데이터에서 어떤 일이 발생하는지 확인하는 2부에서 계속 지켜봐 주시기 바랍니다.

참고자료

https://dev.to/yugabyte/learn-how-to-write-sql-recursive-cte-in-5-steps-3n88
https://vladmihalcea.com/hibernate-with-recursive-query/
https://vladmihalcea.com/dto-projection-jpa-query/
https://tudborg.com/posts/2022-02-04-postgres-hierarchical-data-with-ltree/
https://aregall.tech/hibernate-6-custom-functions#heading-implementing-a-custom-function
https://www.amazon.co.uk/Joe-Celkos-SQL-Smarties-Programming/dp/0128007613
https://madecurious.com/curiosities/trees-in-postgresql/
https://schinckel.net/2014/11/27/postgres-tree-shootout-part-2:-adjacency-list-using-ctes/

위 내용은 PostgreSQL 및 Spring Data JPA를 사용한 계층적 데이터의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!