PostgreSQL에서 EAV를 JSONB로 교체

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

소개

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

이 문제를 해결하는 가장 쉬운 방법은 각 속성 값에 대해 데이터베이스 테이블에 열을 만들고 특정 엔터티 인스턴스에 필요한 항목을 간단히 채우는 것입니다. 엄청난! 문제가 해결되었습니다... 테이블에 수백만 개의 레코드가 포함되어 새 레코드를 추가해야 할 때까지입니다.

EAV 패턴을 고려하십시오(엔티티 속성 값), 꽤 자주 발생합니다. 한 테이블에는 엔터티(레코드)가 포함되어 있고, 다른 테이블에는 속성 이름(속성)이 포함되어 있으며, 세 번째 테이블에는 엔터티를 해당 특성과 연결하고 현재 엔터티에 대한 해당 특성 값이 포함되어 있습니다. 이를 통해 다양한 개체에 대해 다양한 속성 집합을 가질 수 있고 데이터베이스 구조를 변경하지 않고도 즉시 속성을 추가할 수 있습니다.

그러나 EVA 접근 방식에 몇 가지 단점이 없었다면 저는 이 게시물을 작성하지 않았을 것입니다. 예를 들어 각각 1개의 속성을 가진 하나 이상의 엔터티를 얻으려면 쿼리에 2개의 조인이 필요합니다. 첫 번째는 속성 테이블과의 조인이고 두 번째는 값 테이블과의 조인입니다. 엔터티에 2개의 속성이 있는 경우 4개의 조인이 필요합니다! 또한 모든 속성은 일반적으로 문자열로 저장되므로 결과와 WHERE 절 모두에 대해 유형 캐스팅이 수행됩니다. 쿼리를 많이 작성하면 리소스 사용 측면에서 상당히 낭비가 됩니다.

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

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

JSONB 데이터 유형을 사용하면 엔터티 테이블에 JSONB 열 하나만 추가하여 번거로운 EAV 패턴을 대체할 수 있으므로 데이터베이스 설계가 크게 단순화됩니다. 하지만 많은 사람들은 이것이 생산성 저하를 동반해야 한다고 주장합니다... 그래서 이 글을 썼습니다.

테스트 데이터베이스 설정

이 비교를 위해 $9.5 빌드에 PostgreSQL 80를 새로 설치하여 데이터베이스를 생성했습니다. DigitalOcean 우분투 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 열을 사용한 접근 방식을 사용하여 동일한 데이터로 테이블을 채웠습니다. 엔터티_jsonb.properties. 따라서 우리는 전체 속성 집합 중에서 여러 가지 다른 데이터 유형을 받았습니다. 예시 데이터:

{
  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 예제를 보면 업데이트를 수행하려면 엔터티_id와 엔터티_속성_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"}';

이는 좀 더 복잡합니다. 속성 열의 JSON 개체에 @> 연산자 오른쪽에 있는 개체가 포함되어 있는지 확인합니다. 가독성이 낮고 생산성이 높습니다(아래 참조).

한 번에 여러 속성을 선택해야 하는 경우 JSONB를 더욱 쉽게 사용할 수 있습니다. JSONB 접근 방식이 실제로 필요한 곳입니다. 조인이 필요 없이 결과 집합에서 속성을 추가 열로 선택하기만 하면 됩니다.

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

EAV를 사용하면 쿼리하려는 각 속성에 대해 2개의 조인이 필요합니다. 제 생각에는 위의 쿼리는 데이터베이스 설계가 크게 단순화되었음을 보여줍니다. JSONB 쿼리를 작성하는 방법에 대한 추가 예제도 참조하세요. 이 우편.
이제 성능에 대해 이야기할 차례입니다.

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

내가 사용한 성능을 비교하기 위해 분석 설명 쿼리에서 실행 시간을 계산합니다. 쿼리 플래너가 처음 실행되는 데 시간이 더 오래 걸리기 때문에 각 쿼리는 최소한 세 번 실행되었습니다. 먼저 인덱스 없이 쿼리를 실행했습니다. 분명히 이는 EAV에 필요한 조인이 인덱스를 사용할 수 없기 때문에(외부 키 필드는 인덱스되지 않음) JSONB의 장점이었습니다. 그런 다음 EAV 값 테이블의 2개 외래 키 열에 대한 인덱스와 인덱스를 생성했습니다. 진 JSONB 열의 경우.

데이터 업데이트를 통해 시간(ms) 기준으로 다음과 같은 결과가 나타났습니다. 척도는 대수적입니다.

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

속성 값을 기준으로 데이터를 선택하면 다음과 같은 결과를 얻습니다(일반 척도).

JSONB는 인덱스가 없는 EAV보다 빠르게 작동하지만 인덱스가 있는 EAV의 경우 여전히 JSONB보다 빠르게 작동합니다. 그런데 JSONB 쿼리의 시간이 동일하다는 것을 알았고 이로 인해 GIN 인덱스가 작동하지 않는다는 사실을 알게 되었습니다. 속성이 채워진 열에 GIN 인덱스를 사용하면 포함 연산자 @>를 사용할 때만 적용됩니다. 나는 이것을 새로운 테스트에 사용했고 시간에 큰 영향을 미쳤습니다: 단 0,153ms! 이는 EAV보다 15000배 빠르며 ->> 연산자보다 25000배 빠릅니다.

충분히 빨랐던 것 같아요!

데이터베이스 테이블 크기

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

EAV 접근 방식의 경우 테이블 크기는 약 3068MB이고 인덱스는 최대 3427MB로 총 6,43GB입니다. JSONB 접근 방식은 테이블에 1817MB, 인덱스에 318MB(2,08GB)를 사용합니다. 3 배 더 적은 것으로 나타났습니다! 우리는 모든 JSONB 객체에 속성 이름을 저장하기 때문에 이 사실에 조금 놀랐습니다.

그러나 여전히 숫자는 그 자체로 나타납니다. EAV에서는 속성 값당 2개의 정수 외래 키를 저장하여 8바이트의 추가 데이터가 생성됩니다. 또한 EAV는 모든 속성 값을 텍스트로 저장하는 반면 JSONB는 가능한 경우 내부적으로 숫자 및 부울 값을 사용하므로 공간이 더 작아집니다.

결과

전반적으로 엔터티 속성을 JSONB 형식으로 저장하면 데이터베이스를 훨씬 쉽게 설계하고 유지 관리할 수 있다고 생각합니다. 많은 쿼리를 실행하는 경우 모든 항목을 엔터티와 동일한 테이블에 유지하면 실제로 더 효율적으로 작동합니다. 그리고 이것이 데이터 간의 상호 작용을 단순화한다는 사실은 이미 장점이지만 결과 데이터베이스의 볼륨은 3배 더 작습니다.

또한 수행된 테스트에 따르면 성능 손실이 매우 미미하다는 결론을 내릴 수 있습니다. 어떤 경우에는 JSONB가 EAV보다 훨씬 빨라서 훨씬 더 좋습니다. 하지만 물론 이 벤치마크가 모든 측면(예: 매우 많은 수의 속성을 가진 엔터티, 기존 데이터의 속성 수의 상당한 증가 등)을 다루지는 않으므로 이를 개선하는 방법에 대한 제안 사항이 있으면 , 자유롭게 댓글로 남겨주세요!

출처 : habr.com