[SQL] DELETE vs TRUNCATE (PostgreSQL, MySQL, SQL Server, Oracle)

1. DELETE(DML) vs TRUNCATE(DDL)

DELETE: 데이터 조작 언어(DML) 명령어

DELETE는 데이터 조작 언어(DML)에 속하는 명령어로, 테이블 내부의 데이터를 조작하는 데 사용됩니다. 

 DELETE의 핵심은 개별 행(row) 수준에서 작동한다는 점입니다. WHERE 절을 사용하여 특정 조건을 만족하는 행만 선택적으로 삭제하거나, WHERE 절 없이 테이블의 모든 행을 삭제할 수 있습니다.

 DELETE가 행 단위로 작동한다는 것은 각 행을 삭제할 때마다 데이터베이스의 표준 트랜잭션 및 일관성 메커니즘과 상호작용해야 함을 의미합니다. 여기에는 다음과 같은 작업이 포함됩니다.

• 삭제되는 각 행에 대한 트랜잭션 로그(또는 PostgreSQL의 WAL, Oracle의 Redo 로그) 기록
• 관련 인덱스 업데이트
• ON DELETE 트리거 실행
• 참조 무결성을 위한 외래 키(Foreign Key) 제약 조건 확인

이처럼 DELETE는 데이터의 무결성과 일관성을 보장하기 위해 설계된 정밀하고 안전한 데이터 조작 도구이며, 그 기능은 구조적 변경이 아닌 데이터 자체의 조작에 초점을 맞춥니다.

 

TRUNCATE: 데이터 정의 언어(DDL) 명령어

 TRUNCATE는 데이터 정의 언어(DDL)에 속합니다. DDL 명령어는 데이터베이스 객체의 구조를 정의하거나 수정하는 데 사용됩니다.

TRUNCATE는 행을 하나씩 처리하는 대신, 테이블 데이터를 저장하는 데 사용된 데이터 페이지(page)나 익스텐트(extent)의 할당을 해제하는 방식으로 작동합니다. 일부 시스템에서는 내부적으로 테이블을 삭제(DROP)했다가 다시 생성(CREATE)하는 것과 유사한 방식으로 동작하기도 합니다.

 이러한 메커니즘은 단순한 데이터 조작이 아닌, 테이블의 물리적 구조에 대한 변경으로 간주되므로 DDL로 분류됩니다. TRUNCATE는 행 수준의 처리를 우회하기 때문에 DELETE와 비교 시 다음과 같은 동작 차이를 보입니다.

• 빠른 성능
• 최소한의 로깅
• ON DELETE 트리거 미작동

2. 보편적인 메커니즘 비교

DBMS 종류에 관계없이 DELETE와 TRUNCATE는 몇 가지 공통적인 작동 방식과 성능을 알아보겠습니다. 

성능과 효율성: 행 스캔 vs 페이지 할당 해제

 DELETE 작업은 삭제할 행을 찾기 위해 테이블 스캔이나 인덱스 스캔을 수행하고, 각 행을 개별적으로 제거합니다. 이 과정은 상당한 양의 I/O와 CPU 오버헤드를 유발하며, 특히 수백만 개 이상의 행을 가진 대용량 테이블에서 모든 데이터를 삭제할 경우 매우 오랜 시간이 소요될 수 있습니다.

반면, TRUNCATE는 테이블 데이터를 담고 있는 데이터 페이지의 할당을 해제하는 방식으로 작동합니다. 이는 소수의 메타데이터만 변경하는 작업이므로 I/O가 거의 발생하지 않아 대용량 테이블이라도 거의 즉시 완료됩니다. 또한, TRUNCATE는 디스크 공간을 즉시 회수하므로 PostgreSQL의 VACUUM과 같은 추가적인 공간 정리 작업이 필요하지 않습니다.

 

