PostgreSQL에서 테이블 열 정렬하기
GitLab에서는 새 테이블의 열이 최소한의 공간을 사용하도록 정렬되어야 합니다. 이를 쉽게 하는 방법은 변수 크기(text, varchar, 배열, json, jsonb 등)의 타입 크기를 기준으로 내림차순으로 정렬하는 것입니다.
C 구조체와 유사하게 테이블의 공간은 열의 순서에 영향을 받습니다. 이는 열의 크기가 다음 열의 타입에 따라 정렬되기 때문입니다. 예를 들어, id (정수, 4바이트), name (텍스트, 변수), user_id (정수, 4바이트)로 이루어진 열 순서를 고려해 보겠습니다.
첫 번째 열은 4바이트 정수입니다. 다음 열은 변수 길이의 텍스트입니다. text 데이터 유형은 1 워드 정렬이 필요하며, 64비트 플랫폼에서 1 워드는 8바이트입니다. 정렬 요구 사항을 충족시키기 위해 첫 번째 열 다음에 4개의 0을 추가하여 id가 4바이트를 차지한 후 4바이트의 정렬 패딩이 추가되고, 그 후에 name이 저장됩니다. 따라서 이 경우 8바이트가 4바이트 정수를 저장하는 데 소비됩니다.
행 간의 공간 또한 정렬 패딩의 영향을 받습니다. user_id 열은 4바이트만 차지하고, 64비트 플랫폼에서는 정렬 패딩을 위해 4개의 0이 추가되어 “clear” 워드로 시작하는 다음 행을 저장할 수 있습니다.
결과적으로 각 열의 실제 크기는 (변수 길이 데이터와 24바이트 튜플 헤더를 제외한) 8바이트, 변수, 8바이트입니다. 따라서 각 행은 최소한 16바이트의 공간이 필요합니다. 테이블에 몇 개의 행이 들어있는 경우에는 문제가 되지 않지만, 수백만 개의 행을 저장하는 경우 열의 순서를 바꿈으로써 공간을 절약할 수 있습니다. 위 예에서는 이상적인 열 순서가 다음과 같습니다.
-
id(정수, 4바이트) -
user_id(정수, 4바이트) -
name(텍스트, 변수)
또는
-
name(텍스트, 변수) -
id(정수, 4바이트) -
user_id(정수, 4바이트)
이 예에서 id와 user_id 열은 함께 묶이므로, 이들을 저장하는 데는 8바이트만 필요합니다. 따라서 각 행은 공간을 8바이트 더 적게 사용하게 됩니다.
Ruby on Rails 5.1부터 ID의 기본 데이터 유형은 bigint로, 8바이트를 사용합니다. 여기서는 더 현실적인 재정렬 시나리오를 보여주기 위해 예시로 integer를 사용하고 있습니다.
유형 크기
PostgreSQL 문서에는 많은 정보가 있지만, 일반적인 유형의 크기를 쉽게 확인할 수 있도록 여기에 해당 크기를 나열합니다. 여기서 “워드”는 32비트 플랫폼에서 4바이트이고, 64비트 플랫폼에서는 8바이트인 워드 크기를 나타냅니다.
| 유형 | 크기 | 필요한 정렬 |
|---|---|---|
smallint
| 2바이트 | 1 워드 |
integer
| 4바이트 | 1 워드 |
bigint
| 8바이트 | 8바이트 |
real
| 4바이트 | 1 워드 |
double precision
| 8바이트 | 8바이트 |
boolean
| 1바이트 | 필요 없음 |
text / string
| 변수, 데이터에 1바이트 추가 | 1 워드 |
bytea
| 변수, 데이터에 1 또는 4바이트 추가 | 1 워드 |
timestamp
| 8바이트 | 8바이트 |
timestamptz
| 8바이트 | 8바이트 |
date
| 4바이트 | 1 워드 |
“변수” 크기는 실제 크기가 저장되는 값에 따라 다릅니다. 만약 PostgreSQL이 이를 직접 행에 넣을 수 있다고 판단하면 그렇게 하지만, 매우 큰 값의 경우 데이터를 외부에 저장하고 (1워드 크기의) 포인터를 열에 저장합니다. 따라서 변수 크기의 열은 항상 테이블의 끝에 있어야 합니다.
실제 예시
events 테이블을 예시로 삼아 현재 다음과 같은 레이아웃을 가지고 있습니다.
| 열 | 유형 | 크기 |
|---|---|---|
id
| 정수 | 4바이트 |
target_type
| character varying | 변수 |
target_id
| 정수 | 4바이트 |
title
| character varying | 변수 |
data
| text | 변수 |
project_id
| 정수 | 4바이트 |
created_at
| timestamp without time zone | 8바이트 |
updated_at
| timestamp without time zone | 8바이트 |
action
| 정수 | 4바이트 |
author_id
| 정수 | 4바이트 |
열을 정렬하기 위한 패딩 추가 후, 열은 다음과 같이 고정된 크기의 청크로 나뉩니다.
| 청크 크기 | 열 |
|---|---|
| 8바이트 | id
|
| 변수 | target_type
|
| 8바이트 | target_id
|
| 변수 | title
|
| 변수 | data
|
| 8바이트 | project_id
|
| 8바이트 | created_at
|
| 8바이트 | updated_at
|
| 8바이트 |
action, author_id
|
따라서 변수 크기의 데이터와 튜플 헤더를 제외하고, 각 행당 적어도 8 * 6 = 48바이트가 필요합니다.
이를 최적화하기 위해 다음과 같은 열 순서를 사용할 수 있습니다.
| 열 | 유형 | 크기 |
|---|---|---|
created_at
| timestamp without time zone | 8바이트 |
updated_at
| timestamp without time zone | 8바이트 |
id
| 정수 | 4바이트 |
target_id
| 정수 | 4바이트 |
project_id
| 정수 | 4바이트 |
action
| 정수 | 4바이트 |
author_id
| 정수 | 4바이트 |
target_type
| character varying | 변수 |
title
| character varying | 변수 |
data
| text | 변수 |
이를 통해 다음과 같은 청크가 생성됩니다.
| 청크 크기 | 열 |
|---|---|
| 8바이트 | created_at
|
| 8바이트 | updated_at
|
| 8바이트 |
id, target_id
|
| 8바이트 |
project_id, action
|
| 8바이트 | author_id
|
| 변수 | target_type
|
| 변수 | title
|
| 변수 | data
|
이 경우에는 변수 크기의 데이터를 제외하고 각 행당 40바이트가 필요합니다. 8바이트의 공간이 절약된다고 해서 크게 들리지 않을 수 있지만, events 테이블과 같이 큰 테이블의 경우에는 중요한 이슈가 될 수 있습니다. 예를 들어, 8천만 개의 행을 저장할 때, 이는 열의 순서를 조금 바꾸는 것만으로 적어도 610MB의 공간을 절약할 수 있습니다.
도움말