Ups! Maybe the requirements changed or maybe, just like me, you only later learned what precision means when defining decimal type for a mysql column. If you are like me, let me share something…
Imagine we have a
rating column for a product. Before, the rating could be a decimal number between 1.0 and 5.0, now, we want to turn that into rating between 1.0 and 10.0. In the past, since we knew only one decimal point can be behind the number so we set precision to 1:
t.decimal :rating, precision: 1
Well – wrong assumption and since that was set and our table grew to million of records, now, we have a bit of an issue with the new requirement.
To clarify, precision for a decimal in mysql does not mean digits behind the decimal point – it includes all the digits and scale is what defines how many digits are after a decimal point.
DECIMAL(M,N) says that M is the number of significant digits that are stored for values, and N represents the number of digits that can be stored following the decimal point.
So, to satisfy the new requirement, we need to modify `rating` column to have new decimal type – DECIMAL(3,1) (ex. 10.0 – 3 digits total, one after decimal point). Unfortunately, changing the type for the existing column will lock the table and we might experience downtime.
We could apply a different fix for this particular problem with ratings, but here we are learning how to change a decimal type of the column without the downtime so let’s focus on that.
So how do we go from what we have to the same named column with correct type? Well, here are the steps:
- Add new column with correct type
- Update model to handle writing to both columns whenever possible
- Migrate data for existing records to newly created column
- Rename old
- Rename ‘rating_new`
- Remove custom getter and setter for rating
Each of these steps will ensure the app is still running as expected while we switch from rating (incorrect type) to rating_new (correct_type) and then back to rating column but now with correct type.
Add new column with correct type
t.decimal :rating_new, precision: 3, decimal: 1
Handle writing to new column
Model should now understand that both columns are to be written to. And we make it safe around deploys by always making sure the column exist before we save.
This gives us necessary freedom for the deploy and for later changes on the name of the column.
In the model `/app/models/product.rb`
def rating=(value) raise_rating_save_error = true if Product.column_names.include?('rating_new') write_attribute(:rating, value) raise_rating_save_error = false end if Product.column_names.include?('rating') if value.to_d < 10 || raise_rating_save_error write_attribute(:rating, value) end end end
A simple test for the methods could look like:
# Minitest test "Storing rating under 9, stores to both rating and rating new" do rating = 9 product = create(:product, rating: rating) if Product.column_names.include?('rating') assert_equal rating, product[:rating] assert_equal rating, product.rating end if Product.column_names.include?('rating_new') assert_equal rating, product[:rating_new] assert_equal rating, product.rating_new end end
And for reading we taking raiting_new first whenever possible:
def rating self[:rating_new] || self[:rating] end
Migrate data for existing records to newly created column
We need to make sure all the data from rating is now present also in the rating_new, we can use whatever approach to data migrations we want – rake task is the simplest and it could look something like:
desc 'Copy product rating to rating_new' task copy_rating_to_rating_new: :environment do Product.where(rating_new: nil).where.not(rating: nil).find_each do |product| # choose strategy for updating # background job / direct update end end
rating column to
rename_column :products, :rating, old_rating
rename_column :products, :rating_new, rating
Remove custom getter and setter for rating
Now that we have rating with the correct decimal type, we can remove all the additional code in
Yay! Things work. There is still the
old_rating column that we might want to remove in the maintenance window, bu at least for now, we can accept more refined ratings. The old rating column has still the same old name with new precision!