Skip to content

Add note_metadata sharding key

Eugenia Grieffrequested to merge
550695-define-a-sharding-key-for-note_metadata-table into master

What does this MR do and why?

Add sharding key to note_metadata table

  • Add sharding key column namespace_id
  • Add trigger to populate sharding key on insert and update
  • Add NOT VALID NOT NULL constraint
  • Create index for sharding key
  • Add NOT VALID foreign key for namespace_id

A Notes::NoteMetadata 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 note_metadata can be populated from the notes table using a trigger:

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

This MR follows a similar process to Add `suggestions` sharding key (!207836 - merged) and reuses the trigger created in that MR.

References

Related to #572843 (closed)

Migration output

UP 
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28501
main: == 20251013132828 AddShardingKeyToNoteMetadata: migrating =====================
main: -- add_column(:note_metadata, :namespace_id, :bigint)
main:    -> 0.0224s
main: == 20251013132828 AddShardingKeyToNoteMetadata: migrated (0.0405s) ============

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28501
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28563
ci: == 20251013132828 AddShardingKeyToNoteMetadata: migrating =====================
ci: -- add_column(:note_metadata, :namespace_id, :bigint)
ci:    -> 0.0577s
ci: == 20251013132828 AddShardingKeyToNoteMetadata: migrated (0.0908s) ============

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28563
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28618
main: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: 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.0359s
main: -- execute("CREATE TRIGGER set_sharding_key_for_note_metadata_on_insert_and_update\nBEFORE INSERT OR UPDATE ON note_metadata\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
main:    -> 0.0020s
main: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: migrated (0.0594s) =====

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28618
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28667
ci: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: 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.0191s
ci: -- execute("CREATE TRIGGER set_sharding_key_for_note_metadata_on_insert_and_update\nBEFORE INSERT OR UPDATE ON note_metadata\nFOR EACH ROW\n\nEXECUTE FUNCTION sync_sharding_key_with_notes_table()\n")
ci:    -> 0.0014s
ci: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: migrated (0.0425s) =====

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28667
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28710
main: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: migrating ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0178s
main: -- index_exists?(:note_metadata, :namespace_id, {:name=>"index_note_metadata_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0016s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:note_metadata, :namespace_id, {:name=>"index_note_metadata_on_namespace_id", :algorithm=>:concurrently})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: migrated (0.0558s) =======

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28710
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28748
ci: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: migrating ================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0173s
ci: -- index_exists?(:note_metadata, :namespace_id, {:name=>"index_note_metadata_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0013s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:note_metadata, :namespace_id, {:name=>"index_note_metadata_on_namespace_id", :algorithm=>:concurrently})
ci:    -> 0.0029s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: migrated (0.0570s) =======

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28748
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28832
main: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: migrating ====
main: -- current_schema(nil)
main:    -> 0.0008s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE note_metadata\nADD CONSTRAINT check_67a890ebba\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
main:    -> 0.0019s
main: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: migrated (0.0616s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28832
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29131
ci: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: 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 note_metadata\nADD CONSTRAINT check_67a890ebba\nCHECK ( namespace_id IS NOT NULL )\nNOT VALID;\n")
ci:    -> 0.0019s
ci: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: migrated (0.0460s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29131
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29168
main: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: migrating =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("LOCK TABLE namespaces, note_metadata IN SHARE ROW EXCLUSIVE MODE")
main:    -> 0.0004s
main: -- execute("ALTER TABLE note_metadata ADD CONSTRAINT fk_2a22435354 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0016s
main: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: migrated (0.0547s) ====

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29168
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29282
ci: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: migrating =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE namespaces, note_metadata IN SHARE ROW EXCLUSIVE MODE")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE note_metadata ADD CONSTRAINT fk_2a22435354 FOREIGN KEY (namespace_id) REFERENCES namespaces (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0011s
ci: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: migrated (0.0642s) ====

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 29282
DOWN 
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27786
main: == 20251013132828 AddShardingKeyToNoteMetadata: reverting =====================
main: -- remove_column(:note_metadata, :namespace_id, :bigint)
main:    -> 0.0201s
main: == 20251013132828 AddShardingKeyToNoteMetadata: reverted (0.0391s) ============

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27786
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27852
ci: == 20251013132828 AddShardingKeyToNoteMetadata: reverting =====================
ci: -- remove_column(:note_metadata, :namespace_id, :bigint)
ci:    -> 0.0204s
ci: == 20251013132828 AddShardingKeyToNoteMetadata: reverted (0.0422s) ============

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27852
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27893
main: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: reverting ==============
main: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_note_metadata_on_insert_and_update ON note_metadata")
main:    -> 0.0170s
main: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: reverted (0.0396s) =====

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27893
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27932
ci: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: reverting ==============
ci: -- execute("DROP TRIGGER IF EXISTS set_sharding_key_for_note_metadata_on_insert_and_update ON note_metadata")
ci:    -> 0.0126s
ci: == 20251013132954 AddShardingKeyTriggerOnNoteMetadata: reverted (0.0312s) =====

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27932
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27973
main: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: reverting ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0112s
main: -- indexes(:note_metadata)
main:    -> 0.0016s
main: -- current_schema(nil)
main:    -> 0.0001s
main: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: reverted (0.0352s) =======

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 27973
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28014
ci: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: reverting ================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0113s
ci: -- indexes(:note_metadata)
ci:    -> 0.0016s
ci: -- current_schema(nil)
ci:    -> 0.0001s
ci: == 20251013133122 AddShardingKeyIndexOnNoteMetadata: reverted (0.0424s) =======

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28014
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28063
main: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: reverting ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("            ALTER TABLE note_metadata\n            DROP CONSTRAINT IF EXISTS check_67a890ebba\n")
main:    -> 0.0008s
main: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: reverted (0.0269s)

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28063
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28120
ci: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: reverting ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("            ALTER TABLE note_metadata\n            DROP CONSTRAINT IF EXISTS check_67a890ebba\n")
ci:    -> 0.0007s
ci: == 20251013133208 AddNotNullConstraintOnNoteMetadataShardingKey: reverted (0.0292s)

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28120
main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28165
main: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: reverting =============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: reverted (0.0922s) ====

main: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28165
ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28203
ci: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: reverting =============
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: == 20251013133313 AddNoteMetadataNamespaceIdForeignKey: reverted (0.0954s) ====

ci: == [advisory_lock_connection] object_id: 129520, pg_backend_pid: 28203

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