PostgreSQL 테이블 중복데이터 제거

설계, 운영이 잘 되는 DB에선 많이 발생하는 문제는 아닐테지만 테이블의 특정 컬럼값을 기준으로 중복된 데이터를 삭제해야 하는 경우가 있다.

필자의 경우 PostgreSQL을 사용하던 중 이전 담당자가 테이블을 TEST_01_TB ~ TEST_10_TB 이런식으로 물리적으로 10개로 나눠서 설계하여 사용하던걸 인수받았는데 감리 검수에서 10개의 테이블을 하나로 합치라는 개선안내를 받았다.

Hash Partition을 적용하여 테이블을 합쳤으나 파티션을 적용하니 기존에 복합키로 사용하던 Unique 조건이나 데이터를 생성 시 Insert쿼리문에서 ON CONFLICT 컬럼 DO NOTHING 옵션도 적용할 수 없어서 빠지게 되었는데 이 때문에 중복데이터가 발생하였다.

중복된 데이터를 어떻게 찾아서 지우느냐에 따라 수행시간에 큰 차이가 날거라고 생각해서 구글링을 해보니 몇가지 방법을 찾을 수 있었다.

Test Table Schema

CREATE TABLE MEMBER_TB (
	MEM_ID BIGSERIAL PRIMARY KEY,
	MEM_NAME VARCHAR(20) NOT NULL,
    MEM_PNUM VARCHAR(11) NOT NULL,
    MEM_EMAIL TEXT NOT NULL
);

CREATE TABLE BLACKLIST_TB (
    MEM_PNUM VARCHAR(11) PRIMARY KEY
);

ROW_NUMBER()

유저 테이블에의 MEM_PNUM 컬럼을 기준으로 중복된 데이터가 있어서 이를 지우기 위해선 어떻게 해야할까? 구글링을 하고 제일먼저 찾은 방법은 ROW_NUMBER() 를 활용한 방법이었다.

DELETE FROM MEMBER_TB
WHERE MEM_ID IN ( SELECT MEM_ID 
                 FROM (SELECT MEM_ID,
                       		ROW_NUMBER() OVER(PARTITION BY MEM_PNUM ORDER BY MEM_ID ) RNUM
                       FROM MEMBER_TB )
                WHERE RNUM > 1 
             );

바로 적용할까 싶었지만 타겟 테이블의 데이터건수는 수십억개라 좀 더 빠른 방법이 없는지 더 찾아보던 중 Self Join과 Delete Using을 활용한 방법을 찾을 수 있었다.

DELETE USING

특정 테이블을 참조하여 데이터를 삭제할 때 PostgreSQL의 경우 MySQL처럼 DELETE JOIN을 제공하진 않지만 USING을 사용하여 DELETE JOIN과 유사한 기능을 지원한다.

DELETE FROM MEMBER_TB T1
USING BLACKLIST_TB T2
WHERE T1.MEM_PNUM = T2.MEM_PNUM;

예를 들면 이 처럼 USING 키워드를 사용하여 특정 테이블을 참조하여 유저테이블에서 블랙리스트 유저정보만 지울 수 있다.

그럼 여기서 SELF JOIN을 활용하여 MEM_PNUM, MEM_EMAIL 컬럼의 중복데이터를 삭제하고 싶으면 SQL문을 다음과 같은 식으로 작성할 수 있다.

DELETE FROM MEMBER_TB T1
USING MEMBER_TB T2
WHERE T1.CTID < T2.CTID
  AND T1.MEM_PNUM = T2.MEM_PNUM
  AND T1.MEM_EMAIL = T2.MEM_EMAIL;

참고한 자료에선 1000만건의 데이터 중 10만건이 중복발생했을 때 약 42초정도 걸렸다고 했고 적용 전 확인해보기 위해 샘플로 Row가 약 800만에 중복데이터가 32만건 정도 들어있는 테이블로 테스트를 해보니 쿼리 실행에 약 1분정도 소요된 것을 확인하고 본래 타겟 테이블에 적용하였다.

그 후 데이터 생성 로직을 변경하여 중복데이터가 들어올 수 없도록 수정하였고 설계와 테스트를 더 잘 해야겠다는 생각이 절실하게 드는 하루였다.

Reference
PostgreSQL DELETE JOIN

중복된 관측치 제거하기

How to delete duplicate rows in postgresql?