PostgreSQL에서 EAV를 JSONB로 교체하기

요약: JSONB는 쿼리 성능을 저하시키지 않고도 데이터베이스 스키마 개발을 크게 단순화할 수 있습니다.

소개

관계형 DB(데이터베이스) 세계에서 아마도 가장 오래된 사용 사례 중 하나인 고전적인 예를 들어 보겠습니다. 엔티티가 있고, 이 엔티티의 특정 속성(속성)을 저장해야 합니다. 하지만 모든 인스턴스가 동일한 속성 집합을 갖는 것은 아니며, 앞으로 더 많은 속성이 추가될 수도 있습니다.

이 문제를 해결하는 가장 쉬운 방법은 DB 테이블에 각 속성 값에 대한 열을 만들고 특정 엔터티 인스턴스에 필요한 값만 입력하는 것입니다. 좋습니다! 문제는 해결되었습니다... 테이블에 수백만 개의 레코드가 있고 새 레코드를 추가해야 할 때까지는 말이죠.

EAV 패턴을 고려해 보자(엔티티-속성-값), 이는 매우 일반적입니다. 한 테이블에는 엔터티(레코드)가 있고, 다른 테이블에는 속성 이름(속성)이 있으며, 세 번째 테이블에는 엔터티와 해당 속성을 연결하고 현재 엔터티에 대한 해당 속성 값을 포함합니다. 이를 통해 다양한 객체에 대해 서로 다른 속성 집합을 사용할 수 있을 뿐만 아니라 데이터베이스 구조를 변경하지 않고도 "즉시" 속성을 추가할 수 있습니다.

하지만 EVA 방식에 단점이 없다면 이 글을 쓰지 않았을 것입니다. 예를 들어, 각각 하나의 속성을 가진 하나 이상의 엔터티를 가져오려면 쿼리에 두 개의 조인이 필요합니다. 첫 번째는 속성 테이블과의 조인이고, 두 번째는 값 테이블과의 조인입니다. 엔터티에 속성이 두 개 있으면 조인 네 개가 필요합니다! 게다가 모든 속성은 일반적으로 문자열로 저장되기 때문에 결과와 WHERE 조건 모두에 형변환이 필요합니다. 쿼리를 많이 작성하면 리소스 사용 측면에서 매우 낭비적입니다.

이러한 명백한 단점에도 불구하고 EAV는 오랫동안 이러한 종류의 문제를 해결하는 데 사용되어 왔습니다. 이는 불가피한 단점이었고, 더 나은 대안은 없었습니다.
그런데 PostgreSQL에 새로운 "기술"이 등장했습니다.

PostgreSQL 9.4부터 바이너리 JSON 데이터를 저장하기 위해 JSONB 데이터 유형이 추가되었습니다. JSONB 형식으로 JSON을 저장하는 것은 일반적으로 일반 텍스트 JSON보다 공간과 시간이 약간 더 많이 소요되지만, 작업 수행 속도는 훨씬 빠릅니다. JSONB는 인덱싱도 지원하여 쿼리 속도가 더욱 빨라집니다.

JSONB 데이터 유형을 사용하면 엔터티 테이블에 JSONB 열 하나만 추가하여 번거로운 EAV 패턴을 대체할 수 있어 데이터베이스 설계를 크게 간소화할 수 있습니다. 하지만 많은 사람들이 이러한 방식이 성능 저하를 초래한다고 주장합니다. 바로 이러한 이유로 이 글을 썼습니다.

테스트 데이터베이스 설정

이 비교를 위해 $9.5 빌드에서 PostgreSQL 80를 새로 설치하여 데이터베이스를 만들었습니다. DigitalOcean Ubuntu 14.04 postgresql.conf 파일에서 몇 가지 매개변수를 설정한 후 실행했습니다. 이 psql을 사용하는 스크립트입니다. 다음 테이블은 데이터를 EAV로 표현하기 위해 생성되었습니다.

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

아래는 동일한 데이터가 저장되지만 JSONB 유형 열에 속성이 있는 테이블입니다. 속성.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

훨씬 간단해 보이지 않나요? 그런 다음 엔터티 테이블에 추가되었습니다(실재 & 엔티티_jsonb) 10만 개의 레코드가 있고, 이에 따라 EAV 패턴과 JSONB 컬럼을 사용한 접근 방식을 사용한 동일한 데이터로 테이블이 채워졌습니다. entity_jsonb.속성. 따라서 전체 속성 집합에서 여러 유형의 데이터를 얻었습니다. 예시 데이터:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

이제 두 옵션에 대해 동일한 데이터가 있습니다. 실제 구현을 비교해 보겠습니다!

디자인 단순화

앞서 데이터베이스 설계가 크게 간소화되었다고 언급했습니다. EAV에 세 개의 테이블을 사용하는 대신, 속성에 JSONB 열을 사용하는 단일 테이블로 간소화되었습니다. 하지만 이러한 간소화는 쿼리에 어떻게 반영될까요? 엔터티의 속성 하나를 업데이트하는 방법은 다음과 같습니다.

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

보시다시피 마지막 쿼리는 더 이상 간단해 보이지 않습니다. JSONB 객체의 속성 값을 업데이트하려면 다음 함수를 사용해야 합니다. jsonb_set()새 값을 JSONB 객체로 전달해야 합니다. 하지만 식별자를 미리 알 필요는 없습니다. EAV 예제를 살펴보면, 업데이트를 수행하려면 entity_id와 entity_attribute_id를 모두 알아야 합니다. JSONB 열의 속성을 엔티티 이름을 기준으로 업데이트하려면 이 모든 작업이 한 줄로 간단하게 완료됩니다.

이제 새로운 색상을 기준으로 방금 업데이트한 엔터티를 선택해 보겠습니다.

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

