들어가기 앞서
회사 내 DB 중에는 매월 약 40만 건 정도의 데이터가 누적되는 테이블이 존재한다. 게다가 10년동안 쌓여서 현재 약 4천만 건 정도 된다. 테라 단위의 빅데이터를 다루는 회사에 비할 바는 아니지만 테이블 하나에 조금 많은 양의 데이터를 저장하고 있는 것은 맞다. 가만히 놔두면 테이블이 점점 비대해져 아무 생각없이 쿼리를 날렸다가는 이도저도 못 하는 난처한 상황을 맞닥뜨리게 될 것이다.
매월 약 40만건씩 늘어나는 테이블을 어떻게 관리하고 있을까?
그렇다면 우리의 DB는 어떻게 되어 있을까?
호기심은 여기서부터 시작했다.
해당 테이블 DDL을 잠깐 보겠다.(실제와 다른 예시이다.)
DDL (Data Definition Language) 데이터 정의어 데이터베이스를 정의하는 언어를 말하며 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어이다.
create table dalichoi
(
id integer generated always as identity,
year smallint not null,
month smallint not null,
reg_date date not null,
... 생략 ...
)
partition by RANGE (reg_date, year);
create table dalichoi_y2024
partition of dalichoi
FOR VALUES FROM ('2024-01-01', '2024') TO ('2024-12-31', '2024');
... 생략 ...
create 명령이 끝나기 전 partition by RANGE (reg_date, year); 라는 명령어가 보인다.
제목에서 이미 언급했지만, 오늘 이야기의 주제는 바로 데이터베이스의 파티션이다. 대용량 테이블을 다루는 가장 기본적인 기법이기도 하고, 앞으로 비즈니스가 더 커지고 잘 될수록 자주 접하게 될테니 미리 준비해 본다. 참고로 DBA 수준까지는 아니지만 개인적으로 백엔드 개발자가 알고 있으면 좋겠다 싶은 정도까지만 내용을 준비했다.
💡 참고로 PostgreSQL의 테이블 파티셔닝은 상속 파티셔닝(Partitioning Using Inheritance)과 선언적 파티셔닝 (Declarative Partitioning)으로 나눌 수 있다. 과거 PostgreSQL은 부모 테이블과 자식 테이블 등 상속을 활용한 파티셔닝을 이용했으나, PostgreSQL 10버전(2017) 이후로는 선언적 파티셔닝을 통해 좀 더 쉽게 테이블 파티셔닝을 구현할 수 있게 됐다. 여기서는 선언적 파티셔닝에 대해서만 다룬다.
파티셔닝(Partitioning)이란?
Partition이란 분할하다, 나누다라는 뜻을 가지고 있는 단어로, 데이터베이스에서 파티셔닝이란 논리적으로 하나의 큰 테이블을 더 작은 물리적 조각으로 나누는 것을 말한다. 마치 하나의 테이블인 것 처럼 보이지만, 내부적으로는 물리적으로 특정 조건에 따라 나뉘어 있는 테이블을 파티션 테이블이라고 한다.
파티셔닝 종류
파티션의 종류는 크게 2가지로 구분된다. 바로 수직 파티셔닝과 수평 파티셔닝이다.
수직 파티셔닝(Vertical Partitioning)
Column을 기준으로 테이블을 나누는 방식(정규화 결과도 해당)이다. 데이터의 일부 열을 빼내는 형태로 분할하기때문에 정규화도 수직 파티셔닝과 관련된 과정이라고 할 수 있다. 하지만 기본적으로 수직 파티셔닝은 이미 정규화된 데이터를 분리하는 과정이라고 생각하면 된다.
수평 파티셔닝(Horizontal Partitioning)
Row를 기준으로 테이블을 나누는 방식이다. 주로 성능이나 가용성을 위해 기준점이 되는 Key를 기준으로 여러 곳에 분산 저장한다. 일반적으로 분산 저장 기술에서 파티셔닝은 수평 분할을 의미한다. 회사의 해당 테이블에서 사용하고 있는 방식도 이 수평 파티셔닝이기 때문에 이 방식을 중점으로 더 알아보도록 하겠다.
왜 나누는 걸까?
1. 성능상의 이점
대용량의 테이블을 스캔할 때 모든 데이터를 대상으로 하기보다 특정 파티션을 대상으로 좁힌다면 훨씬 빠른 조회 성능을 발휘 할 수 있다. 즉 디스크 읽기 비용을 줄인다. 데이터 양이 아무리 많아도 대부분의 쿼리가 특정 하위 파티션 테이블로 한정 지을 수 있기 때문에 쿼리 실행 비용을 최적화 할 수 있게 된다. 바로 이렇게 해당 파티션만 읽고 대상이 아닌 파티션은 스캔하지 않는 것을 파티션 프루닝 Partition Pruning이라고 한다. 참고로 PostgreSQL DB는 enable_partition_pruning = on 이라는 옵션이 있는데 기본이 on 으로 설정되어 있다. off로 변경하면 모든 파티션을 스캔하게 된다.
Pruning 이라는 단어는 ‘가지치기’라는 의미가 있다. 여담이지만, 도커에도 prune이라는 단어가 등장한다. 도커에서는 명령어로 사용되고 있는데, 사용하지 않는 컨테이너나 이미지 등을 제거할 때 사용한다.
2. 관리의 편의성
필요없는 데이터를 한 번에 지울 수 있다. 예를 들어 2024년 데이터가 잘못되어 통으로 삭제라도 한다 가정하면 데이터의 변경을 기록하는 REDO나 UNDO 등 여러가지 작업들로 수행 시간이 한참 걸릴 수 있다. 하지만 연도별로 파티셔닝이 되어 있다면 특정 파티션만 drop 하면 훨씬 빠르게 정리할 수 있다. 그리고 특정 파티션을 복사하여 해당 파티션만 편집 수행을 한 뒤 운영 서버에 교체하는 방식으로도 작업이 가능하다.
💡 UNDO, REDO?
UNDO는 작업 롤백과, 읽기 일관성, 복구를 한다. REDO와 UNDO는 복구 방법에 차이가 있지만, 공통점은 복구를 한다는 것이다.
REDO는 ‘다시 하다’라는 뜻을 가지고 UNDO는 "원상태로 돌리다" 라는 뜻을 가지고 있다. 즉 REDO는 무언가를 다시 하는 것이고 UNDO는 무언가를 되돌리는 것이다. REDO는 기본적으로 복구의 역할을 한다. 서버에 무슨 작업을 하든지 모두 REDO에 기록이 된다. (UNDO 포함)
3. vacuum 사고 방지
이 말을 이해하려면 Vacuum이라는 생소한 단어를 또 설명해야 될 거 같은데, 여기서는 간략하게 개념만 짚고 넘어 가겠다. 참고로 vacuuming 이라는 단어는 '청소기 돌리기'라는 뜻으로 이것을 데이터베이스 용어로 사용한 경우이다.
Vacuum은 PostgreSQL MVCC의 독특한 구현 방식 덕분에 탄생한 개념으로 데이터베이스의 성능과 저장 공간을 관리하기 위한 작업이다. PostgreSQL은 데이터를 삭제하거나 업데이트 해도 기존 데이터를 즉시 지우지 않고, 사용하지 않는(dead) 상태로 표시한다. 이렇게 하면 동시성을 높일 수 있지만, 시간이 지나면서 죽은 튜플(dead tuples)이 쌓여 디스크 공간을 차지하고 쿼리 성능이 저하되게 된다. 그래서 주기적으로 사용하지 않는 죽은 튜플을 제거하여 공간을 확보한다거나 새 데이터를 삽입할 때 사용할 수 있는 공간을 추적하고, 트랜잭션ID 겹침 방지 작업 등 부수적인 일을 해야 하는데, 바로 이게 vacuum의 대표적인 역할이다. PostgreSQL이 자동으로 vacuum을 수행하기(Autovacuum 데몬)때문에 일반적으로 수동으로 실행 할 필요는 없다.
파티션으로 나누지 않은 아주 큰 단일 테이블은 Vacuum 자체의 성능도 나빠질 뿐만 아니라 Autovacuum 역시 아주 오랜 시간에 걸쳐 작업이 이뤄질 것이다. 만약 테이블을 전체 잠그고 테이블을 완전히 재작성 해서 공간을 재구성하는 작업이라도 하는 경우에는 최악의 사고가 발생할 수도 있다.
파티션 키
파티션을 분할하기 위해서는 파티션 키가 필요하다. 이 파티션 키는 테이블을 분할하기 위해 기준으로 삼는 키 컬럼 또는 컬럼 집합을 말한다. 위 회사 내 테이블 예시를 기준으로 보면 ‘reg_date, year’ 컬럼 집합에 해당된다.
파티션 키를 선정하는 기준
- 해당 컬럼이 가지는 값의 범위가 명확한 게 좋다. 대개 varchar, number, date 타입의 컬럼으로 파티셔닝 한다. 파티션 키에 대해서는 NULL을 허용하나 가능한 한 NOT NULL 컬럼이 좋다. 파티션 테이블은 반드시 모든 데이터가 특정 파티션에 매핑되어야 하므로, NULL 값을 저장하려면 기본 파티션(Default Partition)을 설정해야 한다. 파티션 키가 되는 컬럼에 NULL이 들어오면 해당 레코드는 설정해 둔 DEFAULT 파티션으로 들어가게 된다.
- 파티션 사이즈가 예측 가능한 컬럼이 좋다.
- 데이터 분포가 고르고, 상대적으로 많이 엑세스되는 컬럼이 좋다.
- 파티션 컬럼은 한 개 이상 복합키 구성이 가능하다.
CREATE TABLE sales (
sale_id serial,
sale_date date,
amount numeric
) PARTITION BY RANGE (sale_date);
-- 특정 범위 파티션
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
-- 기본 파티션 (NULL 처리 포함)
CREATE TABLE sales_default PARTITION OF sales
DEFAULT;언제 사용하는 게 좋을까?
테이블 크기 증가로 인한 성능 문제
테이블이 크면 인덱스 스캔, 시퀀셜 스캔이 느려질 수 있다. 일반적으로 테이블 크기가 서버가 감당하기 힘든 수준을 넘어가면 성능 문제를 방지하기 위해 파티셔닝을 고려한다.
일정 필드를 기준으로 데이터가 잘 구분되는 경우
날짜나 ID와 같이 일정한 기준으로 데이터가 나뉘어 있다면, 이를 파티션 키로 사용해 쿼리 효율을 높일 수 있다.
빈번한 데이터 삭제 또는 아카이빙이 필요한 경우
특정 기간의 데이터만 보관하고 오래된 데이터를 삭제하는 패턴이라면, 파티셔닝을 통해 필요한 파티션만 삭제하여 테이블 잠금과 전체 테이블 스캔을 피할 수 있다.
The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server. https://www.postgresql.org/docs/current/ddl-partitioning.html
작은 테이블에 하면 오히려 오버헤드만 더 커지기 때문에 어느 정도 큰 테이블에 하는 게 좋다. 한 테이블이 2GB 이상이라면 고려한다는 얘기도 있고, 수십 GB 이상이 예상될 때 고려한다는 얘기도 있는데, 서버의 사양에 따라 테이블이 크다는 기준은 모두 다를 것이다. Postgresql 공식 문서에서는 서버 메모리보다 테이블이 클 경우 테이블 파티셔닝을 권장하고 있다. 결국 각자의 서버 사양이 다르기 때문에 각자의 사양과 목적에 맞게 적절한 테이블 파티셔닝을 고려하는 게 좋을 것 같다.
- 참고) PostgreSQL 테이블 사이즈 조회하는 방법
-- 인덱스 포함
select pg_size_pretty(pg_total_relation_size('unv_manpower_y2022'));
-- 1746 MB
-- 인덱스 미포함
select pg_size_pretty(pg_relation_size('unv_manpower_y2022'));
-- 1040 MB
-- 사이즈가 큰 테이블 조회
select A.relname as TABLE_NAME,
A.oid as OID,
A.relfilenode as FILE_NAME,
pg_relation_size(A.oid)/1024/1024 as "FILE_SIZE(MB)"
from pg_class A
order by 4 desc
limit 50;
수평 파티셔닝(Horizontal Partitioning) 테이블 종류
1. Range Partitioning(범위 분할)
글 상단의 예시 테이블처럼 연속적인 숫자나 날짜를 기준으로 파티셔닝 하는 방법이다. 파티션 키 값이 range 내 존재하는지 여부로 구분한다. 우편번호나 날짜 등 일정한 기준과 간격을 가진 데이터에 적합하다.
CREATE TABLE message (
id serial,
created_date date not null,
title varchar,
contents text
) PARTITION BY RANGE (created_date);
CREATE TABLE message_y2024m11 PARTITION OF message
FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE message_y2024m10 PARTITION OF message
FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
- 포인트는 'partition by range' 와 'partition of' 이다.
2. List Partitioning(목록 분할)
여러 데이터가 균등하게 분포되어 있을 때 특정한 값(‘국가’나 ‘도시’ 등)을 골라서 분할하는 방법이다. 순서에 맞지 않고 연관되지 않은 데이터를 그룹화 할 수 있다.
CREATE TABLE employee (
id serial,
name varchar(50) not null,
age integer,
job character varying(50) not null
) PARTITION BY LIST (job);
CREATE TABLE employee_developer PARTITION OF employee
FOR VALUES IN ('Senior Developer', 'Intermediate Developer', 'Junior Developer');
CREATE TABLE employee_analyst PARTITION OF employee
FOR VALUES IN ('Senior Analyst', 'Junior Analyst', 'Data Analyst', 'Data Scientist');
3. Hash Partitioning(해시 분할)
파티션 키의 해시 값에 의한 분할 방법이다. 데이터가 어느 한 파티션에 치우치지 않고 균등한 분할이 가능하다. 하지만 특정 데이터가 어느 해시 파티션에 존재하는지 판단하기 어렵다. 파티션을 위한 특정한 범위가 없거나 한쪽으로 데이터가 치우치는 경향을 보일 때 사용하기 적합하다.
PostgreSQL에서는 Modulus와 Remainder를 지정하여 파티셔닝을 한다. 각 파티션 테이블은 파티션 키의 해시 값이 지정된 Modulus로 나누어서 지정된 나머지가 되는 행들을 보유하게 된다. 예를 들어 파티션마다 모듈러가 4고 나머지가 0,1,2,3인 경우 파티션 키의 해시 값이 10인 경우 4로 나눈 나머지는 2가 되므로 2인 파티션에 들어가게 된다. 해시 값이 12인 경우 4로 나누면 0이 되므로 0인 파티션에 들어가게 된다. 이 로직을 통해 골고루 분배를 하며 데이터 액세스 성능을 올릴 수 있다.
-- 1. 마스터 테이블 생성
CREATE TABLE orders (
order_id SERIAL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL
) PARTITION BY HASH (customer_id);
-- 2. 파티션 테이블 생성
CREATE TABLE orders_p0 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_p1 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_p2 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_p3 PARTITION OF orders
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
-- 3. 데이터 삽입 (자동으로 적절한 파티션에 들어감)
INSERT INTO orders (customer_id, order_date, amount)
VALUES
(1, '2024-11-01', 100.50),
(2, '2024-11-02', 200.00),
(3, '2024-11-03', 300.25),
(4, '2024-11-04', 400.75);
-- 4. 데이터 확인 (특정 파티션에 들어간 데이터 확인 가능)
SELECT * FROM orders_p0;
SELECT * FROM orders_p1;
SELECT * FROM orders_p2;
SELECT * FROM orders_p3;
4. Composite Partitioning(합성 분할)
위에서 언급했던 분할 방법들을 복합적으로 사용하는 것을 말한다. 즉, 파티션의 Sub-partitioning을 의미한다. 큰 파티션에 대한 I/O 요청을 여러 파티션으로 분산할 수 있다. 예를 들면 범위로 먼저 분할하고, 그 다음 해시를 통해 균등하게 분산시키는 것을 생각해 볼 수 있다.
파티션 인덱스
대용량 테이블의 인덱스 역시 파티셔닝 할 수 있다. 인덱스 파티셔닝에 사용되는 키가 테이블 파티션 키와 동일하면 Local partitioned index, 줄여서 Local index라고 말한다. 그리고 개별 파티션과 연결되는 인덱스 파티션이 아니라 전체 파티션에 걸쳐 고유성을 보장하는 인덱스를 Global partitioned index, 줄여서 Global index라고 한다. 하지만 PostgreSQL은 글로벌 인덱스는 지원하지 않는다.
PostgreSQL은 각 파티션에 대해 별도의 인덱스를 생성하고 관리할 수 있는 파티션 Partial 인덱스를 제공한다. 파티션 마다 다른 인덱스(B*Tree, Covering Index, BRIN 등)를 생성할 수도 있다.
꼭 기억해야 할 내용
1. 파티션 프루닝 where 조건절에 반드시 넣기
파티션 테이블을 조회할 때 주의해야 할 점은 원하는 하위 테이블만 조회하기 위한 ‘파티션 프루닝’ 작업이 실행 계획 전에 먼저 일어난다는 점이다.
단일 테이블에서 성능이 잘 나오던 SQL이 느닷없이 느려지는 경우도 그 원인을 찾기 위해 실행 계획을 보면, 거의 대부분 파티션 프루닝 작업에서 모든 하위테이블을 대상으로 해 버리는 경우에 발생한다. 특히 파티션 테이블과 파티션 테이블의 join 작업에서 예상치 않게 비효율적인 실행 계획이 만들어 진다.
이를 해결하기 위해서는 파티션 테이블을 사용하는 SQL 구문을 작성할 때, 반드시 의도한 대로 파티션 테이블만 잘 참조하는지 explain 명령으로 꼭 확인하는 것 이다. where 조건절에 반드시 해당 파티션 키 조건을 추가해서 효율적으로 동작하도록 챙겨주기 바란다.
explain SELECT *
FROM dalichoi
WHERE reg_date between '2024-01-01' and '2024-12-31';
다른 테이블과 join을 할 경우에도 파티션 프루닝 적용됨
explain select * from company c
inner join dalichoi d on d.id = c.id
where d.reg_date between '2024-01-01' and '2024-12-31';- 이 외 파티션 프루닝 시 주의사항(참고용)
- 예상과 달리, 파티션 Pruning이 동작하지 않는 경우가 존재한다.
- IN 절 내에 Subquery를 사용할 경우에는 파티션 Pruning이 동작하지 않는다.
- IN 절 사용시 파티션 Pruning을 유도하기 위해서는 실제 CHECK 조건을 입력해야 한다.
- NL 조인으로 수행되면 추가 조건 없이도 파티션 Pruning이 수행된다.
- Hash 조인으로 수행되면 추가 조건을 반드시 입력해야 한다. 따라서, 쿼리 작성 후에 Explain 결과 확인 및 추가 조건을 입력할 필요가 있다.
- Hash 조인으로 수행될 때를 고려해서, 상수 조건을 추가하는 것을 습관화 하는 것이 좋다.
2. PK 또는 Unique 제약 조건을 설정하려면, 파티션 키도 포함할 것
PostgreSQL에서 파티셔닝된 테이블에 PRIMARY KEY 또는 UNIQUE 제약 조건을 설정하려면, 언제나 파티션의 기준이 되는 컬럼(파티션 키)이 반드시 해당 제약 조건에 포함되어야 한다. 이는 파티션 내에서 고유성을 보장할 뿐만 아니라, 전체 테이블 수준에서도 고유성을 유지하기 위해 필요하다.
-- fail
CREATE TABLE message (
id serial PRIMARY KEY,
created_date date not null,
title varchar,
contents text
) PARTITION BY RANGE (created_date);
-- [0A000] 오류: unique constraint on partitioned table must include all partitioning columns
-- Detail: PRIMARY KEY constraint on table "message" lacks column "created_date" which is part of the partition key.
-- success
CREATE TABLE message (
id serial,
created_date date not null,
title varchar,
contents text,
PRIMARY KEY (id, created_date)
) PARTITION BY RANGE (created_date);
'개발 > DevOps' 카테고리의 다른 글
| 트랜잭션과 격리 수준(Isolation Level) (0) | 2024.12.08 |
|---|---|
| Docker 빌드에서 운영까지 (using docker compose) (0) | 2022.12.30 |
| Docker 컨테이너간 통신 (0) | 2022.12.29 |
| Docker Container Storage (0) | 2022.12.29 |
| Docker 컨테이너 리소스 관리 (0) | 2022.12.29 |