Change all primary keys of type integer to bigint, including references

Question

Rails 5.1 changed the default type for primary keys from integer to bigint. We missed this and now we've got a mix of integers and bigints as a result.

We should get out in front of this before integer overflow hits us, again (other project, other story).

I'd love to see a clever migration script changing all

• primary keys
• foreign keys
• references where we don't have a foreign key (nevermind the fact that we always should have foreign keys declared)?

A way to list primary keys of type integer would be a good start.

Answer 1

WARNING: Changing primary key type on a large table is slow. Plan for it, if you can.

This question wasn't particularly popular... comment "just do the work" and voting to close. I'll share what I learned anyways, hopefully it's helpful to someone.

OK, lets "just do work":

A hint to what are integer primary keys and what are bigints, are in the schema.rb

1. Identify integer primary keys

#schema.rb
create_table "users", id: :serial, force: :cascade do |t|

Hints for integer primary keys are id: :serial, ref this SO answer: https://stackoverflow.com/a/54683428/252799

How large are your primary keys?

#Rails console
ActiveRecord::Base.connection.execute('SELECT MAX(id) FROM comments').first
{"max"=>91718106}

Max allowed value is 2 billion, 2 147 483 647 to be exact.

2. Create a migration

rails g migration change_primary_key_to_bigint

3. Add the changes to primary keys

class ChangePrimaryKeyToBigint < ActiveRecord::Migration[6.0]
  def up
    change_column :users, :id, :bigint

4. Find all references for each primary key you change

    change_column :users, :id, :bigint
    change_column :user_companies, :user_id, :bigint
    change_column :posts, :author_id, :bigint

Be thorough!

Search your schema.rb for all _id and carefully assess each.

You probably have got several references, join tables, custom reference naming or even polymorfic associations the you may overlook.

5. Run it

And wait. For hours depending on the size of the tables.

If zero downtime is important to you, this post looks promising: https://buildkite.com/blog/avoiding-integer-overflows-with-zero-downtime