Our website uses cookies for analytics and services improvement. To learn more check Privacy Policy

What you lost when you used structure.sql instead of schema.rb in your Rails project

Both schema formats have their own pros and cons. I didn’t want to write when and why we should use structure.sql or schema.rb but I might do it in the next article. You can read more in the official documentation. In this article I would like to show you a short production story.

We have jumped into a new project which had database performance problems. It was easy to notice that client's team had struggled with it also. For example, they made database replication to redirect the most time-consuming queries to second instance. Numerous requests and millions of records in tables were causing a real problem.

The client’s team had various ideas on how to solve the problem. We discussed about:

  • scaling DB horizontally
  • materialized SQL views
  • extract the most crucial part as a microservice

But we decided to start from the beginning and we started digging. New Relic (APM - Application Performance Monitoring tool) was our best friend. We have detected the 5 most time-consuming queries and investigated them one by one.

I would like to show one of them:

performance_before_changes

Query which is run so often (46 times per minute) can't be that slow. Let’s investigate why? SQL explain and other checks proved that everything happened because of one line


validates :column_id, uniqueness: { scope: :other_column_id }

and lack of unique index on those columns in the database.

By adding index:


def change
    add_index :table, [:column_id, :other_column_id], unique: true
end

We have achieved a nice result. Average query time fell down from 731 to 1.5 ms which solves one of the biggest DB problem.

performance_after_changes

Success story but what the hell is the advantage of using schema.rb instead of structure.sql as our schema format?

If schema.rb had been used, mentioned issue would not have existed. Thanks to Rubocop-rails which is used in the project. We are enthusiasts of this tool :)

We might have learned about this issue earlier or even previous team could catch it immediately after problematic validation was added. Rubocop Rails/UniqueValidationWithoutIndex check highlight it:


app/models/example.rb:10:3: C: Rails/UniqueValidationWithoutIndex: Uniqueness validation should have a unique index on the database column.
  validates :column_id, uniqueness: { scope: :other_column_id }
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Rubocop checks indexes in the schema.rb file so there is no verification if there is no schema.rb file. Probably there are other tools that can detect it as fast as Rubocop. Let me know if you know any helpful tools or tips that allow detecting database optimization problems