postgresql - delete, truncate의 비교

  delete 명령어

행이 삭제될 때 리소스 소모가 truncate에 비해 상대적으로 크다. -> how?

trigger가 걸려있다면 행이 삭제될때마다 실행된다.

이전에 할당된 영역은 삭제되어, 빈 테이블이나 클러스터에 남는다. -> why?

 truncate명령어

테이블의 모든 데이터를 삭제한다.

delete와 비교하여 상대적으로 빠르게 데이터를 삭제한다.

rollback 정보를 만들지 않으며 즉시 커밋한다.

때문에 한 번 truncate를 수행하면 rollback 할 수 없다.

행을 삭제할 때 trigger가 걸려있더라고 trigger를 실행하지 않는다.

Truncate 옵션

postgresql의 truncate 옵션에는 다음 4가지가 있다.

- RESTART IDENTITY : 타깃 테이블에 걸려있는 시퀀스(자동증가 값)을 재시작하면서

테이블 정보를 모두 삭제

- CONTINUE IDENTITY : 시퀀스 값 리셋 X, 데이터만 모두 삭제

- CASCADE : 타깃 테이블의 데이터를 모두 삭제하고 이 테이블과 연결된 데이터가

다른 테이블에 있으면, 다른 테이블의 데이터도 모두 삭제.

외래키 기준으로 다른 테이블을 참조할 때.

- RESTRICT : 타깃 테이블에 연결된 데이터가 하나라도 있으면 데이터를 삭제하지 않는다.

Postgresql에서의 delete, truncate 비교

database 사용자 입장에서는 똑같이 데이터를 삭제하는 작업처럼 보인다.

내부적으로는 다르게 작동한다.

DELETE를 수행하고나면

-database pages 안에 dead rows가 남는다.

-dead rows의 dead pointer가 색인에 남는다.

반면 TRUNCATE를 수행하고 나면

dead rows, indices(색인)이 비워지고 table 통계가 초기화된다.

신규 테이블을 생성할 때의 설정값과 비슷하게 구성됨. 

-> 정확하게 어떻게 구성되는지는 공식문서를 보고 비교 해봐야한다.

TRUNCATE는 작업하는데 상수 시간이 걸린다.

DELETE는 테이블의 row 개수에 따라 가변적이다.

적당한 개수의 row를 삭제할때에는 DELETE가 빠르나,

많은 개수의 row를 삭제할 때에는 truncate가 빠르다.

적은 개수의 row를 삭제할 때에만 DELETE를 사용하고 이외의 경우엔 TRUNCATE 할 것.

출처 

https://www.lob.com/blog/truncate-vs-delete-efficiently-clearing-data-from-a-postgres-table

https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886

(더 자세한 truncate와 delete 비교글)