[go: up one dir, main page]
Skip to content

Add system_note_metadata sharding key

Mario Celirequested to merge
514589-add-sharding-key into master

What does this MR do and why?

  • Add sharding key columns
  • Add trigger to populate sharding key
  • Add NOT VALID NOT NULL constraint
  • Schedule sharding key indexes to be created ASYNC

notes table has 3 sharding key columns, but ideally it could only have 2 (no project_id needed):

  • namespace_id: Used for group level noteables. Currently unreliable for records that also have a project_id. For these, we can always trust in project_id to get the associated namespace_id
  • project_id: Original column, we know it has the correct values, but can be replaced with namespace_id.
  • organization_id: Used for personal snippets which are only associated with an organization

So, since we are introducing the sharding key in this table from scratch, we can get it right the first time and only add 2 columns. We simply need the trigger to account for the current status of the notes table (some records have both project_id and namespace_id, and for these, namespace_id might have an incorrect value until we fix it in a later iteration). Then, the backfill will need to follow the same pattern. Other tables that will inherit from notes can use the same pattern, and then we will only have to fix notes in the future, not all those that were blocked by it too.

Migration output

UP 
main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63260
main: == 20250918203528 AddShardingKeyToSystemNoteMetadata: migrating ===============
main: -- add_column(:system_note_metadata, :namespace_id, :bigint)
main:    -> 0.0267s
main: -- add_column(:system_note_metadata, :organization_id, :bigint)
main:    -> 0.0015s
main: == 20250918203528 AddShardingKeyToSystemNoteMetadata: migrated (0.0333s) ======

