[GPRD] Recreate invalid index_vulnerability_reads_on_location_image_trigram

Production Change

Change Summary

As described in Index creation failed on 2022-10-01 leaving an ... (#7844 - closed), the index_vulnerability_reads_on_location_image_trigram is currently in an invalid state.

After discussing possible next steps in this thread, it was suggested to create a change request to work with a DBRE to execute a manual creation of the index, which is the purpose of this change request.

Possible steps for this CR are based on this comment:

(try to recreate the index) again a few times, with CONCURRENTLY, under control – observing the logs and progress in pg_stat_progress_create_index, e.g., sampling it into a log:

while sleep 0.5; do
 gitlab-psql --csv -Xtc "select now(), * from pg_stat_progress_create_index"
done  2>&1 | tee -a issue_XXXXX_pg_stat_progress_create_index.log

Additionally, it might also help to monitor – for the backend(s) building the index – pg_stat_activity (wait_event_type / wait_event) and pg_locks.

Change Details

  1. Services Impacted - ServicePatroni
  2. Change Technician - @rhenchen.gitlab
  3. Change Reviewer - @alexander-sosna
  4. Time tracking - 1 hour
  5. Downtime Component - none

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 30 minutes

  • During a non-peak period
  • Set label changein-progress /label ~change::in-progress
  • Open a TMUX session in the Primary node
  • Open a psql connection as gitlab
  • Set maintenance_work_mem to '10 GB' (Table size 9187 MB) 
    SET maintenance_work_mem TO '10 GB';
  • Create the index_vulnerability_reads_on_location_image_trigram index 
    /*application:web,db_config_name:main*/ CREATE INDEX CONCURRENTLY "index_vulnerability_reads_on_location_image_trigram" ON "vulnerability_reads" USING gin ("location_image" gin_trgm_ops) WHERE report_type = ANY (ARRAY[2, 7]) AND location_image IS NOT NULL;
  • Observe the logs and progress in pg_stat_progress_create_index 
    while sleep 0.5; do
  sudo gitlab-psql --csv -Xtc "select now(), * from pg_stat_progress_create_index"
done  2>&1 | tee -a issue_14368_pg_stat_progress_create_index.log
  • Monitor the backends building the index 
    sudo gitlab-psql -Xc "select * from pg_stat_activity where query like '/*application:web,db_config_name:main*/ CREATE INDEX CONCURRENTLY%';"
  • 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) - 5 minutes

  • Kill the background process creating the index 
    SELECT pg_cancel_backend(pid)
  • Drop the index 
    DROP INDEX index_vulnerability_reads_on_location_image_trigram;
  • 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.
    • 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 Rafael Henchen