CS2026.10.016 min read

DELETE: 행 삭제의 MVCC 동작과 물리적 공간 회수의 차이

DELETE는 테이블에서 조건에 맞는 행(row)을 제거하는 SQL 데이터 조작어(DML)다. PostgreSQL에서는 행을 즉시 물리적으로 지우지 않고 삭제 표시만 하며, 이후 VACUUM이 공간을 재사용 가능하게 만든다. 이 글은 DELETE의 동작 원리, RETURNING, 외래 키(FK) 연쇄 삭제, 대량 삭제 전략, TRUNCATE와의 차이를 다룬

By 송민성

1. 개념

DELETE는 테이블에서 WHERE 조건을 만족하는 행(row)을 제거하는 데이터 조작어(DML, Data Manipulation Language) 문이다.

sql

DELETE FROM 테이블명
WHERE 조건;

WHERE를 생략하면 테이블의 모든 행이 삭제된다. 테이블 구조(스키마)와 인덱스 정의는 그대로 남는다. 이 점에서 테이블 자체를 제거하는 DROP TABLE, 모든 행을 빠르게 비우는 TRUNCATE와 구분된다.

2. 왜 사용하는가

만료된 세션, 임시 데이터, 탈퇴 회원 정보처럼 더 이상 필요 없는 데이터를 제거한다.
개인정보 삭제 요구처럼 법적·정책적 이유로 데이터를 지워야 한다.
잘못 입력된 데이터를 정정한다.
테이블 크기를 관리해 조회 성능과 저장 비용을 통제한다.

3. 동작 원리

3.1 논리적 삭제 표시: MVCC

PostgreSQL은 다중 버전 동시성 제어(MVCC, Multi-Version Concurrency Control)를 사용한다. DELETE가 실행되어도 해당 행 버전(tuple)은 디스크에서 즉시 사라지지 않는다. 대신 행 헤더의 xmax에 삭제한 트랜잭션 ID가 기록된다.

삭제 트랜잭션이 커밋되기 전에는 다른 트랜잭션이 여전히 그 행을 볼 수 있다(격리 수준에 따라 다름).
커밋된 이후 시작한 스냅샷에서는 그 행이 보이지 않는다.
이 행 버전을 "죽은 튜플(dead tuple)"이라 부른다. 어떤 활성 트랜잭션도 더 이상 볼 필요가 없게 되면 정리 대상이 된다.

3.2 물리적 정리: VACUUM

죽은 튜플이 차지한 공간은 VACUUM(또는 autovacuum)이 회수한다. 다만 일반 VACUUM은 확보한 공간을 같은 테이블에서 재사용할 수 있게 표시할 뿐, 보통 운영체제에 파일 크기를 돌려주지 않는다. 파일 끝쪽 페이지가 완전히 비어 있는 경우에는 파일이 줄어들 수 있다. 파일을 재작성해 크기를 줄이려면 VACUUM FULL을 쓰는데, 이는 테이블에 배타적 잠금(ACCESS EXCLUSIVE)을 건다.

3.3 인덱스와 제약 조건

삭제된 행을 가리키는 인덱스 항목도 VACUUM 때 정리된다.
외래 키(FK, Foreign Key)가 이 테이블을 참조하면 ON DELETE 동작이 적용된다: NO ACTION(기본값), RESTRICT, CASCADE, SET NULL, SET DEFAULT.
행 삭제 트리거(BEFORE/AFTER DELETE)가 있으면 실행된다.

4. 코드 예제

아래는 PostgreSQL에서 그대로 실행할 수 있는 예제다.

sql

-- 준비
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active'
);

CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount  INT NOT NULL
);

INSERT INTO users (name, status) VALUES
    ('kim', 'active'), ('lee', 'inactive'), ('park', 'inactive');
INSERT INTO orders (user_id, amount) VALUES (1, 100), (2, 200), (2, 300), (3, 400);

-- 1) 조건부 삭제 + RETURNING으로 삭제된 행 확인
DELETE FROM users
WHERE name = 'lee'
RETURNING id, name;
-- orders의 user_id = 2 행은 ON DELETE CASCADE로 함께 삭제된다.

-- 2) 다른 테이블을 조건에 사용: USING
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.status = 'inactive';

-- 3) 서브쿼리 조건
DELETE FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- 4) 삭제 전 반드시 SELECT로 대상 확인 (트랜잭션 활용)
BEGIN;
DELETE FROM users WHERE status = 'inactive' RETURNING *;
-- 결과가 의도와 다르면
ROLLBACK;
-- 맞다면 COMMIT;

대량 삭제는 배치로 나누면 잠금 시간과 WAL 생성량을 분산할 수 있다.

sql

