답변

DELETE와 TRUNCATE는 모두 테이블을 정리하는 도구이지만 다른 방식으로 작동합니다:

• DELETE는 조건에 따라 또는 모든 행을 삭제하며, WHERE를 지원하고, 롤백할 수 있으며 트리거가 활성화됩니다. 많은 수의 행이 있는 경우 느릴 수 있습니다(한 번에 한 행씩).
• TRUNCATE는 전체 테이블을 즉시 비우며, WHERE를 사용할 수 없고, 종종 행별로 로깅되지 않으며, 항상 트리거를 활성화하지 않습니다. 대부분의 경우 이 작업은 취소할 수 없으며 일부 DBMS에서는 롤백할 수 없습니다.

세부 사항:

• DELETE는 트랜잭션 롤백(ROLLBACK)을 지원하지만, TRUNCATE는 그렇지 않거나 DBMS에 따라 다릅니다.
• TRUNCATE는 일반적으로 identity/sequence 카운터를 초기화합니다.
• TRUNCATE는 외래 키나 테이블에 대한 참조가 있을 경우 적용할 수 없습니다.

예시:

-- 3년 이상 된 주문 삭제
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- 테이블을 완전히 비우기(빠르게)
TRUNCATE TABLE logs;

함정 질문

TRUNCATE 후 데이터를 복구할 수 있나요?

일반적인 오류: DELETE처럼 TRUNCATE도 트랜잭션으로 롤백할 수 있다고 생각하는 것. 그러나 TRUNCATE는 일반적으로 롤백하거나 UNDO할 수 없으며, 일부 DBMS(예: PostgreSQL)에서는 트랜잭션 내에서 작업할 경우에만 가능합니다.

예시(대부분의 DBMS에서):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- 대다수의 DBMS에서 작동하지 않음, 데이터 손실!

주제에 대한 세부 사항을 모르고 발생한 실제 오류의 예

---

이야기

생산 데이터베이스에서 TEST 대신 TRUNCATE를 사용하여 임시 데이터를 삭제했습니다. 행 삭제에 대한 감사 트리거는 DELETE에만 작동하여 손실된 데이터, 시간 및 사용자에 대한 정보가 없습니다. 사건 조사가 어려웠습니다.

---

이야기

외래 키가 있는 경우 TRUNCATE로 테이블을 정리했습니다. SQL은 참조 무결성 제약 조건 때문에 오류를 발생시켰고, 스크립트가 완료되지 않았으며 자동화가 중단되었습니다. 해결책: 외래 키를 일시적으로 제거하거나 DELETE를 사용하십시오.

---

이야기

대규모 테이블에서 DELETE를 통해 대량으로 데이터를 삭제하는 경우 배치 처리 없이 이루어졌습니다: 긴 잠금으로 인해 데이터베이스가 "멈췄고" 다른 트랜잭션이 잠금 해제를 기다려야 했습니다. 결과적으로 시스템이 중단되었습니다. 해결법: DELETE LIMIT/OFFSET을 사용하거나 지원되는 경우 배치를 사용하십시오.