Slow database queries are a common performance bottleneck for Ruby on Rails apps. Simplifying a complex query is often not possible due to the underlying business logic. Instead, you can extract parts of a query, cache and reuse them to improve performance. In this tutorial, I’ll describe a range of techniques on how to do it.
We will cover more advanced ActiveRecord and PostgreSQL topics like
EXPLAIN ANALYZE, and query plan visualizer tool.
Let’s get started!
When subquery caching makes sense for slow ActiveRecord queries?
Subquery caching is a bit more convoluted solution for optimizing slow queries. Before you decide to implement it you should double-check if more straightforward fixes could help instead:
- add missing database indexes and to avoid inefficient
Seq Scanon large collections
- make sure that the database server is adequately scaled and has a decent cache hit rate. You can use my RailsPGExtras gem to validate that
- check if PostgreSQL configuration is correct for the type of the database server that you’re using. PGTune is your friend here
- validate that your queries are not locking for too long, e.g., because of too wide ActiveRecord transaction scope
You can read out my other blogpost for an overview of tips for optimizing slow Rails queries.
If none of the above approaches helps, let’s discuss how you can split slow queries into the reusable parts.
Sample database structure
How to determine which part of a query to cache?
Unfortunately, there’s no simple answer to this question. It’s totally dependent on your application’s logic.
For this tutorial, we will assume the following database structure to explore some common scenarios:
User can belong to many Groups with a different role via the GroupAssignment join model. Posts are published in a Group by a User.
Let’s now explore potential slow queries that could originate in a system with similar schema.
Analyzing slow queries
We will focus on optimizing a single controller. It returns not approved posts that were published in groups that a current user is an admin in. It also validates if a user is an admin in at least one group.
A similar complexity level requiring data from four and more tables to execute a single query is common in non-trivial Rails apps.
eager_load because relation tables are used for both filter query and post’s JSON structure. It ensures that a single query will be executed to fetch and instantiate all the records eliminating N+1 issue. You can check out this blog post for more details on differences between
eager_load ActiveRecord methods.
admin_groups returns a relation object so two queries are merged together. The final query from
posts_to_moderate method generates the following SQL:
When analyzing the visualizer plans, it’s always worth noting how many
Join operations the query performs. One way to speed up query execution is to limit the number of joins.
BTW all the tools mentioned above are useful if you already know which queries you want to put under the microscope. To discover the actual bottleneck queries in your application, you can use ScoutAPM. First, display the endpoints sorted by % of processing time consumed. Then you can deep dive into analyzing which action spends significant time in the database layer and precisely what queries are the most time-consuming.
Now that we know how to find and analyze bottleneck queries let’s learn how to split them into more performant and cacheable pieces.
Extracting query parts to instance variables
The simplest extraction you can do is to fetch IDs of admin groups and reuse them for both authorization method and posts fetching. You don’t even need the actual group objects. You can read only the join model, significantly reducing the complexity. Just make sure to have all the foreign keys in place. You can read more about ensuring data integrity on the database level in my other blog post.
This implementation will still perform two queries like the initial one, but they will be significantly less complex. Results of the query checking admin memberships are reused in posts fetching query, reducing the need for two join operations!
Let’s see how it looks under the
EXPLAIN ANALYZE microscope:
As you can see, both
EXPLAIN query output and visualizer plan are now simpler. We’ve eliminated two join operations from potentially bottleneck query.
The best part is that we did not have to change the business logic of our app at all. Learning to spot reusable parts of Active Record queries is an insanely useful technique for optimizing the database performance of non-trivial Rails apps.
BTW be careful to always cache primitive values like arrays of integer IDs. If you cache an
ActiveRecord::ActiveRelation object and try to reuse it in the other query, it will just make the final query more complex by adding a subquery. Also, caching complex objects instead of primitives, will consume more memory.
Is fewer queries always better?
Not necesarily. In the above example number of queries remains the same. The result of one query was reused in the other, so it was a clear win.
In some cases, two more complex queries might share a common part. Extracting it into a separate third query and then reusing could lead to a better performance despite the additional database call. Make sure always to measure the results of your optimizations with a dataset resembling production.
Caching parts of queries in Rails cache
Using instance variables is the most straightforward caching technique. The caveat is that the value still has to be calculated for every request. In some cases, storing the value in a periodically refreshed, persistent cache might be a better solution.
To calculate admin group memberships for a given user only once every 15 minutes you could use Rails cache:
To avoid blocking the webserver thread, you can also consider refreshing the cached values in the recurring Sidekiq job:
Alternatively, you could manually refresh the cache for each user whenever his group memberships are updated, e.g., using ActiveRecord callbacks.
Check out the official docs for in-depth info on how to use Rails cache.
Using materialized views to cache parts of logic
Another approach you could use to cache parts of your queries is materialized views. If the logic is more complex you can build a materialized view containing the cached data.
Make sure to use materialized views, not standard ones. Non materialized views are just cached queries, not the actual data, so they don’t give any performance benefit.
Materialized views have to be refreshed periodically but are fully readable during the refresh. A detailed explanation of how to use materialized views is outside of the scope of this tutorial. Check out the Scenic gem documentation for more info.
I’ve seen impressive performance improvements from caching the subqueries. Just remember that adding a cache layer always increases the complexity and is a potential source of sneaky bugs. Make sure to start with more straightforward SQL optimization techniques first before trying to implement caching.