[GSTG] Drop index_ci_builds_runner_id_pending_covering index from ci_builds

Production Change

Change Summary

Because of #6821 (closed) we are not sure if dropping any index from ci_builds using a regular database migration would cause a similar incident. In gitlab-org/gitlab!91258 (comment 1009958797) it was suggested to drop ci_builds indices via CR issue because it can be executed when there isn't any autovacuum (to prevent wraparound) process on the ci_builds table and then the regular migration would be turned into a no-op for production.

The code that was using this index was disabled by a few feature flags and finally removed in %15.2: gitlab-org/gitlab!91595 (merged)

We know that this index is no longer used by any other query from the application:

image

Source

Change Details

  1. Services Impacted - ServicePostgres
  2. Change Technician - @steveazz
  3. Change Reviewer - @bshah11
  4. Time tracking - 1 day
  5. Downtime Component - None

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 15 minutes

  • Set label changein-progress /label ~change::in-progress
  • Connect to the main database
  • Verify that there isn't any autovacuum(to prevent wraparound) process running on ci_builds: 
          SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start) as duration, query
      FROM pg_stat_activity
      WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state != 'idle' AND age(clock_timestamp(), query_start) > '00:01:00'
      ORDER BY age(clock_timestamp(), query_start) DESC;
  • Drop the index_ci_builds_runner_id_pending_covering index: 
          DROP INDEX CONCURRENTLY "index_ci_builds_runner_id_pending_covering";
  • Verify that the index is gone \di+ index_ci_builds_runner_id_pending_covering
  • Connect to the ci database
  • Verify that there isn't any autovacuum(to prevent wraparound) process running on ci_builds: 
          SELECT pid, application_name, client_addr, state, age(clock_timestamp(), query_start) as duration, query
      FROM pg_stat_activity
      WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND state != 'idle' AND age(clock_timestamp(), query_start) > '00:01:00'
      ORDER BY age(clock_timestamp(), query_start) DESC;
  • Drop the index_ci_builds_runner_id_pending_covering index: 
          DROP INDEX CONCURRENTLY "index_ci_builds_runner_id_pending_covering";
  • Verify that the index is gone \di+ index_ci_builds_runner_id_pending_covering
  • Set label changecomplete /label ~change::complete

Rollback

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

Estimated Time to Complete (mins) - ~ 24 hours

  • Recreate the index on main using: 
          CREATE INDEX index_ci_builds_runner_id_pending_covering ON ci_builds USING btree (runner_id, id) INCLUDE (project_id) WHERE (((status)::text = 'pending'::text) AND ((type)::text = 'Ci::Build'::text));
  • Recreate the index on ci using: 
          CREATE INDEX index_ci_builds_runner_id_pending_covering ON ci_builds USING btree (runner_id, id) INCLUDE (project_id) WHERE (((status)::text = 'pending'::text) AND ((type)::text = 'Ci::Build'::text));
  • Set label changeaborted /label ~change::aborted

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 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.
    • 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 (If needed! Cases include DB change) prior to 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 Steve Xuereb