[performance] add account block DB cache and remove block query joins (#1085)

* add account block DB cache and remove reliance on relational joins

* actually include cache key arguments...

* add a PutBlock() method which also updates the block cache, update tests accordingly

* use `PutBlock` instead of `Put(ctx, block)`

* add + use functions for deleting + invalidating blocks

Signed-off-by: kim <grufwub@gmail.com>
Co-authored-by: tsmethurst <tobi.smethurst@protonmail.com>

1 files changed, 153 insertions, 51 deletions

diff --git a/internal/db/bundb/relationship.go b/internal/db/bundb/relationship.go
index 66e48e441..f6df95524 100644
--- a/internal/db/bundb/relationship.go
+++ b/internal/db/bundb/relationship.go
@@ -21,23 +21,37 @@ package bundb
 import (
 	"context"
 	"database/sql"
+	"errors"
 	"fmt"
+	"time"
 
+	"codeberg.org/gruf/go-cache/v3/result"
 	"github.com/superseriousbusiness/gotosocial/internal/db"
 	"github.com/superseriousbusiness/gotosocial/internal/gtsmodel"
 	"github.com/uptrace/bun"
 )
 
 type relationshipDB struct {
-	conn *DBConn
+	conn       *DBConn
+	accounts   *accountDB
+	blockCache *result.Cache[*gtsmodel.Block]
 }
 
-func (r *relationshipDB) newBlockQ(block *gtsmodel.Block) *bun.SelectQuery {
-	return r.conn.
-		NewSelect().
-		Model(block).
-		Relation("Account").
-		Relation("TargetAccount")
+func (r *relationshipDB) init() {
+	// Initialize block result cache
+	r.blockCache = result.NewSized([]result.Lookup{
+		{Name: "ID"},
+		{Name: "AccountID.TargetAccountID"},
+		{Name: "URI"},
+	}, func(b1 *gtsmodel.Block) *gtsmodel.Block {
+		b2 := new(gtsmodel.Block)
+		*b2 = *b1
+		return b2
+	}, 1000)
+
+	// Set cache TTL and start sweep routine
+	r.blockCache.SetTTL(time.Minute*5, false)
+	r.blockCache.Start(time.Second * 10)
 }
 
 func (r *relationshipDB) newFollowQ(follow interface{}) *bun.SelectQuery {
@@ -49,43 +63,143 @@ func (r *relationshipDB) newFollowQ(follow interface{}) *bun.SelectQuery {
 }
 
 func (r *relationshipDB) IsBlocked(ctx context.Context, account1 string, account2 string, eitherDirection bool) (bool, db.Error) {
+	// Look for a block in direction of account1->account2
+	block1, err := r.getBlock(ctx, account1, account2)
+	if err != nil && !errors.Is(err, db.ErrNoEntries) {
+		return false, err
+	}
+
+	if block1 != nil {
+		// account1 blocks account2
+		return true, nil
+	} else if !eitherDirection {
+		// Don't check for mutli-directional
+		return false, nil
+	}
+
+	// Look for a block in direction of account2->account1
+	block2, err := r.getBlock(ctx, account2, account1)
+	if err != nil && !errors.Is(err, db.ErrNoEntries) {
+		return false, err
+	}
+
+	return (block2 != nil), nil
+}
+
+func (r *relationshipDB) GetBlock(ctx context.Context, account1 string, account2 string) (*gtsmodel.Block, db.Error) {
+	// Fetch block from database
+	block, err := r.getBlock(ctx, account1, account2)
+	if err != nil {
+		return nil, err
+	}
+
+	// Set the block originating account
+	block.Account, err = r.accounts.GetAccountByID(ctx, block.AccountID)
+	if err != nil {
+		return nil, err
+	}
+
+	// Set the block target account
+	block.TargetAccount, err = r.accounts.GetAccountByID(ctx, block.TargetAccountID)
+	if err != nil {
+		return nil, err
+	}
+
+	return block, nil
+}
+
+func (r *relationshipDB) getBlock(ctx context.Context, account1 string, account2 string) (*gtsmodel.Block, db.Error) {
+	return r.blockCache.Load("AccountID.TargetAccountID", func() (*gtsmodel.Block, error) {
+		var block gtsmodel.Block
+
+		q := r.conn.NewSelect().Model(&block).
+			Where("? = ?", bun.Ident("block.account_id"), account1).
+			Where("? = ?", bun.Ident("block.target_account_id"), account2)
+		if err := q.Scan(ctx); err != nil {
+			return nil, r.conn.ProcessError(err)
+		}
+
+		return &block, nil
+	}, account1, account2)
+}
+
+func (r *relationshipDB) PutBlock(ctx context.Context, block *gtsmodel.Block) db.Error {
+	return r.blockCache.Store(block, func() error {
+		_, err := r.conn.NewInsert().Model(block).Exec(ctx)
+		return r.conn.ProcessError(err)
+	})
+}
+
+func (r *relationshipDB) DeleteBlockByID(ctx context.Context, id string) db.Error {
+	if _, err := r.conn.
+		NewDelete().
+		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
+		Where("? = ?", bun.Ident("block.id"), id).
+		Exec(ctx); err != nil {
+		return r.conn.ProcessError(err)
+	}
+
+	// Drop any old value from cache by this ID
+	r.blockCache.Invalidate("ID", id)
+	return nil
+}
+
+func (r *relationshipDB) DeleteBlockByURI(ctx context.Context, uri string) db.Error {
+	if _, err := r.conn.
+		NewDelete().
+		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
+		Where("? = ?", bun.Ident("block.uri"), uri).
+		Exec(ctx); err != nil {
+		return r.conn.ProcessError(err)
+	}
+
+	// Drop any old value from cache by this URI
+	r.blockCache.Invalidate("URI", uri)
+	return nil
+}
+
+func (r *relationshipDB) DeleteBlocksByOriginAccountID(ctx context.Context, originAccountID string) db.Error {
+	blockIDs := []string{}
+
 	q := r.conn.
 		NewSelect().
 		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
-		Column("block.id")
+		Column("block.id").
+		Where("? = ?", bun.Ident("block.account_id"), originAccountID)
 
-	if eitherDirection {
-		q = q.
-			WhereGroup(" OR ", func(inner *bun.SelectQuery) *bun.SelectQuery {
-				return inner.
-					Where("? = ?", bun.Ident("block.account_id"), account1).
-					Where("? = ?", bun.Ident("block.target_account_id"), account2)
-			}).
-			WhereGroup(" OR ", func(inner *bun.SelectQuery) *bun.SelectQuery {
-				return inner.
-					Where("? = ?", bun.Ident("block.account_id"), account2).
-					Where("? = ?", bun.Ident("block.target_account_id"), account1)
-			})
-	} else {
-		q = q.
-			Where("? = ?", bun.Ident("block.account_id"), account1).
-			Where("? = ?", bun.Ident("block.target_account_id"), account2)
+	if err := q.Scan(ctx, &blockIDs); err != nil {
+		return r.conn.ProcessError(err)
 	}
 
-	return r.conn.Exists(ctx, q)
+	for _, blockID := range blockIDs {
+		if err := r.DeleteBlockByID(ctx, blockID); err != nil {
+			return err
+		}
+	}
+
+	return nil
 }
 
-func (r *relationshipDB) GetBlock(ctx context.Context, account1 string, account2 string) (*gtsmodel.Block, db.Error) {
-	block := &gtsmodel.Block{}
+func (r *relationshipDB) DeleteBlocksByTargetAccountID(ctx context.Context, targetAccountID string) db.Error {
+	blockIDs := []string{}
 
-	q := r.newBlockQ(block).
-		Where("? = ?", bun.Ident("block.account_id"), account1).
-		Where("? = ?", bun.Ident("block.target_account_id"), account2)
+	q := r.conn.
+		NewSelect().
+		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
+		Column("block.id").
+		Where("? = ?", bun.Ident("block.target_account_id"), targetAccountID)
 
-	if err := q.Scan(ctx); err != nil {
-		return nil, r.conn.ProcessError(err)
+	if err := q.Scan(ctx, &blockIDs); err != nil {
+		return r.conn.ProcessError(err)
 	}
-	return block, nil
+
+	for _, blockID := range blockIDs {
+		if err := r.DeleteBlockByID(ctx, blockID); err != nil {
+			return err
+		}
+	}
+
+	return nil
 }
 
 func (r *relationshipDB) GetRelationship(ctx context.Context, requestingAccount string, targetAccount string) (*gtsmodel.Relationship, db.Error) {
@@ -144,30 +258,18 @@ func (r *relationshipDB) GetRelationship(ctx context.Context, requestingAccount
 	rel.Requested = requested
 
 	// check if the requesting account is blocking the target account
-	blockingQ := r.conn.
-		NewSelect().
-		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
-		Column("block.id").
-		Where("? = ?", bun.Ident("block.account_id"), requestingAccount).
-		Where("? = ?", bun.Ident("block.target_account_id"), targetAccount)
-	blocking, err := r.conn.Exists(ctx, blockingQ)
-	if err != nil {
+	blockA2T, err := r.getBlock(ctx, requestingAccount, targetAccount)
+	if err != nil && !errors.Is(err, db.ErrNoEntries) {
 		return nil, fmt.Errorf("GetRelationship: error checking blocking: %s", err)
 	}
-	rel.Blocking = blocking
+	rel.Blocking = (blockA2T != nil)
 
 	// check if the requesting account is blocked by the target account
-	blockedByQ := r.conn.
-		NewSelect().
-		TableExpr("? AS ?", bun.Ident("blocks"), bun.Ident("block")).
-		Column("block.id").
-		Where("? = ?", bun.Ident("block.account_id"), targetAccount).
-		Where("? = ?", bun.Ident("block.target_account_id"), requestingAccount)
-	blockedBy, err := r.conn.Exists(ctx, blockedByQ)
-	if err != nil {
+	blockT2A, err := r.getBlock(ctx, targetAccount, requestingAccount)
+	if err != nil && !errors.Is(err, db.ErrNoEntries) {
 		return nil, fmt.Errorf("GetRelationship: error checking blockedBy: %s", err)
 	}
-	rel.BlockedBy = blockedBy
+	rel.BlockedBy = (blockT2A != nil)
 
 	return rel, nil
 }