8
.
05
.
2024
9
.
09
.
2022
Ruby on Rails
Backend

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

Mariusz Kozieł
Chief Executive Officer

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

Mariusz Kozieł
Chief Executive Officer

Check my Twitter

Check my Linkedin

Did you like it? 

Sign up To VIsuality newsletter

READ ALSO

Michal - Highlights from Ruby Unconf 2024

Highlights from Ruby Unconf 2024

14
.
11
.
2023
Michał Łęcicki
Conferences
Visuality
Cezary Kłos - Optimizing Cloud Infrastructure by $40 000 Annually

Optimizing Cloud Infrastructure by $40 000 Annually

14
.
11
.
2023
Cezary Kłos
Backend
Ruby on Rails

Smooth Concurrent Updates with Hotwire Stimulus

14
.
11
.
2023
Michał Łęcicki
Hotwire
Ruby on Rails
Software
Tutorial

Table partitioning in Rails, part 2 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
Ruby on Rails

N+1 in Ruby on Rails

14
.
11
.
2023
Katarzyna Melon-Markowska
Ruby on Rails
Ruby
Backend

Turbo Streams and current user

29
.
11
.
2023
Mateusz Bilski
Hotwire
Ruby on Rails
Backend
Frontend

Showing progress of background jobs with Turbo

14
.
11
.
2023
Michał Łęcicki
Ruby on Rails
Ruby
Hotwire
Frontend
Backend

Table partitioning in Rails, part 1 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend
Ruby on Rails

Table partitioning types - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend

Indexing partitioned table - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
SQL Views in Ruby on Rails

SQL views in Ruby on Rails

14
.
11
.
2023
Jan Grela
Backend
Ruby
Ruby on Rails
Postgresql
Design your bathroom in React

Design your bathroom in React

14
.
11
.
2023
Bartosz Bazański
Frontend
React
Lazy Attributes in Ruby - Krzysztof Wawer

Lazy attributes in Ruby

14
.
11
.
2023
Krzysztof Wawer
Ruby
Software

Exporting CSV files using COPY - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Ruby
Ruby on Rails
Michał Łęcicki - From Celluloid to Concurrent Ruby

From Celluloid to Concurrent Ruby: Practical Examples Of Multithreading Calls

14
.
11
.
2023
Michał Łęcicki
Backend
Ruby
Ruby on Rails
Software

Super Slide Me - Game Written in React

14
.
11
.
2023
Antoni Smoliński
Frontend
React
Jarek Kowalewski - ILIKE vs LIKE/LOWER - Postgres Stories

ILIKE vs LIKE/LOWER - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Ruby
Ruby on Rails
Postgresql

A look back at Friendly.rb 2023

14
.
11
.
2023
Cezary Kłos
Conferences
Ruby

Debugging Rails - Ruby Junior Chronicles

14
.
11
.
2023
Piotr Witek
Ruby on Rails
Backend
Tutorial

GraphQL in Ruby on Rails: How to Extend Connections

14
.
11
.
2023
Cezary Kłos
Ruby on Rails
GraphQL
Backend
Tutorial

Tetris on Rails

17
.
03
.
2024
Paweł Strzałkowski
Ruby on Rails
Backend
Frontend
Hotwire

EURUKO 2023 - here's what you've missed

14
.
11
.
2023
Michał Łęcicki
Ruby
Conferences

Easy introduction to Connection Pool in ruby

14
.
11
.
2023
Michał Łęcicki
Ruby on Rails
Backend
Ruby
Tutorial

When crazy ideas bring great time or how we organized our first Conference!

04
.
12
.
2023
Alexander Repnikov
Ruby on Rails
Conferences
Visuality