teams: do not load memberships in teams endpoint (!566) · Merge requests · iGEM HQ Tech / iGEM.org Backend

Fabio Maschi requested to merge feat/remove-memberships-relation into main Oct 16, 2024

Team endpoint (GET /teams/:teamID) is currently the most public-facing expensive endpoint we have in the BE. It loads lots of relations (joins), and the resulted SQL query is huge:

SELECT "Team"."id" AS "Team_id", "Team"."groupID" AS "Team_groupID", "Team"."name" AS "Team_name", "Team"."slug" AS "Team_slug", "Team"."region" AS "Team_region", "Team"."country" AS "Team_country", "Team"."institutions" AS "Team_institutions", "Team"."track" AS "Team_track", "Team"."kind" AS "Team_kind", "Team"."section" AS "Team_section", "Team"."status" AS "Team_status", "Team"."wikiURL" AS "Team_wikiURL", "Team"."softwareURL" AS "Team_softwareURL", "Team"."comment" AS "Team_comment", "Team"."isRemote" AS "Team_isRemote", "Team"."competitionUUID" AS "Team_competitionUUID", "Team"."auditCreated" AS "Team_auditCreated", "Team"."auditUpdated" AS "Team_auditUpdated", "Team__Team_competition"."uuid" AS "Team__Team_competition_uuid", "Team__Team_competition"."year" AS "Team__Team_competition_year", "Team__Team_competition"."type" AS "Team__Team_competition_type", "Team__Team_competition"."wikiSlug" AS "Team__Team_competition_wikiSlug", "Team__Team_competition"."website" AS "Team__Team_competition_website", "Team__Team_competition"."status" AS "Team__Team_competition_status", "Team__Team_competition"."auditCreated" AS "Team__Team_competition_auditCreated", "Team__Team_competition"."auditUpdated" AS "Team__Team_competition_auditUpdated", "Team__Team_sessions"."uuid" AS "Team__Team_sessions_uuid", "Team__Team_sessions"."title" AS "Team__Team_sessions_title", "Team__Team_sessions"."description" AS "Team__Team_sessions_description", "Team__Team_sessions"."type" AS "Team__Team_sessions_type", "Team__Team_sessions"."status" AS "Team__Team_sessions_status", "Team__Team_sessions"."topics" AS "Team__Team_sessions_topics", "Team__Team_sessions"."locationUUID" AS "Team__Team_sessions_locationUUID", "Team__Team_sessions"."teamID" AS "Team__Team_sessions_teamID", "Team__Team_sessions"."startTime" AS "Team__Team_sessions_startTime", "Team__Team_sessions"."endTime" AS "Team__Team_sessions_endTime", "Team__Team_sessions"."photoURL" AS "Team__Team_sessions_photoURL", "Team__Team_sessions"."metadata" AS "Team__Team_sessions_metadata", "Team__Team_sessions"."zoomMeetingID" AS "Team__Team_sessions_zoomMeetingID", "Team__Team_sessions"."eventUUID" AS "Team__Team_sessions_eventUUID", "Team__Team_sessions"."videoUUID" AS "Team__Team_sessions_videoUUID", "Team__Team_sessions"."auditCreated" AS "Team__Team_sessions_auditCreated", "Team__Team_sessions"."auditUpdated" AS "Team__Team_sessions_auditUpdated", "Team__Team_sessions__Team__Team_sessions_video"."peertubeUUID" AS "Team__Team_sessions__Team__Team_sessions_video_peertubeUUID", "Team__Team_sessions__Team__Team_sessions_video"."duration" AS "Team__Team_sessions__Team__Team_sessions_video_duration", "Team__Team_sessions__Team__Team_sessions_video"."thumbnailPath" AS "Team__Team_sessions__Team__Team_sessions_video_thumbnailPath", "Team__Team_requirements"."teamID" AS "Team__Team_requirements_teamID", "Team__Team_requirements"."type" AS "Team__Team_requirements_type", "Team__Team_requirements"."isAccepted" AS "Team__Team_requirements_isAccepted", "Team__Team_requirements"."auditCreated" AS "Team__Team_requirements_auditCreated", "Team__Team_requirements"."auditUpdated" AS "Team__Team_requirements_auditUpdated", "Team__Team_artifacts"."uuid" AS "Team__Team_artifacts_uuid", "Team__Team_artifacts"."type" AS "Team__Team_artifacts_type", "Team__Team_artifacts"."teamID" AS "Team__Team_artifacts_teamID", "Team__Team_artifacts"."language" AS "Team__Team_artifacts_language", "Team__Team_artifacts"."videoUUID" AS "Team__Team_artifacts_videoUUID", "Team__Team_artifacts"."metadata" AS "Team__Team_artifacts_metadata", "Team__Team_artifacts"."auditCreated" AS "Team__Team_artifacts_auditCreated", "Team__Team_artifacts"."auditUpdated" AS "Team__Team_artifacts_auditUpdated", "Team__Team_deliverables"."teamID" AS "Team__Team_deliverables_teamID", "Team__Team_deliverables"."deliverableUUID" AS "Team__Team_deliverables_deliverableUUID", "Team__Team_deliverables"."artifactUUID" AS "Team__Team_deliverables_artifactUUID", "Team__Team_deliverables"."formUUID" AS "Team__Team_deliverables_formUUID", "Team__Team_deliverables"."sessionUUID" AS "Team__Team_deliverables_sessionUUID", "Team__Team_deliverables"."submitted" AS "Team__Team_deliverables_submitted", "Team__Team_deliverables"."auditCreated" AS "Team__Team_deliverables_auditCreated", "Team__Team_deliverables"."auditUpdated" AS "Team__Team_deliverables_auditUpdated", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."uuid" AS "6f965dc48a58fa04e53cebb1f4121e6fcfa3a022", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."type" AS "b323d46f6d7c483a8f31412044abb8cf3b2d4be2", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."group" AS "5468241fe6a72a3ce3f761b9df5e8388290b012a", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."displayOrder" AS "644cdd296b18ce622e8554eeb737ea8883b82adc", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."title" AS "c761f6e060f0466424443461a2c8a14294b28986", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."icon" AS "17982d5e1e5d2d93da87335e3dc4f5155f20ea98", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."completeStatus" AS "76f93dd934e326e69919690fd2c0c916f580d8db", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."auditCreated" AS "7b589234987640a6a73ed6945ebdb0f4a054236c", "Team__Team_deliverables__Team__Team_deliverables_deliverable"."auditUpdated" AS "c1532f4acc22426c17a30f06e53e7218c9a402f6", "Team__Team_deliverables__Team__Team_deliverables_artifact"."uuid" AS "Team__Team_deliverables__Team__Team_deliverables_artifact_uuid", "Team__Team_deliverables__Team__Team_deliverables_artifact"."type" AS "Team__Team_deliverables__Team__Team_deliverables_artifact_type", "Team__Team_deliverables__Team__Team_deliverables_artifact"."teamID" AS "457eab387fd5d7d64a27b936f178cc0089d30fd5", "Team__Team_deliverables__Team__Team_deliverables_artifact"."language" AS "23c5158fe8d03a33eb335194bebe28427770c782", "Team__Team_deliverables__Team__Team_deliverables_artifact"."videoUUID" AS "23766052a3f0222406cedcfaf2d2fd6dda7bd231", "Team__Team_deliverables__Team__Team_deliverables_artifact"."metadata" AS "78474989135c8d19bd30d7c5b71d85100c4532e3", "Team__Team_deliverables__Team__Team_deliverables_artifact"."auditCreated" AS "ebd24a8df48c257c79bad32a7c5d6dae7d7c45ef", "Team__Team_deliverables__Team__Team_deliverables_artifact"."auditUpdated" AS "c099566016e3a0c07d741c58e696f2cb01d80fbc", "Team__Team_deliverables__Team__Team_deliverables_form"."uuid" AS "Team__Team_deliverables__Team__Team_deliverables_form_uuid", "Team__Team_deliverables__Team__Team_deliverables_form"."type" AS "Team__Team_deliverables__Team__Team_deliverables_form_type", "Team__Team_deliverables__Team__Team_deliverables_form"."status" AS "Team__Team_deliverables__Team__Team_deliverables_form_status", "Team__Team_deliverables__Team__Team_deliverables_form"."teamID" AS "Team__Team_deliverables__Team__Team_deliverables_form_teamID", "Team__Team_deliverables__Team__Team_deliverables_form"."userID" AS "Team__Team_deliverables__Team__Team_deliverables_form_userID", "Team__Team_deliverables__Team__Team_deliverables_form"."competitionUUID" AS "fafaae1779c295de38bc4736e6e047d0e5507e17", "Team__Team_deliverables__Team__Team_deliverables_form"."created" AS "Team__Team_deliverables__Team__Team_deliverables_form_created", "Team__Team_group"."id" AS "Team__Team_group_id", "Team__Team_group"."type" AS "Team__Team_group_type", "Team__Team_group__Team__Team_group_memberships"."uuid" AS "Team__Team_group__Team__Team_group_memberships_uuid", "Team__Team_group__Team__Team_group_memberships"."role" AS "Team__Team_group__Team__Team_group_memberships_role", "Team__Team_group__Team__Team_group_memberships"."status" AS "Team__Team_group__Team__Team_group_memberships_status", "Team__Team_group__Team__Team_group_memberships"."memberUUID" AS "Team__Team_group__Team__Team_group_memberships_memberUUID", "Team__Team_group__Team__Team_group_memberships"."groupID" AS "Team__Team_group__Team__Team_group_memberships_groupID", "Team__Team_group__Team__Team_group_memberships"."auditCreated" AS "Team__Team_group__Team__Team_group_memberships_auditCreated", "Team__Team_group__Team__Team_group_memberships"."auditUpdated" AS "Team__Team_group__Team__Team_group_memberships_auditUpdated", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."uuid" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_uuid", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."username" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_username", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."systemRole" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_systemRole", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."status" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_status", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."auditCreated" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_auditCreated", "00307b399a1d67142208eca0db7a5c1ff0427d3b"."auditUpdated" AS "00307b399a1d67142208eca0db7a5c1ff0427d3b_auditUpdated", "232da196428cba8856d6dc1c65826e456705b92a"."id" AS "232da196428cba8856d6dc1c65826e456705b92a_id", "232da196428cba8856d6dc1c65826e456705b92a"."accountUUID" AS "232da196428cba8856d6dc1c65826e456705b92a_accountUUID", "232da196428cba8856d6dc1c65826e456705b92a"."firstName" AS "232da196428cba8856d6dc1c65826e456705b92a_firstName", "232da196428cba8856d6dc1c65826e456705b92a"."lastName" AS "232da196428cba8856d6dc1c65826e456705b92a_lastName", "232da196428cba8856d6dc1c65826e456705b92a"."publicName" AS "232da196428cba8856d6dc1c65826e456705b92a_publicName", "232da196428cba8856d6dc1c65826e456705b92a"."birthdate" AS "232da196428cba8856d6dc1c65826e456705b92a_birthdate", "232da196428cba8856d6dc1c65826e456705b92a"."gender" AS "232da196428cba8856d6dc1c65826e456705b92a_gender", "232da196428cba8856d6dc1c65826e456705b92a"."affiliation" AS "232da196428cba8856d6dc1c65826e456705b92a_affiliation", "232da196428cba8856d6dc1c65826e456705b92a"."country" AS "232da196428cba8856d6dc1c65826e456705b92a_country", "232da196428cba8856d6dc1c65826e456705b92a"."nationality" AS "232da196428cba8856d6dc1c65826e456705b92a_nationality", "232da196428cba8856d6dc1c65826e456705b92a"."photoURL" AS "232da196428cba8856d6dc1c65826e456705b92a_photoURL", "232da196428cba8856d6dc1c65826e456705b92a"."linkedInURL" AS "232da196428cba8856d6dc1c65826e456705b92a_linkedInURL", "232da196428cba8856d6dc1c65826e456705b92a"."institution" AS "232da196428cba8856d6dc1c65826e456705b92a_institution", "232da196428cba8856d6dc1c65826e456705b92a"."title" AS "232da196428cba8856d6dc1c65826e456705b92a_title", "232da196428cba8856d6dc1c65826e456705b92a"."languages" AS "232da196428cba8856d6dc1c65826e456705b92a_languages", "232da196428cba8856d6dc1c65826e456705b92a"."interests" AS "232da196428cba8856d6dc1c65826e456705b92a_interests", "Team__Team_sessions__Team__Team_sessions_location"."uuid" AS "Team__Team_sessions__Team__Team_sessions_location_uuid", "Team__Team_sessions__Team__Team_sessions_location"."type" AS "Team__Team_sessions__Team__Team_sessions_location_type", "Team__Team_sessions__Team__Team_sessions_location"."displayOrder" AS "Team__Team_sessions__Team__Team_sessions_location_displayOrder", "Team__Team_sessions__Team__Team_sessions_location"."number" AS "Team__Team_sessions__Team__Team_sessions_location_number", "Team__Team_sessions__Team__Team_sessions_location"."title" AS "Team__Team_sessions__Team__Team_sessions_location_title", "Team__Team_sessions__Team__Team_sessions_location"."icon" AS "Team__Team_sessions__Team__Team_sessions_location_icon", "Team__Team_sessions__Team__Team_sessions_location"."liveStreamUUID" AS "c64d0ade8f6ecd7c303693d016f83e8fe54a454c", "Team__Team_sessions__Team__Team_sessions_location"."eventUUID" AS "Team__Team_sessions__Team__Team_sessions_location_eventUUID", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."formUUID" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_formUUID", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."versionUUID" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_versionUUID", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."value" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_value", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."authorUUID" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_authorUUID", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."authorName" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_authorName", "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."created" AS "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772_created" FROM "team" "Team" LEFT JOIN "competition" "Team__Team_competition" ON "Team__Team_competition"."uuid"="Team"."competitionUUID"  LEFT JOIN "session" "Team__Team_sessions" ON "Team__Team_sessions"."teamID"="Team"."id"  LEFT JOIN "video" "Team__Team_sessions__Team__Team_sessions_video" ON "Team__Team_sessions__Team__Team_sessions_video"."peertubeUUID"="Team__Team_sessions"."videoUUID"  LEFT JOIN "team_requirement" "Team__Team_requirements" ON "Team__Team_requirements"."teamID"="Team"."id"  LEFT JOIN "team_artifact" "Team__Team_artifacts" ON "Team__Team_artifacts"."teamID"="Team"."id"  LEFT JOIN "team_deliverable" "Team__Team_deliverables" ON "Team__Team_deliverables"."teamID"="Team"."id"  LEFT JOIN "deliverable" "Team__Team_deliverables__Team__Team_deliverables_deliverable" ON "Team__Team_deliverables__Team__Team_deliverables_deliverable"."uuid"="Team__Team_deliverables"."deliverableUUID"  LEFT JOIN "team_artifact" "Team__Team_deliverables__Team__Team_deliverables_artifact" ON "Team__Team_deliverables__Team__Team_deliverables_artifact"."uuid"="Team__Team_deliverables"."artifactUUID"  LEFT JOIN "form" "Team__Team_deliverables__Team__Team_deliverables_form" ON "Team__Team_deliverables__Team__Team_deliverables_form"."uuid"="Team__Team_deliverables"."formUUID"  LEFT JOIN "group" "Team__Team_group" ON "Team__Team_group"."id"="Team"."groupID"  LEFT JOIN "membership" "Team__Team_group__Team__Team_group_memberships" ON "Team__Team_group__Team__Team_group_memberships"."groupID"="Team__Team_group"."id"  LEFT JOIN "account" "00307b399a1d67142208eca0db7a5c1ff0427d3b" ON "00307b399a1d67142208eca0db7a5c1ff0427d3b"."uuid"="Team__Team_group__Team__Team_group_memberships"."memberUUID"  LEFT JOIN "user" "232da196428cba8856d6dc1c65826e456705b92a" ON "232da196428cba8856d6dc1c65826e456705b92a"."accountUUID"="00307b399a1d67142208eca0db7a5c1ff0427d3b"."uuid"  LEFT JOIN "session_location" "Team__Team_sessions__Team__Team_sessions_location" ON "Team__Team_sessions__Team__Team_sessions_location"."uuid"="Team__Team_sessions"."locationUUID"  LEFT JOIN "last_form_version" "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772" ON "bf1c8526e350ae04bc8ddbb559ca97d07ba9b772"."formUUID"="Team__Team_deliverables__Team__Team_deliverables_form"."uuid" WHERE ( (("Team"."id" = 5037)) ) AND ( "Team"."id" IN (5037) )

