New applications are great, it is easier to add new functionalities and life is all roses. But you do not want to be at that stage forever because that would mean that not many people are using your application. Hopefully, there will be a time in which your users start liking your application, it becomes a big success and it starts to grow! With that grow, more and more records are added to your tables and things tend to get more complex. One day the product managers asks for a new feature: Customer Support wants to have a new page in which they can search users by email. As a good engineer, you determine that having a new index would be a good thing to boost search performance on that new page, You have added indexes to your tables in the past without noticing anything special, you know how it goes. But this time when you run the migration, you notice that it is taking a while. It does not run as fast as it used to be… your migration is taking a while. On top of that, someone writes in the chat that they are getting reports of new users telling that they cannot create an account. Alarms start going off… You probably know how that feels, but… do not let that be you! Let us write a postmortem of what happened in this imaginary situation and see what can you could do about it to ensure that this never happens to you again.
Analyzing the problem
Once the incident is over, let us check what happened here.
Investigating the database documentation, you realize that it turns out that Postgres and MySQL lock write access (but not read access) to a table while they are creating an index on it. Since in the beginning your application just had a few users, that was not a big issue, nobody had time to realize that there was an ongoing lock on writes, but since the users table became larger, and therefore migrations started taking longer to apply, being impossible to insert, edit or delete records on a table during a long period of time has become an issue.
Postgres has a solution: Algorithm: :concurrently
Luckily Postgres has something to do about this. It turns out that you can create index concurrently, and Active Record knows how to take advantage of it. You just need to add the option algorithm: :concurrently to your index migration… but… is that all what I need?
Things in Rails tend to be very simple, but in this case we need to tweak one more thing. It turns out that Active Record runs migrations within a transaction, which is usually good thing so that migrations can rollback automatically if something goes wrong, but in order to make concurrent index creation possible, Postgres requires us to disable that.
Luckily again, Active Record is prepared for that. You just need to add disable_ddl_transaction! within your migration and Active Record will disable those automatic transactions.
As a bonus, remember to isolate migrations that need to create indexes concurrently within their own file from other migrations, so that the latter are not affected by disable_ddl_transaction!. Your migration file should look similar to this:
|
1 2 3 4 5 6 7 |
class AddIndexOnEmailToUsers < ActiveRecord::Migration[7.0] disable_ddl_transaction! def change add_index :users, :email, algorithm: :concurrently end end |
If you enjoyed this post do not forget to subscribe so that you do not miss any of my future updates. If you want me to write about something in particular, you can let me know about it in the comment section below.
See you in my next post! Adiós!