Skip to content

Keep track of projects a user contributed to

Description

Finding projects that are visible to a particular user is quite expensive. In https://gitlab.com/gitlab-org/gitlab-ce/issues/40525#note_48635425, @yorickpeterse proposes to create a separate table that keeps track of projects a user contributed to. This can be used to considerably speed up queries that involve project visibility.

For related issues that would benefit from this see

We're going to tackle this in two steps/releases:

  1. Create and populate a table user_contributed_projects to track projects a user contributed to. The goal is to get this in 10.6
  2. Leverage the above table to speed up queries that involve project visibility. This will be done in 10.7 once the table has been populated and is kept in sync. Issue to be created.

Proposal

In this issue, we only tackle part 1:

  • Create table user_contributed_projects
  • Populate table in a migration
  • Keep track of contributions and maintain consistency of table

Schema

The schema of this table would be very simple and only contain two columns:

  1. The user ID
  2. The ID of the project

Both have a foreign key to the appropriate table with a cascading delete

Usage

Using this table would be very simple: everywhere we currently do WHERE projects.visibility_level IN (...) we INNER JOIN this new table onto projects. Using this approach the visibility_level filter should only apply to projects you actually contributed to. We may need to make further changes, but I think this should be enough.

Edited by Andreas Brandl