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.


For better visualization, click here.

Comments

Popular posts from this blog

Dynamic Tic Tac Toe Game in Jquery