리소스 소비: 로깅 공간 사용량

 데이터베이스의 모든 변경 사항은 복구를 위해 로그에 기록됩니다. DELETE와 TRUNCATE는 로깅 방식에서 큰 차이를 보입니다.

 DELETE는 '완전 로깅(fully logged)' 작업입니다. 즉, 삭제되는 모든 행의 데이터가 트랜잭션 로그(SQL Server), WAL(PostgreSQL), Redo/Undo 로그(Oracle)에 기록됩니다. 대량의 행을 삭제할 경우, 이로 인해 로그 파일이 급격하게 커져 성능 저하를 유발하고 막대한 디스크 공간을 차지할 수 있습니다.

 TRUNCATE는 '최소 로깅(minimally logged)' 작업입니다. 개별 행의 삭제를 기록하는 대신, 데이터 페이지나 익스텐트의 할당 해제 정보만을 로그에 기록합니다. 이러한 최소 로깅 방식이 TRUNCATE는 DELETE 보다 일반적으로 속도가 빠릅니다.

트리거 실행 동작

데이터베이스 트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 프로시저입니다.

• DELETE: 삭제되는 각 행에 대해 ON DELETE 트리거를 실행합니다. 이를 통해 복잡한 비즈니스 로직, 감사 추적, 연관 데이터 정리 등의 작업을 수행할 수 있습니다.
• TRUNCATE: 행 단위 처리를 우회하기 때문에 ON DELETE 트리거를 실행하지 않습니다.
• 예외: PostgreSQL은 다른 주요 DBMS에는 없는 고유한 기능으로, 전체 TRUNCATE 작업에 대해 한 번 실행되는 ON TRUNCATE 트리거를 지원합니다.

3. DBMS별 구현 및 동작 방식

DELETE와 TRUNCATE의 기본적인 차이점은 보편적이지만, 실제 구현 방식과 세부 동작은 각 DBMS마다 차이가 존재합니다. PostgreSQL, MySQL(InnoDB 스토리지 엔진 기준), Microsoft SQL Server, Oracle 4가지 RDBMS의 핵심적인 차이점을 비교하며 알아보겠습니다.

트랜잭션 동작 및 롤백(Rollback) 가능성

PostgreSQL & SQL Server: 트랜잭션이 보장되는 TRUNCATE

PostgreSQL과 SQL Server는 TRUNCATE를 트랜잭션이 보장되는 DDL 문으로 취급합니다. 즉, BEGIN TRANSACTION 블록 내에서 TRUNCATE를 실행한 후 ROLLBACK을 호출하면 테이블의 데이터가 완전히 복원됩니다.

• PostgreSQL의 메커니즘: PostgreSQL은 다중 버전 동시성 제어(MVCC) 시스템을 활용하여 이를 구현합니다. TRUNCATE가 실행되면, PostgreSQL은 테이블에 대한 새로운 물리적 파일(relfilenode)을 생성합니다. 트랜잭션이 COMMIT 되면 이 새로운 빈 파일이 공식적으로 테이블의 파일이 되고 이전 파일은 삭제 대기 상태가 됩니다. 반면 ROLLBACK이 실행되면, 새로 생성된 파일은 버려지고 이전 데이터 파일로 즉시 복귀합니다.
• SQL Server의 메커니즘: SQL Server에서 TRUNCATE는 최소 로깅 작업이지만, 페이지 할당 해제 정보가 트랜잭션 로그에 기록됩니다. ROLLBACK은 이 로그 정보를 사용하여 페이지 할당 해제를 되돌림으로써 테이블을 복원합니다.

MySQL (InnoDB) & Oracle: 암시적 커밋(Implicit Commit)

 MySQL과 Oracle에서는 DDL 문이 실행될 때 현재 진행 중인 트랜잭션이 Implicit하게 COMMIT되는 동작 방식을 가집니다. DDL문 실행 전과 후에 각각 COMMIT이 발생합니다.

 이러한 특성 때문에 TRUNCATE 문은 사용자가 정의한 더 큰 트랜잭션의 일부가 될 수 없으며, 일단 실행되면 롤백이 불가능합니다.

