DB 인사이드 | PostgreSQL Data Alignment - Introduction

 

일반적으로 DBMS에서 테이블을 생성할 때 Column의 순서에 대한 고려는 크게 하지 않을 것입니다. 하지만, Column의 순서가 스토리지나 성능에 영향을 미친다면, Column의 순서를 고려해 볼 수 있을 것입니다. PostgreSQL은 데이터를 정렬하여 연속적으로 저장하기 때문에 Column의 순서가 스토리지나 성능에 영향을 미칠 수 있습니다. 상황에 따라 미치는 영향이 미비하여 무시할 수도 있고, Column의 수가 많거나 데이터가 많은 경우 미치는 영향이 커서 Column 순서를 고려해 볼 수 있습니다.

본 문서에서는 PostgreSQL에서 Column 순서를 고려해야 하는 이유와 Column 순서에 따른 데이터 정렬 과정, 이를 통한 테이블을 최적화하는 방법을 테스트를 통해 설명합니다.

📢 본 문서에서는 pageinspect Extension을 사용하여 Tuple 정보를 확인하는 과정이 포함되어 있습니다. Extension 설치는 DB 인사이드 | PostgreSQL Extension - Introduction에서 확인할 수 있습니다.

 

 

1. Row Size Check

PostgreSQL Document에서 모든 Row는 Table Row Layout에 따라서 24Byte 고정 크기의 HeapTupleHeaderData Layout을 포함하고 있어, Row의 가장 작은 크기는 24Byte입니다.

SELECT pg_column_size( ROW() ) ;
 pg_column_size
----------------
             24

📢 Table Row Layout (PostgreSQL Document)
PostgreSQL에서 Page는 데이터 저장소의 기본 단위입니다. 이 Page의 크기는 고정되어 있으며, 테이블 데이터가 여러 Page에 분산되어 저장됩니다. 모든 Page와 Row에는 데이터에 대한 메타 정보를 포함하는 헤더가 있습니다. PostgreSQL의 모든 테이블 Row는 동일한 방식으로 구성되며, 23Byte의 고정 크기 헤더와 1Byte의 사용자 데이터가 포함된 24Byte의 HeapTupleHeaderData Layout이 포함됩니다. HeapTupleHeaderData Layout은 아래와 같은 내용을 포함합니다.

Field	Type	Length	Description
t_xmin	TransactionId	4 Bytes	insert XID stamp
t_xmax	TransactionId	4 Bytes	delete XID stamp
t_cid	CommandId	4 Bytes	insert and/or delete CID stamp (overlays with t_xvac)
t_xvac	TransactionId	4 Bytes	XID for VACUUM operation moving a row version
t_ctid	ItemPointerData	6 Bytes	current TID of this or newer row version
t_infomask2	unit16	2 Bytes	number of attributes, plus various flag bits
t_infomask	unit16	2 Bytes	various flag bits
t_hoff	unit8	1 Bytes	offset to user data

 

그 다음 추가되는 Column에 대해 각 Column의 Data Type 길이만큼 공간을 차지합니다.

-- 예시 1)
-- 24Byte(Header) + 4Byte(int) = 28Byte
SELECT pg_column_size( ROW( 1::int ) ) ;
 pg_column_size
----------------
             28

-- 예시 2)
-- 24Byte(Header) + 8Byte(bigint) + 4byte(int) = 36Byte
SELECT pg_column_size( ROW( 1::bigint , 1::int ) ) ;
 pg_column_size
----------------
             36

위 예시의 결과로 확인할 수 있듯이 Column이 많은 Row일수록 더 많은 디스크 공간을 사용하게 됩니다.

• 예시 1 24Byte의 헤더 크기와 4Byte의 int형 Column 크기를 가지므로 28Byte(24Byte+4Byte)가 됩니다.
• 예시 2 24Byte의 헤더 크기와 8Byte의 bigint형 Column, 4Byte의 int형 Column 크기를 가지므로 36Byte(24Byte+8Byte+4Byte)가 됩니다.

예시 2에서 Column의 위치를 변경한 결과를 확인하면 아래 예시 3과 같습니다.

-- 예시 3)
-- 24Byte(Header) + 4Byte(int) + 8byte(bigint) + 4Byte(추가) = 40Byte
SELECT pg_column_size( ROW( 1::int , 1::bigint ) ) ;
 pg_column_size
----------------
             40

