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비트 플랫폼에서는 “clear” 단어로 시작하는 다음 행을 저장하기 위해 4개의 0이 추가됩니다.

결과적으로 각 열의 실제 크기는 (가변 길이 데이터 및 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 documentation 에는 풍부한 정보가 있지만 일반적인 유형의 크기를 여기에 나열하여 쉽게 조회할 수 있도록 합니다. 여기서 “단어”는 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 정수 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 바이트가 필요합니다.

열 순서를 변경함으로써 최소한 8바이트가 저장됩니다. 큰 events 테이블에서는 지금 달라질 수 있습니다. 예를 들어, 80,000,000 행을 저장하면 610MB를 절약할 수 있습니다.