PostgreSQL locks in Rails Migrations
When we need to update huge data by adding a rails Migrations or a rake/Thor task. We should worry about the PostgreSQL locks. Every time when we work with the production data for updating, there is always an risk of locking the database. By locking the database, I mean, the data do not get updated, or simply it would delay the process.
So let's see the case of locking the PostgreSQL:
So, In this example if we need to update the default value of available from nil to false, and let's say we have 1 million of data, this migration will only add the new values and won't affect the old values.
class SetAvailableDefaultValueOnArticles < ActiveRecord::Migration[5.0]
def change
change_column_default :articles, :available, from: nil, to: false
end
end
And when we need to update the old values, we might write another migration or rake command to update.
i.e
Article.where(available: nil).update_all(available: false)
So, Looking at this command, it should update the default value from nil to false.
In this,the main problem will be locking the postgreSQL. It will break the process and only half or 20% percent of data might get updated and also the execution might take days.
So as a solution we could update the data in batches to overcome risk of the table locks:
At first we need to update the fields by adding the migration to add default value.
class SetAvailableDefaultValueOnArticles < ActiveRecord::Migration[5.0]
def change
change_column_default :articles,:available,from: nil,to: false
end
end
And then we write another migration or thor/rake task to update the value in batches.
class BackportAvailableColumnInArticles < ActiveRecord::Migration[5.0]
def up
say_with_time "Backport articles.available default" do
Article.unscoped.select(:id).find_in_batches.with_index do |batch, index|
say("Processing batch #{index}\r", true)
Article.unscoped.where(id: batch, available: nil).update_all(available: false)
end
end
end
end
To use unscoped there must be a method in Article.rb
For.eg.
class Article < ActiveRecord::Base
def self.default_scope
where :available => nil
end
end
If you don't have method in Article, we can simply prefer this way:
class BackportAvailableColumnInArticles < ActiveRecord::Migration[5.0]
def up
say_with_time "Backport articles.available default" do
Article.all.select(:id).find_in_batches.with_index do |batch, index|
say("Processing batch #{index}\r", true)
Article.all.where(id: batch, available: nil).update_all(available: false)
end
end
end
end
I fit the solution in my way, so yes there might be other effective way to solve this table locks in PostgreSQL.
Comments
Post a Comment