[ PostgreSQL ] 테이블 데이터 삭제 (TRUNCATE TABLE, DELETE) (transaction test)

postgresql truncate

postrgesql db의 테이블 데이터를 삭제하는 방법 중 하나인 TRUNCATE TALBE 명령어를 테스트 테이블 부터 생성하여 해당 명령어를 사용하고 기록해봅니다.

TRUNCATE TABLE 명령어는 많은 데이터를 가지고 있는 테이블의 경우에도 데이터를 빠르게 전부 삭제할 수 있는 명령어입니다.
테이블의 데이터를 삭제하는 방법에는 DELETE 명령어도 있지만, 많은 양의 데이터가 있는 테이블의 데이터를 전체 삭제할 경우 DELETE는 TRUNCATE TALBE 명령어보다 비효율적입니다.

DELETE 명령어는 테이블의 데이터 삭제 시 테이블의 스캔이 발생하지만, TRUNCATE TABLE 명령어는 스캔 없이 바로 모든 데이터를 삭제하여 더 빠릅니다.
또한 postgresql에서 DELETE로 데이터를 삭제해도 삭제된 데이터의 디스크 공간은 바로 다른 데이터의 저장을 위해 사용되지 않고 남아있게 됩니다. (이러한 공간은 postgresql vacuum을 통해 정리 및 재사용될 수 있습니다) 이에 비해 TRUNCATE TABLE 명령어는 디스크 공간을 바로 회수하여 사용할 수 있게 된다는 차이점도 있습니다. 

테스트는 PostgreSQL 10.18 버전에서 테스트되었습니다.

TRUNCATE TABLE 테스트

먼저 테스트를 위한 간단한 테이블 생성 및 데이터를 insert 합니다.

# 테이블 생성
CREATE TABLE IF NOT EXISTS table_truncate (
    no SERIAL PRIMARY KEY,
    name VARCHAR(5) NOT NULL
);

# 데이터 추가
INSERT INTO table_truncate(name) VALUES ('삼성전자');
INSERT INTO table_truncate(name) VALUES ('LG전자');

# 조회
SELECT * FROM table_truncate;

간단하게 테스트 테이블 table_truncate 테이블을 만들고, 데이터를 insert 하였습니다.

insert 실행 결과

테이블 시퀀스값 조회

# 테이블 시퀀스값 조회
SELECT currval(pg_get_serial_sequence('table_truncate', 'no'));

select 실행 결과

생성한 테이블의 시퀀스 번호를 확인해보면 insert를 두 번 하였으므로, 위와 같이 '2'인 것을 확인할 수 있습니다.
이 부분은 이후 TRUNCATE TABLE을 실행한 후에 비교하기 위해서 확인한 부분입니다.

TRUNCATE TABLE 실행

# TRUNCATE TABLE 실행
TRUNCATE TABLE table_truncate;

# 실행 테이블 조회
SELECT * FROM table_truncate;

# 실행 테이블 시퀀스 값 조회
SELECT currval(pg_get_serial_sequence('table_truncate', 'no'));

truncate table 실행 결과

TRUNCATE TABLE 명령어 실행 후 해당 테이블 조회를 해보면 데이터가 전부 삭제된 것을 확인할 수 있습니다. 다만 테이블의 PRIMARY KEY인 'no' 시퀀스 값은 그대로 유지되고 있는 것을 확인 할 수 있습니다. 

테이블의 시퀀스 값까지 초기화를 하기 위해서는 TRUNCATE TABLE 명령 시, 아래처럼 RESTART IDENTITY를 추가해줘야 합니다. 

다시 데이터를 INSERT 후, RESTART IDENTITY를 추가하여 TRUNCATE TABLE 명령을 실행해 봤습니다.

# 초기화 이후 다시 데이터 isnert
INSERT INTO table_truncate(name) VALUES ('삼성전자');
INSERT INTO table_truncate(name) VALUES ('LG전자');

# 시퀀스 no 값 3, 4 로 추가 확인
SELECT * FROM table_truncate;

# 시퀀스 포함 초기화
TRUNCATE TABLE table_truncate RESTART IDENTITY;

# 시퀀스 초기화 확인하기 위해 데이터 추가
INSERT INTO table_truncate(name) VALUES ('삼성전자');

# 시퀀스 조회
SELECT currval(pg_get_serial_sequence('table_truncate', 'no'));

RESTART IDENTITY 테스트

마지막 결과를 보면 위와 같이 RESTART IDENTITY를 추가하여 실행 시, table_truncate의 시퀀스 값인 no가 초기화되어 '1'로 데이터가 insert 된 것을 확인할 수 있습니다.

트랜잭션(TRANSACTION) 테스트

TRUNCATE TABLE 명령어는 트랜잭션 내에서 실행 시 안전하게 롤백될 수 있습니다. 이를 한번 테스트해 봤습니다.

테스트 전 테이블의 상태는 아래와 같이 두 행의 데이터가 들어가 있는 상태입니다. 

롤백 테스트 전 테이블 데이터

테스트 실행 쿼리는 아래와 같습니다. 

 
# 트랜잭션 시작
BEGIN;

# 테이블 데이터 삭제
TRUNCATE TABLE table_truncate;

# 데이터 삭제한 테이블 조회
SELECT * FROM table_truncate;

# 트랜잭션 롤백
ROLLBACK;

# 트랜잭션 롤백 후 데이터 조회
SELECT * FROM table_truncate;

실행 결과 : 

트랜잭션 롤백 테스트 

위의 중간에서와 같이 트랜잭션 내(BEGIN; 이후)에서 TRUNCATE TABLE 명령어 실행 이후 동일한 세션에서 해당 테이블을 조회해보면, 테이블의 값이 삭제된 것을 확인할 수 있습니다. 
하지만 이후 ROLLBACK 명령어가 실행되고나서 다시 조회 해보면, 데이터가 삭제되지 않고 유지되어 있는 것을 확인할 수 있습니다. 
TRUNCATE TABLE 명령어는 트랜잭션 내에서 안전하게 롤백될 수 있다는 것을 확인해봤습니다.