InfoGrab DocsInfoGrab Docs

PostgreSQL에서 테이블 칼럼 순서 지정

요약

GitLab에서는 새로운 테이블의 칼럼이 최소한의 공간을 사용하도록 순서를 정하도록 요구합니다. C 구조체와 마찬가지로 테이블의 공간은 칼럼 순서에 따라 달라집니다. user_id (integer, 4바이트) 첫 번째 칼럼은 4바이트 정수입니다.

GitLab에서는 새로운 테이블의 칼럼이 최소한의 공간을 사용하도록 순서를 정하도록 요구합니다. 이를 위한 쉬운 방법은 타입 크기를 기준으로 내림차순으로 정렬하되, 가변 크기(text, varchar, 배열, json, jsonb 등)를 마지막에 배치하는 것입니다.

C 구조체와 마찬가지로 테이블의 공간은 칼럼 순서에 따라 달라집니다. 이는 칼럼의 크기가 다음 칼럼의 타입에 따라 정렬되기 때문입니다. 다음 예시를 살펴보겠습니다:

  • id (integer, 4바이트)

  • name (text, 가변)

  • user_id (integer, 4바이트)

첫 번째 칼럼은 4바이트 정수입니다. 다음은 가변 길이의 text입니다. text 데이터 타입은 1워드 정렬이 필요하며, 64비트 플랫폼에서 1워드는 8바이트입니다. 정렬 요구 사항을 충족하기 위해 첫 번째 칼럼 바로 뒤에 4개의 0이 추가되므로, id는 4바이트를 차지하고, 이후 4바이트의 정렬 패딩이 추가된 다음에야 name이 저장됩니다. 따라서 이 경우 4바이트 정수를 저장하는 데 8바이트가 사용됩니다.

행과 행 사이의 공간도 정렬 패딩의 영향을 받습니다. user_id 칼럼은 4바이트만 사용하지만, 64비트 플랫폼에서는 다음 행이 "깔끔한" 워드로 시작할 수 있도록 4개의 0이 정렬 패딩으로 추가됩니다.

결과적으로 각 칼럼의 실제 크기는 (가변 길이 데이터와 24바이트 튜플 헤더 제외) 8바이트, 가변, 8바이트가 됩니다. 이는 두 개의 4바이트 정수에 대해 각 행이 최소 16바이트를 필요로 한다는 의미입니다. 테이블에 행이 몇 개 없다면 문제가 되지 않지만, 수백만 건의 행을 저장하기 시작하면 순서를 바꾸어 공간을 절약할 수 있습니다. 위의 예시에서 이상적인 칼럼 순서는 다음과 같습니다:

  • id (integer, 4바이트)

  • user_id (integer, 4바이트)

  • name (text, 가변)

또는

  • name (text, 가변)

  • id (integer, 4바이트)

  • user_id (integer, 4바이트)

이 예시에서 id와 user_id 칼럼이 함께 묶이므로, 두 칼럼을 저장하는 데 8바이트만 필요합니다. 즉, 각 행이 8바이트 적은 공간을 차지하게 됩니다.

Ruby on Rails 5.1부터 ID의 기본 데이터 타입은 8바이트를 사용하는 bigint입니다. 위 예시에서는 더 현실적인 재정렬 시나리오를 보여주기 위해 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 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 테이블처럼 큰 테이블에서는 중요해집니다. 예를 들어, 8,000만 건의 행을 저장할 경우 단순히 칼럼 순서만 바꾸어도 최소 610MB의 공간을 절약할 수 있습니다.

PostgreSQL에서 테이블 칼럼 순서 지정

GitLab v19.1
원문 보기
요약

GitLab에서는 새로운 테이블의 칼럼이 최소한의 공간을 사용하도록 순서를 정하도록 요구합니다. C 구조체와 마찬가지로 테이블의 공간은 칼럼 순서에 따라 달라집니다. user_id (integer, 4바이트) 첫 번째 칼럼은 4바이트 정수입니다.

GitLab에서는 새로운 테이블의 칼럼이 최소한의 공간을 사용하도록 순서를 정하도록 요구합니다. 이를 위한 쉬운 방법은 타입 크기를 기준으로 내림차순으로 정렬하되, 가변 크기(text, varchar, 배열, json, jsonb 등)를 마지막에 배치하는 것입니다.

C 구조체와 마찬가지로 테이블의 공간은 칼럼 순서에 따라 달라집니다. 이는 칼럼의 크기가 다음 칼럼의 타입에 따라 정렬되기 때문입니다. 다음 예시를 살펴보겠습니다:

  • id (integer, 4바이트)

  • name (text, 가변)

  • user_id (integer, 4바이트)

첫 번째 칼럼은 4바이트 정수입니다. 다음은 가변 길이의 text입니다. text 데이터 타입은 1워드 정렬이 필요하며, 64비트 플랫폼에서 1워드는 8바이트입니다. 정렬 요구 사항을 충족하기 위해 첫 번째 칼럼 바로 뒤에 4개의 0이 추가되므로, id는 4바이트를 차지하고, 이후 4바이트의 정렬 패딩이 추가된 다음에야 name이 저장됩니다. 따라서 이 경우 4바이트 정수를 저장하는 데 8바이트가 사용됩니다.

행과 행 사이의 공간도 정렬 패딩의 영향을 받습니다. user_id 칼럼은 4바이트만 사용하지만, 64비트 플랫폼에서는 다음 행이 "깔끔한" 워드로 시작할 수 있도록 4개의 0이 정렬 패딩으로 추가됩니다.

결과적으로 각 칼럼의 실제 크기는 (가변 길이 데이터와 24바이트 튜플 헤더 제외) 8바이트, 가변, 8바이트가 됩니다. 이는 두 개의 4바이트 정수에 대해 각 행이 최소 16바이트를 필요로 한다는 의미입니다. 테이블에 행이 몇 개 없다면 문제가 되지 않지만, 수백만 건의 행을 저장하기 시작하면 순서를 바꾸어 공간을 절약할 수 있습니다. 위의 예시에서 이상적인 칼럼 순서는 다음과 같습니다:

  • id (integer, 4바이트)

  • user_id (integer, 4바이트)

  • name (text, 가변)

또는

  • name (text, 가변)

  • id (integer, 4바이트)

  • user_id (integer, 4바이트)

이 예시에서 id와 user_id 칼럼이 함께 묶이므로, 두 칼럼을 저장하는 데 8바이트만 필요합니다. 즉, 각 행이 8바이트 적은 공간을 차지하게 됩니다.

Ruby on Rails 5.1부터 ID의 기본 데이터 타입은 8바이트를 사용하는 bigint입니다. 위 예시에서는 더 현실적인 재정렬 시나리오를 보여주기 위해 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 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 테이블처럼 큰 테이블에서는 중요해집니다. 예를 들어, 8,000만 건의 행을 저장할 경우 단순히 칼럼 순서만 바꾸어도 최소 610MB의 공간을 절약할 수 있습니다.