Deduplicate database records in a database table
This guide describes a strategy for introducing database-level uniqueness constraint (unique index) to existing database tables with data.
Requirements:
- Attribute modifications (
INSERT
,UPDATE
) related to the columns happen only via ActiveRecord (the technique depends on AR callbacks). - Duplications are rare and mostly happen due to concurrent record creation. This can be verified by checking the production database table via teleport (reach out to a database maintainer for help).
The total runtime mainly depends on the number of records in the database table. The migration will require scanning all records; to fit into the post-deployment migration runtime limit (about 10 minutes), database table with less than 10 million rows can be considered a small table.
Deduplication strategy for small tables
The strategy requires 3 milestones. As an example, we're going to deduplicate the issues
table based on the title
column where the title
must be unique for a given project_id
column.
Milestone 1:
- Add a new database index (not unique) to the table via post-migration (if not present already).
- Add model-level uniqueness validation to reduce the likelihood of duplicates (if not present already).
- Add a transaction-level advisory lock to prevent creating duplicate records.
The second step on its own will not prevent duplicate records, see the Rails guides for more information.
Post-migration for creating the index:
def up
add_concurrent_index :issues, [:project_id, :title], name: INDEX_NAME
end
def down
remove_concurrent_index_by_name :issues, INDEX_NAME
end
The Issue
model validation and the advisory lock:
class Issue < ApplicationRecord
validates :title, uniqueness: { scope: :project_id }
before_validation :prevent_concurrent_inserts
private
# This method will block while another database transaction attempts to insert the same data.
# After the lock is released by the other transaction, the uniqueness validation may fail
# with record not unique validation error.
# Without this block the uniqueness validation wouldn't be able to detect duplicated
# records as transactions can't see each other's changes.
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
Milestone 2:
- Implement the deduplication logic in a post deployment migration.
- Replace the existing index with a unique index.
How to resolve duplicates (e.g., merge attributes, keep the most recent record) depends on the features built on top of the database table. In this example, we keep the most recent record.
def up
model = define_batchable_model('issues')
# Single pass over the table
model.each_batch do |batch|
# find duplicated (project_id, title) pairs
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
# Locate all records by (project_id, title) pairs and keep the most recent record.
# The lookup should be fast enough if duplications are rare.
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
NOTE: This is a destructive operation with no possibility of rolling back. Make sure that the deduplication logic is tested thoroughly.
Replacing the old index with a unique index:
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
Milestone 3:
- Remove the advisory lock by removing the
prevent_concurrent_inserts
ActiveRecord callback method.
NOTE: This milestone must be after a required stop.
Deduplicate strategy for large tables
When deduplicating a large table we can move the batching and the deduplication logic into a batched background migration.
Milestone 1:
- Add a new database index (not unique) to the table via post migration.
- Add model-level uniqueness validation to reduce the likelihood of duplicates (if not present already).
- Add a transaction-level advisory lock to prevent creating duplicate records.
Milestone 2:
- Implement the deduplication logic in a batched background migration and enqueue it in a post deployment migration.
Milestone 3:
- Finalize the batched background migration.
- Replace the existing index with a unique index.
- Remove the advisory lock by removing the
prevent_concurrent_inserts
ActiveRecord callback method.
NOTE: This milestone must be after a required stop.