[DB 안티패턴] 대용량 데이터를 위한 테이블 분리

[문제코드]

 

테이블에 적재하는 데이터의 양이 점차 많아질수록 Index 에 대한 부하 뿐만 아니라 I/O 에 대한 부하가 걸리게 된다. 테이블의 양이 점점 많아지고 있을 때, 아래와 같이 데이터를 분리할 칼럼을 선택해 테이블을 분리할 수 있다.

 

CREATE TABLE Bugs_2008 ( ... );
CREATE TABLE Bugs_2009 ( ... );
CREATE TABLE Bugs_2010 ( ... );

 

해당 구조는 아래와 같이 여러 단점을 가지고 있다.

 

1 . 데이터 정합성 관리

데이터베이스 행에 데이터를 삽입할 때 테이블을 선택하는 것은 오로지 사용자 책임이다.

 

INSERT INTO Bugs_2010 (..., date_reported, ...)
	VALUES (..., '2010-06-01', ...);

INSERT INTO Bugs_2011 (..., date_reported, ...)
	VALUES (..., '2011-02-20', ...);

 

날짜연도에 따라서 테이블을 생성해야 하는데 테이블 생성하는 것을 잊어버렸다면 에러가 발생할 수 있다.

또한, 한 해 동안 버그 개수를 세보려고 하는데 통계 정합성이 맞지 않을 수 있다. 2010 년 관련 값이 Bugs_2009 테이블에 삽입되었던 것이다. 잘못된 어플리케이션의 로직을 방어하기 위해 데이터베이스 기준으로 제약할 수 있는 방법이 있긴 하다. 아래와 같이 CEHCK 조건을 필드에 적용하면 된다.

 

CREATE TABLE Bugs_2009 (
	...
    date_reported DATE_CHECK (EXTRACT(YEAR FROM date_reported) = 2009)
)

 

2 . 데이터 수정 불편

UPDATE 구문으로 단순히 데이터를 변경하고 싶지만, 테이블을 나눈 기준 열 칼럼의 값이 바뀌면 해당 테이블에서 삭제하고 다른 테이블에 데이터를 옮겨야한다.

 

INSERT INTO Bugs_2009 (bug_id, date_reported, ...)
	SELECT bug_id, date_reported, ...
    FROM Bugs_2010
    WHERE bug_id = 1234;
    
DELETE FROM Bugs_2010 WHERE bug_id = 1234;

 

3 . 유일성 보장

테이블을 나누는 기준인 PK 칼럼은 유일함이 보장되어야 한다. 한 테이블에서 다른 테이블로 행을 옮겼을 때 PK 값이 다른 행과 충돌하지 않는다는 확신이 있어야 한다.

 

4 . 여러 테이블에 걸쳐 조회

여러 테이블에 걸쳐 조회할 필요가 생길 때 분리된 모든 테이블을 UNION 으로 묶어서 재구성한 다음 쿼리를 실행해야 한다.

 

SELECT b.status COUNT(*) AS count_per_status FROM (
	SELECT * FROM Bugs_2008
    	UNION ALL
    SELECT * FROM Bugs_2009
    	UNION ALL
    SELECT * FROM Bugs_2010 ) AS b
GROUP BY b.status;

 

5 . 테이블에 새로운 칼럼 추가 시 모두 변경

테이블에 새로운 칼럼 추가 시 모든 분리된 테이블에 똑같은 칼럼을 추가해야 한다.

 

[해결방법]

수평 분할 사용과 수직 분할 사용을 고려할 수 있다.

 

1 . 수평 분할

행을 여러 파티션으로 분리하는 규칙과 함께 논리적 테이블을 생성하는 것만으로도 충분하다. 물리적으로는 테이블이 분리되었지만, 논리적으로는 하나의 테이블처럼 사용할 수 있다.

 

CREATE TABLE Bugs (
	bug_id SERIAL PRIMARY KEY,
    ...
    date_reported DATE
) PARTITION BY HASH (YEAR(date_reported))
PARTITIONS 4;

 

테이블을 직접 분리했을 때랑 다르게 잘못된 데이터가 분리된 테이블로 들어갈 위험이 없다는 장점이 있다. 분리 기준이 되었던 칼럼의 값을 업데이트해도 문제가 없고 분리 테이블을 모두 접근해서 쿼리를 할 필요도 없다.

다만, 위 예제에서 4년 이상이 된 데이터가 있다면, 파티션 중 하나에는 두 연도의 데이터가 들어갈 수 있다.

 

2 . 수직 분할

수직 분할은 테이블에 있는 칼럼 중 크기가 아주 큰 칼럼이거나 거의 사용되지 않는 칼럼이 있을 경우 고려할 수 있는 방법이다. BLOB 이나 TEXT 칼럼은 크기가 가변적이고 매우 커질 수 있는 칼럼이라서 같은 테이블에서 조회 & 수정 & 삭제 시 성능 저하를 초래한다. 거의 사용되지 않는 칼럼도 리소스 낭비가 있을 뿐이다. 이렇게 가변적이거나 크기가 아주 크거나 자주 사용되지 않는 칼럼들만 모아서 별도의 종속 테이블로 분리하는 것이 좋다.

 

// 고정 크기의 타입만 정의
CREATE TABLE Bugs (
	bug_id SERIAL PRIMARY KEY,
    summary CHAR(80),
    date_reported DATE,
    reported_by BIGINT UNSIGNED,
	FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
)
// 가변 크기의 타입만 정의
CREATE TABLE BugDescriptions (
	bug_id BIGINT UNSIGNED PRIMARY KEY,
    description VARCHAR(1000),
    resolution VARCHAR(1000),
    FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
)