This monster returns 990 rows for team Example in dev!

From [16/Oct/2024:11:00:00] till [16/Oct/2024:11:30:14], we had 2402 customer requests to production. Out of them, only 44 were longer than 2s:

9942ms	/v1/teams/5081
8070ms	/v1/teams/5068
7645ms	/v1/teams/5044
6987ms	/v1/teams/5036
6930ms	/v1/teams/5304
6635ms	/v1/teams/5291
6215ms	/v1/teams/5437
6170ms	/v1/teams/5478
5982ms	/v1/teams/5301
5562ms	/v1/teams/5494
5452ms	/v1/teams/5376
5324ms	/v1/teams/5315
5198ms	/v1/teams/5238
5070ms	/v1/teams/5295
5052ms	/v1/teams/5280
4988ms	/v1/teams/5249
4943ms	/v1/teams/5159
4386ms	/v1/teams/5398
4330ms	/v1/teams/5187
4008ms	/v1/teams/5490
3973ms	/v1/teams/5358
3883ms	/v1/tickets/<dedacted>/document
3877ms	/v1/teams/5494
3869ms	/v1/teams/5157
3806ms	/v1/teams/hq
3796ms	/v1/teams/5247
3785ms	/v1/teams/5305
3723ms	/v1/teams/5396
3395ms	/v1/teams/5453
3327ms	/v1/tickets/<dedacted>/document
3258ms	/v1/teams/5187
3249ms	/v1/judging/ballots/<dedacted>/5358/votes
3095ms	/v1/teams/5314
3025ms	/v1/tickets/<dedacted>/document
2873ms	/v1/teams/5446
2823ms	/v1/events/9248ba4e-e9f2-43c2-87d5-a74702d6fac4/tickets/mine
2813ms	/v1/competitions/5198bd74-a6bc-482d-bf02-d0f7dc5f33c7/teams
2651ms	/v1/teams/hq
2418ms	/v1/teams/5294
2409ms	/v1/teams/5419
2363ms	/v1/events/12a18285-5c3e-45c6-8ada-e4e44aa3e31c/sessions/schedule
2265ms  /v1/teams/5280
2155ms  /v1/teams/memberships/mine
2067ms  /v1/teams/5171

During this period, we had 72 requests to GET /teams/:teamID endpoint, the remaining ones were cached and delivered in less than 50ms

We clearly need to improve teams endpoint

Edited Oct 16, 2024 by Fabio Maschi

teams: do not load memberships in teams endpoint

Merge request reports