I have an endpoint that accepts incoming data, checks it for errors and imports into the database. Incoming data can be up to 300 000 rows. Stack is - Ruby on Rails, Postgres, Redis, Sidekiq, dry-validation. Current flow:
- load data into Redis;
- prepare/transform;
- validate and mark every row as valid/invalid;
- fetch valid rows and bulk import them.
I need an advice on how to improve the performance of the validation step here because sometimes it takes more than a day to validate a large file.
Some details
It basically loops through every row in the background and applies validation rules like
rows.each do |row|
result = validate(row)
set_status(row, result) # mark as valid/invalid
end
Some validation rules are uniqueness checks - and they're heavy because they check uniqueness across the whole database. Example:
rule(:sku, :name) do
if Product.where(sku: values[:sku]).where.not(name: values[:name]).exists?
# add error
end
end
Needless to say, DB & logs are going mad during validation.
Another approach I tried was to pluck necessary fields from all database records, then loop through and compare every row with this array rather than make DB requests. But comparing with a huge array appeared to be even slower.
def existing_data
@existing_data ||= Product.pluck(:sku, :name, ...)
end
rule(:sku, :name) do
conflict = existing_data.find do |data|
data[0] == values[:sku] && data[1] != values[:name]
end
if conflict.present?
# add error
end
end