PostgreSQL에서 테이블 열 정렬
GitLab에서는 새 테이블의 열을 공간을 가장 적게 사용하도록 정렬해야 합니다. 이를 쉽게 수행하는 방법은 열을 유형 크기에 따라 내림차순으로 정렬하는 것으로, 가변 크기(text, varchar, 배열, json, jsonb 등)는 끝에 배치하는 것입니다.
C 구조체와 유사하게 테이블의 크기는 열의 순서에 따라 영향을 받습니다. 이는 열의 크기가 다음 열의 유형에 따라 정렬되기 때문입니다. 예를 들어 봅시다:
-
id(정수형, 4 바이트) -
name(텍스트, 가변) -
user_id(정수형, 4 바이트)
첫 번째 열은 4 바이트 정수형입니다. 다음 열은 가변 길이의 텍스트입니다. text 데이터 유형은 1 단어 정렬을 요구하며, 64비트 플랫폼에서는 1 단어가 8 바이트입니다. 정렬 요구 사항을 충족하기 위해 첫 번째 열 뒤에 네 개의 0이 추가되어야 하므로, id는 4 바이트를 차지하고, 그 다음에 정렬 패딩으로 4 바이트, 그리고 다음으로 name이 저장됩니다. 그러므로 이 경우, 4 바이트 정수를 저장하는 데 8 바이트가 소모됩니다.
행 간의 공간도 정렬 패딩의 영향을 받습니다. user_id 열은 4 바이트만 차지하며, 64비트 플랫폼에서는 다음 행이 “clear” 단어로 시작할 수 있도록 정렬 패딩으로 4개의 0이 추가됩니다.
결과적으로 각 열의 실제 크기는 (가변 길이 데이터와 24 바이트 튜플 헤더를 생략하고): 8 바이트, 가변, 8 바이트가 됩니다. 이는 각 행이 두 개의 4 바이트 정수형에 대해 최소 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 |
integer | 4 bytes |
target_type |
character varying | variable |
target_id |
integer | 4 bytes |
title |
character varying | variable |
data |
text | variable |
project_id |
integer | 4 bytes |
created_at |
timestamp without time zone | 8 bytes |
updated_at |
timestamp without time zone | 8 bytes |
action |
integer | 4 bytes |
author_id |
integer | 4 bytes |
열을 정렬하기 위해 패딩을 추가하면 열이 다음과 같이 고정 크기 청크로 나뉘게 됩니다:
| 청크 크기 | 열 |
|---|---|
| 8 bytes | id |
| variable | target_type |
| 8 bytes | target_id |
| variable | title |
| variable | data |
| 8 bytes | project_id |
| 8 bytes | created_at |
| 8 bytes | updated_at |
| 8 bytes |
action, author_id
|
이는 가변 크기 데이터와 튜플 헤더를 제외하고 각 행당 최소 8 * 6 = 48 바이트가 필요함을 의미합니다.
다음과 같은 열 순서를 사용하여 이를 최적화할 수 있습니다:
| 열 | 유형 | 크기 |
|---|---|---|
created_at |
timestamp without time zone | 8 bytes |
updated_at |
timestamp without time zone | 8 bytes |
id |
integer | 4 bytes |
target_id |
integer | 4 bytes |
project_id |
integer | 4 bytes |
action |
integer | 4 bytes |
author_id |
integer | 4 bytes |
target_type |
character varying | variable |
title |
character varying | variable |
data |
text | variable |
이렇게 하면 다음과 같은 청크가 생성됩니다:
| 청크 크기 | 열 |
|---|---|
| 8 bytes | created_at |
| 8 bytes | updated_at |
| 8 bytes |
id, target_id
|
| 8 bytes |
project_id, action
|
| 8 bytes | author_id |
| variable | target_type |
| variable | title |
| variable | data |
여기서 가변 크기 데이터와 24 바이트 튜플 헤더를 제외하고 각 행당 40 바이트만 필요합니다. 8 바이트의 절약은 그리 많아 보이지 않을 수 있지만, events 테이블처럼 큰 테이블에서는 문제가 됩니다. 예를 들어, 80,000,000개의 행을 저장할 때 이는 최소 610MB의 공간 절약으로 이어집니다. 단지 몇 개의 열 순서만 변경했을 뿐입니다.
도움말