예시 2에서는 36Byte의 크기가 필요하지만, Column의 위치만 바꾼 예시 3은 40Byte의 크기가 필요한 것으로 확인됩니다. 4Byte의 크기가 추가적으로 더 필요하다는 것을 확인할 수 있습니다. 다른 Data Type에 대해서도 위와 같은 결과를 확인할 수 있습니다.

-- 예시 4-1) 24Byte(Header) + 8Byte(bigint) + 2byte(smallint) + 1byte(boolean) = 35Byte
SELECT pg_column_size( ROW( 1::bigint , 1::smallint , false::boolean ) ) ;
 pg_column_size
----------------
             35

-- 예시 4-2) 24Byte(Header) + 1Byte(boolean) + 2Byte(smallint) + 8Byte(bigint) + 5Byte(추가) = 40Byte
SELECT pg_column_size( ROW( false::boolean , 1::smallint , 1::bigint ) ) ;
 pg_column_size
----------------
             40

-- 예시 4-3) 24Byte(Header) + 1Byte(boolean) + 8Byte(bigint) + 2Byte(smallint) + 7Byte(추가) = 42Byte
SELECT pg_column_size( ROW( false::boolean , 1::bigint , 1::smallint ) ) ;
 pg_column_size
----------------
             42

예시 4는 사용된 Column의 Data Type과 개수는 동일하며, Column의 위치만 바뀌었습니다. ( bigint , smallint , boolean )으로 구성된 예시 4-1의 Row는 35Byte, ( boolean , smallint, bigint )로 구성된 예시 4-2의 Row는 40Byte, ( boolean , bigint , smallint )로 구성된 예시 4-3의 Row는 42Byte의 크기가 필요하다는 것을 확인할 수 있습니다. 가장 큰 크기를 필요로 하는 예시 4-3은 가장 작은 크기를 필요로 하는 예시 4-1 보다 약 20%의 디스크 공간을 더 사용하므로, PostgreSQL에서는 Data Type에 따른 Column의 순서가 디스크 사용량에 영향을 미칠 수 있다는 것을 예상할 수 있습니다.

 

PostgreSQL에서 Column들은 Data Type 정렬 요구 사항에 따라 정렬됩니다. 이때의 정렬은 각 Column의 데이터가 적절한 위치에서 시작되도록 하여 스토리지 및 접근 성능을 최적화하는 것을 목표로 합니다. 정렬을 위해 Column 데이터 사이에 빈 공간을 삽입하는 패딩(Padding)을 사용하여 데이터가 적절한 위치에서 시작하도록 합니다. 즉, 예시 3과 예시 4에서 추가되는 Byte는 PostgreSQL이 효율적인 메모리 접근과 접근 성능 최적화를 위해 데이터 정렬(Data Alignment)을 수행하면서 늘어난 크기입니다.

 

 

 

2. 데이터 정렬(Data Alignment)

PostgreSQL에서 Data Type의 정렬 요구 사항은 pg_type Catalog를 통해 확인할 수 있습니다. PostgreSQL 17 기준으로 pg_type Catalog에는 639개의 Data Type이 정의되어 있습니다.

SELECT COUNT(*) FROM pg_type ;
 count
-------
   639

Data Type에 따라 typalign, typlen, typstorage 값이 부여되어 있습니다.

📢 본 문서에서는 Data Type에 따른 데이터 정렬에 대해 설명하므로 typalign과 typlen을 중점적으로 확인하며, typstorage는 TOAST 저장 방식에 대한 내용으로 개념만 정의합니다.

 

typalign

typalign은 특정 Data Type을 저장할 때 요구되는 메모리 정렬 기준을 의미합니다. 여러 Data Type이 연속적으로 저장될 때 해당 Data Type이 지정된 경계에서 시작하도록 하기 위해서 앞에 패딩(Padding)이 삽입될 수 있습니다. PostgreSQL에서는 정렬 기준을 아래 표와 같이 4가지로 정의하고 있으며, 이로 인해 성능 최적화와 데이터 접근 속도 향상에 기여합니다.

값	설명	Data Type 에시
c	char alignment, no alignment needed (1byte).	char, boolean
s	short alignment (2byte).	smallint
i	int alignment (4byte)	int, real
d	double alignment (8byte)	bigint, double precision, timestamp

 

typlen

