Temporarily disable `index_events_on_author_id_and_created_at_merge_requests` index on merge requests table

Production Change

Change Summary

Disable the index_events_on_author_id_and_created_at_merge_requests index that is not being highly used. This will allow us to measure the impact that dropping these indexes would have, while still being able to re-enable the indexes quickly if query performance suffers.

If no adverse query performance is noted, we will proceed to drop these indexes in Drop index `index_events_on_author_id_and_creat... (gitlab-org/gitlab#415090 - closed)

Change Details

  1. Services Impacted - ServicePatroni and ServiceWeb because it depends on these
  2. Change Technician - @ahmadsherif
  3. Change Reviewer - @alexander-sosna
  4. Time tracking - 100 minutes
  5. Downtime Component - None

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 80 minutes

  • Set label changein-progress /label ~change::in-progress

  • Disable the description index - in a production psql console execute Estimated Time to Complete (mins) - 20 minutes

    update pg_index set indisvalid = false where indexrelid = (select oid from pg_class where relname = 'index_events_on_author_id_and_created_at_merge_requests');
  • Run the following query to verify that the index index_events_on_author_id_and_created_at_merge_requests is not in use

EXPLAIN query that should not use `index_events_on_author_id_and_created_at_merge_requests`

 
EXPLAIN SELECT
	id
FROM
	"events"
WHERE
	"author_id" = '142752' -- @gholtz's user id (I think)
	AND created_at >= '2023-09-11'
LIMIT 20;

Rollback

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

Estimated Time to Complete (mins) - 20 minutes

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

Expected output of explain query that should not use `index_events_on_author_id_and_created_at_merge_requests`

 
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.70..29.02 rows=20 width=8)
  ->  Index Scan using index_events_on_author_id_and_created_at on events  (cost=0.70..44.59 rows=31 width=8)
        Index Cond: ((author_id = 142752) AND (created_at >= '2023-09-11 00:00:00+00'::timestamp with time zone))

Monitoring

Key metrics to observe

Change Reviewer checklist

C4 C3 C2 C1:

  • Check if the following applies:
    • The scheduled day and time of execution of the change is appropriate.
    • The change plan is technically accurate.
    • The change plan includes estimated timing values based on previous testing.
    • The change plan includes a viable rollback plan.
    • The specified metrics/monitoring dashboards provide sufficient visibility for the change.

C2 C1:

  • Check if the following applies:
    • The complexity of the plan is appropriate for the corresponding risk of the change. (i.e. the plan contains clear details).
    • The change plan includes success measures for all steps/milestones during the execution.
    • The change adequately minimizes risk within the environment/service.
    • The performance implications of executing the change are well-understood and documented.
    • The specified metrics/monitoring dashboards provide sufficient visibility for the change.
      • If not, is it possible (or necessary) to make changes to observability platforms for added visibility?
    • The change has a primary and secondary SRE with knowledge of the details available during the change window.
    • The change window has been agreed with Release Managers in advance of the change. If the change is planned for APAC hours, this issue has an agreed pre-change approval.
    • The labels blocks deployments and/or blocks feature-flags are applied as necessary.

Change Technician checklist

  • Check if all items below are complete:
    • The change plan is technically accurate.
    • This Change Issue is linked to the appropriate Issue and/or Epic
    • Change has been tested in staging and results noted in a comment on this issue.
    • A dry-run has been conducted and results noted in a comment on this issue.
    • The change execution window respects the Production Change Lock periods.
    • For C1 and C2 change issues, the change event is added to the GitLab Production calendar.
    • For C1 and C2 change issues, the SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue and await their acknowledgement.)
    • For C1 and C2 change issues, the SRE on-call provided approval with the eoc_approved label on the issue.
    • For C1 and C2 change issues, the Infrastructure Manager provided approval with the manager_approved label on the issue.
    • Release managers have been informed prior to any C1, C2, or blocks deployments change being rolled out. (In #production channel, mention @release-managers and this issue and await their acknowledgment.)
    • There are currently no active incidents that are severity1 or severity2
    • If the change involves doing maintenance on a database host, an appropriate silence targeting the host(s) should be added for the duration of the change.
Edited by Ahmad Sherif