TRUNCATE 이전에 같은 트랜잭션 블록에서 수행된 모든 작업은 TRUNCATE가 실행되는 시점에 영구적으로 커밋됩니다.

 

 

다음 표는 각 DBMS별 TRUNCATE의 롤백 가능성을 요약한 것입니다.

DBMS	TRUNCATE 롤백 가능 여부	특징
PostgreSQL	O	MVCC 및 새로운 relfilenode 생성을 통해 구현.
SQL Server	O	최소 로깅되지만 롤백 가능한 작업.
MySQL (InnoDB)	X	암시적 COMMIT을 유발하여 트랜잭션의 일부가 될 수 없음.
Oracle	X	DDL 실행 전후에 암시적 COMMIT 발생

 

외래 키(Foreign Key) 제약 조건 처리 방식

외래 키에 의해 참조되는 테이블을 TRUNCATE를 사용해 비우려고 할 때 각 DBMS에는 서로 다른 규칙들이 존재합니다. 

• PostgreSQL: CASCADE 옵션을 제공합니다. TRUNCATE my_table CASCADE;를 실행하면 my_table을 참조하는 모든 테이블이 재귀적으로 함께 TRUNCATE됩니다. 이 기능은 강력하지만, 의도치 않은 대량 데이터 삭제로 이어질 수 있어 주의가 필요합니다. 기본값인 RESTRICT는 외래 키 참조가 존재할 경우 오류를 발생시킵니다.
• SQL Server: 외래 키 제약 조건에 의해 참조되는 테이블의 TRUNCATE를 엄격하게 금지합니다. CASCADE 옵션이나 세션 수준의 재정의 기능이 없습니다. 제약 조건을 스크립트로 저장한 후 삭제(DROP)하고, TRUNCATE를 수행한 다음, 제약 조건을 다시 생성하는 것이 대안이 될 수 있습니다.
• Oracle: SQL Server와 유사하게, 활성화된 외래 키 제약 조건의 부모 테이블을 TRUNCATE하는 것을 금지합니다. 제약 조건을 먼저 비활성화해야 합니다. Oracle 12c 부터 CASCADE 옵션이 도입되었는데, 외래 키가 ON DELETE CASCADE로 정의된 경우에만 작동합니다.
• MySQL: TRUNCATE에 대한 CASCADE 옵션이 없습니다. 외래 키 제약 조건이 존재하면 작업이 실패합니다. 일반적인 해결책은 세션 동안 일시적으로 외래 키 검사를 비활성화하는 것입니다.

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE my_table;
SET FOREIGN_KEY_CHECKS=1

 

• 위 방법 효과적이지만, 검사가 비활성화된 동안 다른 작업이 수행될 경우 데이터가 비일관적인 상태에 놓일 위험이 있어 주의가 필요합니다.

잠금(Locking), 동시성, 격리 수준

데이터 삭제 작업 중 발생하는 잠금 메커니즘은 시스템의 동시성에 큰 영향을 미칩니다.

• DELETE (보편적): 삭제되는 행에 대해 행 수준 잠금(예: PostgreSQL의 ROW EXCLUSIVE)을 획득합니다. 이를 통해 다른 트랜잭션이 동일한 테이블의 다른 행을 읽고 쓰는 작업을 계속할 수 있어 동시성을 극대화합니다.
• TRUNCATE (DBMS별 상이)
  • PostgreSQL: 테이블에 ACCESS EXCLUSIVE 잠금을 획득합니다. 가장 강력한 잠금으로, SELECT를 포함한 모든 다른 작업을 차단합니다.
  • SQL Server: 테이블에 스키마 수정(Sch-M) 잠금을 획득합니다. 모든 다른 작업을 차단하며, 단순 SELECT 쿼리가 획득하는 가벼운 스키마 안정성(Sch-S) 잠금과도 호환되지 않습니다.
  • MySQL: 테이블 메타데이터 잠금(METADATA_LOCK)을 필요로 하며, 이는 다른 세션이 동일한 테이블에 대해 DDL이나 다른 메타데이터 잠금을 필요로 하는 작업을 수행하는 것을 차단합니다.
  • Oracle: DDL 작업으로서 배타적인 DDL 잠금을 필요로 하여 다른 DDL 작업을 막고, 데이터 변경을 막기 위한 DML 잠금도 획득합니다.

 TRUNCATE는 테이블 수준 잠금이라는 동작 때문에 동시성이 높은 온라인 트랜잭션 처리(OLTP) 시스템에는 근본적으로 부적합합니다. 이 명령어를 실시간 운영 환경에서 사용하면 심각한 애플리케이션 차원의 블로킹과 타임아웃을 유발할 수 있습니다. 따라서 TRUNCATE는 유지보수 시간, ETL과 같은 배치 작업, 또는 개발/테스트 환경을 위한 도구로 간주되어야 합니다. 따라서, 높은 동시성이 요구되는 환경에서는 성능 비용을 감수하더라도 DELETE가 최선의 선택일 수 있습니다.

 