main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63260
ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63261
ci: == 20250918203528 AddShardingKeyToSystemNoteMetadata: migrating ===============
ci: -- add_column(:system_note_metadata, :namespace_id, :bigint)
ci:    -> 0.0027s
ci: -- add_column(:system_note_metadata, :organization_id, :bigint)
ci:    -> 0.0014s
ci: == 20250918203528 AddShardingKeyToSystemNoteMetadata: migrated (0.0128s) ======

ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63261
main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63263
main: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: migrating ========
main: -- execute("CREATE OR REPLACE FUNCTION set_sharding_key_for_system_note_metadata()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_organization_id BIGINT;\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"organization_id\", \"project_id\", \"namespace_id\"\n  INTO note_organization_id, note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_organization_id IS NOT NULL THEN\n    NEW.\"organization_id\" := note_organization_id;\n    NEW.\"namespace_id\" := NULL;\n  ELSIF 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    NEW.\"organization_id\" := NULL;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n    NEW.\"organization_id\" := NULL;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
main:    -> 0.0028s
main: -- execute("CREATE TRIGGER set_sharding_key_for_system_note_metadata_on_insert\nBEFORE INSERT ON system_note_metadata\nFOR EACH ROW\n\nEXECUTE FUNCTION set_sharding_key_for_system_note_metadata()\n")
main:    -> 0.0015s
main: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: migrated (0.0090s) 

main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63263
ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63264
ci: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: migrating ========
ci: -- execute("CREATE OR REPLACE FUNCTION set_sharding_key_for_system_note_metadata()\nRETURNS TRIGGER AS\n$$\nDECLARE\n  note_organization_id BIGINT;\n  note_project_id BIGINT;\n  note_namespace_id BIGINT;\nBEGIN\n  IF NEW.\"note_id\" IS NULL THEN\n    RETURN NEW;\n  END IF;\n\n  SELECT \"organization_id\", \"project_id\", \"namespace_id\"\n  INTO note_organization_id, note_project_id, note_namespace_id\n  FROM \"notes\"\n  WHERE \"id\" = NEW.\"note_id\";\n\n  IF note_organization_id IS NOT NULL THEN\n    NEW.\"organization_id\" := note_organization_id;\n    NEW.\"namespace_id\" := NULL;\n  ELSIF 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    NEW.\"organization_id\" := NULL;\n  ELSE\n    NEW.\"namespace_id\" := note_namespace_id;\n    NEW.\"organization_id\" := NULL;\n  END IF;\n\n  RETURN NEW;\nEND\n$$ LANGUAGE PLPGSQL;\n")
ci:    -> 0.0111s
ci: -- execute("CREATE TRIGGER set_sharding_key_for_system_note_metadata_on_insert\nBEFORE INSERT ON system_note_metadata\nFOR EACH ROW\n\nEXECUTE FUNCTION set_sharding_key_for_system_note_metadata()\n")
ci:    -> 0.0022s
ci: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: migrated (0.0211s) 

ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63264
main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63266
main: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: migrating 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE system_note_metadata\nADD CONSTRAINT check_f2c4e04565\nCHECK ( num_nonnulls(namespace_id, organization_id) = 1 )\nNOT VALID;\n")
main:    -> 0.0008s
main: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: migrated (0.0087s) 

main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63266
ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63267
ci: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: migrating 
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE system_note_metadata\nADD CONSTRAINT check_f2c4e04565\nCHECK ( num_nonnulls(namespace_id, organization_id) = 1 )\nNOT VALID;\n")
ci:    -> 0.0008s
ci: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: migrated (0.0158s) 

ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63267
main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63271
main: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: migrating ===
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:system_note_metadata, :namespace_id, {:name=>"index_system_note_metadata_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0016s
main: -- add_index_options(:system_note_metadata, :namespace_id, {:name=>"index_system_note_metadata_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0001s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- index_exists?(:system_note_metadata, :organization_id, {:name=>"index_system_note_metadata_on_organization_id", :algorithm=>:concurrently})
main:    -> 0.0010s
main: -- add_index_options(:system_note_metadata, :organization_id, {:name=>"index_system_note_metadata_on_organization_id", :algorithm=>:concurrently})
main:    -> 0.0000s
main: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: migrated (0.0450s) 

main: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63271
ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63272
ci: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: migrating ===
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- index_exists?(:system_note_metadata, :namespace_id, {:name=>"index_system_note_metadata_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0016s
ci: -- add_index_options(:system_note_metadata, :namespace_id, {:name=>"index_system_note_metadata_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0002s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:system_note_metadata, :organization_id, {:name=>"index_system_note_metadata_on_organization_id", :algorithm=>:concurrently})
ci:    -> 0.0010s
ci: -- add_index_options(:system_note_metadata, :organization_id, {:name=>"index_system_note_metadata_on_organization_id", :algorithm=>:concurrently})
ci:    -> 0.0000s
ci: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: migrated (0.0348s) 

ci: == [advisory_lock_connection] object_id: 155560, pg_backend_pid: 63272
DOWN 
main: == [advisory_lock_connection] object_id: 155280, pg_backend_pid: 62431
main: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: reverting ===
main: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: reverted (0.0385s) 

main: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: reverting 
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE system_note_metadata\n            DROP CONSTRAINT IF EXISTS check_f2c4e04565\n")
main:    -> 0.0007s
main: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: reverted (0.0089s) 

main: == [advisory_lock_connection] object_id: 155280, pg_backend_pid: 62431
ci: == [advisory_lock_connection] object_id: 155280, pg_backend_pid: 62452
ci: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: reverting ===
ci: == 20250918222201 AddShardingKeyIndexesOnSystemNoteMetadataAsync: reverted (0.0158s) 

ci: == 20250918221702 AddNotNullConstraintOnSystemNoteMetadataShardingKey: reverting 

main: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 62768
main: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: reverting ========
main: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_system_note_metadata_on_insert ON system_note_metadata")
main:    -> 0.0209s
main: -- execute("DROP FUNCTION IF EXISTS set_sharding_key_for_system_note_metadata()")
main:    -> 0.0014s
main: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: reverted (0.0273s) 

main: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 62768
ci: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 62793
ci: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: reverting ========
ci: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_system_note_metadata_on_insert ON system_note_metadata")
ci:    -> 0.0024s
ci: -- execute("DROP FUNCTION IF EXISTS set_sharding_key_for_system_note_metadata()")
ci:    -> 0.0013s
ci: == 20250918211138 AddShardingKeyTriggerOnSystemNoteMetadata: reverted (0.0124s) 

ci: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 62793

main: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 63019
main: == 20250918203528 AddShardingKeyToSystemNoteMetadata: reverting ===============
main: -- remove_column(:system_note_metadata, :organization_id, :bigint)
main:    -> 0.0177s
main: -- remove_column(:system_note_metadata, :namespace_id, :bigint)
main:    -> 0.0015s
main: == 20250918203528 AddShardingKeyToSystemNoteMetadata: reverted (0.0277s) ======

main: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 63019
ci: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 63039
ci: == 20250918203528 AddShardingKeyToSystemNoteMetadata: reverting ===============
ci: -- remove_column(:system_note_metadata, :organization_id, :bigint)
ci:    -> 0.0020s
ci: -- remove_column(:system_note_metadata, :namespace_id, :bigint)
ci:    -> 0.0016s
ci: == 20250918203528 AddShardingKeyToSystemNoteMetadata: reverted (0.0119s) ======

ci: == [advisory_lock_connection] object_id: 155260, pg_backend_pid: 63039

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 #514589

Edited by Mario Celi

Merge request reports