Store mentioned users, groups, projects in DB using postgres array type
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
issue_user_mentions
when using array fields
Query plans for 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
[ ] Changelog entry[ ] Documentation created/updated or follow-up review issue created-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
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