자동 생성 값(Identity/Auto-increment/Sequence) 관리

자동으로 증가하는 식별자 컬럼의 값은 DELETE와 TRUNCATE에 의해 다르게 처리됩니다.

• PostgreSQL: TRUNCATE는 시퀀스 값에 대한 명시적인 제어를 제공합니다. RESTART IDENTITY 옵션은 연결된 시퀀스를 시작 값으로 재설정하며, 기본값인 CONTINUE IDENTITY는 시퀀스 값을 그대로 유지합니다. DELETE는 시퀀스에 영향을 주지 않습니다.
• MySQL: TRUNCATE는 AUTO_INCREMENT 카운터를 초기 값으로 재설정합니다.
  DELETE는 카운터를 재설정하지 않으며, 다음 삽입되는 행은 마지막으로 가장 높았던 값에서 계속됩니다.
• SQL Server: TRUNCATE는 IDENTITY 컬럼을 원래의 시드(seed) 값으로 재설정합니다.
  DELETE는 그렇지 않습니다. DELETE 이후에 IDENTITY 값을 재설정하려면 DBCC CHECKIDENT('table_name', RESEED, new_value) 명령어를 사용해야 합니다.
• Oracle: 시퀀스는 테이블과 별개의 객체이므로 TRUNCATE가 자동으로 시퀀스를 재설정하지 않습니다. 재설정이 필요하다면 시퀀스를 수동으로 삭제하고 재생성하거나, 최신 버전(18c 이상)에서는 ALTER SEQUENCE... RESTART를 사용하여 더 간단하게 변경할 수 있습니다. DELETE는 시퀀스에 영향을 주지 않습니다.

 

로깅 내부 구조와 복구 가능성

TRUNCATE가 '로깅되지 않는다'는 오해와 달리, 모든 TRUNCATE 작업은 어떤 형태로든 복구를 위해 로그에 기록됩니다. 이러한 오해의 이유는 로깅되는 내용의 상세 수준 때문입니다.

• DELETE 로깅: 삭제된 각 행의 전체 데이터를 로그에 기록합니다. 이를 통해 특정 시점 복구(Point-in-Time Recovery, PITR)를 사용하여 DELETE 직전의 상태로 돌아갈 수 있으며, 로그를 역으로 재생하여 DELETE 작업을 '취소'할 수 있습니다.
• TRUNCATE 로깅: 페이지/익스텐트의 할당 해제 정보만 기록합니다. 이는 시스템 장애 시 복구(TRUNCATE가 완전히 완료되었거나 전혀 실행되지 않은 상태를 보장)와 트랜잭션 롤백(지원되는 DBMS의 경우)이 가능합니다.
• PITR에 미치는 영향: TRUNCATE는 행 데이터를 로그에 남기지 않기 때문에, 트랜잭션 로그만으로는 TRUNCATE 직전 시점으로 복구할 수 없습니다. 실수로 TRUNCATE를 실행한 경우, TRUNCATE가 발생하기 이전에 생성된 백업을 사용하여 전체 데이터베이스나 테이블스페이스를 복원한 후 원하는 시점까지 롤 포워드해야 합니다. 이 때문에 TRUNCATE로 인한 데이터 손실 복구는 DELETE에 비해 훨씬 더 복잡하고 시스템에 큰 영향을 미치는 작업이 됩니다.

