# How to optimize DB writes in Ruby on Rails

#### Foreword
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.](https://devs.monade.io/how-to-optimize-rails-db-reads-in-rails)*

### Transactions
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:
```ruby
1000.times do
  User.create!(email: 'hey@example.com')
end
```
is much slower than
```ruby
User.transaction do
  1000.times do
    User.create!(email: 'hey@example.com')
  end
end
```

### Validations
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.<br>
In some cases disabling validations can significantly speed up operations, especially when we are dealing with massive operations like imports and such.<br>
To skip validations we can call the save method as:
```ruby
record.save(validate: false)
```

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 `UPDATE` operation.

Those methods are `insert_all`, `insert_all!`, `upsert_all` and `upsert_all!`.

The main advantage is that insertion time is orders of magnitude faster.

**Warning:** with those methods model validations and callbacks **will not** be invoked. (`before_create`, `after_save`, etc.)

If you're using Rails < 6, you can achieve the same functionality using this gem:<br>
https://github.com/jamis/bulk_insert

### Massive deletions
The same thing is valid for deletions.

If you need to prune mass data, consider that this:
```ruby
User.each(&:destroy)
```
Is much slower than this:
```ruby
User.transaction do
  User.each(&:destroy)
end
```
And it's **much much** slower than this:
```ruby
User.delete_all
```

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.

For instance:
* Don't make call an `#update` in the `after_create` hook. Use `before_create` and change the column, instead.

**Don't**
```ruby
class User
  after_create do
    update(is_valid_user: true) if email.ends_with?('@gmail.com')
  end
end
```

**Do**
```ruby
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 `save` to 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.

**Don't**
```ruby
user.update!(email: '___', password: '___')
...
...
user.update!(is_valid: true) if user.email.ends_with?('@gmail.com')
user.update!(metadata: metadata) unless metadata.nil?
```

**Do**
```ruby
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 ($1, $2, $3, $4, $5) 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_name` column on ly (but not on `last_name`).

The same issue can appear with triggers: don't over use them, because they can slow down a LOT your write performance.

## About Us

This article is part of the [Mònade](https://monade.io) Developers Blog, written by @[Piero Dotti](@ProGM).