PostgreSQL - 대용량 데이터베이스에서 배열 사용의 성능

우리가 600만 개의 레코드가 있는 테이블이 있다고 가정해 봅시다. 정수형 열이 16개이고 몇 개의 텍스트 열이 있습니다. 읽기 전용 테이블이므로 각 정수형 열에는 인덱스가 있습니다.
각 레코드는 약 50-60바이트 정도입니다.

테이블 이름은 아이템입니다.

서버는: 12GB RAM, 1.5TB SATA, 4코어로 구성되어 있으며 모두 postgres를 위한 서버입니다.

이 데이터베이스에는 많은 테이블이 있으므로 RAM은 모든 데이터베이스를 커버하지 못합니다.

테이블 아이템에 a_elements라는 열 (큰 정수형 배열 유형)을 추가하고 싶습니다.
이 열에는 각 레코드당 최대 50-60개의 요소가 있을 것입니다.

그 후에 나는 이 열에 GIN 인덱스를 생성하고 일반적인 쿼리는 다음과 같을 것이다:

select * from item where ...... and '{5}' <@ a_elements;

두 번째로 전통적인 옵션도 있습니다.

테이블 아이템에 열 a_elements를 추가하지 않고 id_item과 id_element 두 개의 열로 구성된 elements 테이블을 생성합니다.

• id_item


• id_element

이 테이블은 약 2억 개의 레코드를 가질 것입니다.

나는이 테이블에서 파티셔닝을 수행하여 테이블 요소의 레코드 수가 2000만 개, 테이블 항목의 레코드 수가 50만 개로 줄어들 수 있습니다.

두 번째 옵션 쿼리는 다음과 같습니다:

아이템에서 item.*을 선택합니다.
아이템에서 요소와 (item.id_item = elements.id_item) 조인합니다.
.... 조건이 참인 경우
그리고 elements.id_element = 5인 경우

성능 측면에서 어떤 옵션이 더 좋을지 궁금합니다.
Postgres는 인덱스 GIN(option 1)을 사용하여 여러 개의 다른 인덱스를 사용할 수 있습니까?

이 데이터를 가져오는 데는 20일이 걸릴 예정이므로 좋은 결정을 내리기 위해 필요합니다.

답변 1

테이블 요소를 만들다 (id_item bigint, id_element bigint);
elements에 추가하기
  1부터 10,000,000까지 생성된 문자열에서 (random()*524288)::int와 (random()*32768)::int 선택하기;
\timing
elements 테이블의 id_item을 기준으로 인덱스(elements_id_item) 만들기;
시간: 15470,685 ms
elements 테이블의 id_element를 기준으로 인덱스(elements_id_element) 만들기;
시간: 15121,090 ms
relation, pg_size_pretty(pg_relation_size(relation)) 선택하기
  (
    unnest(array['elements','elements_id_item', 'elements_id_element'])
      as relation
  )를 다시 선택하기;
      relation       | pg_size_pretty 
---------------------+----------------
 elements            | 422 MB
 elements_id_item    | 214 MB
 elements_id_element | 214 MB
배열 테이블 만들기 (id_item bigint, a_elements bigint[]);
arrays에 elements 테이블의 id_element를 배열로 선택해서 추가하기;
arrays 테이블의 a_elements를 기준으로 gin 인덱스(arrays_a_elements_idx) 만들기;
시간: 22102,700 ms
relation, pg_size_pretty(pg_relation_size(relation)) 선택하기
  (
    unnest(array['arrays','arrays_a_elements_idx']) as relation
  )를 다시 선택하기;
       relation        | pg_size_pretty 
-----------------------+----------------
 arrays                | 108 MB
 arrays_a_elements_idx | 73 MB

그래서 반면에 배열은 더 작고, 더 작은 인덱스를 가지고 있습니다. 결정을 내리기 전에 2억 개의 요소를 테스트해 보는 것이 좋습니다.

답변 2

PostgreSQL - 대용량 데이터베이스에서 배열 사용의 성능
(PostgreSQL - Big 데이터베이스에서 배열 사용의 성능)
대용량 데이터베이스에서 배열을 사용하여 성능을 높이는 것은 많은 도움이 될 수 있습니다. PostgreSQL은 기본적으로 배열 데이터 유형을 지원하며, 이를 효과적으로 활용함으로써 데이터베이스의 성능을 향상시킬 수 있습니다. 이 문서에서는 PostgreSQL에서 배열을 사용하는 방법과 그로 인해 얻을 수 있는 성능 향상에 대해 알아보겠습니다.
첫째로, 배열은 대량의 데이터를 효율적으로 저장할 수 있는 구조입니다. 많은 데이터를 다루는 경우, 배열을 사용하면 데이터를 일부 그룹화하여 관리할 수 있습니다. 예를 들어, 주문 데이터에서 여러 제품을 포함하는 배열을 사용할 수 있습니다. 이렇게 하면 각 주문에 대한 정보를 한 번에 처리할 수 있으므로 일괄 처리 작업을 효율적으로 수행할 수 있습니다.
둘째로, 배열은 대용량 데이터베이스에서 데이터 접근 속도를 향상시킬 수 있습니다. 배열은 인덱스를 통해 빠른 데이터 검색을 가능하게 합니다. 배열은 순차적으로 저장되며, 인덱스를 사용하여 데이터에 빠르게 액세스할 수 있습니다. 이는 대량의 데이터에 대한 쿼리 성능을 향상시키는 데 도움이 됩니다.
셋째로, 배열은 데이터의 중복을 효과적으로 관리할 수 있습니다. 배열을 사용하면 중복된 데이터를 한 번에 관리할 수 있습니다. 이는 데이터베이스의 용량을 줄여주고 일관성을 유지하는 데 도움이 됩니다. 또한, 중복 데이터를 처리하는 데 걸리는 시간과 비용을 절감할 수 있습니다.
넷째로, 배열은 복잡한 데이터 구조를 효과적으로 처리할 수 있습니다. PostgreSQL에서는 배열을 사용하여 다양한 유형의 데이터를 저장하고 검색할 수 있습니다. 배열 내부에 배열을 저장하여 다차원 데이터 구조를 표현하는 것도 가능합니다. 이를 통해 복잡한 데이터 탐색 및 쿼리 작업을 단순화할 수 있습니다.
마지막으로, 배열은 PostgreSQL에서 데이터 분석 및 집계 작업을 수행하는 데에도 유용합니다. 배열을 사용하면 여러 데이터를 그룹화하고 연산을 수행할 수 있습니다. 예를 들어, 여러 제품의 판매량을 배열로 저장하여 총 판매량을 계산할 수 있습니다. 이러한 데이터 집계 작업을 효과적으로 수행하면 데이터 분석 및 의사 결정에 필요한 정보를 빠르게 얻을 수 있습니다.
이렇듯, 대용량 데이터베이스에서 배열을 사용하는 것은 PostgreSQL에서 성능을 향상시키는 데 중요한 역할을 합니다. 배열은 데이터 그룹화, 데이터 접근 속도 향상, 중복 데이터 관리, 복잡한 데이터 구조 처리 및 데이터 분석 작업에 효과적입니다. 따라서, PostgreSQL을 사용하는 기업이나 개발자라면 배열을 적절히 활용하여 데이터베이스의 성능을 향상시키는 것을 고려해보는 것이 좋습니다.
(Word count: 296)