포스트그레스 JSON 인덱스는 기존의 정규화 테이블에 비해 효율이 충분히 높은가요?

포스트그레스 JSON 인덱스는 기존의 정규화 테이블에 비해 효율이 충분히 높은가요?

현재 Postgresql 버전에서는 JSON 콘텐츠에 다양한 기능이 도입되어 있습니다만, 실제로 사용할 필요가 있는지 어떤지를 고민하고 있습니다.즉, 무엇이 유효하고 무엇이 무효인지에 대한 「베스트 프랙티스」는 아직 확립되어 있지 않습니다.적어도 찾을 수 없습니다.

구체적인 예를 들어 오브젝트에 대한 테이블이 있습니다.이 테이블에는 그 오브젝트의 대체 이름 목록이 포함되어 있습니다.이 모든 데이터는 검색 목적으로 JSON 열에 포함됩니다.예를 들어 (다른 관련 없는 필드는 모두 건너뜁니다.)

create table stuff (id serial primary key, data json);
insert into stuff(data) values('{"AltNames":["Name1","Name2","Name3"]}')

"alt name 중 하나가 'foobar'인 모든 개체를 나열" 형식으로 몇 가지 쿼리가 필요합니다.예상 테이블 크기는 약 몇 백만 개의 레코드입니다.이를 위해 Postgres JSON 쿼리를 사용할 수 있으며 인덱싱할 수도 있습니다(예를 들어 JSON 배열에서 요소를 찾기 위한 색인).하지만, 그렇게 해야 할까요, 아니면 권장되지 않는 왜곡된 회피책일까요?

물론 전형적인 대안은 메인 테이블에 이름과 외부 키를 포함한 일대다 관계에 대한 추가 테이블을 추가하는 것입니다.그 성능은 충분히 이해되고 있습니다.그러나 이는 테이블과 JSON 간의 데이터 복제(정합성 위험이 있음) 또는 요청 시마다 동적으로 데이터를 반환하는 JSON 생성(그 자체의 성능 저하)을 의미하기 때문에 자체적인 단점이 있습니다.

"alt name 중 하나가 'foobar'인 모든 개체를 나열" 형식으로 몇 가지 쿼리가 필요합니다.예상 테이블 크기는 약 몇 백만 개의 레코드입니다.여기에는 Postgres JSON 쿼리를 사용할 수 있으며 인덱싱할 수도 있습니다(예: JSON 배열의 Index For Finding Element).하지만, 그렇게 해야 할까요, 아니면 권장되지 않는 왜곡된 회피책일까요?

그렇게 할 수는 있지만 그렇다고 네가 해야 한다는 뜻은 아니야.어떤 의미에서는 베스트 프랙티스가 이미 잘 문서화되어 있습니다(예를 들어 hstore vs XML vs EAV vs 별도의 테이블 사용 참조). 새로운 데이터형은 검증 및 구문을 제외하고 모든 목적과 실제 목적에서 이전의 비구조화 또는 반구조화 옵션과 다르지 않습니다.

바꿔 말하면, 똑같은 늙은 돼지처럼 화장을 한 거야.

JSON은 hstore, 어레이 유형 및 tsvector와 동일한 방식으로 반전 검색 트리 인덱스를 사용할 수 있는 기능을 제공합니다.이들은 정상적으로 동작하지만 주로 거리순으로 정렬된 인근(생각 기하학 유형)의 점을 추출하기 위해 설계되어 있으며 사전적 순서로 값 목록을 추출하기 위해 설계되어 있지 않다는 점에 유의하십시오.

예를 들어 로만의 답변이 개략적으로 설명한 두 가지 계획을 들어보자.

• 인덱스 스캔을 수행하는 사용자는 디스크 페이지를 직접 통과하여 인덱스에 표시된 순서대로 행을 검색합니다.
• 비트맵 인덱스 스캔은 행이 포함될 수 있는 모든 디스크 페이지를 식별하여 불필요한 영역을 건너뛰는 시퀀스 스캔을 수행하는 것처럼 디스크에 표시되는 대로 읽습니다.

질문으로 돌아가자: Postgres 테이블을 거대한 JSON 스토어로 사용하면 번잡하고 크기가 큰 반전 트리 인덱스를 사용하면 앱의 성능이 향상됩니다.그러나 이러한 솔루션도 그다지 좋은 방법은 아니며, 병목현상을 해결할 때 적절한 관계형 설계까지 얻을 수 없습니다.

결론은 hstore 또는 EAV를 사용할 때 얻을 수 있는 것과 다르지 않습니다.

1. 인덱스가 필요한 경우(즉, where 절 또는 join 절에 자주 표시됨) 데이터를 별도의 필드에 넣어야 할 수 있습니다.
2. 주로 화장품이라면 JSON/hstore/EAV/XML/make-you-sleep-your-night로 괜찮습니다.

시도해 볼 만하다고 할 수 있겠네요.몇 가지 테스트(레코드 100,000개, JSON 어레이의 요소 10개까지)를 생성하여 작동 방식을 확인했습니다.

create table test1 (id serial primary key, data json);
create table test1_altnames (id int, name text);

create or replace function array_from_json(_j json)
returns text[] as
$func$
    select array_agg(x.elem::text)
    from json_array_elements(_j) as x(elem)
$func$
language sql immutable;

with cte as (
    select
        (random() * 100000)::int as grp, (random() * 1000000)::int as name
    from generate_series(1, 1000000)
), cte2 as (
    select
        array_agg(Name) as "AltNames"
    from cte
    group by grp
)
insert into test1 (data)
select row_to_json(t)
from cte2 as t

insert into test1_altnames (id, name)
select id, json_array_elements(data->'AltNames')::text
from test1

create index ix_test1 on test1 using gin(array_from_json(data->'AltNames'));
create index ix_test1_altnames on test1_altnames (name);

JSON 쿼리(내 컴퓨터에서는 30ms):

select * from test1 where '{489147}' <@ array_from_json(data->'AltNames');

"Bitmap Heap Scan on test1  (cost=224.13..1551.41 rows=500 width=36)"
"  Recheck Cond: ('{489147}'::text[] <@ array_from_json((data -> 'AltNames'::text)))"
"  ->  Bitmap Index Scan on ix_test1  (cost=0.00..224.00 rows=500 width=0)"
"        Index Cond: ('{489147}'::text[] <@ array_from_json((data -> 'AltNames'::text)))"

이름이 포함된 쿼리 테이블(내 컴퓨터에서는 15ms):

select * from test1 as t where t.id in (select tt.id from test1_altnames as tt where tt.name = '489147');

"Nested Loop  (cost=12.76..20.80 rows=2 width=36)"
"  ->  HashAggregate  (cost=12.46..12.47 rows=1 width=4)"
"        ->  Index Scan using ix_test1_altnames on test1_altnames tt  (cost=0.42..12.46 rows=2 width=4)"
"              Index Cond: (name = '489147'::text)"
"  ->  Index Scan using test1_pkey on test1 t  (cost=0.29..8.31 rows=1 width=36)"
"        Index Cond: (id = tt.id)"

하려면 비용이 (이름/이름).test1_altnames행을 선택하는 것보다 조금 더 복잡합니다.JSON을 사용하다

언급URL : https://stackoverflow.com/questions/18799704/are-postgres-json-indexes-efficient-enough-compared-with-classic-normalized-tabl