My experience with Rails Active Record migrations

WOCinTechChat.com

Imagine you’re a sales rep. You need to organize the steps you’ll take to make a sale. Think emails, phone calls and more.

You’d want to use one of SalesLoft’s most popular features, a Cadence, to manage this process in an organized way. How do you know if your Cadence is effective? A more recent feature called Cadence Analytics showcases a ton of data to help you answer that question.

A few months ago, I had the opportunity to collaborate with the software engineers that built Cadence Analytics. The team wanted me to use a Rails Active Record migration to change the data type of primary key ids from int to bigint in several tables that make up the Cadence Analytics database.

Overview

First, I’ll explain why this work was necessary. I’ll share the questions we considered along the way. I’ll also share which teams we collaborated with in order to approach this task successfully and with minimal impact to our customers.

Why

This modification was necessary for two reasons.

PostgreSQL, our relational database management system, has three integer data types: smallint, integer and bigint. Each has a maximum number it can hold (learn more here).

A lot of data is consumed in Cadence Analytics. We were afraid of auto-incrementing int fields within a given table reaching its max integer. If this occurred, PostgreSQL would raise a numeric field overflow error. The impact of an error like this depends on your application. That said, once an error like this happens, no more inserts can occur on the respective table.

Secondly, the team wanted the primary key and foreign key data types to be the same. If a primary key id datatype was bingint, we wanted the foreign key that referenced it to be bigint as well.

Preliminary questions

Before getting started, we connected with the database engineering and tech operations teams. My top questions for them were:

  • How long will a migration like this take?

I learned that the duration of a migration is dependent on the size of the table. This meant that a migration on a table that contained a lot of data could take several minutes or more.

I also learned that when a table is undergoing a migration, it is locked until that migration has completed. Note, there are different levels of locking that a table can undergo. In our case, the type of migration we wanted to run would lock a table such that reads or writes on that table would be blocked.

For these reasons, the team and I decided that it would be best to perform the migrations outside of business hours.

How

In a Ruby on Rails application, the specific process we used to change the data type of the primary key ids from int to bigint is called an Active Record migration. A migration takes your current database schema and modifies it to add, remove or alter table columns or entries.

Since SalesLoft uses a continuous integration development process, I divided my work into three phases for a given migration.

Phase one: create a standalone migration

  • Create a new branch to begin my work on.

Phase two: execute migration on my branch and submit a pull request

  • Execute the migration file locally with rake db:migrate. This command will also make the changes to our database and update our schema.

Phase three: execute migration manually in production and merge pull request

  • Open an interactive shell in one of the cadence analytics pods.

It was atypical for us to execute the migration changes manually with SQL. This approach allowed us to easily view the resulting logs separate from logging that occurs during the deployment process.

Conclusion

Photo by Val Vesa on Unsplash

My key takeaways from this experience are that:

  • A task like this requires teamwork.

Thanks for reading!

Software engineer | My mom makes the best empanadas you'll ever taste instagram.com/gailscaribb

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store