Adding missing database indexes to your legacy rails app

Photo by Christian Joudrey

If you're like me, you're maintaining a Rails app that has had lots of different maintainers and database schema philosophies over the years. This can result in some data relationships having indexes and some not. If you're also like me, you know that might be slowing down your application in production, but you don't know how much and you don't want to go through and find all of the missing indexes. In this post I'm going to give you step-by step instructions on how to fill in the gaps on your relationship indexes and hopefully speed up your application in the meantime!

1. Find Your Missing indexes

The first step is to identify the places in your database schema that need an index. While there may be other ways of doing this, I reached for the help of the database_consistency gem to scan my has_one and has_many relationships.

1. Add gem 'database_consistency' to your Gemfile (I chose to add it to my :development, :test group, more on that in step 4)
2. Add a .database_consistency.config that disables all checks other than MissingIndexChecker:

DatabaseConsistencyCheckers:
  MissingIndexChecker:
    enabled: true
  ColumnPresenceChecker:
    enabled: false
  LengthConstraintChecker:
    enabled: false
  NullConstraintChecker:
    enabled: false
  UniqueIndexChecker:
    enabled: false
  BelongsToPresenceChecker:
    enabled: false
  MissingUniqueIndexChecker:
    enabled: false
  PrimaryKeyTypeChecker:
    enabled: false
  ForeignKeyTypeChecker:
    enabled: false
  RedundantIndexChecker:
    enabled: false
  RedundantUniqueIndexChecker:
    enabled: false

1. Run bundle exec database_consistancy to find your missing database indexes

   • if you want to get really fancy, you could do something like:

   echo "class AddMissingIndexCheckerIndexes < ActiveRecord::Migration[6.1]\
     \n  def change\
     \n$(\
       RAILS_ENV=test bundle exec database_consistency |\
         awk \
         -v pre='MissingIndexChecker fail'\
         -v post='associated model should have proper index in the database'\
         '{gsub(pre, "    #")};gsub(post, "")1'\
     )\
     \n  end\
     \nend" \
        > db/migrate/$(date '+%Y%m%d%H%M%S')_add_missing_index_checker_indexes.rb
   

   this should generate a file something like (actual output from one of our apps):
   

   class AddMissingIndexCheckerIndexes < ActiveRecord::Migration[6.1]
     def change
       # User account_list_invites
       # User options
       # Organization organization_accounts
       # DonorAccount addresses
       # DonorAccount addresses_including_deleted
       # DonorAccount primary_address
       # AccountList account_list_invites
       # Contact addresses
       # Contact addresses_including_deleted
       # Contact primary_address
       # Contact excluded_appeal_contacts
       # Contact pledges
       # DesignationAccount designation_profile_accounts
       # Announcement::Action user_announcements
     end
   end
   

   Each row will be a row of output from database_consistency with the model class and the relationship name.

2. Add Indexes (building add_index calls for each)

Each row of the output will be from database_consistency with the model class and the relationship name. In a boring instance these will map to column name and table name respectively.

basic example:

You will hopefully have a few that you can transform without any hand-holding, like:

# Store products
add_index(:products, :store_id)

polymorphic relationships:

But some will be harder, for example in our application we have Addresses with multiple types of addressables. In this instance we were able to cover multiple missing indexes with on multi-column index:

# Company addresses
# Contact addresses
add_index(:addresses, [:addressable_type, :addressable_id])

partial indexes:

An even more challenging type of index is for a relationship that has a scope attached to it. One example is an album that has a title track:

class Album < ActiveRecord::Base
  has_many :tracks
  has_one :title_track, -> { where(number: 1) }, class: Track
end

If you are using Postgres, you can use a partial index:

# Album title_track
add_index(:tracks, :album_id, where: 'number = 1', name: index_title_tracks_on_album_id)

It is important to name partial indexes because otherwise Rails will use the default generated index name (which will hopefully fail because you have already indexed the album_id foreign key).

If you are using MySQL, you can still index based on both columns

# Album title_track
add_index(:tracks, [:album_id, :number])

In the end you should have a file full of add_indexes waiting to improve your performance.

Safe migrations (Postgres Only)

In Postgres, adding an index non-concurrently blocks writes, which depending on the size of your tables and your threshold for downtime, you might want to add the indexes concurrently. The only downside here is that the migration runs without a transaction so it has the potential to put you in a partially migrated state. Here is how our migrations look:

class AddMissingIndexCheckerIndexes < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!
  def change
    # Album title_track
    add_index(:tracks, [:album_id, :number], algorithm: :concurrently, if_not_exists: true)
  end
end

Note: We are using the strong_migration on some of our Rails applications to prevent us from performing any Rails migrations that might lock our tables for a significant amount of time. If you have been bitten by migration issues before, check it out!

3. Speed up your app

After deploying these changes, you should hopefully see some helpful speed-ups, but your mileage may vary. But at least you can check one item off your performance improvement checklist!

4. Prevent future issues

You can prevent future instances of this happening by adding database_consistency to your CI configuration. We're using TravisCI, so we added - RAILS_ENV=test bundle exec database_consistency to the script: section of our .travis.yml.

Closing

If you were able to use this for one of your Rails apps, I would love to hear about how it went. Bonus points if you can measure and share some performance improvements! Now go forth and index!

Comments

[Fatko Dima]

You may consider github.com/fatkodima/online_migrat... as an alternative to strong_migrations. It is a superset of it feature-wise, but not just suggests in words what to do, but has actual migration helpers, which you can use to do what you want.

It has migrations helpers for:

• renaming tables/columns
• changing columns types (including changing primary/foreign keys from integer to bigint)
• adding columns with default values
• adding different types of constraints
• and others

Additionally, it has an internal framework for running data migrations on very large tables using background migrations. For example, you can use background migrations to migrate data that’s stored in a single JSON column to a separate table instead; backfill values from one column to another (as one of the steps when changing column type); or backfill some column’s value from an API.