데이터베이스 테이블 rotation - DB에 데이터가 계속해서 쌓여서 용량이 꽉차면 어떡하죠...?
안녕하세요. 오늘은 데이터베이스 table rotation에 대해서 공부해보도록 하겠습니다.
log rotation이라는 용어는 많이 들어보셨을 거에요!
용량이 금방 커지는 로그를 관리하지 않을 시
로그 파일이 너무 커져서 시스템의 디스크 용량을 모두 차지하여 부팅이 안되는 등의 문제가 발생할 수 있습니다.
이를 예방하기 위해서 로그 파일이 일정 크기 이상 커지거나 일정 기간이 지난 경우 제거하고는 합니다.
이렇게 로그 파일의 크기를 일정하게 유지하는 일련의 작업들을 log rotation 이라고 합니다.
로그 파일과 비슷하게 데이터베이스의 테이블에도 데이터가 계속해서 쌓일 수가 있습니다.
데이터베이스 테이블의 용량을 무제한으로 설정할 수는 없기 때문에 table rotation을 수행해줘야 합니다!
이제 table rotation을 어떻게 수행할 수 있는지 알아볼까요?
- 사실 데이터베이스 테이블 rotation이라는 용어는 제가 임의로 만든 것이지만 의도를 전달하기에는 충분하다고 생각하여 이번 포스팅에서 계속 사용하도록 하겠습니다!
- 저는 이번 공부에서 postgresql을 사용했습니다. 혹시 다른 DBMS를 사용하더라도 비슷한 기능이 있을테니 재밌게 봐주세요 :)
1. DELETE 문과 WHERE 절을 활용한 rotation 방법
'주기적으로 오래된 데이터를 삭제하는 DELETE 문을 실행하면 되지 않나?'
데이터베이스의 테이블을 주기적으로 지워야 하는 문제를 들었을 때, 제일 먼저 생각났던 방법입니다.
아마 log rotation을 알고 있으신 분들은 동일한 방식으로 가장 먼저 떠올리셨을 것 같습니다.
하지만 log rotation과 동일한 방식이라면 동일한 결점을 가지고 있을 수 있습니다.
log rotation은 로그를 작성하고 있는 파일이 바뀔 때, 파일 디스크립터가 바뀔 때 로그가 유실될 수 있다는 단점이 있습니다.
이와 비슷하게 DELETE문으로 많은 양의 데이터를 지우면 데이터베이스의 성능에 영향을 줄 수 있을 것이라고 생각하여 테스트를 진행해봤습니다.
1.1. 1차 테스트
방법
1. 다음과 같은 스키마의 간단한 테이블을 만듭니다.
CREATE TABLE messages(
id UUID PRIMARY KEY,
date TIMESTAMP NOT NULL, -- message 데이터가 생성된 시각을 의미합니다.
content VARCHAR(128) -- message 데이터의 내용을 의미합니다.
);2. 테스트를 진행하기 위해 '오늘'부터 30일 전까지의 데이터를 준비한다
- 데이터 사이의 시간 간격은 0.1초입니다.
예를 들어, date 열의 값을2023년 10월 1일 00시 00분 00초, 2023년 10월 1일 00시 00분 01초, ... 이런 식으로 데이터를 준비했습니다.
3. date가 30일 전인 행들을 제거하는 DELETE 문을 실행하고, 걸리는 시간을 측정한다.
결과
| date 열에 index를 생성했을 때 | |
| 생성한 행 수 | 2592000개 |
| 행을 준비하는데 걸린 시간 | 274.67초 |
| date가 30일 전인 행들을 제거하는데 걸린 시간 | 0.06초 |
| date 열에 index를 생성하지 않았을 때 | |
| 생성한 행 수 | 2592000개 |
| 행을 준비하는데 걸린 시간 | 270.01초 |
| date가 30일 전인 행들을 제거하는데 걸린 시간 | 0.24초 |
생각보다 DELETE 문의 실행 속도가 빨랐습니다!
이 정도면 실서비스 중에 DELETE 문을 실행시켜도 큰 영향을 주지 않을 것 같아서(?)
데이터 양을 늘리고, 반복 횟수도 늘려서 다시 테스트를 진행해봐야 할 것 같아요.
※ 마침 테스트를 추가적으로 진행하게 되어서 데이터가 계속해서 INSERT 되고 있을 때,
DELETE 문이 실행되면 어떤 영향을 줄지 알아볼 수 있도록 테스트를 준비해보겠습니다.
1.2. 2차 테스트
방법
1. 다음과 같은 스키마의 간단한 테이블을 만듭니다.
CREATE TABLE messages(
id UUID PRIMARY KEY,
date TIMESTAMP NOT NULL, -- message 데이터가 생성된 시각을 의미합니다.
content VARCHAR(128) -- message 데이터의 내용을 의미합니다.
);2. '오늘'부터 30일 전까지의 데이터를 준비한다.
- 데이터 사이의 시간 간격은 0.1초입니다.
예를 들어, date 열의 값을2023년 10월 1일 00시 00분 00.1초, 2023년 10월 1일 00시 00분 00.2초, ... 이런 식으로 데이터를 준비했습니다.
3. date가 30일 전인 행들을 제거하는 DELETE 문을 실행하고, 걸리는 시간을 측정한다.
4. 데이터를 계속해서 INSERT 하는 스크립트를 실행한다.
5. 10초 후에 date가 29일 전인 행들을 제거하는 DELETE 문을 실행하고, 걸리는 시간을 측정한다.
결과
- date 열에 index를 생성했을 때 (10번 반복)
- 25920000 개의 행을 테스트 용으로 생성했습니다.
- 방법 3에서 864000개의 행을 지우는데 평균 1.93초가 소요되었습니다.
- 방법 5에서 864000개의 행을 지우는데 평균 1.34초가 소요되었습니다.
- date 열에 index를 생성하지 않았을 때 (10번 반복)
- 25920000 개의 행을 테스트 용으로 생성했습니다.
- 방법 3에서 864000개의 행을 지우는데 평균 14.89초가 소요되었습니다.
- 방법 5에서 864000개의 행을 지우는데 평균 5.73초가 소요되었습니다.
※ 자세한 결과
| date 열에 index를 생성했을 때 | |||||||||||
| 1 회 | 2 회 | 3 회 | 4 회 | 5 회 | 6 회 | 7 회 | 8 회 | 9 회 | 10 회 | 평균 | |
| 방법 3에서 행 864000개를 지우는데 걸린 시간 | 2.18 | 1.97 | 2.46 | 1.27 | 1.99 | 1.76 | 1.94 | 1.92 | 1.81 | 2.03 | 1.933 |
| 방법 5에서 행 864000개를 지우는데 걸린 시간 | 0.92 | 1.22 | 1.6 | 1.05 | 1.84 | 1.41 | 1.3 | 1.24 | 1.19 | 1.63 | 1.34 |
| date 열에 index를 생성하지 않았을 때 | |||||||||||
| 1 회 | 2 회 | 3 회 | 4 회 | 5 회 | 6 회 | 7 회 | 8 회 | 9 회 | 10 회 | 평균 | |
| 방법 3에서 행 864000개를 지우는데 걸린 시간 | 11.58 | 13.59 | 12.62 | 13.88 | 18.64 | 15.34 | 13.06 | 14.44 | 19.68 | 16.11 | 14.894 |
| 방법 5에서 행 864000개를 지우는데 걸린 시간 | 8.01 | 3.95 | 4.04 | 5.47 | 3.91 | 4.6 | 7.49 | 4.51 | 6.2 | 9.14 | 5.732 |
데이터가 많아짐에 따라서 시간이 대폭 늘어난 것을 확인할 수 있었습니다.
서비스의 규모가 커질수록 데이터를 삭제하는데 시간이 오래 걸리게 되므로 더 빠른 방법을 찾아봤습니다!
※ 그건 그렇고, 방법 5에서 행 864000개를 지우는데 걸리는 시간이 방법 3에서 행 864000개를 지우는데 걸리는 시간보다 짧은 이유가 무엇인지 궁금하네요...
2. PostgreSQL partition을 활용한 rotation 방법
관련 내용들을 찾다가 partition을 활용하여 빠르고, 편리하게 오래된 데이터를 지우는 방법을 발견했습니다!
방법이 자세하게 설명되어 있어서 쉽게 테스트 할 수 있었습니다 ㅎㅎ
2.1. 테스트
방법
1. 다음 쿼리를 이용해서 간단한 테이블과 파티션, 트리거들을 만듭니다.
CREATE TABLE messages(
id UUID PRIMARY KEY,
date TIMESTAMP NOT NULL, -- message 데이터가 생성된 시각을 의미합니다.
content VARCHAR(128) -- message 데이터의 내용을 의미합니다.
);
create table messages1(like messages including all, constraint ck check(extract(day from date)=1)) inherits(messages);
create table messages2(like messages including all, constraint ck check(extract(day from date)=2)) inherits(messages);
create table messages3(like messages including all, constraint ck check(extract(day from date)=3)) inherits(messages);
create table messages4(like messages including all, constraint ck check(extract(day from date)=4)) inherits(messages);
create table messages5(like messages including all, constraint ck check(extract(day from date)=5)) inherits(messages);
create table messages6(like messages including all, constraint ck check(extract(day from date)=6)) inherits(messages);
create table messages7(like messages including all, constraint ck check(extract(day from date)=7)) inherits(messages);
create table messages8(like messages including all, constraint ck check(extract(day from date)=8)) inherits(messages);
create table messages9(like messages including all, constraint ck check(extract(day from date)=9)) inherits(messages);
create table messages10(like messages including all, constraint ck check(extract(day from date)=10)) inherits(messages);
create table messages11(like messages including all, constraint ck check(extract(day from date)=11)) inherits(messages);
create table messages12(like messages including all, constraint ck check(extract(day from date)=12)) inherits(messages);
create table messages13(like messages including all, constraint ck check(extract(day from date)=13)) inherits(messages);
create table messages14(like messages including all, constraint ck check(extract(day from date)=14)) inherits(messages);
create table messages15(like messages including all, constraint ck check(extract(day from date)=15)) inherits(messages);
create table messages16(like messages including all, constraint ck check(extract(day from date)=16)) inherits(messages);
create table messages17(like messages including all, constraint ck check(extract(day from date)=17)) inherits(messages);
create table messages18(like messages including all, constraint ck check(extract(day from date)=18)) inherits(messages);
create table messages19(like messages including all, constraint ck check(extract(day from date)=19)) inherits(messages);
create table messages20(like messages including all, constraint ck check(extract(day from date)=20)) inherits(messages);
create table messages21(like messages including all, constraint ck check(extract(day from date)=21)) inherits(messages);
create table messages22(like messages including all, constraint ck check(extract(day from date)=22)) inherits(messages);
create table messages23(like messages including all, constraint ck check(extract(day from date)=23)) inherits(messages);
create table messages24(like messages including all, constraint ck check(extract(day from date)=24)) inherits(messages);
create table messages25(like messages including all, constraint ck check(extract(day from date)=25)) inherits(messages);
create table messages26(like messages including all, constraint ck check(extract(day from date)=26)) inherits(messages);
create table messages27(like messages including all, constraint ck check(extract(day from date)=27)) inherits(messages);
create table messages28(like messages including all, constraint ck check(extract(day from date)=28)) inherits(messages);
create table messages29(like messages including all, constraint ck check(extract(day from date)=29)) inherits(messages);
create table messages30(like messages including all, constraint ck check(extract(day from date)=30)) inherits(messages);
create table messages31(like messages including all, constraint ck check(extract(day from date)=31)) inherits(messages);
create table messages_def(like messages including all) inherits(messages);
create or replace function insert_message() returns trigger as $$
declare
begin
case extract(day from NEW.date)
when 1 then insert into messages1 values (NEW.*);
when 2 then insert into messages2 values (NEW.*);
when 3 then insert into messages3 values (NEW.*);
when 4 then insert into messages4 values (NEW.*);
when 5 then insert into messages5 values (NEW.*);
when 6 then insert into messages6 values (NEW.*);
when 7 then insert into messages7 values (NEW.*);
when 8 then insert into messages8 values (NEW.*);
when 9 then insert into messages9 values (NEW.*);
when 10 then insert into messages10 values (NEW.*);
when 11 then insert into messages11 values (NEW.*);
when 12 then insert into messages12 values (NEW.*);
when 13 then insert into messages13 values (NEW.*);
when 14 then insert into messages14 values (NEW.*);
when 15 then insert into messages15 values (NEW.*);
when 16 then insert into messages16 values (NEW.*);
when 17 then insert into messages17 values (NEW.*);
when 18 then insert into messages18 values (NEW.*);
when 19 then insert into messages19 values (NEW.*);
when 20 then insert into messages20 values (NEW.*);
when 21 then insert into messages21 values (NEW.*);
when 22 then insert into messages22 values (NEW.*);
when 23 then insert into messages23 values (NEW.*);
when 24 then insert into messages24 values (NEW.*);
when 25 then insert into messages25 values (NEW.*);
when 26 then insert into messages26 values (NEW.*);
when 27 then insert into messages27 values (NEW.*);
when 28 then insert into messages28 values (NEW.*);
when 29 then insert into messages29 values (NEW.*);
when 30 then insert into messages30 values (NEW.*);
when 31 then insert into messages31 values (NEW.*);
end case;
return null;
end;
$$ language plpgsql strict;2. '오늘'부터 30일 전까지의 데이터를 준비한다(데이터 사이의 시간 간격은 0.1초).
- 데이터 사이의 시간 간격은 0.1초입니다.
예를 들어, date 열의 값을2023년 10월 1일 00시 00분 00.1초, 2023년 10월 1일 00시 00분 00.2초, ... 이런 식으로 데이터를 준비했습니다.
3. 30일 전 데이터를 저장하는 partition을 삭제하고 새롭게 생성하는데 걸리는 시간을 측정한다.
4. 데이터를 계속해서 INSERT 하는 스크립트를 실행한다.
5. 10초 후에 29일 전 데이터를 저장하는 partition을 삭제하고 새롭게 생성하는데 걸리는 시간을 측정한다.
결과
- date 열에 index를 생성했을 때 (10번 반복)
- 25920000 개의 행을 테스트 용으로 생성했습니다.
- 방법 3에서 partition을 삭제하고 새롭게 만드는데 평균 0.01초가 소요되었습니다.
- 방법 5에서 partition을 삭제하고 새롭게 만드는데 평균 0.008초가 소요되었습니다.
- date 열에 index를 생성하지 않았을 때 (10번 반복)
- 25920000 개의 행을 테스트 용으로 생성했습니다.
- 방법 3에서 partition을 삭제하고 새롭게 만드는데 평균 0.087초가 소요되었습니다.
- 방법 5에서 partition을 삭제하고 새롭게 만드는데 평균 0.067초가 소요되었습니다.
※ 자세한 결과
| date 열에 index를 생성했을 때 | |||||||||||
| 1 회 | 2 회 | 3 회 | 4 회 | 5 회 | 6 회 | 7 회 | 8 회 | 9 회 | 10 회 | 평균 | |
| 방법 3에서 partition을 삭제하고 새롭게 만드는데 걸린 시간 | 0.008 | 0.01 | 0.009 | 0.006 | 0.009 | 0.022 | 0.015 | 0.007 | 0.007 | 0.008 | 0.010 |
| 방법 5에서 partition을 삭제하고 새롭게 만드는데 걸린 시간 | 0.007 | 0.006 | 0.006 | 0.006 | 0.005 | 0.020 | 0.005 | 0.006 | 0.006 | 0.010 | 0.008 |
| date 열에 index를 생성하지 않았을 때 | |||||||||||
| 1 회 | 2 회 | 3 회 | 4 회 | 5 회 | 6 회 | 7 회 | 8 회 | 9 회 | 10 회 | 평균 | |
| 방법 3에서 partition을 삭제하고 새롭게 만드는데 걸린 시간 | 0.009 | 0.02 | 0.006 | 0.012 | 0.009 | 0.008 | 0.010 | 0.006 | 0.006 | 0.006 | 0.087 |
| 방법 5에서 partition을 삭제하고 새롭게 만드는데 걸린 시간 | 0.007 | 0.015 | 0.005 | 0.006 | 0.010 | 0.006 | 0.006 | 0.006 | 0.005 | 0.005 | 0.067 |
DELETE 문을 사용했을 때와 비교하면 속도가 매우 빠른 것을 한눈에 알 수 있습니다.
(단위가 초에서 밀리초라 바뀌었네요!)
3. 결론
테스트를 진행하면서 느꼈던 것은 생각보다 DELETE 문이 빠르다는 것이었습니다.
서비스의 규모가 작다면 굳이 복잡하게 시스템을 구성할 필요가 없겠더라고요!
하지만 서비스의 규모가 빠른 속도로 커질 것 같다면 partition을 이용하는 것이 여러 방면에서 이득이니 미리 준비하는 것이 좋을 것 같습니다.
작은 규모의 서비스라면 DELETE 문을 이용한 data 정리로 충분하지만
큰 규모라면 최소한 partition 이용을 검토해보는 것이 좋을 것 같습니다!
table rotation을 공부해봤는데 어떠셨나요?
간단하게 공부하려고 했는데 연휴도 끼고, 테스트도 길어지면서 오랜 기간 작성하게 되었네요 ㄷㄷㄷ
2가지 방법 모두 테스트를 진행하면서 결과를 비교하는 과정이 정말 재밌었습니다.
하지만 테스트를 한번 실행하는데 시간이 오래 걸려서 충분히 깊게 파보지 못한 것이 아쉽네요 ㅜ
다음에 기회가 된다면 방법 5에서 행 864000개를 지우는데 걸리는 시간이 방법 3에서 행 864000개를 지우는데 걸리는 시간보다 짧은 이유를 알아보고 싶고,
이번에 사용한 partition이 기능이 무엇인지 조금 더 구체적으로 공부해보고 싶네요 ㅎㅎ
테스트 결과가 다르거나 더 좋은 방법이 있거나 놓친 부분이 있으면 피드백 부탁드립니당!