Use separate columns for associations in "events" instead of polymorphic associations
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
I'm extracting this from https://gitlab.com/gitlab-org/gitlab-ce/issues/31806.
With https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/12463 most of the problems mentioned in https://gitlab.com/gitlab-org/gitlab-ce/issues/31806 will be taken care of. However, the following remains:
Use individual columns instead of the target_type and target_id columns, and add foreign keys + indexes for these columns:
- diff_note_id instead of
target_type = 'DiffNote' - discussion_note_id instead of
target_type = 'DiscussionNote' - issue_id instead of
target_type = 'Issue' - legacy_diff_note_id instead of
target_type = 'LegacyDiffNote' - milestone_id instead of
target_type = 'Milestone' - note_id instead of
target_type = 'Note' - merge_request_id instead of
target_type = 'MergeRequest'
Because the "events" table is large we should do this using a background migration. Because of the mentioned MR this can't be included until at least 10.1.
One downside of the above approach is that we need a great deal of extra indexes (probably at least 1 for every column). It's not clear to me yet if updating these indexes on an INSERT is more expensive than updating the current target_type and target_id indexes. A big benefit however is that we can set up foreign keys, preventing orphans (which I'm pretty sure exist right now).