Introduce counter_cache: :timescaledb

[jonatas]

Counter caches are interesting but they have a serious problem of bringing vacuum issues:

See more about the benefits of this denormalization:
https://thoughtbot.com/blog/what-is-counter-cache#counter-cache-to-the-rescue

But there are some issues with this approach too:

https://www.timescale.com/blog/counter-analytics-in-postgresql-beyond-simple-data-denormalization/

class Like < ApplicationRecord
  belongs_to :content, counter_cache: true
end

Now, let's think about introducing it to the timescaledb gem to track such events on a hypertable and migrate counter caches to continuous aggregates.

Syntax Ideas:
Automatic:

class Like < Hypertable
  belongs_to :content, counter_cache: :timescaledb
  # ...
end

Configurable:

class Like < Hypertable
  belongs_to :content, counter_cache: [:hourly, :daily]
  # ....
end

Behind the scenes it can find or build a continuous aggregates for it. Injecting some code like this:

class Like < Hypertable
  # ...
  scope :counter, -> { select(:content_id, "count(*)").group(1) }

  continous_aggregate :counter, timeframes: [:hour, :day]
end

Also patching the relation class to introduce the methods:

Content.class_eval do

  has_many :likes_per_hour, foreign_class: Like::PerHour
  has_many :likes_per_day

  scope :total_likes_count, -> { select("sum(count) as total") }

end
content = Content.first
content.total_likes_count # => "select sum(count) as total from likes_daily where content_id = $1"
content.likes_hourly #  => "select * from likes_hourly where content_id = $1"
content.likes_daily # =>  "select * from likes_daily where content_id = $1"