Skip to content

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 table issuable_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. Create and enqueue a batched background migration that fixes the potential duplicates (ONLY FOR SELF-HOSTED?)

Mark each issuable_resource_links record with is_unique=true as the migration confirms it.

(For the production DB, we are hoping the records are already unique with respect to issue_id and link we can easily check this through a replica/clone without running the batched background migration.) Because issuable_resource_links should be small enough, we might be able to also run the background migration on production?

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

  1. Remove the partial index from all environments (SaaS + self-hosted.) Do this concurrently in a post-deployment migration.
  2. Start ignoring the column is_unique and follow the removal procedure.
Edited by Marc Saleiko