[performance] reduce account stats database calls (#4496)

Reduces both code complexity and the number of separate database transactions we need to make by moving account statistics operations into the database as side-effects of the operations that effect them. In contrast to currently, where we manually update account statistics at the application layer.

Reviewed-on: https://codeberg.org/superseriousbusiness/gotosocial/pulls/4496
Co-authored-by: kim <grufwub@gmail.com>
Co-committed-by: kim <grufwub@gmail.com>

5 files changed, 434 insertions, 260 deletions

diff --git a/internal/db/bundb/relationship_follow.go b/internal/db/bundb/relationship_follow.go
index f37efa94b..ca0ac2bf8 100644
--- a/internal/db/bundb/relationship_follow.go
+++ b/internal/db/bundb/relationship_follow.go
@@ -189,7 +189,7 @@ func (r *relationshipDB) getFollow(ctx context.Context, lookup string, dbQuery f
 func (r *relationshipDB) PopulateFollow(ctx context.Context, follow *gtsmodel.Follow) error {
 	var (
 		err  error
-		errs = gtserror.NewMultiError(2)
+		errs gtserror.MultiError
 	)
 
 	if follow.Account == nil {
@@ -218,8 +218,10 @@ func (r *relationshipDB) PopulateFollow(ctx context.Context, follow *gtsmodel.Fo
 }
 
 func (r *relationshipDB) PutFollow(ctx context.Context, follow *gtsmodel.Follow) error {
-	return r.state.Caches.DB.Follow.Store(follow, func() error {
-		_, err := r.db.NewInsert().Model(follow).Exec(ctx)
+	return r.insertFollow(ctx, follow, func(tx bun.Tx) error {
+		_, err := tx.NewInsert().
+			Model(follow).
+			Exec(ctx)
 		return err
 	})
 }
@@ -239,7 +241,6 @@ func (r *relationshipDB) UpdateFollow(ctx context.Context, follow *gtsmodel.Foll
 			Exec(ctx); err != nil {
 			return err
 		}
-
 		return nil
 	})
 }
@@ -249,104 +250,63 @@ func (r *relationshipDB) DeleteFollow(
 	sourceAccountID string,
 	targetAccountID string,
 ) error {
+	return r.deleteFollow(ctx, func(tx bun.Tx) (*gtsmodel.Follow, error) {
+		var deleted gtsmodel.Follow
+		deleted.AccountID = sourceAccountID
+		deleted.TargetAccountID = targetAccountID
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("account_id"), sourceAccountID).
+			Where("? = ?", bun.Ident("target_account_id"), targetAccountID).
+			Returning("?", bun.Ident("id")).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.Follow
-	deleted.AccountID = sourceAccountID
-	deleted.TargetAccountID = targetAccountID
-
-	// Delete follow from origin
-	// account, to targeting account,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("account_id"), sourceAccountID).
-		Where("? = ?", bun.Ident("target_account_id"), targetAccountID).
-		Returning("?", bun.Ident("id")).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with source / target account IDs,
-	// manually calling invalidate hook in case it isn't cached.
-	r.state.Caches.DB.Follow.Invalidate("AccountID,TargetAccountID",
-		sourceAccountID, targetAccountID)
-	r.state.Caches.OnInvalidateFollow(&deleted)
-
-	// Delete every list entry that was created targetting this follow ID.
-	if err := r.state.DB.DeleteAllListEntriesByFollows(ctx, deleted.ID); err != nil {
-		return gtserror.Newf("error deleting list entries: %w", err)
-	}
-
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteFollowByID(ctx context.Context, id string) error {
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.Follow
-	deleted.ID = id
-
-	// Delete follow with given ID,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("id"), id).
-		Returning("?, ?",
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with ID, manually
-	// call invalidate hook in case not cached.
-	r.state.Caches.DB.Follow.Invalidate("ID", id)
-	r.state.Caches.OnInvalidateFollow(&deleted)
-
-	// Delete every list entry that was created targetting this follow ID.
-	if err := r.state.DB.DeleteAllListEntriesByFollows(ctx, id); err != nil {
-		return gtserror.Newf("error deleting list entries: %w", err)
-	}
+	return r.deleteFollow(ctx, func(tx bun.Tx) (*gtsmodel.Follow, error) {
+		var deleted gtsmodel.Follow
+		deleted.ID = id
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("id"), id).
+			Returning("?, ?",
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteFollowByURI(ctx context.Context, uri string) error {
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.Follow
-
-	// Delete follow with given URI,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("uri"), uri).
-		Returning("?, ?, ?",
-			bun.Ident("id"),
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with URI, manually
-	// call invalidate hook in case not cached.
-	r.state.Caches.DB.Follow.Invalidate("URI", uri)
-	r.state.Caches.OnInvalidateFollow(&deleted)
-
-	// Delete every list entry that was created targetting this follow ID.
-	if err := r.state.DB.DeleteAllListEntriesByFollows(ctx, deleted.ID); err != nil {
-		return gtserror.Newf("error deleting list entries: %w", err)
-	}
+	return r.deleteFollow(ctx, func(tx bun.Tx) (*gtsmodel.Follow, error) {
+		var deleted gtsmodel.Follow
+		deleted.URI = uri
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("uri"), uri).
+			Returning("?, ?, ?",
+				bun.Ident("id"),
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteAccountFollows(ctx context.Context, accountID string) error {
@@ -354,24 +314,52 @@ func (r *relationshipDB) DeleteAccountFollows(ctx context.Context, accountID str
 	// deleted for cache invaliation.
 	var deleted []*gtsmodel.Follow
 
-	// Delete all follows either from
-	// account, or targeting account,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		WhereOr("? = ? OR ? = ?",
-			bun.Ident("account_id"),
-			accountID,
-			bun.Ident("target_account_id"),
-			accountID,
-		).
-		Returning("?, ?, ?",
-			bun.Ident("id"),
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
+	if err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+		// Delete all follows either from
+		// account, or targeting account,
+		// returning the deleted models.
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			WhereOr("? = ? OR ? = ?",
+				bun.Ident("account_id"),
+				accountID,
+				bun.Ident("target_account_id"),
+				accountID,
+			).
+			Returning("?, ?, ?",
+				bun.Ident("id"),
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+
+			// the RETURNING here will cause an ErrNoRows
+			// to be returned on DELETE, which is caught
+			// outside this RunInTx() func, and ensures we
+			// return early here to *not* update statistics.
+			return err
+		}
+
+		for _, follow := range deleted {
+			// Decrement origin following statistic.
+			if err := decrementAccountStats(ctx, tx,
+				"following_count",
+				follow.AccountID,
+			); err != nil {
+				return err
+			}
+
+			// Decrement target followers statistic.
+			if err := decrementAccountStats(ctx, tx,
+				"followers_count",
+				follow.TargetAccountID,
+			); err != nil {
+				return err
+			}
+		}
+
+		return nil
+	}); err != nil && !errors.Is(err, db.ErrNoEntries) {
 		return err
 	}
 
@@ -397,3 +385,77 @@ func (r *relationshipDB) DeleteAccountFollows(ctx context.Context, accountID str
 
 	return nil
 }
+
+func (r *relationshipDB) insertFollow(ctx context.Context, follow *gtsmodel.Follow, insert func(bun.Tx) error) error {
+	return r.state.Caches.DB.Follow.Store(follow, func() error {
+		return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			// Perform the insert operation.
+			if err := insert(tx); err != nil {
+				return gtserror.Newf("error inserting follow: %w", err)
+			}
+
+			// Increment origin following statistic.
+			if err := incrementAccountStats(ctx, tx,
+				"following_count",
+				follow.AccountID,
+			); err != nil {
+				return err
+			}
+
+			// Increment target followers statistic.
+			return incrementAccountStats(ctx, tx,
+				"followers_count",
+				follow.TargetAccountID,
+			)
+		})
+	})
+}
+
+func (r *relationshipDB) deleteFollow(ctx context.Context, delete func(bun.Tx) (*gtsmodel.Follow, error)) error {
+	var follow *gtsmodel.Follow
+
+	if err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) (err error) {
+		// Perform delete operation.
+		follow, err = delete(tx)
+		if err != nil {
+
+			// the RETURNING here will cause an ErrNoRows
+			// to be returned on DELETE, which is caught
+			// outside this RunInTx() func, and ensures we
+			// return early here to *not* update statistics.
+			return err
+		}
+
+		// Decrement origin following statistic.
+		if err := decrementAccountStats(ctx, tx,
+			"following_count",
+			follow.AccountID,
+		); err != nil {
+			return err
+		}
+
+		// Decrement target followers statistic.
+		return decrementAccountStats(ctx, tx,
+			"followers_count",
+			follow.TargetAccountID,
+		)
+	}); err != nil && !errors.Is(err, db.ErrNoEntries) {
+		return err
+	}
+
+	if follow == nil {
+		return nil
+	}
+
+	// Invalidate cached follow with ID, manually
+	// call invalidate hook in case not cached.
+	r.state.Caches.DB.Follow.Invalidate("ID", follow.ID)
+	r.state.Caches.OnInvalidateFollow(follow)
+
+	// Delete every list entry that was created targetting this follow ID.
+	if err := r.state.DB.DeleteAllListEntriesByFollows(ctx, follow.ID); err != nil {
+		return gtserror.Newf("error deleting list entries: %w", err)
+	}
+
+	return nil
+}
diff --git a/internal/db/bundb/relationship_follow_req.go b/internal/db/bundb/relationship_follow_req.go
index 513f5abb0..6a9fbea09 100644
--- a/internal/db/bundb/relationship_follow_req.go
+++ b/internal/db/bundb/relationship_follow_req.go
@@ -167,7 +167,7 @@ func (r *relationshipDB) getFollowRequest(ctx context.Context, lookup string, db
 func (r *relationshipDB) PopulateFollowRequest(ctx context.Context, follow *gtsmodel.FollowRequest) error {
 	var (
 		err  error
-		errs = gtserror.NewMultiError(2)
+		errs gtserror.MultiError
 	)
 
 	if follow.Account == nil {
@@ -196,8 +196,10 @@ func (r *relationshipDB) PopulateFollowRequest(ctx context.Context, follow *gtsm
 }
 
 func (r *relationshipDB) PutFollowRequest(ctx context.Context, follow *gtsmodel.FollowRequest) error {
-	return r.state.Caches.DB.FollowRequest.Store(follow, func() error {
-		_, err := r.db.NewInsert().Model(follow).Exec(ctx)
+	return r.insertFollowRequest(ctx, follow, func(tx bun.Tx) error {
+		_, err := tx.NewInsert().
+			Model(follow).
+			Exec(ctx)
 		return err
 	})
 }
@@ -208,22 +210,17 @@ func (r *relationshipDB) UpdateFollowRequest(ctx context.Context, followRequest
 		// If we're updating by column, ensure "updated_at" is included.
 		columns = append(columns, "updated_at")
 	}
-
 	return r.state.Caches.DB.FollowRequest.Store(followRequest, func() error {
-		if _, err := r.db.NewUpdate().
+		_, err := r.db.NewUpdate().
 			Model(followRequest).
 			Where("? = ?", bun.Ident("follow_request.id"), followRequest.ID).
 			Column(columns...).
-			Exec(ctx); err != nil {
-			return err
-		}
-
-		return nil
+			Exec(ctx)
+		return err
 	})
 }
 
 func (r *relationshipDB) AcceptFollowRequest(ctx context.Context, sourceAccountID string, targetAccountID string) (*gtsmodel.Follow, error) {
-	// Get original follow request.
 	followReq, err := r.GetFollowRequest(ctx, sourceAccountID, targetAccountID)
 	if err != nil {
 		return nil, err
@@ -242,11 +239,9 @@ func (r *relationshipDB) AcceptFollowRequest(ctx context.Context, sourceAccountI
 		Notify:          followReq.Notify,
 	}
 
-	if err := r.state.Caches.DB.Follow.Store(follow, func() error {
-		// If the follow already exists, just
-		// replace the URI with the new one.
-		_, err := r.db.
-			NewInsert().
+	// Insert the new follow modelled after request into database.
+	if err := r.insertFollow(ctx, follow, func(tx bun.Tx) error {
+		_, err := tx.NewInsert().
 			Model(follow).
 			On("CONFLICT (?,?) DO UPDATE set ? = ?", bun.Ident("account_id"), bun.Ident("target_account_id"), bun.Ident("uri"), follow.URI).
 			Exec(ctx)
@@ -255,12 +250,8 @@ func (r *relationshipDB) AcceptFollowRequest(ctx context.Context, sourceAccountI
 		return nil, err
 	}
 
-	// Delete original follow request.
-	if _, err := r.db.
-		NewDelete().
-		Table("follow_requests").
-		Where("? = ?", bun.Ident("id"), followReq.ID).
-		Exec(ctx); err != nil {
+	// Delete the follow request now that it's accepted and not needed.
+	if err := r.DeleteFollowRequestByID(ctx, followReq.ID); err != nil {
 		return nil, err
 	}
 
@@ -275,12 +266,9 @@ func (r *relationshipDB) AcceptFollowRequest(ctx context.Context, sourceAccountI
 }
 
 func (r *relationshipDB) RejectFollowRequest(ctx context.Context, sourceAccountID string, targetAccountID string) error {
-	// Delete follow request first.
 	if err := r.DeleteFollowRequest(ctx, sourceAccountID, targetAccountID); err != nil {
 		return err
 	}
-
-	// Delete follow request notification
 	return r.state.DB.DeleteNotifications(ctx, []gtsmodel.NotificationType{
 		gtsmodel.NotificationFollowRequest,
 	}, targetAccountID, sourceAccountID)
@@ -291,89 +279,63 @@ func (r *relationshipDB) DeleteFollowRequest(
 	sourceAccountID string,
 	targetAccountID string,
 ) error {
+	return r.deleteFollowRequest(ctx, func(tx bun.Tx) (*gtsmodel.FollowRequest, error) {
+		var deleted gtsmodel.FollowRequest
+		deleted.AccountID = sourceAccountID
+		deleted.TargetAccountID = targetAccountID
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("account_id"), sourceAccountID).
+			Where("? = ?", bun.Ident("target_account_id"), targetAccountID).
+			Returning("?", bun.Ident("id")).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.FollowRequest
-	deleted.AccountID = sourceAccountID
-	deleted.TargetAccountID = targetAccountID
-
-	// Delete all follow reqs either
-	// from account, or targeting account,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("account_id"), sourceAccountID).
-		Where("? = ?", bun.Ident("target_account_id"), targetAccountID).
-		Returning("?", bun.Ident("id")).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with source / target account IDs,
-	// manually calling invalidate hook in case it isn't cached.
-	r.state.Caches.DB.FollowRequest.Invalidate("AccountID,TargetAccountID",
-		sourceAccountID, targetAccountID)
-	r.state.Caches.OnInvalidateFollowRequest(&deleted)
-
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteFollowRequestByID(ctx context.Context, id string) error {
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.FollowRequest
-	deleted.ID = id
-
-	// Delete follow with given URI,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("id"), id).
-		Returning("?, ?",
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with URI, manually
-	// call invalidate hook in case not cached.
-	r.state.Caches.DB.FollowRequest.Invalidate("ID", id)
-	r.state.Caches.OnInvalidateFollowRequest(&deleted)
+	return r.deleteFollowRequest(ctx, func(tx bun.Tx) (*gtsmodel.FollowRequest, error) {
+		var deleted gtsmodel.FollowRequest
+		deleted.ID = id
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("id"), id).
+			Returning("?, ?",
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteFollowRequestByURI(ctx context.Context, uri string) error {
-	// Gather necessary fields from
-	// deleted for cache invaliation.
-	var deleted gtsmodel.FollowRequest
-
-	// Delete follow with given URI,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		Where("? = ?", bun.Ident("uri"), uri).
-		Returning("?, ?, ?",
-			bun.Ident("id"),
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
-		return err
-	}
-
-	// Invalidate cached follow with URI, manually
-	// call invalidate hook in case not cached.
-	r.state.Caches.DB.FollowRequest.Invalidate("URI", uri)
-	r.state.Caches.OnInvalidateFollowRequest(&deleted)
+	return r.deleteFollowRequest(ctx, func(tx bun.Tx) (*gtsmodel.FollowRequest, error) {
+		var deleted gtsmodel.FollowRequest
+		deleted.URI = uri
+
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("uri"), uri).
+			Returning("?, ?, ?",
+				bun.Ident("id"),
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+			return nil, err
+		}
 
-	return nil
+		return &deleted, nil
+	})
 }
 
 func (r *relationshipDB) DeleteAccountFollowRequests(ctx context.Context, accountID string) error {
@@ -381,24 +343,44 @@ func (r *relationshipDB) DeleteAccountFollowRequests(ctx context.Context, accoun
 	// deleted for cache invaliation.
 	var deleted []*gtsmodel.FollowRequest
 
-	// Delete all follows either from
-	// account, or targeting account,
-	// returning the deleted models.
-	if _, err := r.db.NewDelete().
-		Model(&deleted).
-		WhereOr("? = ? OR ? = ?",
-			bun.Ident("account_id"),
-			accountID,
-			bun.Ident("target_account_id"),
-			accountID,
-		).
-		Returning("?, ?, ?",
-			bun.Ident("id"),
-			bun.Ident("account_id"),
-			bun.Ident("target_account_id"),
-		).
-		Exec(ctx); err != nil &&
-		!errors.Is(err, db.ErrNoEntries) {
+	if err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+		// Delete all follows either from
+		// account, or targeting account,
+		// returning the deleted models.
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			WhereOr("? = ? OR ? = ?",
+				bun.Ident("account_id"),
+				accountID,
+				bun.Ident("target_account_id"),
+				accountID,
+			).
+			Returning("?, ?, ?",
+				bun.Ident("id"),
+				bun.Ident("account_id"),
+				bun.Ident("target_account_id"),
+			).
+			Exec(ctx); err != nil {
+
+			// the RETURNING here will cause an ErrNoRows
+			// to be returned on DELETE, which is caught
+			// outside this RunInTx() func, and ensures we
+			// return early here to *not* update statistics.
+			return err
+		}
+
+		for _, follow := range deleted {
+			// Decrement target follow requests count.
+			if err := decrementAccountStats(ctx, tx,
+				"follow_requests_count",
+				follow.TargetAccountID,
+			); err != nil {
+				return err
+			}
+		}
+
+		return nil
+	}); err != nil && !errors.Is(err, db.ErrNoEntries) {
 		return err
 	}
 
@@ -414,3 +396,56 @@ func (r *relationshipDB) DeleteAccountFollowRequests(ctx context.Context, accoun
 
 	return nil
 }
+
+func (r *relationshipDB) insertFollowRequest(ctx context.Context, follow *gtsmodel.FollowRequest, insert func(bun.Tx) error) error {
+	return r.state.Caches.DB.FollowRequest.Store(follow, func() error {
+		return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			// Perform the insert operation.
+			if err := insert(tx); err != nil {
+				return gtserror.Newf("error inserting follow request: %w", err)
+			}
+
+			// Increment target follow requests count.
+			return incrementAccountStats(ctx, tx,
+				"follow_requests_count",
+				follow.TargetAccountID,
+			)
+		})
+	})
+}
+
+func (r *relationshipDB) deleteFollowRequest(ctx context.Context, delete func(bun.Tx) (*gtsmodel.FollowRequest, error)) error {
+	var follow *gtsmodel.FollowRequest
+
+	if err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) (err error) {
+		// Perform delete operation.
+		follow, err = delete(tx)
+		if err != nil {
+
+			// the RETURNING here will cause an ErrNoRows
+			// to be returned on DELETE, which is caught
+			// outside this RunInTx() func, and ensures we
+			// return early here to *not* update statistics.
+			return err
+		}
+
+		// Decrement target follow requests count.
+		return decrementAccountStats(ctx, tx,
+			"follow_requests_count",
+			follow.TargetAccountID,
+		)
+	}); err != nil && !errors.Is(err, db.ErrNoEntries) {
+		return err
+	}
+
+	if follow == nil {
+		return nil
+	}
+
+	// Invalidate cached follow with ID, manually
+	// call invalidate hook in case not cached.
+	r.state.Caches.DB.FollowRequest.Invalidate("ID", follow.ID)
+	r.state.Caches.OnInvalidateFollowRequest(follow)
+
+	return nil
+}
diff --git a/internal/db/bundb/relationship_test.go b/internal/db/bundb/relationship_test.go
index a8b456286..c1336aa50 100644
--- a/internal/db/bundb/relationship_test.go
+++ b/internal/db/bundb/relationship_test.go
@@ -194,7 +194,7 @@ func (suite *RelationshipTestSuite) TestGetFollowBy() {
 
 			// Attempt to place the follow in database (if not already).
 			if err := suite.db.PutFollow(ctx, follow); err != nil {
-				if err != db.ErrAlreadyExists {
+				if !errors.Is(err, db.ErrAlreadyExists) {
 					// Unrecoverable database error.
 					t.Fatalf("error creating follow: %v", err)
 				}
@@ -306,7 +306,7 @@ func (suite *RelationshipTestSuite) TestGetFollowRequestBy() {
 
 			// Attempt to place the follow in database (if not already).
 			if err := suite.db.PutFollowRequest(ctx, followReq); err != nil {
-				if err != db.ErrAlreadyExists {
+				if !errors.Is(err, db.ErrAlreadyExists) {
 					// Unrecoverable database error.
 					t.Fatalf("error creating follow request: %v", err)
 				}
diff --git a/internal/db/bundb/status.go b/internal/db/bundb/status.go
index 5b72f5fbe..3dbd5f671 100644
--- a/internal/db/bundb/status.go
+++ b/internal/db/bundb/status.go
@@ -579,10 +579,21 @@ func insertStatus(ctx context.Context, tx bun.Tx, status *gtsmodel.Status) error
 		return gtserror.Newf("error inserting status: %w", err)
 	}
 
-	return nil
+	// Increment status author statistics.
+	return incrementAccountStats(ctx, tx,
+		"statuses_count",
+		status.AccountID,
+	)
 }
 
 func (s *statusDB) UpdateStatus(ctx context.Context, status *gtsmodel.Status, columns ...string) error {
+	var isPinning bool
+	for _, col := range columns {
+		if col == "pinned_at" {
+			isPinning = true
+			break
+		}
+	}
 	return s.state.Caches.DB.Status.Store(status, func() error {
 		// It is safe to run this database transaction within cache.Store
 		// as the cache does not attempt a mutex lock until AFTER hook.
@@ -634,12 +645,33 @@ func (s *statusDB) UpdateStatus(ctx context.Context, status *gtsmodel.Status, co
 			}
 
 			// Finally, update the status
-			_, err := tx.NewUpdate().
+			if _, err := tx.NewUpdate().
 				Model(status).
 				Column(columns...).
 				Where("? = ?", bun.Ident("status.id"), status.ID).
-				Exec(ctx)
-			return err
+				Exec(ctx); err != nil {
+				return err
+			}
+
+			switch {
+			case !isPinning:
+				// nothing
+				return nil
+
+			case !status.PinnedAt.IsZero():
+				// Increment author pinned statistics.
+				return decrementAccountStats(ctx, tx,
+					"statuses_pinned_count",
+					status.AccountID,
+				)
+
+			default:
+				// Decrement author pinned statistics.
+				return decrementAccountStats(ctx, tx,
+					"statuses_pinned_count",
+					status.AccountID,
+				)
+			}
 		})
 	})
 }
@@ -653,10 +685,30 @@ func (s *statusDB) DeleteStatusByID(ctx context.Context, id string) error {
 	// Delete status from database and any related links in a transaction.
 	if err := s.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
 
+		// delete the status itself
+		if _, err := tx.NewDelete().
+			Model(&deleted).
+			Where("? = ?", bun.Ident("id"), id).
+			Returning("?, ?, ?, ?, ?, ?",
+				bun.Ident("account_id"),
+				bun.Ident("boost_of_id"),
+				bun.Ident("in_reply_to_id"),
+				bun.Ident("attachments"),
+				bun.Ident("poll_id"),
+				bun.Ident("pinned_at"),
+			).
+			Exec(ctx); err != nil {
+
+			// the RETURNING here will cause an ErrNoRows
+			// to be returned on DELETE, which is caught
+			// outside this RunInTx() func, and ensures we
+			// return early here to *not* update statistics.
+			return err
+		}
+
 		// delete links between this
 		// status and any emojis it uses
-		if _, err := tx.
-			NewDelete().
+		if _, err := tx.NewDelete().
 			TableExpr("? AS ?", bun.Ident("status_to_emojis"), bun.Ident("status_to_emoji")).
 			Where("? = ?", bun.Ident("status_to_emoji.status_id"), id).
 			Exec(ctx); err != nil {
@@ -665,33 +717,33 @@ func (s *statusDB) DeleteStatusByID(ctx context.Context, id string) error {
 
 		// delete links between this
 		// status and any tags it uses
-		if _, err := tx.
-			NewDelete().
+		if _, err := tx.NewDelete().
 			TableExpr("? AS ?", bun.Ident("status_to_tags"), bun.Ident("status_to_tag")).
 			Where("? = ?", bun.Ident("status_to_tag.status_id"), id).
 			Exec(ctx); err != nil {
 			return err
 		}
 
-		// delete the status itself
-		if _, err := tx.
-			NewDelete().
-			Model(&deleted).
-			Where("? = ?", bun.Ident("id"), id).
-			Returning("?, ?, ?, ?, ?",
-				bun.Ident("account_id"),
-				bun.Ident("boost_of_id"),
-				bun.Ident("in_reply_to_id"),
-				bun.Ident("attachments"),
-				bun.Ident("poll_id"),
-			).
-			Exec(ctx); err != nil &&
-			!errors.Is(err, db.ErrNoEntries) {
+		// decrement status author statistics.
+		if err := decrementAccountStats(ctx, tx,
+			"statuses_count",
+			deleted.AccountID,
+		); err != nil {
 			return err
 		}
 
+		if !deleted.PinnedAt.IsZero() {
+			// decrement author pinned statistics.
+			if err := decrementAccountStats(ctx, tx,
+				"statuses_pinned_count",
+				deleted.AccountID,
+			); err != nil {
+				return err
+			}
+		}
+
 		return nil
-	}); err != nil {
+	}); err != nil && !errors.Is(err, db.ErrNoEntries) {
 		return err
 	}
 
@@ -788,7 +840,8 @@ func (s *statusDB) getStatusReplyIDs(ctx context.Context, statusID string) ([]st
 	return s.state.Caches.DB.InReplyToIDs.Load(statusID, func() ([]string, error) {
 		var statusIDs []string
 
-		// Status reply IDs not in cache, perform DB query!
+		// Status reply IDs not in
+		// cache, perform DB query!
 		if err := s.db.
 			NewSelect().
 			Table("statuses").
@@ -832,7 +885,8 @@ func (s *statusDB) getStatusBoostIDs(ctx context.Context, statusID string) ([]st
 	return s.state.Caches.DB.BoostOfIDs.Load(statusID, func() ([]string, error) {
 		var statusIDs []string
 
-		// Status boost IDs not in cache, perform DB query!
+		// Status boost IDs not in
+		// cache, perform DB query!
 		if err := s.db.
 			NewSelect().
 			Table("statuses").
@@ -966,10 +1020,7 @@ func (s *statusDB) GetStatusInteractions(
 	return interactions, nil
 }
 
-func (s *statusDB) GetStatusByEditID(
-	ctx context.Context,
-	editID string,
-) (*gtsmodel.Status, error) {
+func (s *statusDB) GetStatusByEditID(ctx context.Context, editID string) (*gtsmodel.Status, error) {
 	edit, err := s.state.DB.GetStatusEditByID(
 		gtscontext.SetBarebones(ctx),
 		editID,
@@ -977,6 +1028,5 @@ func (s *statusDB) GetStatusByEditID(
 	if err != nil {
 		return nil, err
 	}
-
 	return s.GetStatusByID(ctx, edit.StatusID)
 }
diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go
index 39849ba73..ac58dd6f4 100644
--- a/internal/db/bundb/util.go
+++ b/internal/db/bundb/util.go
@@ -25,6 +25,8 @@ import (
 
 	"code.superseriousbusiness.org/gotosocial/internal/cache"
 	"code.superseriousbusiness.org/gotosocial/internal/db"
+	"code.superseriousbusiness.org/gotosocial/internal/gtserror"
+	"code.superseriousbusiness.org/gotosocial/internal/gtsmodel"
 	"code.superseriousbusiness.org/gotosocial/internal/log"
 	"code.superseriousbusiness.org/gotosocial/internal/paging"
 	"github.com/uptrace/bun"
@@ -151,6 +153,7 @@ func notExists(ctx context.Context, query *bun.SelectQuery) (bool, error) {
 // loadPagedIDs loads a page of IDs from given SliceCache by `key`, resorting to `loadDESC` if required. Uses `page` to sort + page resulting IDs.
 // NOTE: IDs returned from `cache` / `loadDESC` MUST be in descending order, otherwise paging will not work correctly / return things out of order.
 func loadPagedIDs(cache *cache.SliceCache[string], key string, page *paging.Page, loadDESC func() ([]string, error)) ([]string, error) {
+
 	// Check cache for IDs, else load.
 	ids, err := cache.Load(key, loadDESC)
 	if err != nil {
@@ -171,6 +174,30 @@ func loadPagedIDs(cache *cache.SliceCache[string], key string, page *paging.Page
 	return ids, nil
 }
 
+// incrementAccountStats will increment the given column in the `account_stats` table matching `account_id`.
+func incrementAccountStats(ctx context.Context, tx bun.Tx, col bun.Ident, accountID string) error {
+	if _, err := tx.NewUpdate().
+		Model((*gtsmodel.AccountStats)(nil)).
+		Where("? = ?", bun.Ident("account_id"), accountID).
+		Set("? = (? + 1)", col, col).
+		Exec(ctx); err != nil {
+		return gtserror.Newf("error updating %s: %w", col, err)
+	}
+	return nil
+}
+
+// decrementAccountStats will decrement the given column in the `account_stats` table matching `account_id`.
+func decrementAccountStats(ctx context.Context, tx bun.Tx, col bun.Ident, accountID string) error {
+	if _, err := tx.NewUpdate().
+		Model((*gtsmodel.AccountStats)(nil)).
+		Where("? = ?", bun.Ident("account_id"), accountID).
+		Set("? = (? - 1)", col, col).
+		Exec(ctx); err != nil {
+		return gtserror.Newf("error updating %s: %w", col, err)
+	}
+	return nil
+}
+
 // updateWhere parses []db.Where and adds it to the given update query.
 func updateWhere(q *bun.UpdateQuery, where []db.Where) {
 	for _, w := range where {