DB 인사이드 | PostgreSQL Data Alignment - Introduction에 이어 본 문서에서는 실제로 Data Alignment을 위한 Column 위치 최적화 과정을 기술하며, 그 결과 스토리지 사용량 변화를 확인해 보겠습니다.
1. 데이터 정렬을 위한 Column 위치 최적화
데이터 정렬을 위한 Column 위치 최적화는 불필요한 패딩(Padding) 사용을 감소시켜 공간 낭비를 최소화하고 검색 성능 향상에 목적이 있습니다. pg_type Catalog의 typalign과 typlen을 활용하여 최적화된 Column 위치를 지정할 수 있습니다.
Column 정렬 규칙
Column 정렬은 고정 크기의 Data Type을 먼저 배치한 후 가변 크기의 Data Type을 배치합니다.
고정 크기의 Data Type 정렬 규칙
고정 크기의 Data Type은 pg_type.typlen > 0 것을 의미합니다.
- pg_type.typlen의 값이 큰 Data Type 순서로 pg_type.typalign의 값이 d → i → s → c 순으로 Column을 배치합니다. pg_type.typlen이 음수의 경우 가변 크기를 의미하므로 이 단계에 포함되지 않습니다.
- char Data Type과 같이 고정 크기의 문자형 컬럼은(pg_type.typalign = c) 크기가 큰 순서로 Column을 배치합니다.
가변 크기의 Data Type 정렬 규칙
가변 크기의 Data Type은 pg_type.typlen = -1것을 의미합니다.
- numeric, decimal과 같은 숫자형 가변 크기 Column을 먼저 배치합니다.
- varchar와 같은 문자형 가변 크기 Column을 배치합니다.
- text, bytea, jsonb, xml과 같은 긴 문자열 가변 크기 Column을 배치합니다. (길이의 변동이 가장 크고, TOAST에 저장될 가능성이 높음)
Column 정렬 규칙 정리
| 순서 | pg_type.typlen 조건 | pg_type.typalign 조건 | 세부 조건 | 예시 Data Type |
| 1 | 양수 | d | pg.type.typlen 값이 큰 순서로 배치 | timetz, bigint, timestamp |
| 2 | 양수 | i | pg.type.typlen 값이 큰 순서로 배치 | macaddr8, regclass, int |
| 3 | 양수 | s | pg.type.typlen 값이 큰 순서로 배치 | tid, smallint |
| 4 | 양수 | c | pg.type.typlen 값이 큰 순서로 배치 | name, char(50), char(2), boolean |
| 5 | -1 | i | 숫자형 가변 크기 Data Type | numeric, decimal |
| 6 | -1 | i | 문자형 가변 크기 Data Type | varchar |
| 7 | -1 | i | 긴 문자열 가변 크기 Data Type | text, bytea, jsonb, xml |
가변 길이 Data Type과 TOAST 테이블
가변 크기 Data Type을 고정 크기 Data Type 이후에 배치하는 이유를 확인해 보겠습니다.
가변 크기 문자열 Data Type의 경우 Tuple 헤더와는 별도로 데이터 헤더가 존재합니다. 데이터 헤더는 저장하는 데이터 길이가 126개 까지는 1Byte가 추가되고, 127개부터는 4Byte가 추가됩니다.
CREATE TABLE tt1 ( c1 text ) ;
INSERT INTO tt1 SELECT REPEAT( 'A' , 126 ) ;
INSERT INTO tt1 SELECT REPEAT( 'A' , 127 ) ;
SELECT LENGTH( tt1.c1 ) AS word_length ,
pg_column_size( ROW( tt1.* ) ) AS total_byte ,
pg_column_size( ROW( tt1.* ) ) - 24 AS exclude_tuple_header_byte ,
pg_column_size( ROW( tt1.* ) ) - 24 - LENGTH( tt1.c1 ) AS data_header_byte
FROM tt1 ;
word_length | total_byte | exclude_tuple_header_byte | data_header_byte
-------------+------------+---------------------------+------------------
126 | 151 | 127 | 1
127 | 155 | 131 | 4또한, Tuple의 크기가 큰 경우(보통 2KB), typlen=-1, typstorage=x에 따라서 해당 Data Type의 Column 데이터는 압축이 되며, 압축 후에도 Tuple의 크기가 크다면 TOAST 테이블에 저장하게 됩니다. 데이터가 TOAST 테이블에 저장되면 실제 테이블에는 TOAST에 대한 정보가 저장됩니다.
INSERT INTO tt1 SELECT REPEAT( 'A' , 1024 ) ;
INSERT INTO tt1 SELECT REPEAT( 'A' , 2048 ) ;
INSERT INTO tt1 SELECT REPEAT( 'A' , 3072 ) ;
SELECT LENGTH( tt1.c1 ) AS word_length ,
pg_column_size( ROW( tt1.* ) ) AS total_byte ,
pg_column_size( ROW( tt1.* ) ) - 24 AS exclude_tuple_header_byte
FROM tt1 ;
word_length | total_byte | exclude_tuple_header_byte
-------------+------------+---------------------------
126 | 151 | 127
127 | 155 | 131
1024 | 1052 | 1028
2048 | 59 | 35위의 결과로 가변 크기 Data Type은 일정 크기 이상이 되면 TOAST에 저장될 가능성이 크기 때문에 고정 크기 Data Type 이후에 배치하여 길이의 변동성에 대응할 수 있습니다.
2. 실제 예시
지금까지 Data Type에 따른 Column 순서에 따라 Tuple 크기의 변화가 있다는 것을 확인하였습니다. 본 장에서는 데이터가 많을 때, Column 순서 고려 여부에 따른 테이블 크기를 비교해 보겠습니다.
먼저, 아래와 같이 구성되어 있는 테이블을 생성합니다.
CREATE TABLE default_col (
send_status boolean ,
send_date timestamp ,
receive_status boolean ,
receive_date timestamp ,
id int
) ;테이블 생성문으로 Tuple당 필요한 크기는 Tuple 헤더 24Byte를 제외하고 데이터 정렬을 위한 패딩(Padding)을 포함하여 36Byte가 필요하다고 추측해 볼 수 있습니다. 실제로 36Byte가 필요한지 데이터를 입력한 후 확인해 보겠습니다.
-- default_col 테이블
SELECT pg_column_size( ROW( 't'::boolean , now()::timestamp , 't'::boolean , now()::timestamp , 1::int ) ) - 24 AS size ;
size
------
36
1,000만 건의 데이터를 입력합니다.
INSERT INTO default_col ( send_status , send_date , receive_status , receive_date , id )
SELECT TRUE , NOW() , TRUE , NOW() , 1111111111
FROM generate_series( 1 , 10000000 ) ;앞서 Tuple당 필요한 크기를 확인해 보기 위해 pageinspect Extension을 통해 실제 데이터가 어떻게 저장되어 있는지 확인합니다.
-- default_col Table
SELECT lp_len - 24 AS length , t_data FROM heap_page_items( get_raw_page( 'default_col' , 'main' , 0 ) ) gx ;
length | t_data
--------+----------------------------------------------------------------------------
36 | \x0100000000000000ec1efdf2f3d102000100000000000000ec1efdf2f3d10200c7353a42
36 | \x0100000000000000ec1efdf2f3d102000100000000000000ec1efdf2f3d10200c7353a42
36 | \x0100000000000000ec1efdf2f3d102000100000000000000ec1efdf2f3d10200c7353a42
... 이하생략 ...default_col 테이블은 Tuple 헤더 24Btye를 제외하고 36Byte의 크기로 저장되었습니다. t_date 결과는 아래와 같이 구분됩니다. 테이블 생성문으로 예상해 본 Tuple의 크기와 동일합니다.
데이터 정렬에 따른 불필요한 패딩(Padding)을 최소화하여 테이블 크기를 감소시키기 위해 default_col 테이블의 정렬 정보를 확인합니다.
SELECT isc.column_name AS column_name ,
isc.data_type AS data_type ,
pt.typname AS typname ,
pt.typalign AS typalign ,
pt.typlen AS typlen
FROM pg_class pc
JOIN information_schema.columns isc ON ( isc.table_schema = pc.relnamespace::regnamespace::text AND isc.table_name = pc.relname )
JOIN pg_type pt ON ( pt.typname = isc.udt_name )
WHERE 1 = 1
AND pc.relname = 'default_col'
AND pc.relnamespace::regnamespace::text = 'public'
ORDER BY isc.ordinal_position ;
column_name | data_type | typname | typalign | typlen
----------------+-----------------------------+-----------+----------+--------
send_status | boolean | bool | c | 1
send_date | timestamp without time zone | timestamp | d | 8
receive_status | boolean | bool | c | 1
receive_date | timestamp without time zone | timestamp | d | 8
id | integer | int4 | i | 4
(5 rows)1. 데이터 정렬을 위한 Column 위치 최적화에서 Column 정렬 규칙에 따라 default_col 테이블을 ordering_col 테이블로 재생성합니다.
CREATE TABLE ordering_col (
send_date timestamp ,
receive_date timestamp ,
id integer ,
send_status boolean ,
receive_status boolean
) ;테이블 생성문으로 Tuple당 필요한 크기는 Tuple 헤더 24Byte를 제외하고 22Byte가 필요하다고 추측해 볼 수 있습니다. Column 정렬 규칙에 따라 재생성한 ordering_col 테이블은 패딩(Padding)이 불필요합니다. 실제로 22Byte가 필요한지 데이터를 입력한 후 확인해 보겠습니다.
-- ordering_col 테이블
SELECT pg_column_size( ROW( now()::timestamp , now()::timestamp , 1::int , 't'::boolean , 't'::boolean ) ) - 24 AS size ;
size
------
22
default_col 테이블에 입력한 동일한 1,000만 건의 데이터를 입력합니다.
INSERT INTO ordering_col ( send_status , send_date , receive_status , receive_date , id )
SELECT TRUE , NOW() , TRUE , NOW() , 1111111111
FROM generate_series( 1 , 10000000 ) ;앞서 Tuple당 필요한 크기를 확인해 보기 위해 pageinspect Extension을 통해 실제 데이터가 어떻게 저장되어 있는지 확인합니다.
-- default_col Table
SELECT lp_len - 24 AS length , t_data FROM heap_page_items( get_raw_page( 'ordering_col' , 'main' , 0 ) ) gx ;
length | t_data
--------+------------------------------------------------
22 | \xfc9995ad80d20200fc9995ad80d20200c7353a420101
22 | \xfc9995ad80d20200fc9995ad80d20200c7353a420101
22 | \xfc9995ad80d20200fc9995ad80d20200c7353a420101
... 이하생략 ...ordering_col 테이블은 Tuple 헤더 24Btye를 제외하고 22Byte의 크기로 저장되었습니다. t_date 결과는 아래와 같이 구분됩니다. 테이블 생성문으로 예상해 본 Tuple의 크기와 동일합니다.
Tuple 크기의 변화에 따라 테이블의 크기를 비교하기 위해 Column 정렬을 하지 않은 테이블(default_col)과 Column 정렬을 한 테이블(ordering_col)의 테이블의 크기를 확인합니다.
SELECT pg_size_pretty( pg_relation_size( 'default_col' ) ) AS default_col ,
pg_size_pretty( pg_relation_size( 'ordering_col' ) ) AS ordering_col ;
default_col | ordering_col
-------------+--------------
651 MB | 498 MB1,000만 건의 데이터를 입력한 후 테이블 크기를 비교한 결과, Column 순서를 조정한 ordergin_col 테이블이 default_col 테이블에 비해 153MB 적게(약 25% 감소) 사용하는 것을 확인할 수 있습니다.
3. Column 순서 변경의 이점
-
스토리지 사용량 감소: 데이터 정렬 기준에 따른 Column 순서 변경으로 정렬에 사용되는 불필요한 패딩(Padding)을 최소화하여 스토리지 사용량을 감소시킬 수 있습니다. 위 예시에서는 615MB의 테이블을 데이터 정렬 기준에 따라 Column 순서를 변경한 후 498MB로 사용량이 약 25% 감소한 것을 확인할 수 있습니다. -
TOAST 활용 최적화: text, jsonb, xml과 같이 긴 문자열 가변 크기 데이터를 마지막에 배치함으로써 TOAST로 빠르게 저장될 수 있습니다. I/O 비용 절감: 스토리지 사용량 감소와 같은 이유로 하나의 Page에 더 많은 Row 데이터가 저장되어 I/O 비용이 절감되며 쿼리 성능 개선의 이점이 있습니다. 즉, 더 적은 Page 수로 더 많은 Row 데이터를 Caching 할 수 있습니다. 아래 EXPLAIN을 보면 default_col 테이블의 경우 83,334 Page의 I/O가 발생하며, ordering_col 테이블의 경우 63,695 Page의 I/O가 발생합니다.
EXPLAIN ( ANALYZE , BUFFERS , COSTS OFF )
SELECT * FROM default_col ;
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on default_col (actual time=0.059..7179.697 rows=10000000 loops=1)
Buffers: shared read=83334
... 생략 ...EXPLAIN ( ANALYZE , BUFFERS , COSTS OFF )
SELECT * FROM ordering_col ;
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on ordering_col (actual time=0.129..6775.119 rows=10000000 loops=1)
Buffers: shared read=63695
... 생략 ...
기획 및 글 | 플랫폼기술연구팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PostgreSQL Data Alignment - Introduction (0) | 2025.03.25 |
|---|---|
| DB 인사이드 | PWI - WAL Buffer > Wait Event (0) | 2025.02.11 |
| DB 인사이드 | PWI - WAL Buffer > WAL Record 저장 (0) | 2025.02.10 |
| DB 인사이드 | PostgreSQL New Feature - 17 Release (4) (0) | 2025.01.16 |
| DB 인사이드 | PostgreSQL New Feature - 17 Release (3) (0) | 2024.12.30 |
댓글