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:
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.
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
