Refactor lazy_user_notes and replace db index (!185362) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This MR introduces a new index to replace an existing index: index_notes_on_noteable_id_noteable_type_and_id. The new index will allow batching notes on work items that have an excessive amount of system notes.

The change is accompanied by an update to the lazy_user_notes preload method for work items to only preload user (non-system) notes. Without the new index, the batching did not work for 1 work item record with > 800_000 system notes. lazy_user_notes is used when indexing work item data to Elasticsearch, we also want to index user notes. The indexing of user notes is behind a disabled feature flag currently

I will link to the follow up issues to create this index and remove the other index.

References

Database

adding the index

Index size is about the same

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/37438/commands/114533

The query has been executed. Duration: 175.620 min

CREATE INDEX index_notes_on_noteable_type_noteable_id_system_id ON notes USING btree (noteable_type, noteable_id, system, id);

for 1 record with many notes

SELECT "notes"."id"
FROM "notes"
WHERE "notes"."noteable_id" IN
      (103082749)
  AND "notes"."noteable_type" = 'Issue'
  AND "notes"."system" IS FALSE
ORDER BY "notes"."id" ASC
LIMIT 1;

for many records with many notes

SELECT "notes"."id"
FROM "notes"
WHERE "notes"."noteable_id" IN
      (103082749, 103082743, 91238195, 145019549, 140128025, 140181159, 94281553, 127243215, 140266785, 92966588,
       126822552, 29187838,
       140006729, 140022862, 127721574, 140288607, 137016618, 108775126, 29066138, 157812883, 30580159, 154786262,
       154818155, 35330687, 32162816, 107210296, 140239541, 112698968, 93692643, 127604741, 136589247, 151667706,
       97714099, 135494718, 142049945, 145280191, 148027391, 80613167, 140246421, 146530824, 154236589, 136149374,
       102109365, 105162449, 54171828, 91126452, 91210537, 146867608, 92675558, 12441707, 140259533, 110046851)
  AND "notes"."noteable_type" = 'Issue'
  AND "notes"."system" IS FALSE
ORDER BY "notes"."id" ASC
LIMIT 1;

SELECT "notes"."id"
FROM "notes"
WHERE "notes"."noteable_id" IN (103082749, 103082743, 91238195, 145019549, 140128025, 140181159, 94281553, 127243215, 140266785, 92966588,
       126822552, 29187838,
       140006729, 140022862, 127721574, 140288607, 137016618, 108775126, 29066138, 157812883, 30580159, 154786262,
       154818155, 35330687, 32162816, 107210296, 140239541, 112698968, 93692643, 127604741, 136589247, 151667706,
       97714099, 135494718, 142049945, 145280191, 148027391, 80613167, 140246421, 146530824, 154236589, 136149374,
       102109365, 105162449, 54171828, 91126452, 91210537, 146867608, 92675558, 12441707, 140259533, 110046851) AND "notes"."noteable_type" = 'Issue'
  AND "notes"."id" >= 84972897
ORDER BY "notes"."id" ASC
LIMIT 1 OFFSET 1000;

SELECT "notes".*
FROM "notes"
WHERE "notes"."noteable_id" IN (103082749, 103082743, 91238195, 145019549, 140128025, 140181159, 94281553, 127243215, 140266785, 92966588,
       126822552, 29187838,
       140006729, 140022862, 127721574, 140288607, 137016618, 108775126, 29066138, 157812883, 30580159, 154786262,
       154818155, 35330687, 32162816, 107210296, 140239541, 112698968, 93692643, 127604741, 136589247, 151667706,
       97714099, 135494718, 142049945, 145280191, 148027391, 80613167, 140246421, 146530824, 154236589, 136149374,
       102109365, 105162449, 54171828, 91126452, 91210537, 146867608, 92675558, 12441707, 140259533, 110046851) AND "notes"."noteable_type" = 'Issue'
  AND "notes"."id" >= 84972897
  AND "notes"."id" < 84973948
  AND "notes"."system" = FALSE;

How to set up and validate locally

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited Mar 24, 2025 by Terri Chu

Refactor lazy_user_notes and replace db index