PostgreSQL에서 테이블 열 정렬하기
GitLab에서는 새로운 테이블의 열이 최소한의 공간을 사용하도록 정렬되어야 합니다. 이를 쉽게 할 수 있는 방법은 가변 크기의 열(text, varchar, 배열, json, jsonb 등)을 마지막에 두고 크기에 따라 내림차순으로 정렬하는 것입니다.
C 구조체와 유사하게 테이블의 공간은 열의 순서에 영향을 받습니다. 이는 열의 크기가 다음 열의 유형에 따라 정렬되기 때문입니다. 예를 들어 다음과 같습니다:
-
id(정수, 4바이트) -
name(text, 가변) -
user_id(정수, 4바이트)
첫 번째 열은 4바이트 정수입니다. 다음은 가변 길이의 텍스트입니다. text 데이터 유형은 1-워드 정렬이 필요하며 64비트 플랫폼에서 1 워드는 8바이트입니다. 정렬 요구 사항을 충족하기 위해 첫 번째 열 다음에는 4개의 0이 추가되어 id가 4바이트를 차지한 후 4바이트의 정렬 패딩이 추가되고 그 후에 name이 저장됩니다. 따라서 이 경우 4바이트 정수를 저장하기 위해 8바이트가 소요됩니다.
행 간의 공간 또한 정렬 패딩의 영향을 받습니다. user_id 열은 4바이트만 차지하며 64비트 플랫폼에서는 “clear” 워드로 시작하는 다음 행을 저장하기 위해 4개의 0이 추가됩니다.
결과적으로 각 열의 실제 크기는 다음과 같아야 합니다(가변 길이 데이터와 24바이트 튜플 헤더는 제외): 8바이트, 가변, 8바이트. 이는 각 행이 최소한 16바이트의 공간을 필요로 한다는 것을 의미합니다. 테이블에 몇 개의 행만 있는 경우에는 문제가 되지 않지만, 수백만 개의 행을 저장하기 시작하면 다른 순서를 사용하여 공간을 절약할 수 있습니다. 위 예에서 이상적인 열 순서는 다음과 같습니다:
-
id(정수, 4바이트) -
user_id(정수, 4바이트) -
name(text, 가변)
또는
-
name(text, 가변) -
id(정수, 4바이트) -
user_id(정수, 4바이트)
이러한 경우에 id와 user_id 열은 함께 묶이기 때문에 _모두_를 저장하기 위해서는 8바이트만 필요합니다. 이는 매 행마다 8바이트의 공간이 더 적게 필요함을 의미합니다.
Ruby on Rails 5.1부터 ID의 기본 데이터 유형은 bigint로, 8바이트를 사용합니다. 우리는 더 현실적인 재정렬 시나리오를 보여주기 위해 여기서는 integer를 사용합니다.
유형 크기
PostgreSQL 문서에는 풍부한 정보가 포함되어 있지만 일반적인 유형의 크기를 쉽게 찾을 수 있도록 여기서 자주 사용되는 유형의 크기를 나열합니다. 여기서 “워드(word)”는 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바이트 | 1 워드 |
bytea
| 가변, 1 또는 4 바이트에 데이터가 추가된 1바이트 | 1 워드 |
timestamp
| 8 바이트 | 8 바이트 |
timestamptz
| 8 바이트 | 8 바이트 |
date
| 4 바이트 | 1 워드 |
“가변” 크기는 실제 크기가 저장되는 값에 따라 다릅니다. 만약 PostgreSQL에서 이것을 직접 행에 임베딩할 수 있다고 판정하면 그렇게 하지만, 매우 큰 값의 경우에는 데이터를 외부에 저장한 후 열에 1 워드 크기의 포인터를 저장합니다. 따라서 이러한 가변 크기의 열은 테이블의 끝에 항상 위치해야 합니다.
실제 예제
다음은 현재 다음과 같은 레이아웃을 가지고 있는 events 테이블을 예로 삼아보겠습니다:
| 열 | 유형 | 크기 |
|---|---|---|
id
| integer | 4 바이트 |
target_type
| character varying | 가변 |
target_id
| integer | 4 바이트 |
title
| character varying | 가변 |
data
| text | 가변 |
project_id
| integer | 4 바이트 |
created_at
| timestamp without time zone | 8 바이트 |
updated_at
| timestamp without time zone | 8 바이트 |
action
| integer | 4 바이트 |
author_id
| integer | 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
| integer | 4 바이트 |
target_id
| integer | 4 바이트 |
project_id
| integer | 4 바이트 |
action
| integer | 4 바이트 |
author_id
| integer | 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
|
여기서 가변 크기의 데이터와 24바이트 튜플 헤더를 제외하면 매 행마다 40 바이트만 필요합니다. 8바이트가 절약된 것은 크게 들리지 않을 수 있지만, events 테이블과 같이 큰 테이블을 저장할 때는 의미가 있을 것입니다. 예를 들어, 8000만 개의 행을 저장할 때, 단순히 몇 개의 열의 순서를 변경함으로써 최소 610MB의 공간을 절약할 수 있습니다.
도움말