typlen은 Data Type의 크기를 정의합니다. 고정 길이의 Data Type은 해당 Data Type의 데이터를 저장하기 위한 크기를 표시합니다. 가변 길이의 Data Type은 일반적으로 -1이 표시되며, NULL로 종료되는 가변 길이의 Data Type은 -2로 표시됩니다.

 

typstorage

typstorage는 typlen이 -1로 표시되는 text나 bytea와 같은 가변 길이 Data Type에 적용되는 속성으로, 해당 Data Type이 TOAST에 쓰일 준비가 되어 있는지 여부와 기본적으로 적용할 저장 전략을 정의합니다. 아래 표와 같이 4가지로 정의하고 있습니다. TOAST는 큰 데이터를 효율적으로 관리하는 방법으로, 테이블 크기를 불필요하게 키우지 않으면서 성능을 최적화하는데 도움을 줍니다.

값	설명
p (plain)	데이터가 테이블에 직접 저장. 데이터 압축과 TOAST를 사용하지 않음. (가변 길이 Data Type이 아닌 경우 항상 이 값을 사용)
e (external)	데이터 압축을 수행하지 않고, 데이터가 일정 크기 이상일 경우 TOAST에 저장.
m (main)	데이터 압축을 수행한 후 데이터가 작을 경우 테이블에 직접 저장 또는 일정 크기 이상일 경우 TOAST에 저장. (마지막 수단)
x (extended)	데이터 압축을 먼저 수행하며 압축 후에도 데이터가 큰 경우 TOAST로 저장. (TOAST 저장이 가능한 Data Type의 일반적인 선택)

📢 데이터의 압축은 toast_compression_threshold에 의해 결정되며, TOAST에 저장되는 기준은 toast_tuple_threshold로 결정되며 일반적으로 2KB입니다.

 

 

위에서 typalign, typlen, typstorage 설명하였습니다. 자주 사용되는 Data Type에 대해 위 세 가지 항목을 확인해 보면 아래와 같습니다.

SELECT typname , typalign , typlen , typstorage
FROM   pg_type
WHERE  typname IN ( 'bool' , 'int2' , 'int4' , 'int8' , 'text' , 'varchar' , 'char' ) ;
 typname | typalign | typlen | typstorage
---------+----------+--------+------------
 bool    | c        |      1 | p
 char    | c        |      1 | p
 int2    | s        |      2 | p
 int4    | i        |      4 | p
 int8    | d        |      8 | p
 text    | i        |     -1 | x
 varchar | i        |     -1 | x

• bool(boolean)과 char Data Type은 고정 길이 1Byte를 필요로 하며, typstorage=p 이므로 데이터가 테이블에 저장됩니다.
• int2(smallint), int4(int), int8(bigint)은 각 Data type의 고정 길이(int2=2Byte, int4=4Byte, int8=8Byte)를 필요로 하며, typstorage=p 이므로 데이터가 테이블에 저장됩니다. int2는 정렬을 위해 2Byte가 필요하며(typalign=s), int4는 정렬을 위해 4Byte(typalign=i)가 필요합니다. int8은 정렬을 위해 8Byte(typalign=d)가 필요합니다.
• text와 varchar Data Type은 가변 길이로 typlen=-1로 표시됩니다. typstorage=x 이므로 Extended를 의미하며, Tuple의 크기가 2KB 이상이면 가변 길이 Data Type의 데이터가 압축되고 압축된 데이터 크기에 따라 테이블이나 TOAST에 저장됩니다.

 

 

 

3. 데이터 정렬에 따른 패딩(Padding)

PostgreSQL에서 Data Type의 정렬 요구 사항은 pg_type Catalog를 통해 확인하였습니다. 그 중 typalign과 typlen을 유심히 살펴봐야 합니다. 앞서 설명한 예시 2는 ( bigint , int )로 구성된 Row로 24Byte(헤더)를 제외하고 12Byte의 크기를 차지합니다.

-- 예시 2) 24Byte(헤더) 제외 : 8Byte + 4Byte = 12Byte
SELECT pg_column_size( ROW( 1::bigint , 1::int ) ) - 24 ;
 pg_column_size
----------------
             12

pg_type Catalog에서 bigint와 int Data Type에 대해 확인하면 bigint(int8) Data Type은 typlen=8로 크기가 8Byte이며, typalign=d로 정렬에 8Byte를 필요합니다. int(int4) Data Type은 typlen=4로 크기가 4Byte이며, typalign=i로 정렬에 4Byte를 필요합니다.

