Logo

TODAY I LEARNED

Using Rails to migrate columns from JSON to JSONB in Postgresql

Postgres offers data type json to store any structure easily, but one dissadvantage is that filtering by properties stored in the json column are super slow, one simple fix before refactoring the whole implementation is to migrate the column to be jsonb, since it is stored in binary form, it supports indexes, a easy and safe way to do it is as follows:

class ModifyJSONDataDataType < ActiveRecord::Migration[6.0]
  def up
    add_column :table_name, :data_jsonb, :jsonb, default: '{}'

    # Copy data from old column to the new one
    TableName.update_all('data_jsonb = data::jsonb')

    # Rename columns instead of modify their type, it's way faster
    rename_column :table_name, :data, :data_json
    rename_column :table_name, :data_jsonb, :data
  end

  def down
    safety_assured do
      rename_column :table_name, :data, :data_jsonb
      rename_column :table_name, :data_json, :data
    end
  end
end

Then, using another migration(due the ability to disable transactions), add an index to it

class AddIndexToDataInTableName < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!

  def change
    add_index :table_name, :data, name: "data_index", using: :gin, algorithm: :concurrently

    # You can even add indexes to virtual properties:
    # add_index :table_name, "((data->'country')::text)", :name => "data_country_index", using: 'gin', algorithm: :concurrently
  end
end

Learned by Edwin Cruz on Mar 8, 2021

973983 adforcodereview v3 0211021 c02 021121