Add suggestions sharding key

What does this MR do and why?

Add sharding key to suggestions table.

• Add sharding key column namespace_id
• Add trigger to populate sharding key on insert and update
• Add NOT VALID NOT NULL constraint
• Schedule sharding key index to be created ASYNC This was changed to be created synchronously because the index creation only takes ~40s (re !207836 (comment 2813979463))
• Add NOT VALID foreign key for namespace_id column

A Suggestion record is associated with a Note. The notes table has 3 sharding key columns (project_id, namespace_id, organization_id), but one of them is temporary (project_id, see #444222 (comment 2765183660) for more context).

Given this information, the sharding key for suggestions can be populated from the notes table using a trigger:

• If notes.project_id column is populated, we find the project and populate suggestions.namespace_id with the value in project.project_namespace_id
• If notes.namespace_id column is populated, we set this value to suggestions.namespace_id

This MR follows a similar process to !205639 (merged)

References

Related to #572847

Migration output

UP

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67058
main: == 20251007102142 AddShardingKeyToSuggestions: migrating ======================
main: -- add_column(:suggestions, :namespace_id, :bigint)
main:    -> 0.0091s
main: == 20251007102142 AddShardingKeyToSuggestions: migrated (0.0231s) =============

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67058

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67189
ci: == 20251007102142 AddShardingKeyToSuggestions: migrating ======================
ci: -- add_column(:suggestions, :namespace_id, :bigint)
ci:    -> 0.0098s
ci: == 20251007102142 AddShardingKeyToSuggestions: migrated (0.0262s) =============

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67189


main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62387
main: == 20251007102313 AddShardingKeyTriggerOnSuggestions: migrating ===============
main: -- execute("CREATE OR REPLACE FUNCTION sync_sharding_key_with_notes_table()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL OR NEW.\"namespace_id\" IS NOT NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_project_id IS NOT NULL THEN\n    SELECT \"project_namespace_id\" FROM \"projects\"\n    INTO NEW.\"namespace_id\" WHERE \"projects\".\"id\" = note_project_id;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
main:    -> 0.0106s
main: -- execute("CREATE TRIGGER set_sharding_key_for_suggestions_on_insert_and_update\nBEFORE INSERT OR UPDATE ON suggestions\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
main:    -> 0.0018s
main: == 20251007102313 AddShardingKeyTriggerOnSuggestions: migrated (0.0264s) ======

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62387

cci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62580
ci: == 20251007102313 AddShardingKeyTriggerOnSuggestions: migrating ===============
ci: -- execute("CREATE OR REPLACE FUNCTION sync_sharding_key_with_notes_table()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL OR NEW.\"namespace_id\" IS NOT NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_project_id IS NOT NULL THEN\n    SELECT \"project_namespace_id\" FROM \"projects\"\n    INTO NEW.\"namespace_id\" WHERE \"projects\".\"id\" = note_project_id;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
ci:    -> 0.0104s
ci: -- execute("CREATE TRIGGER set_sharding_key_for_suggestions_on_insert_and_update\nBEFORE INSERT OR UPDATE ON suggestions\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
ci:    -> 0.0016s
ci: == 20251007102313 AddShardingKeyTriggerOnSuggestions: migrated (0.0280s) ======


main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67307
main: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: migrating =====
main: -- current_schema(nil)
main:    -> 0.0005s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE suggestions\nADD CONSTRAINT check_e69372e45f\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0006s
main: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: migrated (0.0337s)

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67307

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67430
ci: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: migrating =====
ci: -- current_schema(nil)
ci:    -> 0.0005s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE suggestions\nADD CONSTRAINT check_e69372e45f\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: migrated (0.0375s)

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67430

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67624
main: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: migrating ============
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0140s
main: -- index_exists?(:suggestions, :namespace_id, {:name=>"index_suggestions_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0013s
main: -- add_index_options(:suggestions, :namespace_id, {:name=>"index_suggestions_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0001s
main: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: migrated (0.0566s) ===

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67624

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67799
ci: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: migrating ============
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0134s
ci: -- index_exists?(:suggestions, :namespace_id, {:name=>"index_suggestions_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0013s
ci: -- add_index_options(:suggestions, :namespace_id, {:name=>"index_suggestions_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0001s
ci: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: migrated (0.0596s) ===

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 67799

DOWN

main: == [advisory_lock_connection] object_id: 129280, pg_backend_pid: 66005
main: == 20251007102142 AddShardingKeyToSuggestions: reverting ======================
main: -- remove_column(:suggestions, :namespace_id, :bigint)
main:    -> 0.0089s
main: == 20251007102142 AddShardingKeyToSuggestions: reverted (0.0229s) =============

main: == [advisory_lock_connection] object_id: 129280, pg_backend_pid: 66005

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66334
ci: == 20251007102142 AddShardingKeyToSuggestions: reverting ======================
ci: -- remove_column(:suggestions, :namespace_id, :bigint)
ci:    -> 0.0180s
ci: == 20251007102142 AddShardingKeyToSuggestions: reverted (0.0396s) =============

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66334

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62068
main: == 20251007102313 AddShardingKeyTriggerOnSuggestions: reverting ===============
main: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_suggestions_on_insert_and_update ON suggestions")
main:    -> 0.0196s
main: -- execute("DROP FUNCTION IF EXISTS sync_sharding_key_with_notes_table()")
main:    -> 0.0019s
main: == 20251007102313 AddShardingKeyTriggerOnSuggestions: reverted (0.0376s) ======

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62068

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62227
ci: == 20251007102313 AddShardingKeyTriggerOnSuggestions: reverting ===============
ci: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_suggestions_on_insert_and_update ON suggestions")
ci:    -> 0.0100s
ci: -- execute("DROP FUNCTION IF EXISTS sync_sharding_key_with_notes_table()")
ci:    -> 0.0018s
ci: == 20251007102313 AddShardingKeyTriggerOnSuggestions: reverted (0.0317s) ======

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 62227

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66495
main: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: reverting =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE suggestions\n            DROP CONSTRAINT IF EXISTS check_e69372e45f\n")
main:    -> 0.0005s
main: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: reverted (0.0217s)

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66495

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66646
ci: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: reverting =====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE suggestions\n            DROP CONSTRAINT IF EXISTS check_e69372e45f\n")
ci:    -> 0.0004s
ci: == 20251007111103 AddNotNullConstraintOnSuggestionsShardingKey: reverted (0.0256s)

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66646

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66770
main: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: reverting ============
main: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: reverted (0.0354s) ===

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66770

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66889
ci: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: reverting ============
ci: == 20251007111146 AddShardingKeyIndexOnSuggestionsAsync: reverted (0.0337s) ===

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 66889

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.