Partial Index로 특정 조건에서만 유니크 제약을 적용할 수 있다

요약

• Partial Index는 특정 조건을 만족하는 행에만 인덱스를 적용하는 기법이다
• 유저별 단일 실행 job 같은 상황에서 이력은 보존하면서 중복 실행을 방지할 수 있다

본문

문제 상황

유저별로 하나의 job만 실행하고 싶을 때 일반 unique constraint를 사용하면:

UNIQUE (user_id, status)

• completed, failed 상태도 하나씩만 저장 가능
• 과거 이력 보존 불가

Partial Index (PostgreSQL)

CREATE UNIQUE INDEX idx_user_running
ON jobs (user_id)
WHERE status IN ('pending', 'running');

• pending, running 상태일 때만 user_id 유니크 적용
• completed, failed는 여러 개 저장 가능 → 이력 보존됨

MySQL 대안 (Partial Index 미지원)

가상 컬럼(Generated Column)과 NULL 특성 활용:

ALTER TABLE jobs ADD COLUMN active_lock VARCHAR(36)
  GENERATED ALWAYS AS (
    CASE WHEN status IN ('pending', 'running') THEN user_id ELSE NULL END
  );
CREATE UNIQUE INDEX idx_active_lock ON jobs (active_lock);

• NULL은 unique 제약에서 제외되는 점을 이용
• 활성 상태일 때만 user_id 값이 들어가고, 완료되면 NULL → 중복 허용

참고

• Scheduler Job은 처리 시간을 고려해 중첩 실행을 방지해야 한다