Super Fast Rails – A promise given is a debt

The SuperFastRails joke

Before reading this one, you should read the previous article to understand fully.

If you don’t have a few extra minutes, here is a summary: I was describing a new revolutionary gem that could automatically optimize any Rails app. It could create missing indexes, remove unused indexes, optimize SQL queries, and handle dangerous migrations. All that without needing you to think about it.

That was of course an April fools’ joke. It worked pretty well, possibly too well:

Not going to consider you for a good long while until I get over the April 1st post. Boomed me good

A promise given is a debt. Now the joke is on you, my friend.

Sorry, that was quite a promise to optimize Rails apps automatically! I will probably not keep it, but I’ll try to be helpful for once. Luckily for me, existing tools or gems already solve the listed optimizations. The only slight difference is that they won’t do that fully automatically. No more jokes; what do we have?

Missing indexes

PgHero is a mountable dashboard for any Rails app that provides a lot of information about PostgreSQL:

• Missing/unused indexes
• Live queries
• Slow queries
• Maintenance/vacuum
• Live connections
• Space usage per tables and indexes
• Tune settings

If your database is not PostgreSQL, you could use lol_dba. Since I mainly use PostgreSQL, I never had a chance to try it.

Unused indexes

Rails PG Extras gives a lot of details about indexes:

• Unused indexes
• Duplicated indexes
• Index cache hit ratio
• Null indexes

It’s also helpful to improve the existing indexes. If the index includes null values, there is probably an improvement to do.

PgHero is also able de detect unused indexes. I recommend installing both gems. The UI of PgHero is more intuitive, but Rails PG Extras provides more advanced information.

Active Record Doctor can detect unused indexes and more.

Optimizing SQL

Most databases have the EXPLAIN statement, which shows the query plan. It should be the first step to optimize any query. Thanks to the query plan, it’s possible to understand why a query is slow.

By calling explain on a relation, ActiveRecord returns the query plan:

Article.text_search("test").explain

# Since Rails 7.1 it accepts options
Article.text_search("test").explain(:analyze)

Or directly from a SQL prompt by prefixing the query with EXPLAIN. Adding the analyze option EXPLAIN ANALYZE will improve the accuracy of the query plan on PostgreSQL.

However, reading a query plan can be difficult. This one is straightforward:

EXPLAIN ANALYZE SELECT count(*) FROM "articles" WHERE (to_tsvector(body) @@ plainto_tsquery('simple', 'test'));

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3546.74..3546.76 rows=1 width=8) (actual time=6523.238..6523.239 rows=1 loops=1)
   ->  Seq Scan on articles  (cost=0.00..3546.62 rows=48 width=0) (actual time=3.214..6522.726 rows=1200 loops=1)
         Filter: (to_tsvector(body) @@ '''test'''::tsquery)
         Rows Removed by Filter: 8490
 Planning Time: 0.210 ms
 Execution Time: 6523.299 ms
(6 rows)

Time: 6525.133 ms (00:06.525)

It tells that the sequential table is slow. But with a query plan viewer, it’s much easier to see that an index is missing:

1+N queries

1+N queries can be detected while developing thanks to the Bullet gem. It’s great because it gives feedback before deploying to production.

There is also Prosopite, which can do that.

Dangerous migrations

Strong migrations detects dangerous migrations and suggest a safer alternative. It’s like a safety net that reminds you how to properly create an index or rename a column without locking the database. There is a similar gem called safe-pg-migrations.

If reliability is more important than shipping fast, it’s essential to use it. However, if the database is small, the traffic is low, and you need to move fast, don’t bother writing migrations without downtime.

Conclusion

I hope this little list compensates a bit for the Super Fast Rails joke. We are lucky to have so many great tools to help us. Thanks to them, any Rails app can be easily prepared to avoid the most common bottlenecks and optimization mistakes. You can definitively make your Rails app Super Fast™.

I may have missed to name other great gems. Do not hesitate to share them with the community.