Store mentioned users, groups, projects in DB using postgres array type (!19088) · Merge requests · GitLab.org / GitLab

Alexandru Croitor requested to merge 21800-mentioned-users-models-with-array-type into master Oct 24, 2019

What does this MR do?

This MR is one of several MRs to add mentioned users, groups and projects to DB.

This MR will be focusing on saving mentioned users, groups and projects to DB for notes, issues, merge requests, epics, snippets that are newly created or updated.

So when a mentionable is created or updated we would parse the content(note or description) and save mentions to DB so that we can further read the mentions from DB rather than re-parsing the content every time.

Why?

When issues, merge requests, epics, etc get a lot of comments(some of them have 15K comments) parsing every single comment to pull off mentions gets very expensive, so storing them in DB should help improve that. Mentions are used for various things, one of which is to build up the list of participants, although participants are not only users that are mentioned.

Out of participants further the notifications for a particular issue, merge request, epic, etc are being computed in combination with project, group, personal notification settings.

Storing mentioned users is also a step forward to get https://gitlab.com/gitlab-org/gitlab/issues/14972 which would perhaps make #14889 that much closer.

Useful References

Query plans for `issue_user_mentions` when using array fields

gitlabhq_development=# EXPLAIN ANALYZE INSERT INTO "issue_user_mentions" ("issue_id", "note_id", "mentioned_users_ids") 
VALUES (3937, 1378712, '{140,141,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,
212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,
242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,
271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,
300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,
330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,
360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,
390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,
419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435}');
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Insert on issue_user_mentions  (cost=0.00..0.01 rows=1 width=112) (actual time=0.061..0.061 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=112) (actual time=0.009..0.010 rows=1 loops=1)
 Planning Time: 0.028 ms
 Trigger for constraint fk_rails_57581fda73: time=0.605 calls=1
 Trigger for constraint fk_rails_3861d9fefa: time=0.266 calls=1
 Execution Time: 0.970 ms

Get all mentioned users for an issue, plan: https://explain.depesz.com/s/neU5

EXPLAIN ANALYZE SELECT "users".*
FROM "users"
WHERE "users"."id" IN (
		SELECT DISTINCT unnest(mentioned_users_ids)
		FROM "issue_user_mentions"
		WHERE "issue_user_mentions"."issue_id" = 3937
		);

Get users for all mentioned groups in an issue, plan: https://explain.depesz.com/s/Ke8d

# EXPLAIN ANALYZE SELECT DISTINCT "users".*
FROM "users"
INNER JOIN "members" ON "members"."user_id" = "users"."id"
	AND "members"."type" IN ('GroupMember')
	AND "members"."source_type" = 'Namespace'
	AND "members"."requested_at" IS NULL
WHERE "members"."source_id" IN (
		SELECT "namespaces"."id"
		FROM "namespaces"
		WHERE "namespaces"."type" IN ('Group')
			AND "namespaces"."id" IN (
				SELECT DISTINCT unnest(mentioned_groups_ids)
				FROM "issue_user_mentions"
				WHERE "issue_user_mentions"."issue_id" = 3937
				)
		);

Get users for all mentioned projects in an issue, plan: https://explain.depesz.com/s/YrC5

EXPLAIN ANALYZE SELECT DISTINCT "users".*
FROM "users"
INNER JOIN "members" ON "members"."user_id" = "users"."id"
	AND "members"."type" IN ('ProjectMember')
	AND "members"."source_type" = 'Project'
	AND "members"."requested_at" IS NULL
WHERE "members"."source_id" IN (
		SELECT "projects"."id"
		FROM "projects"
		WHERE "projects"."id" IN (
				SELECT DISTINCT unnest(mentioned_projects_ids)
				FROM "issue_user_mentions"
				WHERE "issue_user_mentions"."issue_id" = 3937
				)
		);

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

re #21800 (closed)

Edited May 19, 2022 by Coung Ngo

Store mentioned users, groups, projects in DB using postgres array type