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이 저장됩니다. 따라서 이 경우 4바이트 정수를 저장하는 데 8바이트가 사용됩니다.

행 간의 공간 또한 정렬 패딩의 영향을 받습니다. 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 integer 4바이트
target_type 문자열 가변 가변
target_id integer 4바이트
title 문자열 가변 가변
data 텍스트 가변
project_id integer 4바이트
created_at 타임스탬프(타임존 없음) 8바이트
updated_at 타임스탬프(타임존 없음) 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 타임스탬프(타임존 없음) 8바이트
updated_at 타임스탬프(타임존 없음) 8바이트
id integer 4바이트
target_id integer 4바이트
project_id integer 4바이트
action integer 4바이트
author_id integer 4바이트
target_type 문자열 가변 가변
title 문자열 가변 가변
data 텍스트 가변

이렇게 하면 다음과 같은 청크가 생성됩니다.

청크 크기 열
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 테이블과 같이 큰 테이블의 경우에는 중요한 차이입니다. 예를 들어 80,000,000개의 행을 저장하는 경우, 몇 개의 열의 순서를 바꾸는 것만으로 적어도 610MB의 공간을 절약할 수 있습니다.