Skip to content

Disable index_issues_on_relative_position in production

Production Change

Change Summary

We want to disable - not remove - index_issues_on_relative_position to verify that it's safe to delete without adverse performance impact. We know that this index can cause problematic query plans in some cases, like gitlab-org/gitlab#254249 (closed).

Once this change is done we should roll it back as we'll then delete the index entirely in gitlab-org/gitlab!43991 (merged).

Change Details

  1. Services Impacted - ServicePostgres (and everything that uses that; here, ServiceWeb and ServiceSidekiq specifically)
  2. Change Technician - @abrandl
  3. Change Criticality - C3
  4. Change Type - changescheduled
  5. Change Reviewer - TODO
  6. Due Date - 2020-10-07
  7. Time tracking - TODO
  8. Downtime Component - None

Detailed steps for the change

Pre-Change Steps - steps to be completed before execution of the change

Estimated Time to Complete (mins) - 2 mins

  • Verify index is chosen by the planner

    EXPLAIN SELECT pos, next_pos, ABS(pos::bigint - next_pos::bigint) FROM (SELECT relative_position AS pos, LEAD(relative_position) OVER (ORDER BY relative_position DESC) AS next_pos FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'transgourmet-ecommerce/aufbau-co.-sites/%')) AND (relative_position <= 2147483647) ORDER BY "issues"."relative_position" DESC) items WHERE pos IS NULL OR ABS(pos::bigint - next_pos::bigint) >= 2 LIMIT 1;

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - TODO

  • Disable index on the primary

    update pg_index set indisvalid = false where indexrelid = (select oid from pg_class where relname = 'index_issues_on_relative_position');

Post-Change Steps - steps to take to verify the change

Estimated Time to Complete (mins) - 60 mins

Rollback

Rollback steps - steps to be taken in the event of a need to rollback this change

Estimated Time to Complete (mins) - TODO

  • Re-enable index

    update pg_index set indisvalid = true where indexrelid = (select oid from pg_class where relname = 'index_issues_on_relative_position');

  • Verify that it's used again

    EXPLAIN SELECT pos, next_pos, ABS(pos::bigint - next_pos::bigint) FROM (SELECT relative_position AS pos, LEAD(relative_position) OVER (ORDER BY relative_position DESC) AS next_pos FROM "issues" WHERE "issues"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'transgourmet-ecommerce/aufbau-co.-sites/%')) AND (relative_position <= 2147483647) ORDER BY "issues"."relative_position" DESC) items WHERE pos IS NULL OR ABS(pos::bigint - next_pos::bigint) >= 2 LIMIT 1;

Monitoring

Key metrics to observe

Summary of infrastructure changes

  • Does this change introduce new compute instances?
  • Does this change re-size any existing compute instances?
  • Does this change introduce any additional usage of tooling like Elastic Search, CDNs, Cloudflare, etc?

No.

Summary of the above

Changes checklist

  • This issue has a criticality label (e.g. C1, C2, C3, C4) and a change-type label (e.g. changeunscheduled, changescheduled).
  • This issue has the change technician as the assignee.
  • Pre-Change, Change, Post-Change, and Rollback steps and have been filled out and reviewed.
  • Necessary approvals have been completed based on the Change Management Workflow.
  • Change has been tested in staging and resultes noted in a comment on this issue.
  • A dry-run has been conducted and results noted in a comment on this issue.
  • SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue.)
  • There are currently no active incidents.
Edited by Andreas Brandl