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가 추가됩니다.
또한, Tuple의 크기가 큰 경우(보통 2KB),
typlen=-1, typstorage=x에 따라서 해당 Data Type의 Column 데이터는 압축이 되며, 압축 후에도 Tuple의 크기가 크다면 TOAST 테이블에 저장하게 됩니다. 데이터가 TOAST 테이블에 저장되면 실제 테이블에는 TOAST에 대한 정보가 저장됩니다.위의 결과로 가변 크기 Data Type은 일정 크기 이상이 되면 TOAST에 저장될 가능성이 크기 때문에 고정 크기 Data Type 이후에 배치하여 길이의 변동성에 대응할 수 있습니다.
2. 실제 예시
지금까지 Data Type에 따른 Column 순서에 따라 Tuple 크기의 변화가 있다는 것을 확인하였습니다. 본 장에서는 데이터가 많을 때, Column 순서 고려 여부에 따른 테이블 크기를 비교해 보겠습니다.
먼저, 아래와 같이 구성되어 있는 테이블을 생성합니다.
테이블 생성문으로 Tuple당 필요한 크기는 Tuple 헤더 24Byte를 제외하고 데이터 정렬을 위한 패딩(Padding)을 포함하여 36Byte가 필요하다고 추측해 볼 수 있습니다. 실제로 36Byte가 필요한지 데이터를 입력한 후 확인해 보겠습니다.
1,000만 건의 데이터를 입력합니다.
앞서 Tuple당 필요한 크기를 확인해 보기 위해 pageinspect Extension을 통해 실제 데이터가 어떻게 저장되어 있는지 확인합니다.
default_col 테이블은 Tuple 헤더 24Btye를 제외하고 36Byte의 크기로 저장되었습니다. t_date 결과는 아래와 같이 구분됩니다. 테이블 생성문으로 예상해 본 Tuple의 크기와 동일합니다.
데이터 정렬에 따른 불필요한 패딩(Padding)을 최소화하여 테이블 크기를 감소시키기 위해 default_col 테이블의 정렬 정보를 확인합니다.
1. 데이터 정렬을 위한 Column 위치 최적화에서 Column 정렬 규칙에 따라 default_col 테이블을 ordering_col 테이블로 재생성합니다.테이블 생성문으로 Tuple당 필요한 크기는 Tuple 헤더 24Byte를 제외하고 22Byte가 필요하다고 추측해 볼 수 있습니다. Column 정렬 규칙에 따라 재생성한 ordering_col 테이블은 패딩(Padding)이 불필요합니다. 실제로 22Byte가 필요한지 데이터를 입력한 후 확인해 보겠습니다.
default_col 테이블에 입력한 동일한 1,000만 건의 데이터를 입력합니다.
앞서 Tuple당 필요한 크기를 확인해 보기 위해 pageinspect Extension을 통해 실제 데이터가 어떻게 저장되어 있는지 확인합니다.
ordering_col 테이블은 Tuple 헤더 24Btye를 제외하고 22Byte의 크기로 저장되었습니다. t_date 결과는 아래와 같이 구분됩니다. 테이블 생성문으로 예상해 본 Tuple의 크기와 동일합니다.
Tuple 크기의 변화에 따라 테이블의 크기를 비교하기 위해 Column 정렬을 하지 않은 테이블(default_col)과 Column 정렬을 한 테이블(ordering_col)의 테이블의 크기를 확인합니다.
1,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가 발생합니다.
함께 보면 좋은 아티클
