Create a unique index on issuable_resource_links (issue_id, link)
Why are we doing this work
Following the application-level changes done in !139588 (merged), we need to create a unique index on issuable_resource_links (issue_id, link)
in order to fix #377831.
Without the actual DB constraint, we cannot assume the uniqueness is there when we write migrations.
Unfortunately, PG does not support a uniqueness constraint that can be partially valid (NOT VALID
.)
The following implementation plan details how we can synthetically create a NOT VALID uniqueness constraint using a column and an partial uniqueness index.
Follow up to !139435 (comment 1702968893)
Fixes #377831 at the data layer.
Implementation Plan
In release N
Step 1. Create a regular migration and perform the following steps.
- Add a nullable column named
is_unique
to the tableissuable_resource_links
.
Step 2. Create a partial unique index asynchronously for production.
In release N+1
Step 1. Update the Rails model so that whenever a new issuable_resource_links
is created, mark is_unique=true
.
Step 2. Mark each issuable_resource_links
record with is_unique=true
as the migration confirms it.
Step 3. Remove the duplicates for the production DB.
In release N+2
Step 1. Finalize the batched background migration on the self-hosted (make sure this is mentioned in a release post. TODO: check with a release/delivery manager/DB team.)
Step 2. Create a post-deployment migration and create the full (normal) unique index on the self-hosted.
CREATE UNIQUE INDEX CONCURRENTLY ON issuable_resource_links (issue_id, link);
Step 3. Asynchronously create the same full unique index for SaaS.
In release N+3 and later
- Remove the partial index from all environments (SaaS + self-hosted.) Do this concurrently in a post-deployment migration.
- Start ignoring the column
is_unique
and follow the removal procedure.