Skip to content

Add note_diff_files sharding key

Eugenia Grieffrequested to merge
572838-add-note_diff_files-sharding-key into master

What does this MR do and why?

Add note_diff_files sharding key

  • Add sharding key column namespace_id
  • Add trigger to populate sharding key
  • Add NOT VALID NOT NULL constraint
  • Schedule sharding key index to be created ASYNC

A NoteDiffFile record is associated with a DiffNote, which is stored in the notes table. 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 note_diff_files can be populated from this table using a trigger:

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

The trigger fires only on INSERT because NoteDiffFile records are not updated.

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

References

Related to #572838

Migration output

UP 
main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7299
main: == 20251006134849 AddShardingKeyToNoteDiffFiles: migrating ====================
main: -- add_column(:note_diff_files, :namespace_id, :bigint)
main:    -> 0.0189s
main: == 20251006134849 AddShardingKeyToNoteDiffFiles: migrated (0.0362s) ===========

main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7299
ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7300
ci: == 20251006134849 AddShardingKeyToNoteDiffFiles: migrating ====================
ci: -- add_column(:note_diff_files, :namespace_id, :bigint)
ci:    -> 0.0018s
ci: == 20251006134849 AddShardingKeyToNoteDiffFiles: migrated (0.0087s) ===========

ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7300
main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7302
main: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: migrating =============
main: -- execute("CREATE FUNCTION ensure_note_diff_files_sharding_key()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"diff_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.0027s
main: -- execute("CREATE TRIGGER trigger_ensure_note_diff_files_sharding_key\nBEFORE INSERT ON note_diff_files\nFOR EACH ROW\n\nEXECUTE FUNCTION ensure_note_diff_files_sharding_key()\n")
main:    -> 0.0048s
main: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: migrated (0.0110s) ====

main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7302
ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7306
ci: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: migrating =============
ci: -- execute("CREATE FUNCTION ensure_note_diff_files_sharding_key()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  SELECT \"project_id\", \"namespace_id\"\n  INTO note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"diff_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.0020s
ci: -- execute("CREATE TRIGGER trigger_ensure_note_diff_files_sharding_key\nBEFORE INSERT ON note_diff_files\nFOR EACH ROW\n\nEXECUTE FUNCTION ensure_note_diff_files_sharding_key()\n")
ci:    -> 0.0013s
ci: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: migrated (0.0101s) ====

ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7306
main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7316
main: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: migrating ===
main: -- current_schema(nil)
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE note_diff_files\nADD CONSTRAINT check_ebb23d73d7\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0008s
main: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: migrated (0.0092s)

main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7316
ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7317
ci: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: migrating ===
ci: -- current_schema(nil)
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE note_diff_files\nADD CONSTRAINT check_ebb23d73d7\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0006s
ci: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: migrated (0.0131s)

ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7317
main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7320
main: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: migrating ========
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0013s
main: -- add_index_options(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0001s
main: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: migrated (0.0277s)

main: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7320
ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7321
ci: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: migrating ========
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.0013s
ci: -- add_index_options(:note_diff_files, :namespace_id, {:name=>"index_note_diff_files_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0002s
ci: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: migrated (0.0246s)

ci: == [advisory_lock_connection] object_id: 129560, pg_backend_pid: 7321
DOWN 
main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 3522
main: == 20251006134849 AddShardingKeyToNoteDiffFiles: reverting ====================
main: -- remove_column(:note_diff_files, :namespace_id, :bigint)
main:    -> 0.0205s
main: == 20251006134849 AddShardingKeyToNoteDiffFiles: reverted (0.0409s) ===========

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

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 3840
ci: == 20251006134849 AddShardingKeyToNoteDiffFiles: reverting ====================
ci: -- remove_column(:note_diff_files, :namespace_id, :bigint)
ci:    -> 0.0200s
ci: == 20251006134849 AddShardingKeyToNoteDiffFiles: reverted (0.0445s) ===========

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

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 4430
main: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: reverting =============
main: -- execute("DROP TRIGGER IF EXISTS trigger_ensure_note_diff_files_sharding_key ON note_diff_files")
main:    -> 0.0194s
main: -- execute("DROP FUNCTION IF EXISTS ensure_note_diff_files_sharding_key()")
main:    -> 0.0013s
main: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: reverted (0.0372s) ====

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

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 4728
ci: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: reverting =============
ci: -- execute("DROP TRIGGER IF EXISTS trigger_ensure_note_diff_files_sharding_key ON note_diff_files")
ci:    -> 0.0216s
ci: -- execute("DROP FUNCTION IF EXISTS ensure_note_diff_files_sharding_key()")
ci:    -> 0.0011s
ci: == 20251006135027 AddShardingKeyTriggerOnNoteDiffFiles: reverted (0.0440s) ====

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

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 5454
main: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: reverting ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE note_diff_files\n            DROP CONSTRAINT IF EXISTS check_ebb23d73d7\n")
main:    -> 0.0006s
main: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: reverted (0.0289s)

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

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 5814
ci: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: reverting ===
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE note_diff_files\n            DROP CONSTRAINT IF EXISTS check_ebb23d73d7\n")
ci:    -> 0.0006s
ci: == 20251006151417 AddNotNullConstraintOnNoteDiffFilesShardingKey: reverted (0.0364s)

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

main: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 6259
main: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: reverting ========
main: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: reverted (0.0527s)

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

ci: == [advisory_lock_connection] object_id: 129260, pg_backend_pid: 6589
ci: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: reverting ========
ci: == 20251006151642 AddShardingKeyIndexesOnNoteDiffFilesAsync: reverted (0.0512s)

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

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 by Eugenia Grieff

Merge request reports