Advanced counter_cache in Rails with SQL triggers
Tags: rails, ror, postgres, sql
Published: 2019-06-13 20:33:31

---
title: Advanced counter_cache in Rails with SQL triggers
published: true
description: A performant cache of computed data in Rails.
tags: [Ruby on Rails, RoR, PostgreSQL, SQL]
cover_image: https://thepracticaldev.s3.amazonaws.com/i/1d6xa40azw6h1rr5y4f8.jpg
---

Ruby on Rails has useful little feature called [counter_cache](https://guides.rubyonrails.org/association_basics.html#options-for-belongs-to-counter-cache). In a scenario where you have model `Author` that has relationship `hasMany` with model `Book`, rails will save in the column `authors.books_count` respective number of books per given author when you manipulate the database through Active Record (AR) models.

## The problem

That is well and good for simple scenarios. But mine was much more complicated than that. Imagine following models:

```ruby
# models/visit.rb

class Visit < ActiveRecord::Base
  has_many :sells

  def amount
    sells.sum("(sells.price * sells.count) * (1 - sells.discount / 100)")
  end
end
```

```ruby
# models/sell.rb

class Sell < ActiveRecord::Base
  belongs_to :visit
end
```

1. The attribute `amount` is being read every time a request to `Visit` is made. And request to `Visit` can be made in big batches. So having that value **cached** somewhere makes sense from **performance** perspective.
1. I need the computed values to be immediately correct, so offloading the computation to [active_job](https://edgeguides.rubyonrails.org/active_job_basics.html) was not an option.
1. There are many more attributes on my models that need these types of calculations and they are usually even more complex than this example.
1. Rails native `counter_cache` can only keep track of `count()` of the records. For more complex calculations, there is the [counter_culture](https://github.com/magnusvk/counter_culture) gem, but for that one all of the problems from the next point also apply.
1. It is perfectly possible to use `before_save` from AR to [save calculated fields](https://russt.me/2018/06/saving-calculated-fields-in-ruby-on-rails-5/), but I had few concerns:
   - Some of the calculation logic is fairly complex and I was not sure whether it would be possible to express everything in AR that would require just one SQL query.
   - Even if it would be possible to do everything in 1 SQL query, it is still a round trip from the database to the application server and back. And all the calculations [lives in a transaction](https://api.rubyonrails.org/classes/ActiveRecord/Callbacks.html#module-ActiveRecord::Callbacks-label-Transactions), so it will slow down any other operations on rows that are part of the transaction.

## Offloading work to the database

I made the decission to offload the work to [(PostgreSQL) triggers](https://www.postgresql.org/docs/9.2/plpgsql-trigger.html). The obvious disadvantages of this solution are:

1. Only the database knows that the data has been changed. So if your application requires _store & read_ logic, you should check that the read actually returns fresh data.
1. If there is more logic on the application side of things, then you might need to duplicate that also in your database. For example the "soft delete" gem [paranoia](https://github.com/rubysherpas/paranoia) always leaves deleted records in place and just adds a flag that record should be ignored. Your database naturally does not know about this.
1. I personally still consider SQL procedures/triggers a dark magic so debugging those is not a nice experience for me.

## The problem of C(reate) U(pdate) D(elete)

In PostgreSQL depending on the trigger operation (`TG_OP`) following variables are created:

- `NEW` for _Create_ operation
- `OLD` and `NEW` for _Update_ operation
- `OLD` for _Delete_

These variables hold respective state of the row on which the operation happened. This is important especially for the _Update_ operation. Imagine a scenario where the code changes the `visit_id` for a `Sell`. Then the `amount` needs to be recalculated on both `OLD` and `NEW` records (resp: their respective relationships).

The idea of how to solve this in an elegant way plus the proper syntax in (Postgre)SQL is the main topic of this post.

For a good way on how to deal with triggers in _Rails_, I would recommend a great gem - [hair_trigger](https://github.com/jenseng/hair_trigger). The gem works as follows: you will simply store the **SQL** of the trigger in respective **model** and every time this code changes _hair_trigger_ will generate migration that will drop the old trigger and create an updated one.

With this approach you can easily see the "additional logic" of your _Models_ / _tables_. And have the privilege to not care about how to load the triggers in the database.

## Show me the code

```ruby
# models/visit.rb

class Visit < ActiveRecord::Base
  has_many :sells

  def amount
    sells.sum("(sells.price * sells.count) * (1 - sells.discount / 100)")
  end
end
```

```ruby
# models/sell.rb

class Sell < ActiveRecord::Base
  belongs_to :visit

  trigger.after(:insert, :update, :delete)
         .name("after_sells")
         .declare("var_curr sells; var_amount decimal; var_curr_arr sells[];") do
    <<-SQL

      CASE TG_OP
        WHEN 'DELETE' THEN
          var_curr_arr := ARRAY[OLD];
        WHEN 'UPDATE' THEN
          var_curr_arr := ARRAY[OLD, NEW];
        WHEN 'INSERT' THEN
          var_curr_arr := ARRAY[NEW];
      END CASE;

      FOREACH var_curr IN ARRAY var_curr_arr
      LOOP

        var_amount := (SELECT COALESCE(SUM((sells.price * sells.count) * (1 - sells.discount / 100)), 0) FROM sells WHERE sells.visit_id = var_curr.visit_id;

        UPDATE visits SET amount = var_amount WHERE visits.id = var_curr.visit_id;

      END LOOP;
    SQL
  end
end
```

Here's what's happening there:

1. Based on `TG_OP` operation we will create array `var_curr_arr` that will be filled with the rows that has changed.
1. Then we will `FOREACH` over this array. Realistically speaking maximum of two elements will be there, but allows us to [DRY](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) our code.
1. And finally inside the `LOOP` we will do the respective update(s).

After this we just need to let _hair_trigger_ generate new database migrations based on the updated code of our trigger:

```sh
rake db:generate_trigger_migration
```

And then simply run Rails migrations:

```sh
rake db:migrate
```

Now you can go ahead, _create / update / delete_ bunch of _sells_ and see whether _amount_ for respective _visit(s)_ has been updated.

## Notes

- The `COALESCE(..., 0)` trick is here to make sure we don't hit [the problem with NULL](http://www.postgresqltutorial.com/postgresql-coalesce/).
- Splash photo credits: [Crissy Jarvis](https://unsplash.com/@crissyjarvis).