SELECT typname , typalign , typlen
FROM   pg_type
WHERE  typname IN ( 'int8' , 'int4' ) ;
 typname | typalign | typlen
---------+----------+--------
 int8    | d        |      8
 int4    | i        |      4

pg_type Catalog 결과로 앞에 bigint Data Type이 위치하고, 뒤에 오는 int Data Type은 정렬을 위해 4Byte가 필요합니다. 앞선 Data Type이 8Byte이기 때문에 정렬이 필요하지 않아 아래 그림과 같이 bigint Data Type 뒤에 int Data Type이 위치하게 됩니다.

 

예시 3은 ( int , bigint )로 구성된 Row로 24Byte(헤더)를 제외하고 16Byte의 크기를 필요로 합니다. 예시 2와 구성되어 있는 Data Type은 동일하지만, Column 위치가 다른 결과로 4Byte의 크기가 더 필요로 합니다.

-- 예시 3) 24Byte(헤더) 제외 : 4Byte + 8Byte + 4Byte추가 = 16Byte
SELECT pg_column_size( ROW( 1::int , 1::bigint ) ) - 24 ;
 pg_column_size
----------------
             16

앞에 int Data type이 위치하고, 뒤에 오는 bigint Data Type은 정렬을 위해 8Byte가 필요합니다. 하지만, 앞선 Data Type이 4Byte로 bigint Data Type의 8Byte를 정렬할 수 없기 때문에 정렬이 가능하도록 앞선 int Data Type에 4Byte의 패딩(Padding)을 추가합니다. 따라서 아래 그림과 같이 int Data Type 뒤에 4Byte 패딩(Padding)이 추가된 후 bigint Data Type이 위치하게 됩니다.

📢 패딩(Padding)이란
정렬을 위해 Column의 크기를 증가시키기 위해서 의미 없는 데이터(빈 공간)를 추가하는 것을 패딩(Padding)이라고 합니다.

 

예시 4는 ( bigint , smallint, boolean ) Data Type이 Column 순서에 따라 추가되는 패딩(Padding)의 수를 확인할 수 있습니다.

-- 예시 4-1) 8Byte(bigint) + 2byte(smallint) + 1byte(boolean) = 35Byte
SELECT pg_column_size( ROW( 1::bigint , 1::smallint , false::boolean ) ) - 24 ;
 pg_column_size
----------------
             11

-- 예시 4-2) 1Byte(boolean) + 2Byte(smallint) + 8Byte(bigint) + 5Byte(추가) = 40Byte
SELECT pg_column_size( ROW( false::boolean , 1::smallint , 1::bigint ) ) - 24 ;
 pg_column_size
----------------
             16

-- 예시 4-3) 1Byte(boolean) + 8Byte(bigint) + 2Byte(smallint) + 7Byte(추가) = 42Byte
SELECT pg_column_size( ROW( false::boolean , 1::bigint , 1::smallint ) ) - 24 ;
 pg_column_size
----------------
             18

pg_type Catalog에서 bigint, int, boolean Data Type에 대해 확인하면 bigint(int8) Data Type은 typlen=8로 크기가 8Byte이며, typalign=d로 정렬에 8Byte를 필요합니다. smallint(int2) Data Type은 typlen=2로 크기가 2Byte이며, typalign=s로 정렬에 2Byte를 필요합니다. boolean(bool) Data Type은 typlen=1로 크기가 1Byte이며, typalign=c로 정렬이 필요하지 않습니다.

SELECT typname , typalign , typlen
FROM   pg_type
WHERE  typname IN ( 'int8' , 'int4', 'bool' ) ;
 typname | typalign | typlen
---------+----------+--------
 bool    | c        |      1
 int8    | d        |      8
 int4    | i        |      4

 

예시 4를 그림으로 표현하면 아래와 같습니다.

• 예시 4-1은 ( bigint , smallint , boolean ) 순서로 데이터 정렬을 위한 패딩(Padding)이 필요하지 않습니다.
• 예시 4-2는 ( boolean , smallint , bigint ) 순서로 2Byte 정렬이 필요한 smallint Data Type을 위해 boolean Data Type 뒤에 1Byte의 패딩(Padding)과 8Byte 정렬이 필요한 bigint Data Type을 위해 smallint Data Type 뒤에 4Byte의 패딩(Padding)이 추가됩니다.
• 예시 4-3은 ( boolean , bigint , smallint ) 순서로 8Byte 정렬이 필요한 bigint Data Type을 위해 boolean Data Type 뒤에 7Byte의 패딩(Padding)이 추가됩니다.

 

