How to optimize DB writes in Ruby on Rails
4 min read
While read queries can heavily impact performance, writes too can do some damage. In this article, part 2 on our Rails Performance Series, we will examine ways to speed up write queries.
Checkout the part 1 about DB reads here.
Transactions allow us to execute a series of operations atomically, as they were a single operation. If any operation inside the transaction fails, the whole blocks is canceled, and no operation is persisted (rollback).
A nice thing to know is that a database is much faster at committing a transaction with n writes than n writes each. So generally:
1000.times do User.create!(email: 'email@example.com') end
is much slower than
User.transaction do 1000.times do User.create!(email: 'firstname.lastname@example.org') end end
Rails validations are very useful, although they introduce an overhead on record save. Uniqueness constraints are particularly costly, as they imply a
SELECT query before saving to check if the value respects the constraint.
In some cases disabling validations can significantly speed up operations, especially when we are dealing with massive operations like imports and such.
To skip validations we can call the save method as:
DO NOT do this unless you are sure that performance is a real problem.
Massive insertions / updates
From Rails 6 onwards some handy methods were added to do mass operations, using a single
INSERT INTO or
Those methods are
The main advantage is that insertion time is orders of magnitude faster.
Warning: with those methods model validations and callbacks will not be invoked. (
If you're using Rails < 6, you can achieve the same functionality using this gem:
The same thing is valid for deletions.
If you need to prune mass data, consider that this:
Is much slower than this:
User.transaction do User.each(&:destroy) end
And it's much much slower than this:
But, as noted before,
delete_all skips all validations and
before_destroy hooks, so be aware of it.
Minimize number of queries
It's pretty obvious, but a way to reduce writing performance is, as always, to reduce the number of queries.
So, try to make changes to a model all in the same place or collect changes in the model and invoke a query just once.
- Don't make call an
before_createand change the column, instead.
class User after_create do update(is_valid_user: true) if email.ends_with?('@gmail.com') end end
class User before_create do self.is_valid_user = true if email.ends_with?('@gmail.com') end end
- Don't call #touch if you just made a
saveto the model. It's been already
touch-ed by the save
- Don't call save multiple times on the same model during the same request.
user.update!(email: '___', password: '___') ... ... user.update!(is_valid: true) if user.email.ends_with?('@gmail.com') user.update!(metadata: metadata) unless metadata.nil?
user.email = '___' user.password: '___' ... user.is_valid = true if user.email.ends_with?('@gmail.com') user.metadata = metadata unless metadata.nil? user.save! # Just one query ###### # Or, as an alternative: ###### user.assign_attributes(email: '___', password: '___') ... user.is_valid = true if user.email.ends_with?('@gmail.com') user.metadata = metadata unless metadata.nil? user.save! # Just one query
Indexes, triggers and performance
Indexes are great for performance, in general. They speed up read queries a LOT, making a trade-off between speed and occupied memory.
However, you should consider that they also add an extra overhead during writes. In general, this is trivial.
In some scenario, however, if you have too many (unused) indexes, you may see some slowdowns during inserts.
To find it out, just run the query in the console and check the execution time:
User Create (4.3ms) INSERT INTO "users" ("email", "created_at", "updated_at", "confirmation_token", "confirmation_sent_at") VALUES ( , , , , ) RETURNING "id"
In general, it should be a few milliseconds. If it's much higher (like 300ms), it could be caused by too many indexes. Try to prune redundant ones.
A few hints:
- Indexes on column with a low cardinality are usually less effective and useless (example: booleans, enumerators, values often NULL)
- Compound indexes always cover single indexes on the first column. So
INDEX ON (first_name, last_name)also covers searches on the
first_namecolumn on ly (but not on
The same issue can appear with triggers: don't over use them, because they can slow down a LOT your write performance.
This article is part of the Mònade Developers Blog, written by Piero Dotti.