-- 한 번에 최대 1000건씩 삭제 (결과가 0건이 될 때까지 반복 실행)
DELETE FROM orders
WHERE id IN (
    SELECT id FROM orders
    WHERE amount < 150
    ORDER BY id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
);

PostgreSQL의 DELETE에는 LIMIT 구문이 없으므로 위처럼 서브쿼리로 대상을 제한한다.

5. 시간 복잡도 또는 성능 특성

정확한 시간 복잡도는 쿼리 계획과 데이터 분포에 따라 달라진다. 일반적인 특성은 다음과 같다.

대상 탐색: WHERE 컬럼에 B-tree 인덱스가 있고 인덱스 스캔이 선택되면 탐색은 O(log n)이다. 인덱스가 없거나 순차 스캔(Seq Scan)이 선택되면 O(n)이다.
삭제 자체: 삭제할 행 수 k에 비례한다. 각 행마다 xmax 기록, WAL(Write-Ahead Log) 기록, 트리거·FK 검사가 발생한다.
FK 검사: 이 테이블을 참조하는 자식 테이블의 FK 컬럼에 인덱스가 없으면 부모 행 삭제마다 자식 테이블을 스캔해야 해 매우 느려질 수 있다. PostgreSQL은 FK 컬럼에 인덱스를 자동 생성하지 않는다.
TRUNCATE와 비교: TRUNCATE는 행을 하나씩 처리하지 않고 테이블 파일을 통째로 교체하므로 전체 삭제 시 DELETE보다 보통 훨씬 빠르다. 행 단위 DELETE 트리거는 실행되지 않는다.

EXPLAIN (ANALYZE)로 실제 계획을 확인하되, DELETE가 실제로 실행되므로 트랜잭션 안에서 ROLLBACK과 함께 쓴다.

sql

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE user_id = 1;
ROLLBACK;

6. 실무 사용 사례

소프트 삭제(soft delete): deleted_at 컬럼을 두고 UPDATE로 표시만 한다. 복구와 감사가 필요한 서비스에서 흔하다. 대신 모든 조회에 조건이 붙어야 하고, 유니크 제약은 부분 인덱스(WHERE deleted_at IS NULL)로 보완한다.
보관 기간 정리: 로그·세션·토큰 테이블에서 오래된 행을 주기적으로 배치 삭제한다.
파티셔닝 활용: 시간 단위로 파티션을 나눈 테이블은 오래된 파티션을 DROP TABLE 또는 DETACH PARTITION으로 제거해 대량 DELETE와 VACUUM 부담을 피한다.
큐 처리: DELETE ... RETURNING과 FOR UPDATE SKIP LOCKED를 결합해 작업을 하나씩 꺼내는 패턴을 쓴다.

sql

DELETE FROM orders
WHERE id = (
    SELECT id FROM orders
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING *;

7. 주의할 점

`WHERE` 누락: 전체 행이 삭제된다. 실행 전 SELECT로 대상을 확인하고 트랜잭션을 사용한다.
디스크 공간이 즉시 줄지 않는다: 일반 VACUUM 후에도 파일 크기는 대부분 그대로다. 공간 반환이 목적이면 VACUUM FULL이나 TRUNCATE, 파티션 제거를 검토한다.
`NOT IN`과 NULL: 서브쿼리 결과에 NULL이 하나라도 있으면 NOT IN은 어떤 행에도 참이 되지 않아 아무것도 삭제되지 않는다. NOT EXISTS를 권장한다.
CASCADE의 파급 범위: 연쇄 삭제는 의도치 않게 많은 데이터를 지울 수 있다. 중요한 관계는 RESTRICT로 두는 것도 방법이다.
대량 삭제의 부작용: 긴 트랜잭션은 잠금을 오래 잡고, WAL과 복제 지연을 키우며, 죽은 튜플 정리를 막을 수 있다. 배치로 나눠 커밋한다.
복구: 커밋된 DELETE는 ROLLBACK으로 되돌릴 수 없다. 백업이나 시점 복구(PITR)에 의존해야 한다.

8. 핵심 정리

DELETE는 조건에 맞는 행을 제거하는 DML이며, WHERE가 없으면 전체 행이 대상이다.
PostgreSQL에서는 MVCC 때문에 삭제 시 행에 삭제 표시(xmax)만 하고, 실제 공간은 VACUUM이 재사용 가능하게 만든다.
RETURNING, USING, 서브쿼리를 활용하면 삭제 결과 확인과 다중 테이블 조건 처리가 쉬워진다.
FK 자식 컬럼에는 인덱스를 직접 만들어야 부모 삭제가 느려지지 않는다.
전체 삭제는 TRUNCATE, 기간 단위 삭제는 파티션 제거, 대량 삭제는 배치 분할이 유리하다.