Add note_diff_files sync index and FK

What does this MR do and why?

The async creation for index_note_diff_files_on_namespace_id was deployed and already completed in .com over the weekend. Now creating the index synchronously and adding a NOT VALID foreign key on the namespace_id sharding key column.

References

Add sync index on sharding key column for `note... (#575873)

Migration output

UP

main: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20170
main: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: migrating =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: migrated (0.0142s) ====

main: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20170
ci: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20171
ci: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: migrating =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0033s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: migrated (0.0188s) ====

ci: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20171
main: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20173
main: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: migrating ============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE namespaces, note_diff_files IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0003s
main: -- execute("ALTER TABLE note_diff_files ADD CONSTRAINT fk_a3c1c679d6 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0041s
main: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: migrated (0.0158s) ===

main: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20173
ci: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20174
ci: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: migrating ============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE namespaces, note_diff_files IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE note_diff_files ADD CONSTRAINT fk_a3c1c679d6 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0017s
ci: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: migrated (0.0200s) ===

ci: == [advisory_lock_connection] object_id: 129880, pg_backend_pid: 20174

DOWN

main: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21286
main: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: reverting =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0142s
main: -- indexes(:note_diff_files)
main:    -> 0.0014s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:note_diff_files, {:algorithm=>:concurrently, :name=>"index_note_diff_files_on_namespace_id"})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: reverted (0.0436s) ====

main: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21286
ci: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21348
ci: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: reverting =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0177s
ci: -- indexes(:note_diff_files)
ci:    -> 0.0018s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:note_diff_files, {:algorithm=>:concurrently, :name=>"index_note_diff_files_on_namespace_id"})
ci:    -> 0.0021s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251017094044 AddNoteDiffFilesNamespaceIdIndexSync: reverted (0.0567s) ====

ci: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21348
main: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21392
main: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: reverting ============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:note_diff_files, {:column=>:namespace_id})
main:    -> 0.0031s
main: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: reverted (0.0621s) ===

main: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21392
ci: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21435
ci: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: reverting ============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:note_diff_files, {:column=>:namespace_id})
ci:    -> 0.0027s
ci: == 20251017094335 AddNoteDiffFilesNamespaceIdForeignKey: reverted (0.0629s) ===

ci: == [advisory_lock_connection] object_id: 129580, pg_backend_pid: 21435

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #575873