Adding missing database indexes to your legacy rails app
Tags: rails, database, index, postgres
Published: 2021-08-09 19:12:36

---
title: Adding missing database indexes to your legacy rails app
published: true
description: 
tags: rails,database,index,postgres
cover_image: https://cdn.stocksnap.io/img-thumbs/960w/seed-tree_01MVFCVHOD.jpg
---

[Photo](https://stocksnap.io/photo/seed-tree-01MVFCVHOD) by [Christian Joudrey](https://stocksnap.io/author/8926)

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](https://github.com/djezzzl/database_consistency#missingindexchecker) 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`:
```yml
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
```
3. Run `bundle exec database_consistancy` to find your missing database indexes
  - if you want to get really fancy, you could do something like:
    ```bash
    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):
    ```ruby
    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:
```ruby
# Store products
add_index(:products, :store_id)
```

### polymorphic relationships:
But some will be harder, for example in our application we have `Address`es with multiple types of `addressable`s. In this instance we were able to cover multiple missing indexes with on multi-column index:
```ruby
# 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:
```ruby
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:__
```ruby
# 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__
```ruby
# Album title_track
add_index(:tracks, [:album_id, :number])
```

In the end you should have a file full of `add_index`es 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:

```ruby
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](https://github.com/ankane/strong_migrations#adding-an-index-non-concurrently) 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!