Extract counters from accounts table to account_stats table (#9295)

16 files changed, 101 insertions, 44 deletions

diff --git a/app/controllers/admin/instances_controller.rb b/app/controllers/admin/instances_controller.rb
index 8ed0ea421..6f8eaf65c 100644
--- a/app/controllers/admin/instances_controller.rb
+++ b/app/controllers/admin/instances_controller.rb
@@ -31,7 +31,7 @@ module Admin
     end
 
     def subscribeable_accounts
-      Account.with_followers.remote.where(domain: params[:by_domain])
+      Account.remote.where(protocol: :ostatus).where(domain: params[:by_domain])
     end
 
     def filter_params
diff --git a/app/controllers/api/v1/accounts/follower_accounts_controller.rb b/app/controllers/api/v1/accounts/follower_accounts_controller.rb
index daa35769e..ec15debb0 100644
--- a/app/controllers/api/v1/accounts/follower_accounts_controller.rb
+++ b/app/controllers/api/v1/accounts/follower_accounts_controller.rb
@@ -25,7 +25,7 @@ class Api::V1::Accounts::FollowerAccountsController < Api::BaseController
   end
 
   def default_accounts
-    Account.includes(:active_relationships).references(:active_relationships)
+    Account.includes(:active_relationships, :account_stat).references(:active_relationships)
   end
 
   def paginated_follows
diff --git a/app/controllers/api/v1/accounts/following_accounts_controller.rb b/app/controllers/api/v1/accounts/following_accounts_controller.rb
index 6be97b87e..f3e112f2c 100644
--- a/app/controllers/api/v1/accounts/following_accounts_controller.rb
+++ b/app/controllers/api/v1/accounts/following_accounts_controller.rb
@@ -25,7 +25,7 @@ class Api::V1::Accounts::FollowingAccountsController < Api::BaseController
   end
 
   def default_accounts
-    Account.includes(:passive_relationships).references(:passive_relationships)
+    Account.includes(:passive_relationships, :account_stat).references(:passive_relationships)
   end
 
   def paginated_follows
diff --git a/app/controllers/api/v1/blocks_controller.rb b/app/controllers/api/v1/blocks_controller.rb
index 99c53d59a..4cff04cad 100644
--- a/app/controllers/api/v1/blocks_controller.rb
+++ b/app/controllers/api/v1/blocks_controller.rb
@@ -19,7 +19,7 @@ class Api::V1::BlocksController < Api::BaseController
   end
 
   def paginated_blocks
-    @paginated_blocks ||= Block.eager_load(:target_account)
+    @paginated_blocks ||= Block.eager_load(target_account: :account_stat)
                                .where(account: current_account)
                                .paginate_by_max_id(
                                  limit_param(DEFAULT_ACCOUNTS_LIMIT),
diff --git a/app/controllers/api/v1/endorsements_controller.rb b/app/controllers/api/v1/endorsements_controller.rb
index 0f04b488f..2770c7aef 100644
--- a/app/controllers/api/v1/endorsements_controller.rb
+++ b/app/controllers/api/v1/endorsements_controller.rb
@@ -27,7 +27,7 @@ class Api::V1::EndorsementsController < Api::BaseController
   end
 
   def endorsed_accounts
-    current_account.endorsed_accounts
+    current_account.endorsed_accounts.includes(:account_stat)
   end
 
   def insert_pagination_headers
diff --git a/app/controllers/api/v1/follow_requests_controller.rb b/app/controllers/api/v1/follow_requests_controller.rb
index e9aca5f8a..e6888154e 100644
--- a/app/controllers/api/v1/follow_requests_controller.rb
+++ b/app/controllers/api/v1/follow_requests_controller.rb
@@ -33,7 +33,7 @@ class Api::V1::FollowRequestsController < Api::BaseController
   end
 
   def default_accounts
-    Account.includes(:follow_requests).references(:follow_requests)
+    Account.includes(:follow_requests, :account_stat).references(:follow_requests)
   end
 
   def paginated_follow_requests
diff --git a/app/controllers/api/v1/lists/accounts_controller.rb b/app/controllers/api/v1/lists/accounts_controller.rb
index ec4477034..23078263e 100644
--- a/app/controllers/api/v1/lists/accounts_controller.rb
+++ b/app/controllers/api/v1/lists/accounts_controller.rb
@@ -37,9 +37,9 @@ class Api::V1::Lists::AccountsController < Api::BaseController
 
   def load_accounts
     if unlimited?
-      @list.accounts.all
+      @list.accounts.includes(:account_stat).all
     else
-      @list.accounts.paginate_by_max_id(limit_param(DEFAULT_ACCOUNTS_LIMIT), params[:max_id], params[:since_id])
+      @list.accounts.includes(:account_stat).paginate_by_max_id(limit_param(DEFAULT_ACCOUNTS_LIMIT), params[:max_id], params[:since_id])
     end
   end
 
diff --git a/app/controllers/api/v1/statuses/favourited_by_accounts_controller.rb b/app/controllers/api/v1/statuses/favourited_by_accounts_controller.rb
index 8f4070bc7..657e57831 100644
--- a/app/controllers/api/v1/statuses/favourited_by_accounts_controller.rb
+++ b/app/controllers/api/v1/statuses/favourited_by_accounts_controller.rb
@@ -22,7 +22,7 @@ class Api::V1::Statuses::FavouritedByAccountsController < Api::BaseController
 
   def default_accounts
     Account
-      .includes(:favourites)
+      .includes(:favourites, :account_stat)
       .references(:favourites)
       .where(favourites: { status_id: @status.id })
   end
diff --git a/app/controllers/api/v1/statuses/reblogged_by_accounts_controller.rb b/app/controllers/api/v1/statuses/reblogged_by_accounts_controller.rb
index 93b83ce48..4315b0283 100644
--- a/app/controllers/api/v1/statuses/reblogged_by_accounts_controller.rb
+++ b/app/controllers/api/v1/statuses/reblogged_by_accounts_controller.rb
@@ -21,7 +21,7 @@ class Api::V1::Statuses::RebloggedByAccountsController < Api::BaseController
   end
 
   def default_accounts
-    Account.includes(:statuses).references(:statuses)
+    Account.includes(:statuses, :account_stat).references(:statuses)
   end
 
   def paginated_statuses
diff --git a/app/models/account.rb b/app/models/account.rb
index 44963f3e6..593ee29f7 100644
--- a/app/models/account.rb
+++ b/app/models/account.rb
@@ -32,9 +32,6 @@
 #  suspended               :boolean          default(FALSE), not null
 #  locked                  :boolean          default(FALSE), not null
 #  header_remote_url       :string           default(""), not null
-#  statuses_count          :integer          default(0), not null
-#  followers_count         :integer          default(0), not null
-#  following_count         :integer          default(0), not null
 #  last_webfingered_at     :datetime
 #  inbox_url               :string           default(""), not null
 #  outbox_url              :string           default(""), not null
@@ -58,6 +55,7 @@ class Account < ApplicationRecord
   include AccountInteractions
   include Attachmentable
   include Paginable
+  include AccountCounters
 
   enum protocol: [:ostatus, :activitypub]
 
@@ -119,8 +117,6 @@ class Account < ApplicationRecord
 
   scope :remote, -> { where.not(domain: nil) }
   scope :local, -> { where(domain: nil) }
-  scope :without_followers, -> { where(followers_count: 0) }
-  scope :with_followers, -> { where('followers_count > 0') }
   scope :expiring, ->(time) { remote.where.not(subscription_expires_at: nil).where('subscription_expires_at < ?', time) }
   scope :partitioned, -> { order(Arel.sql('row_number() over (partition by domain)')) }
   scope :silenced, -> { where(silenced: true) }
@@ -385,7 +381,9 @@ class Account < ApplicationRecord
         LIMIT ?
       SQL
 
-      find_by_sql([sql, limit])
+      records = find_by_sql([sql, limit])
+      ActiveRecord::Associations::Preloader.new.preload(records, :account_stat)
+      records
     end
 
     def advanced_search_for(terms, account, limit = 10, following = false)
@@ -412,7 +410,7 @@ class Account < ApplicationRecord
           LIMIT ?
         SQL
 
-        find_by_sql([sql, account.id, account.id, account.id, limit])
+        records = find_by_sql([sql, account.id, account.id, account.id, limit])
       else
         sql = <<-SQL.squish
           SELECT
@@ -428,8 +426,11 @@ class Account < ApplicationRecord
           LIMIT ?
         SQL
 
-        find_by_sql([sql, account.id, account.id, limit])
+        records = find_by_sql([sql, account.id, account.id, limit])
       end
+
+      ActiveRecord::Associations::Preloader.new.preload(records, :account_stat)
+      records
     end
 
     private
diff --git a/app/models/account_stat.rb b/app/models/account_stat.rb
new file mode 100644
index 000000000..d5715268e
--- /dev/null
+++ b/app/models/account_stat.rb
@@ -0,0 +1,26 @@
+# frozen_string_literal: true
+
+# == Schema Information
+#
+# Table name: account_stats
+#
+#  id              :bigint(8)        not null, primary key
+#  account_id      :bigint(8)        not null
+#  statuses_count  :bigint(8)        default(0), not null
+#  following_count :bigint(8)        default(0), not null
+#  followers_count :bigint(8)        default(0), not null
+#  created_at      :datetime         not null
+#  updated_at      :datetime         not null
+#
+
+class AccountStat < ApplicationRecord
+  belongs_to :account, inverse_of: :account_stat
+
+  def increment_count!(key)
+    update(key => public_send(key) + 1)
+  end
+
+  def decrement_count!(key)
+    update(key => [public_send(key) - 1, 0].max)
+  end
+end
diff --git a/app/models/concerns/account_counters.rb b/app/models/concerns/account_counters.rb
new file mode 100644
index 000000000..fa3ec9a3d
--- /dev/null
+++ b/app/models/concerns/account_counters.rb
@@ -0,0 +1,31 @@
+# frozen_string_literal: true
+
+module AccountCounters
+  extend ActiveSupport::Concern
+
+  included do
+    has_one :account_stat, inverse_of: :account
+    after_save :save_account_stat
+  end
+
+  delegate :statuses_count,
+           :statuses_count=,
+           :following_count,
+           :following_count=,
+           :followers_count,
+           :followers_count=,
+           :increment_count!,
+           :decrement_count!,
+           to: :account_stat
+
+  def account_stat
+    super || build_account_stat
+  end
+
+  private
+
+  def save_account_stat
+    return unless account_stat&.changed?
+    account_stat.save
+  end
+end
diff --git a/app/models/follow.rb b/app/models/follow.rb
index 7ad56eb78..87fa11425 100644
--- a/app/models/follow.rb
+++ b/app/models/follow.rb
@@ -16,11 +16,8 @@ class Follow < ApplicationRecord
   include Paginable
   include RelationshipCacheable
 
-  belongs_to :account, counter_cache: :following_count
-
-  belongs_to :target_account,
-             class_name: 'Account',
-             counter_cache: :followers_count
+  belongs_to :account
+  belongs_to :target_account, class_name: 'Account'
 
   has_one :notification, as: :activity, dependent: :destroy
 
@@ -39,7 +36,9 @@ class Follow < ApplicationRecord
   end
 
   before_validation :set_uri, only: :create
+  after_create :increment_cache_counters
   after_destroy :remove_endorsements
+  after_destroy :decrement_cache_counters
 
   private
 
@@ -50,4 +49,14 @@ class Follow < ApplicationRecord
   def remove_endorsements
     AccountPin.where(target_account_id: target_account_id, account_id: account_id).delete_all
   end
+
+  def increment_cache_counters
+    account&.increment_count!(:following_count)
+    target_account&.increment_count!(:followers_count)
+  end
+
+  def decrement_cache_counters
+    account&.decrement_count!(:following_count)
+    target_account&.decrement_count!(:followers_count)
+  end
 end
diff --git a/app/models/notification.rb b/app/models/notification.rb
index 4233532d0..2f0a9b78c 100644
--- a/app/models/notification.rb
+++ b/app/models/notification.rb
@@ -75,7 +75,7 @@ class Notification < ApplicationRecord
 
       return if account_ids.empty?
 
-      accounts = Account.where(id: account_ids).each_with_object({}) { |a, h| h[a.id] = a }
+      accounts = Account.where(id: account_ids).includes(:account_stat).each_with_object({}) { |a, h| h[a.id] = a }
 
       cached_items.each do |item|
         item.from_account = accounts[item.from_account_id]
diff --git a/app/models/status.rb b/app/models/status.rb
index 680081724..0449d33e1 100644
--- a/app/models/status.rb
+++ b/app/models/status.rb
@@ -94,17 +94,16 @@ class Status < ApplicationRecord
     end
   }
 
-  cache_associated :account,
-                   :application,
+  cache_associated :application,
                    :media_attachments,
                    :conversation,
                    :status_stat,
                    :tags,
                    :preview_cards,
                    :stream_entry,
-                   active_mentions: :account,
+                   account: :account_stat,
+                   active_mentions: { account: :account_stat },
                    reblog: [
-                     :account,
                      :application,
                      :stream_entry,
                      :tags,
@@ -112,9 +111,10 @@ class Status < ApplicationRecord
                      :media_attachments,
                      :conversation,
                      :status_stat,
-                     active_mentions: :account,
+                     account: :account_stat,
+                     active_mentions: { account: :account_stat },
                    ],
-                   thread: :account
+                   thread: { account: :account_stat }
 
   delegate :domain, to: :account, prefix: true
 
@@ -348,7 +348,7 @@ class Status < ApplicationRecord
 
       return if account_ids.empty?
 
-      accounts = Account.where(id: account_ids).each_with_object({}) { |a, h| h[a.id] = a }
+      accounts = Account.where(id: account_ids).includes(:account_stat).each_with_object({}) { |a, h| h[a.id] = a }
 
       cached_items.each do |item|
         item.account = accounts[item.account_id]
@@ -475,12 +475,7 @@ class Status < ApplicationRecord
   def increment_counter_caches
     return if direct_visibility?
 
-    if association(:account).loaded?
-      account.update_attribute(:statuses_count, account.statuses_count + 1)
-    else
-      Account.where(id: account_id).update_all('statuses_count = COALESCE(statuses_count, 0) + 1')
-    end
-
+    account&.increment_count!(:statuses_count)
     reblog&.increment_count!(:reblogs_count) if reblog?
     thread&.increment_count!(:replies_count) if in_reply_to_id.present? && (public_visibility? || unlisted_visibility?)
   end
@@ -488,12 +483,7 @@ class Status < ApplicationRecord
   def decrement_counter_caches
     return if direct_visibility? || marked_for_mass_destruction?
 
-    if association(:account).loaded?
-      account.update_attribute(:statuses_count, [account.statuses_count - 1, 0].max)
-    else
-      Account.where(id: account_id).update_all('statuses_count = GREATEST(COALESCE(statuses_count, 0) - 1, 0)')
-    end
-
+    account&.decrement_count!(:statuses_count)
     reblog&.decrement_count!(:reblogs_count) if reblog?
     thread&.decrement_count!(:replies_count) if in_reply_to_id.present? && (public_visibility? || unlisted_visibility?)
   end
diff --git a/app/presenters/instance_presenter.rb b/app/presenters/instance_presenter.rb
index 9a9157f1b..457257e26 100644
--- a/app/presenters/instance_presenter.rb
+++ b/app/presenters/instance_presenter.rb
@@ -22,7 +22,7 @@ class InstancePresenter
   end
 
   def status_count
-    Rails.cache.fetch('local_status_count') { Account.local.sum(:statuses_count) }
+    Rails.cache.fetch('local_status_count') { Account.local.joins(:account_stat).sum('account_stats.statuses_count') }
   end
 
   def domain_count