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

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

요구 사항:

열과 관련된 속성 수정(INSERT, UPDATE)은 주로 ActiveRecord를 통해서 발생합니다(기법은 AR 콜백에 따라 다름).
중복은 드물며 대부분 동시 레코드 생성으로 인해 발생합니다. 이는 프로덕션 데이터베이스 테이블을 통해 확인할 수 있습니다. (도움이 필요한 경우 데이터베이스 유지 관리자에게 문의하십시오).

총 실행시간은 주로 데이터베이스 테이블의 레코드 수에 따라 달라집니다. 마이그레이션은 모든 레코드를 스캔해야 하며, 배포 후 마이그레이션 실행시간 한도 (약 10분)에 맞춰야 합니다. 약 1천만 개 미만의 행을 가진 데이터베이스 테이블은 작은 테이블로 간주될 수 있습니다.

작은 테이블에 대한 중복 제거 전략

이 전략은 3가지 단계를 요구합니다. 예를 들어, title 열을 기반으로 project_id 열에 대해 title이 고유해야 하는 issues 테이블의 중복을 제거할 것입니다.

1단계:

테이블에 새 데이터베이스 인덱스(고유하지 않음)를 포스트 마이그레이션을 통해 추가합니다(이미 존재하지 않은 경우).
중복 생성을 방지하기 위해 모델 레벨의 고유성 유효성 검사를 추가합니다(이미 존재하지 않은 경우).
중복 레코드 생성을 방지하기 위해 트랜잭션 레벨의 advisory lock을 추가합니다.

개별적으로 2번째 단계는 중복 레코드를 방지하지 않을 것이며, 자세한 내용은 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 모델 유효성 검사 및 advisory lock:

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단계:

포스트 배포 마이그레이션에서 중복 제거 논리를 구현합니다.
기존 인덱스를 고유 인덱스로 교체합니다.

중복 항목 해결 방법(예: 속성 병합, 가장 최근 레코드 유지)은 데이터베이스 테이블 상에서 구축된 기능에 따라 다를 수 있습니다. 이 예에서는 가장 최근 레코드를 유지합니다.

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단계:

prevent_concurrent_inserts ActiveRecord 콜백 메서드를 제거하여 advisory lock을 제거합니다.

참고: 이 단계는 required stop 이후에 이루어져야 합니다.

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

대규모 테이블을 중복 제거할 때 우리는 배칭(batching)과 중복 제거 논리를 배칭된 백그라운드 마이그레이션으로 옮길 수 있습니다.