Database Indexing Is an Art

What comes to your mind when you think of fine tuning your full stack app? DB index would be top of my list! I often get questions from junior engineers on why should we think of adding and index and why it is so important?

What is a database index?

Indexes are used to quickly locate data without having to search every row in a database table each time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both quick random searches and efficient access to sorted records.

One of the neatest and quickest method to enhance the performance of Rails apps is to optimize database queries. Depending on the type of situation, we can address it in a variety of ways. N+1 queries appear to be common among rails apps, fortunately, easy to solve. However, you may come across some seemingly simple queries that take far longer than they should, adding a correct index helps the best here.

When should we use them?

When it comes to performance of a system, indexes play a vital role in fetching results, be it select or conditional query. Index should be created on columns which are frequently queried. Also it is important to keep the number of indexes small to optimize the insert operations.

How to add them to our rails code?

The quickest way to add indexes is by generating a migration, hence it is important to consider this while we plan to add our tables into the system.

We need to wisely decide whether a field needs index or not. Indexing does take up extra space, and we may not need to store duplicate data without a strong reason. One example is when you are trying to index a polymorphic association, we need to make sure we create double index. Also any frequently used field on sort can be improved by delicate indexes.

If you'd like to add an index on any new column you are adding:

bin/rails generate migration AddPartNumberToProducts part_number:string:index

will generate the appropriate add_column and add_index statements:

class AddPartNumberToProducts < ActiveRecord::Migration[6.0]
  def change
    add_column :products, :part_number, :string
    add_index :products, :part_number
  end
end

We can also add indexes later once we add columns to the databases. Rails supports checking if an index exists before creating one. We can make use of if_not_exists attribute:

add_index :products, :part_number, if_not_exists: true

PostgreSQL allows us to build indexes concurrently without locking up the database. Since operation is concurrent and it doesn’t lock up the table, we can avoid accidental downtimes. Below snippet allows us to run a rails migration with concurrent option.

def change
  add_index :products, :part_number, :algorithm => :concurrently
end

However you can’t run the above from inside of a transaction. To avoid this, we need to use disable_ddl_transaction! . This option was added starting from Rails 4, to run this one migration without a transaction wrapper:

disable_ddl_transaction!
def change
  add_index :products, :part_number, :algorithm => :concurrently
end

Credits : RubyOnRails

More ways to add simple and complex index can be found in the Rails docs.

How to validate if my index works?

Example: I have a table with 10K records, I am searching for a record matches my condition where the column doesn't have any index. We can leverage 'explain' to find the difference in fetch time:

After adding index on the column:

In the above scenario if we have sort or queries based on the part_number, without an index in place we can clearly see the performance cost impact on sequence scan vs index scan. Small steps into big improvements. As a dev, it’s good to keep in mind this as a rule to decide if an index is needed or not is by checking if any columns are referenced in the where, having and order by in the SQL queries.

Any special cases to consider?

Sometimes indexes, especially unique indexes, can be case sensitive. MySQL indexes are already case-insensitive, but PostgreSQL creates case sensitive indexes by default. One good example here can be emails and usernames. If we don’t mention case sensitivity along with validation in rails models with Postgres, the purpose of uniqueness may not be served.

validates_uniqueness_of :username, case_sensitive: false

This won't be sufficient to make it bullet proof. That query during validation wouldn't be able to take advantage of a regular index on username. To guarantee concurrency, there ought to be a unique index on lower(username)

Do we have to index our data?

Not always. Indexes are great way to increase the performance of the application, but sometimes over indexing can slow the performance too. We need to be mindful while indexing. One example can be any index on a table which gets frequently deleted can negatively affect us on performing. The more data, the bigger the index. Huge tables might bite us back too.

One other real example from Jobber, missing indexes on searchable fields can cause a significant amount of delay, with a simple index this can be improved to make the customer delighted.

Key Takeaways

• Indexes in databases work similar to lookups in real books
• create them wisely, considering storage and negative impact on performance
• Preferably fits best in huge tables
• Some rails validations require indexing to make it work

About Jobber

We are hiring for remote positions across Canada at all software engineering levels!

Our awesome Jobber technology teams span across Payments, Infrastructure, AI/ML, Business Workflows & Communications. We work on cutting edge & modern tech stacks using React, React Native, Ruby on Rails, & GraphQL.

If you want to be a part of a collaborative work culture, help small home service businesses scale and create a positive impact on our communities, then visit our careers site to learn more!