소프트 삭제(deleted_at)가 느려지는 이유 — 부분 인덱스와 유니크 재설계

데이터를 실제로 지우지 않고 deleted_at 만 채우는 방식으로 만들었어요. 복구도 되고 감사에도 좋아서 합리적인 선택처럼 보였고요.

2년이 지나니 테이블의 70%가 삭제된 행이에요. 조회는 전부 WHERE deleted_at IS NULL 을 달고 도는데 점점 느려져요.

인덱스를 추가해도 나아지지 않아요. 오히려 인덱스가 커지기만 해서 쓰기가 더 느려집니다.

어느 시점부터는 "이 테이블은 원래 느리다"가 팀의 상식이 돼요. 원인이 설계에 있는데 튜닝으로 풀려니 안 풀리는 거죠. 그래서 아무도 다시 건드리지 않는 테이블이 됩니다.

왜 그런가

소프트 삭제는 "지우지 않는다"는 선택이에요. 그래서 지우지 않은 데이터의 비용을 전부 계속 냅니다.

DB 는 그 행이 논리적으로 삭제됐다는 걸 몰라요. 인덱스에 그대로 들어 있고, 스캔할 때 읽히고, 캐시를 차지하고, 청소 대상에도 들어가요. 삭제 비율이 올라갈수록 같은 결과를 얻는 데 읽어야 하는 양이 늘어납니다.

조건이 늘어나는 것도 문제예요. 모든 조회에 deleted_at IS NULL 이 붙으니, 기존 인덱스들이 이 조건과 궁합이 맞아야 해요. (user_id) 인덱스로 찾은 뒤 deleted_at 을 걸러야 한다면, 삭제 비율이 높을수록 걸러 버리는 행이 많아져요. 인덱스를 탔는데도 느린 전형적인 형태예요.

그리고 유니크 제약이 깨져요. 이메일에 유니크를 걸어 뒀는데 사용자가 탈퇴했다가 같은 이메일로 재가입하면 충돌해요. 논리적으로는 지워진 행인데 DB 는 살아 있는 행으로 보니까요. 그래서 소프트 삭제를 도입하는 순간 유니크 제약을 다시 설계해야 합니다.

마지막으로 조건을 빠뜨리는 사고가 생겨요. 새로 짠 쿼리 하나에 IS NULL 을 안 붙이면 삭제된 데이터가 노출돼요. 이건 성능이 아니라 사고입니다.

코드로 보면

부분 인덱스로 산 것만 담는다:

✕ CREATE INDEX idx_user ON posts(user_id);
✕ -- 삭제된 행까지 전부 들어간다

· 살아 있는 행만 인덱싱 (PostgreSQL 부분 인덱스)
✓ CREATE INDEX idx_user_alive ON posts(user_id)
✓ WHERE deleted_at IS NULL;

· 유니크도 부분 인덱스로
✓ CREATE UNIQUE INDEX uq_email_alive ON users(email)
✓ WHERE deleted_at IS NULL;
· MySQL 에는 부분 인덱스가 없다 —
· 생성 컬럼에 유니크를 걸어 우회한다

· 조건 누락을 코드가 아니라 DB 로 막는다
✓ CREATE VIEW active_posts AS
✓ SELECT * FROM posts WHERE deleted_at IS NULL;

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

부분 인덱스가 이 문제의 핵심 도구예요. PostgreSQL 에서 WHERE deleted_at IS NULL 조건을 인덱스 자체에 넣으면, 인덱스가 살아 있는 행만 담아요. 삭제 비율이 70% 면 인덱스가 30% 크기가 되고, 그만큼 캐시에 잘 들어가고 스캔도 빨라져요.

유니크 문제도 부분 유니크 인덱스로 풀려요. 살아 있는 행 중에서만 유일하면 되니까요. MySQL 에는 부분 인덱스가 없어서, deleted_at 을 포함한 생성 컬럼을 만들어 유니크를 거는 우회가 흔히 쓰여요.

그런데 부분 인덱스도 만능은 아니에요. 옵티마이저가 그 인덱스를 쓰려면 쿼리 조건이 인덱스의 조건을 논리적으로 포함해야 해요. 쿼리가 deleted_at IS NULL 을 정확히 쓰지 않고 deleted_at = NULL 같은 잘못된 형태를 쓰거나, 조건이 파라미터로 들어와 상수가 아니면 인덱스를 못 탈 수 있어요.

더 근본적인 질문은 "정말 안 지워도 되는가"예요. 소프트 삭제를 도입한 이유가 복구 가능성이라면, 실제로 복구 요청이 얼마나 오는지 세어 보세요. 대부분의 서비스에서 30일이 지난 데이터의 복구 요청은 거의 없어요. 그렇다면 30일 뒤 실제 삭제로 옮기고, 필요하면 별도 아카이브 테이블에 넣는 게 훨씬 쌉니다.

감사 목적이라면 소프트 삭제는 애초에 적합한 도구가 아니에요. 감사는 "누가 언제 무엇을 했는가"의 기록이 필요한데, deleted_at 하나로는 그걸 못 남겨요. 별도 감사 로그가 맞는 답입니다.

그리고 개인정보보호 관점도 있어요. 삭제 요청을 받은 데이터를 소프트 삭제로만 처리하면 실제로는 안 지운 거예요. 법적 요구를 만족하지 못할 수 있습니다.

처방

1. 살아 있는 행만 담는 부분 인덱스로 바꾸세요. PostgreSQL 이면 WHERE deleted_at IS NULL 을 인덱스에 넣으세요. 가장 효과가 크고 위험이 작은 조치예요.
2. 유니크 제약을 다시 설계하세요. 부분 유니크 인덱스로 살아 있는 행에만 적용하거나, MySQL 이면 생성 컬럼 우회를 쓰세요.
3. 조건 누락을 DB 로 막으세요. 뷰나 행 수준 보안으로 기본 경로를 만들면, 새로 짠 쿼리가 실수로 삭제된 행을 보지 않아요. ORM 의 기본 스코프도 같은 역할을 합니다.
4. 보존 기간을 정하고 실제로 지우세요. 30일 뒤 하드 삭제, 필요하면 아카이브 테이블로 이동. 이게 근본 해결이에요. 소프트 삭제는 유예 기간이지 영구 보관 방식이 아닙니다.
5. 삭제 비율을 지표로 보세요. 테이블별 삭제 행 비율이 일정 수준을 넘으면 알림을 거세요. 서서히 나빠지는 문제라 지표가 없으면 못 알아챕니다.
6. 감사가 목적이면 별도 감사 로그를 만드세요. 소프트 삭제로 감사를 대신하려는 설계가 두 목적 다 못 채우는 경우가 많아요.

지금 확인해 보세요

얼마나 죽은 데이터를 이고 있나:

· 테이블별 삭제 비율
SELECT count(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted,
       count(*) AS total,
       round(100.0 * count(*) FILTER (WHERE deleted_at IS NOT NULL)
             / nullif(count(*),0), 1) AS pct
  FROM posts;
✕ 50% 를 넘으면 인덱스의 절반이 죽은 데이터다

· 부분 인덱스가 실제로 쓰이는지
EXPLAIN (ANALYZE)
SELECT * FROM posts
 WHERE user_id = 1 AND deleted_at IS NULL;
✓ Index Scan using idx_user_alive 여야 한다

· 조건을 빠뜨린 쿼리가 있는지 (운영)
✓ pg_stat_statements 에서 해당 테이블을 참조하면서
✓ deleted_at 이 없는 쿼리를 찾는다

핵심 한 줄소프트 삭제는 유예지 보관이 아니다. 기한을 정하고 실제로 지워라.