Add sharding key to merge_request_diff_files source table (Step 1.9)

What

Prior to enqueuing the backfill of the partitioned merge_request_diff_files table, we will need to add the sharding key column and update both the table sync trigger and the application to start writing the sharding key.

Why

If we don't, then we will never have a safe point to switch over from unpartitioned table to partitioned table.

Details

Current working plan, based on !177799 (comment 2433222089)

(See also https://docs.gitlab.com/development/database/foreign_keys/#on-an-existing-column)

• Asynchronously create the index of project_id - !186903 (merged)
• Create the FK via add_concurrent_foreign_key with the option validate: false (!191546 (merged))
• Add callbacks to populate data for new/updated records (!184215 (merged))
• BBM backfill of project_id on merge_request_diff_files table
• In a post-migration, use prepare_async_foreign_key_validation

Original Plan

• N (MR1): Add the project_id column
• N (MR1): Post deploy: Change the trigger to include project_id
• N (MR1): Change rails to include project_id

---

• N (MR3): Post deploy: Queue backfill ([LINK TO EVENTUAL MR)
• N+? (MR4): Switch reads with feature flag (this can happen any time after backfill finishes on .com, SM won't ever use this)
• N+RS (MR5): Finalize backfill, has to be after at least one required stop, maybe two
• N+RS (MR6): Remove feature flag, which changes code path back to original table name
• N+RS (MR6): Post deploy (tag release managers): Drop trigger, rename merge_request_diff_files to merge_request_diff_files_orig, rename merge_request_diff_files_99208b8fac to merge_request_diff_files in same transaction, making sure down migration works so release managers can undo if necessary

Diagram

flowchart TB
    app_old("Application Using Old Table<br>(Without Sharding Key)")
    app_old --> old_table_sync("Old Table<br>(Syncing Without Sharding Key)")
    old_table_sync -- Add Sharding Key --> old_table_sharding("Old Table<br>With Sharding Key")
    old_table_sharding -- Replace Table Sync Trigger --> old_table_sharding_syncing("Old Table<br>(Sync Includes Sharding Key)")
    old_table_sharding -- No Sharding Key --> new_table
    old_table_sharding_syncing -- Change Application to Write Sharding Key --> app_old_sharding("Application Using Old Table<br>(With Sharding Key)")
    app_old_sharding -- Queue Backfill --> backfill("Backfill Old to New")
    backfill -- Switchover --> app_new("Application Using New Table")
    old_table_sharding_syncing -- With Sharding Key --> new_table
    old_table_sync -- No Sharding Key --> new_table("New Table<br>(With Sharding Key)")
    app_new --> new_table

Heavily related to #501473 (closed)