Unblock database async index creation

Production Change

Change Summary

We need to remove an invalid index creation statement from the postgres_async_indexes queue. gitlab-org/gitlab!108823 (merged) enqueued an index for ci_build_report_results on (partition_id, id), but the primary key on that table is build_id. Because the async creation doesn't give up until the index is created, all other indexes for the queue are block by this failing statement.

https://log.gprd.gitlab.net/goto/4df8e1e0-9ee1-11ed-85ed-e7557b0a598c

Change Details

  1. Services Impacted - Postgres
  2. Change Technician - DRI for the execution of this change
  3. Change Reviewer - DRI for the review of this change
  4. Time tracking - 5 minutes
  5. Downtime Component - no downtime

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - Estimated Time to Complete in Minutes

  • Set label changein-progress /label ~change::in-progress
  • log into patroni-ci
  • find the offending entry with select * from postgres_async_indexes where table_name = 'ci_build_report_results';
  • delete the entry with delete from postgres_async_indexes where id = ID; ID from the previous step(should be 185)
  • Confirm that the entry was removed: select * from postgres_async_indexes where table_name = 'ci_build_report_results';
  • log into patroni-main
  • find the offending entry with select * from postgres_async_indexes where table_name = 'ci_build_report_results';
  • delete the entry with delete from postgres_async_indexes where id = ID; ID from the previous step
  • Confirm that the entry was removed: select * from postgres_async_indexes where table_name = 'ci_build_report_results';
  • Set label changecomplete /label ~change::complete

Rollback

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

Not needed, but here is the query to recreate the record.

Estimated Time to Complete (mins) - Estimated Time to Complete in Minutes

  • Set label changeaborted /label ~change::aborted
  • insert into postgres_async_indexes(name, definition,table_name, created_at, updated_at) values ('tmp_index_ci_build_report_results_on_partition_id_and_id', 'CREATE INDEX CONCURRENTLY "tmp_index_ci_build_report_results_on_partition_id_and_id" ON "ci_build_report_results" ("partition_id", "id") WHERE partition_id = 101', 'ci_build_report_results', now(), now());

Monitoring

Key metrics to observe

  • Metric: Metric Name
    • Location: Dashboard URL
    • What changes to this metric should prompt a rollback: Describe Changes

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.
    • 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 (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 Marius Bobin