Add `suggestions` sharding key (!207836) · Merge requests · GitLab.org / GitLab

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

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

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.

Edited Oct 09, 2025 by Eugenia Grieff

Add suggestions sharding key

What does this MR do and why?

References

Migration output

MR acceptance checklist

Merge request reports

Add `suggestions` sharding key