[performance] temporarily cache account status counts to reduce no. account counts (#2620)

* temporarily cache account status counts to reduce no. account counts

* whoops, forgot to initAccountCounts()

* use already fetched cache capacity value

* make cache a ptr type

* whoops, use count instead of just select

* fix to correctly use the transaction

* properly wrap that tx :innocent:

* correctly wrap both tx types

* outline retryOnBusy() to allow the fast path to be inlined

* return err on context cancelled

* remove unnecessary storage of context in stmt, fix Exec and Query interface implementations

* shutup linter

3 files changed, 30 insertions, 0 deletions

diff --git a/internal/cache/cache.go b/internal/cache/cache.go
index a278336ae..17fa03323 100644
--- a/internal/cache/cache.go
+++ b/internal/cache/cache.go
@@ -51,6 +51,7 @@ func (c *Caches) Init() {
 	log.Infof(nil, "init: %p", c)
 
 	c.initAccount()
+	c.initAccountCounts()
 	c.initAccountNote()
 	c.initApplication()
 	c.initBlock()
diff --git a/internal/cache/db.go b/internal/cache/db.go
index 894d74109..275a25451 100644
--- a/internal/cache/db.go
+++ b/internal/cache/db.go
@@ -36,6 +36,13 @@ type GTSCaches struct {
 	// AccountNote provides access to the gtsmodel Note database cache.
 	AccountNote structr.Cache[*gtsmodel.AccountNote]
 
+	// TEMPORARY CACHE TO ALLEVIATE SLOW COUNT QUERIES,
+	// (in time will be removed when these IDs are cached).
+	AccountCounts *simple.Cache[string, struct {
+		Statuses int
+		Pinned   int
+	}]
+
 	// Application provides access to the gtsmodel Application database cache.
 	Application structr.Cache[*gtsmodel.Application]
 
@@ -192,6 +199,22 @@ func (c *Caches) initAccount() {
 	})
 }
 
+func (c *Caches) initAccountCounts() {
+	// Simply use size of accounts cache,
+	// as this cache will be very small.
+	cap := c.GTS.Account.Cap()
+	if cap == 0 {
+		panic("must be initialized before accounts")
+	}
+
+	log.Infof(nil, "cache size = %d", cap)
+
+	c.GTS.AccountCounts = simple.New[string, struct {
+		Statuses int
+		Pinned   int
+	}](0, cap)
+}
+
 func (c *Caches) initAccountNote() {
 	// Calculate maximum cache size.
 	cap := calculateResultCacheMax(
diff --git a/internal/cache/invalidate.go b/internal/cache/invalidate.go
index d85c503da..e7dfa9e8a 100644
--- a/internal/cache/invalidate.go
+++ b/internal/cache/invalidate.go
@@ -27,6 +27,9 @@ import (
 // HOOKS TO BE CALLED ON DELETE YOU MUST FIRST POPULATE IT IN THE CACHE.
 
 func (c *Caches) OnInvalidateAccount(account *gtsmodel.Account) {
+	// Invalidate status counts for this account.
+	c.GTS.AccountCounts.Invalidate(account.ID)
+
 	// Invalidate account ID cached visibility.
 	c.Visibility.Invalidate("ItemID", account.ID)
 	c.Visibility.Invalidate("RequesterID", account.ID)
@@ -151,6 +154,9 @@ func (c *Caches) OnInvalidatePollVote(vote *gtsmodel.PollVote) {
 }
 
 func (c *Caches) OnInvalidateStatus(status *gtsmodel.Status) {
+	// Invalidate status counts for this account.
+	c.GTS.AccountCounts.Invalidate(status.AccountID)
+
 	// Invalidate status ID cached visibility.
 	c.Visibility.Invalidate("ItemID", status.ID)