Split feed and user activities #13521

New Issue

GiteaMirror · 2025-11-02T10:45:00-06:00

GiteaMirror commented

2025-11-02 10:45:00 -06:00

Originally created by @lunny on GitHub (Sep 23, 2024).

Problem

Currently, the heatmap, dashboard feed, and personal activities are shared with the same table action. This resulted in some pages becoming slow even if we optimized the database index many times.

Lifecycles

However the content of the 3 places is similar, But the requirements from the 3 places have different lifecycles.

For the dashboard feed, keeping the last several days may be enough. Looks at Github, only about 3 days.
For heatmap and personal activities, all the events should be kept and never deleted.

Resolution

So that we can create another 1 or 2 new tables to resolve the problem, the new table will store the user's activities only.
The possible table definition will be like below.

type UserActivity struct {
    ID int64
    UserID int64 `xorm:"index"`
    Action string // created, deleted, opened merged, reviewed, joined, pushed
    RepoID int64
    IssueID int64
    CommitIDs []string
    EventDate timestamp `xorm:"index"`
}

What's the difference between action table and the new table? action table have many duplicated, once you watch a repository, every event related to this repository will be copied to every watchers. But for the new table, an event will be record only once.

So that, for the current action table, maybe it can be changed like this. It will contain only maybe recent 7 days feeds.

type UserFeed struct {
    ID int64
    WatcherID int64 `xorm:"index"`
    ActivityID int64
    Created timestamp
}

Originally created by @lunny on GitHub (Sep 23, 2024). # Problem Currently, the heatmap, dashboard feed, and personal activities are shared with the same table `action`. This resulted in some pages becoming slow even if we optimized the database index many times. # Lifecycles However the content of the 3 places is similar, But the requirements from the 3 places have different lifecycles. - For the dashboard feed, keeping the last several days may be enough. Looks at Github, only about 3 days. - For heatmap and personal activities, all the events should be kept and never deleted. # Resolution So that we can create another 1 or 2 new tables to resolve the problem, the new table will store the user's activities only. The possible table definition will be like below. ```go type UserActivity struct { ID int64 UserID int64 `xorm:"index"` Action string // created, deleted, opened merged, reviewed, joined, pushed RepoID int64 IssueID int64 CommitIDs []string EventDate timestamp `xorm:"index"` } ``` What's the difference between `action` table and the new table? `action` table have many duplicated, once you watch a repository, every event related to this repository will be copied to every watchers. But for the new table, an event will be record only once. So that, for the current `action` table, maybe it can be changed like this. It will contain only maybe recent 7 days feeds. ```go type UserFeed struct { ID int64 WatcherID int64 `xorm:"index"` ActivityID int64 Created timestamp } ```

GiteaMirror added the type/proposal label 2025-11-02 10:45:00 -06:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#13521