Scaling issues are great because it means that you have traffic on your website. Before you decide that “Rails doesn’t scale!” and start a rewrite to a tech stack with potentially more performance, maybe you should double check your database queries first. Below I describe some tools and techniques you can use to track down, diagnose and optimize slow Active Record SQL query.
Problem with Active Record
I’ve started my programming career developing Rails apps. Thanks to Active Record I could start getting stuff done right away.
Active Record lets you write fairly complex database queries without understanding the underlying SQL. It is its best feature and the greatest sin at the same time.
Below I present a list of tools and techniques I use to tackle database queries in Rails apps. Getting familiar with them can help you understand SQL on a much lower level than an Active Record APIs.
Track down performance bottlenecks
Even its free version offers you plenty of useful insights into your application. I usually use “Transactions” menu bar, and sort results by “Most time consuming”. Following the 80/20 principle fixing the top issue could be enough to bring back the optimal performance.
Although a free version does not offer database level insights, a breakdown table showing execution times is usually enough to track down the problem.
You can hook up PgHero to your production application to gain powerful insights into your database queries performance. One downside is that it does not indicate from which places in the Ruby code the queries originate.
A great feature is that it automatically warns you about slow queries. What’s more, it lets you run
ANALYZE straight from the interface without having to access the database CLI.
One trick you could use here is to enable showing SQL logs in the Rails console:
and later copy the resulting SQL logs (you need to change $1 etc. into argument values manually) to Pg Hero interface:
Alternatively, you could use
to_sql method to translate Active Record query into an underlying SQL. An advantage of automatic logs is that whenever Ruby code executed in the console runs a database query you can see an SQL code without explicitly generating it.
EXPLAIN ANALYZE in Rails
Rails also have a built-in
explain method you can run on an AR query object.
ANALYZE is better because it performs the actual query and provides insight into real execution time, not hypothetical cost. There is a pull request adding more options to
explain method and
ANALYZE is one of them.
If you would like to use
ANALYZE from Rails console right now, you can check out my new gem: activerecord-analyze. It is a bit rough and experimental but works:
With PgHero you can even go all pro and visualize a query plan using a visualizer tool:
Explaining the EXPLAIN ANALYZE
Honestly, I have to admit I have yet to master the understanding of
EXPLAIN ANALYZE and visualizer log reports.
One thing you need to watch out for are
Seq Scans. Every such occurrence is potentially a big no-no because it means that Postgres needs to scan a table row by row to find matching entries. Such query will get slower with each new record added to the table and could be a serious problem in case of larger tables. You can fix it by adding an appropriate index.
What is “appropriate” might not be obvious at first because the way PostgreSQL handles indexes is quite complex. If you are not sure which index to add the best solution is to experiment with different settings and run
EXPLAIN ANALYZE after each change to compare the actual query costs.
Remember that you cannot go all in for indexing every single attribute because every index slows down insert operations.
PostgreSQL docs are a great starting point for wrapping your head around all the ins and outs of
Check database queries optimization results
Once you track down and optimize the queries you should check the actual results.
The best approach would be to perform benchmarks on a production database. The problem is that you probably don’t want to DDOS your production website. Also, remote benchmarks might not be reliable because of varying network conditions. In that case, the best solution could be to perform benchmarks on a local computer, with settings resembling a real production site.
Copy production database
You can copy your production database locally. How to do it exactly depends on your setup. If you use PostgreSQL on Heroku you should check out this article.
Seed local database to mimic production
It is highly possible that due to legal and privacy issues you will not be able to download and copy a real production data to work with it locally.
It that case you could use Factory Bot to seed your database to a size approximating a production. If your factories are capable of generating a unique data all you need to do is to run them in a loop in order to seed a database.
Use Siege for local benchmarks
Once you get your database setup ready you can start benchmarking. Remember to run your Rails app in a production environment, because in development many optimizations are disabled.
Siege is a great and simple to use benchmarking tool. It gives you a detailed report on how a specific endpoint performs:
You can check out my other blog post about optimizing Rails app performance bottleneck, to see the practical use case of Siege.
Although database level optimization techniques have its limits they can take you a long way in improving the performance of your application. I hope that techniques I described will help you get more insight about your database queries. Let me know if you know some other tools useful when working with SQL in Rails.
BTW some of the code samples come from Abot a simple Slack bot I’ve been working on after hours. I invite you to check it out.