위 예시를 통해 Column 순서에 따라 패딩(Padding)으로 인한 Tuple 크기 변화가 있다는 것을 확인하였습니다. 정렬을 고려하지 않은 다수의 Column이 있는 테이블은 패딩(Padding)으로 인해 Tuple 크기가 증가할 수 있고, 이는 테이블 크기를 증가시킵니다. 데이터가 많지 않다면 증가되는 차이는 미비하겠지만, 데이터가 많다면 증가되는 차이는 무시하지 못할 수준이 될 수 있습니다.

 

 

 

4. pageinspect Extension을 통한 패딩(Padding) 확인

예시 4의 그림을 통해 Data Type에 따른 Column 순서에 따라 Tuple 크기의 변화를 확인하였습니다. pageinspect Extension을 통해 실제로 패딩(Padding)이 어떻게 추가되었는지 확인해 볼 수 있습니다.

📢 pageinspect Extension에서 get_raw_page Function으로 얻은 Heap Page를 heap_page_items Function을 통해 Tuple의 원시 데이터(t_data)와 Tuple의 크기(lp_len)를 확인합니다. t_data의 결과는 16진수로 표현되기 때문에 두 자리를 1Byte로 계산해야 합니다. 추가되는 패딩(Padding)은 00으로 표시되므로 실제 데이터와 구분하기 위해 아래 테스트 데이터는 Data Type이 허용하는 최대값을 사용합니다.

-- 예시 4-1
CREATE TABLE test01 ( c1 bigint ,  c2 smallint , c3 boolean ) ;
INSERT INTO test01 VALUES ( 9223372036854775807 , 32767 , 't' ) ;

SELECT lp_len - 24 AS length , t_data FROM heap_page_items( get_raw_page( 'test01' , 'main' , 0 ) ) gx ;
 length |          t_data
--------+--------------------------
     11 | \xffffffffffffff7fff7f01

예시 4-1은 24Byte(헤더)를 제외하고 11Byte의 크기를 필요로 하며, t_date 결과에서 \x를 제외하고 ffffffffffffff7f는 bigint, ff7f는 smallint, 01은 boolean인 것을 확인할 수 있고, 패딩(Padding)은 추가되지 않을 것을 확인할 수 있습니다.

 

-- 예시 4-2
CREATE TABLE test02 ( c1 boolean , c2 smallint , c3 bigint ) ;
INSERT INTO test02 VALUES ( 't' , 32767 , 9223372036854775807 ) ;

SELECT lp_len - 24 AS length , t_data FROM heap_page_items( get_raw_page( 'test02' , 'main' , 0 ) ) gx ;
 length |               t_data
--------+------------------------------------
     16 | \x0100ff7f00000000ffffffffffffff7f

예시 4-2은 24Byte(헤더)를 제외하고 16Byte의 크기를 필요로 하며, t_date 결과에서 \x를 제외하고 01은 boolean, ff7f는 smallint, ffffffffffffff7f는 bigint인 것을 확인할 수 있습니다. 또한, boolean과 smallint 사이에 1Byte, smallint와 bigint 사이에 4Byte의 패딩(Padding)이 추가된 것을 확인할 수 있습니다. 0100ff7f00000000ffffffffffffff7f

 

-- 예시 4-3
CREATE TABLE test03 ( c1 boolean , c2 bigint ,   c3 smallint ) ;
INSERT INTO test03 VALUES ( 't' , 9223372036854775807 , 32767 ) ;

SELECT lp_len - 24 AS length , t_data FROM heap_page_items( get_raw_page( 'test03' , 'main' , 0 ) ) gx ;
 length |                 t_data
--------+----------------------------------------
     18 | \x0100000000000000ffffffffffffff7fff7f

예시 4-3은 24Byte(헤더)를 제외하고 18Byte의 크기를 필요로 하며, t_date 결과에서 \x를 제외하고 01은 boolean, ffffffffffffff7f는 bigint, ff7f는 smallint인 것을 확인할 수 있습니다. 또한, boolean과 bigint 사이에 7Byte 패딩(Padding)이 추가된 것을 확인할 수 있습니다. 0100000000000000ffffffffffffff7fff7f

 

 

 

 

 

 

 

기획 및 글 | 플랫폼기술연구팀