두 번째 방법이 더 짧고(조인 없음!), 따라서 더 읽기 쉽다는 데 동의할 수 있을 것 같습니다. 이 부분에서는 JSONB가 승리합니다! JSON ->> 연산자를 사용하여 JSONB 객체에서 색상을 텍스트 값으로 가져옵니다. JSONB 모델에서 @> 연산자를 사용하여 동일한 결과를 얻는 두 번째 방법도 있습니다.

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

이 방법은 좀 더 복잡합니다. properties 열의 JSON 객체에 @> 연산자 오른쪽에 있는 객체가 포함되어 있는지 확인합니다. 가독성은 떨어지지만 성능은 더 좋습니다(아래 참조).

여러 속성을 한 번에 선택해야 할 때 JSONB를 더욱 쉽게 사용할 수 있도록 해 보겠습니다. JSONB 접근 방식이 진정으로 빛을 발하는 부분은 바로 이 부분입니다. 조인 없이 결과 집합에서 속성을 추가 열로 선택하기만 하면 됩니다.

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

EAV를 사용하면 쿼리하려는 각 속성에 대해 두 개의 조인이 필요합니다. 제 생각에는 위의 쿼리는 데이터베이스 설계를 크게 단순화한 것으로 보입니다. JSONB 쿼리 작성 방법에 대한 더 많은 예시는 다음에서 확인할 수 있습니다. 이 우편.
이제 성능에 대해 이야기해 보겠습니다.

Производительность

내가 사용한 성능을 비교하려면 분석 설명 쿼리에서 실행 시간을 계산하기 위해 각 쿼리를 최소 세 번 실행했습니다. 쿼리 플래너가 처음 실행할 때 시간이 더 오래 걸리기 때문입니다. 먼저 인덱스 없이 쿼리를 실행했습니다. EAV에 필요한 조인에서 인덱스를 사용할 수 없었기 때문에(외래 키 필드에 인덱스가 생성되지 않았기 때문에) JSONB의 장점인 것 같습니다. 그 후 EAV 값 테이블의 두 외래 키 열에 인덱스를 생성하고, 진 JSONB 열에 대한.

데이터 새로 고침 결과 시간(ms)에 따른 결과는 다음과 같습니다. 단위는 대수적입니다.

인덱스를 사용하지 않을 경우 JSONB가 위에서 언급한 이유로 EAV보다 훨씬(>50000-x) 더 빠른 것을 확인할 수 있습니다. 기본 키 열을 인덱싱하면 차이가 거의 사라지지만, JSONB는 여전히 EAV보다 1,3배 더 빠릅니다. 여기서는 JSONB 열의 인덱스가 아무런 영향을 미치지 않는데, 이는 평가 기준에서 속성 열을 사용하지 않기 때문입니다.

속성 가치에 따라 데이터를 선택하면 다음과 같은 결과가 나옵니다(정규 척도).

JSONB가 인덱스가 없을 때는 EAV보다 빠르지만, EAV에 인덱스를 적용하면 JSONB보다 여전히 빠릅니다. 그런데 JSONB 쿼리의 실행 시간이 동일하다는 것을 알게 되었고, 이를 통해 GIN 인덱스가 트리거되지 않는다는 사실을 알게 되었습니다. 속성이 채워진 열에 GIN 인덱스를 사용할 때는 포함 연산자 @>를 사용해야만 작동하는 것 같습니다. 새 테스트에서 이 연산자를 사용했더니 실행 시간에 엄청난 차이가 나타났습니다. 단 0,153ms! 이는 EAV보다 15000배, ->> 연산자보다 25000배 빠른 속도입니다.

꽤 빠른 것 같아요!

DB 테이블 크기

두 접근 방식의 테이블 크기를 비교해 보겠습니다. psql에서는 다음 명령을 사용하여 모든 테이블과 인덱스의 크기를 확인할 수 있습니다. 디티+

EAV 방식의 경우 테이블 크기는 약 3068MB이고 인덱스는 최대 3427MB로 총 6,43GB입니다. JSONB 방식은 테이블에 1817MB, 인덱스에 318MB를 사용하는데, 이는 2,08GB에 해당합니다. 이는 3배나 적은 용량입니다! 각 JSONB 객체에 속성 이름을 저장하기 때문에 이 사실은 다소 놀랍습니다.

하지만 숫자만 봐도 알 수 있습니다. EAV에서는 속성 값당 2개의 정수 외래 키를 저장하여 8바이트의 추가 데이터를 생성합니다. 또한 EAV에서는 모든 속성 값이 텍스트로 저장되는 반면, JSONB는 가능한 경우 내부적으로 숫자 및 부울 값을 사용하여 더 작은 공간을 차지합니다.

결과

전반적으로 JSONB에 엔티티 속성을 저장하면 데이터베이스 설계 및 유지 관리가 훨씬 수월해질 수 있다고 생각합니다. 쿼리를 많이 수행하는 경우, 모든 항목을 엔티티와 동일한 테이블에 저장하면 실제로 더 효율적으로 작동합니다. 데이터 상호작용이 간소화된다는 점도 장점이지만, 결과적으로 데이터베이스 크기가 3배 더 작아집니다.

또한, 저희가 수행한 벤치마크 결과를 보면 성능 ​​저하가 매우 미미하다는 결론을 내릴 수 있습니다. 경우에 따라 JSONB가 EAV보다 훨씬 빠르기 때문에 더욱 우수합니다. 하지만 이 벤치마크가 모든 측면(예: 매우 많은 속성을 가진 엔티티, 기존 데이터의 속성 수 대폭 증가 등)을 포괄하는 것은 아닙니다. 따라서 개선 방안에 대한 제안이 있으시면 언제든지 댓글로 남겨주세요!

출처 : habr.com