4. DELETE, TRUNCATE 사용 

지금까지 살펴본 기술적 차이점을 바탕으로, 가상의 시나리오에 따라 어떤 명령어를 선택하는 것이 유리할지 알아보겠습니다.

4.1. ETL 프로세스에서 스테이징 테이블 비우기

• 권장: TRUNCATE
• 이유: 이 시나리오에서는 속도가 가장 중요합니다. 스테이징 테이블은 다음 데이터 로드를 위해 빠르고 완벽하게 비워져야 합니다. TRUNCATE의 압도적인 성능, 즉각적인 공간 회수, IDENTITY 값 초기화는 ETL 작업에 이상적입니다. 트랜잭션 안전성은 보통 ETL 작업 전체의 상태 관리로 처리되며, 외래 키 제약 조건은 스테이징 테이블에 거의 존재하지 않습니다.

4.2. 테스트 환경 초기화

• 권장: TRUNCATE
• 이유: 테스트를 반복 수행할 때마다 깨끗한 상태의 데이터베이스가 필요합니다. TRUNCATE는 테이블을 빠르게 비울 뿐만 아니라, IDENTITY나 AUTO_INCREMENT 값을 초기화하여 매 테스트가 일관된 조건에서 시작되도록 보장합니다. 이는 예측 가능한 테스트 결과를 얻는 데 매우 중요합니다.

4.3. 운영 환경의 대용량 테이블에서 오래된 데이터 아카이빙 또는 삭제

• 권장: DELETE (배치 처리)
• 이유: 이 작업은 특정 조건을 만족하는 데이터만 선별적으로 삭제해야 하므로 WHERE 절이 필수적입니다 (예: WHERE created_at < '2022-01-01'). TRUNCATE는 이 경우 사용할 수 없습니다. 또한, 한 번에 수백만 건의 데이터를 DELETE하면 트랜잭션 로그가 폭증하고 장시간 잠금이 발생하여 운영 시스템에 심각한 영향을 줄 수 있습니다. 따라서 DELETE를 수천에서 수만 건 단위의 작은 배치(batch)로 나누어 반복 실행하고 각 배치마다 COMMIT하는 전략이 필수적입니다.

4.4. 실시간 운영 중인 테이블의 모든 데이터 삭제

• 권장: 신중한 접근 필요 (주로 배치 DELETE) + DBMS 특성 고려
• 이유: 매우 위험한 작업입니다. 시스템의 동시성을 반드시 유지해야 한다면, 앞서 설명한 배치 DELETE 방식이 유일한 선택지입니다. 짧은 시간 동안의 서비스 중단(유지보수 시간)이 허용된다면 TRUNCATE가 훨씬 빠릅니다. 하지만 이때 DBMS의 특성을 고려해야 합니다. MySQL이나 Oracle에서는 롤백이 불가능한 TRUNCATE 실행 후 다른 문제가 발생할 경우 데이터가 비어있는 치명적인 상태에 놓일 수 있습니다. 

 

 

 

 

참고자료:

https://www.cybertec-postgresql.com/en/postgresql-delete-vs-truncate/

PostgreSQL: DELETE vs. TRUNCATE

https://www.rockdata.net/docs/17/sql-createtrigger.html

PostgreSQL 17 Documentation: CREATE TRIGGER - Redrock Postgres

 

https://dev.mysql.com/doc/refman/9.4/en/truncate-table.html

MySQL :: MySQL 9.4 Reference Manual :: 15.1.42 TRUNCATE TABLE Statement

https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver17

TRUNCATE TABLE (Transact-SQL) - SQL Server

https://stackoverflow.com/questions/3256242/pros-cons-of-truncate-vs-delete-from

Pros & Cons of TRUNCATE vs DELETE FROM