데이터베이스 테이블에서 중복된 레코드 제거하기

이 가이드는 데이터가 있는 기존 데이터베이스 테이블에 데이터베이스 수준의 고유성 제약 조건(고유 인덱스)을 도입하는 전략을 설명합니다.

요구 사항:

  • ActiveRecord를 통해 열과 관련된 속성 수정(INSERT, UPDATE)이 이루어져야 합니다(이 기술은 AR 콜백에 의존합니다).
  • 중복은 드물고 주로 동시 레코드 생성으로 인해 발생합니다. 이는 프로덕션 데이터베이스 테이블을 텔레포트를 통해 확인하여 검증할 수 있습니다(도움이 필요하면 데이터베이스 유지 관리 담당자에게 문의하세요).

전체 실행 시간은 주로 데이터베이스 테이블의 레코드 수에 따라 다릅니다. 마이그레이션은 모든 레코드를 스캔해야 하며, 배포 후 마이그레이션 실행 시간 제한(약 10분)에 맞추기 위해 1,000만 개 미만의 행을 가진 데이터베이스 테이블은 작은 테이블로 간주될 수 있습니다.

작은 테이블을 위한 중복 제거 전략

이 전략은 3개의 이정표를 요구합니다. 예를 들어, title 열을 기준으로 issues 테이블의 중복을 제거할 것입니다. 여기서 title은 특정 project_id 열에 대해 유일해야 합니다.

이정표 1:

  1. 테이블에 새로운 데이터베이스 인덱스(고유하지 않음)를 추가합니다(이미 존재하지 않는 경우).
  2. 중복 가능성을 줄이기 위해 모델 수준의 고유성 검증을 추가합니다(이미 존재하지 않는 경우).
  3. 중복 레코드 생성을 방지하기 위해 트랜잭션 수준의 자문 잠금을 추가합니다.

두 번째 단계만으로는 중복 레코드를 방지할 수 없으니, 더 많은 정보는 Rails 가이드를 참조하세요.

인덱스를 생성하기 위한 후속 마이그레이션:

def up
  add_concurrent_index :issues, [:project_id, :title], name: INDEX_NAME
end

def down
  remove_concurrent_index_by_name :issues, INDEX_NAME
end

Issue 모델 검증 및 자문 잠금:

class Issue < ApplicationRecord
  validates :title, uniqueness: { scope: :project_id }
  before_validation :prevent_concurrent_inserts

  private

  # 이 메소드는 다른 데이터베이스 트랜잭션이 동일한 데이터를 삽입하려고 할 때 차단됩니다.
  # 잠금이 다른 트랜잭션에 의해 해제된 후에는 고유성 검증이 레코드가 유일하지 않다는 오류로 실패할 수 있습니다.

  # 이 블록 없이는 고유성 검증이 중복 레코드를 감지할 수 없습니다. 
  # 트랜잭션은 서로의 변경 사항을 볼 수 없기 때문입니다.
  def prevent_concurrent_inserts
    return if project_id.nil? || title.nil?

    lock_key = ['issues', project_id, title].join('-')
    lock_expression = "hashtext(#{connection.quote(lock_key)})"
    connection.execute("SELECT pg_advisory_xact_lock(#{lock_expression})")
  end
end

이정표 2:

  1. 배포 후 마이그레이션에서 중복 제거 로직을 구현합니다.
  2. 기존 인덱스를 고유 인덱스로 교체합니다.

중복 해결 방법(예: 속합치기, 최신 레코드 유지)는 데이터베이스 테이블 위에 구축된 기능에 따라 다릅니다. 이 예에서는 최신 레코드를 유지합니다.

def up
  model = define_batchable_model('issues')

  # 테이블에 대한 단일 패스
  model.each_batch do |batch|
    # 중복된 (project_id, title) 쌍 찾기
    duplicates = model
      .where("(project_id, title) IN (#{batch.select(:project_id, :title).to_sql})")
      .group(:project_id, :title)
      .having('COUNT(*) > 1')
      .pluck(:project_id, :title)

    value_list = Arel::Nodes::ValuesList.new(duplicates).to_sql

    # (project_id, title) 쌍으로 모든 레코드 위치를 확인하고 최신 레코드를 유지합니다.
    # 중복이 드물 경우 조회가 충분히 빨라야 합니다.
    cleanup_query = <<~SQL
    WITH duplicated_records AS MATERIALIZED (
      SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY project_id, title ORDER BY project_id, title, id DESC) AS row_number
      FROM issues
      WHERE (project_id, title) IN (#{value_list})
      ORDER BY project_id, title
    )
    DELETE FROM issues
    WHERE id IN (
      SELECT id FROM duplicated_records WHERE row_number > 1
    )
    SQL

    model.connection.execute(cleanup_query)
  end
end

def down
  # no-op
end

참고: 이것은 되돌릴 수 없는 파괴적인 작업입니다. 중복 제거 로직이 철저히 테스트되었는지 확인하세요.

기존 인덱스를 고유 인덱스로 교체하기:

def up
  add_concurrent_index :issues, [:project_id, :title], name: UNIQUE_INDEX_NAME, unique: true
  remove_concurrent_index_by_name :issues, INDEX_NAME
end

def down
  add_concurrent_index :issues, [:project_id, :title], name: INDEX_NAME
  remove_concurrent_index_by_name :issues, UNIQUE_INDEX_NAME
end

이정표 3:

  1. prevent_concurrent_inserts ActiveRecord 콜백 메소드를 제거하여 자문 잠금을 제거합니다.

참고: 이 이정표는 필수 정지 이후에 있어야 합니다.

대규모 테이블에 대한 중복 제거 전략

대규모 테이블의 중복을 제거할 때 배치 처리 및 중복 제거 로직을 배치 백그라운드 마이그레이션으로 이동할 수 있습니다.

이정표 1:

  1. 마이그레이션 후 테이블에 새로운 데이터베이스 인덱스(고유하지 않음)를 추가하십시오.
  2. 중복 가능성을 줄이기 위해 모델 수준의 고유성 검증을 추가하십시오(이미 존재하지 않는 경우).
  3. 중복 기록 생성을 방지하기 위해 트랜잭션 수준의 자문 잠금을 추가하십시오.

이정표 2:

  1. 배치 백그라운드 마이그레이션에서 중복 제거 로직을 구현하고 이를 배포 후 마이그레이션에 대기열에 추가하십시오.

이정표 3:

  1. 배치 백그라운드 마이그레이션을 완료하십시오.
  2. 기존 인덱스를 고유 인덱스로 교체하십시오.
  3. prevent_concurrent_inserts ActiveRecord 콜백 메서드를 제거하여 자문 잠금을 제거하십시오.

참고: 이 이정표는 필수 중지 이후에 수행해야 합니다.