Skip to content

Loose foreign key definition experiment

Adam Hegyi requested to merge 343749-loose-fk-experiment into master

Important: rollback steps

The MR adds ON DELETE triggers to the chat_names and ci_runners tables. There is a small chance where the triggers might slow down the DELETE queries related to the tables mentioned earlier.

Removing these triggers will not affect any existing feature and it does not affect data consistency so they're safe to remove:

DROP TRIGGER IF EXISTS chat_names_loose_fk_trigger ON chat_names;
DROP TRIGGER IF EXISTS ci_runners_loose_fk_trigger ON ci_runners;

What does this MR do and why?

This MR adds 2 loose foreign key definitions following the loose FK guidelines (https://docs.gitlab.com/ee/development/database/loose_foreign_keys):

chat_names -> ci_pipeline_chat_data (delete)
ci_runners -> clusters_applications_runners (nullify)

This is the first step for verifying the loose foreign key feature. At this point existing foreign keys will not be removed, so the consistency is still enforced by the FKs. Our goal is to monitor the data growth in the loose_foreign_keys_deleted_records table and see if our cleanup worker can keep up with the incoming data.

How to set up and validate locally

You can trigger the loose FK cleanup service after creating and deleting some records in the rails console:

model = create(:chat_name)
model.destroy!

puts LooseForeignKeys::DeletedRecord.all
puts LooseForeignKeys::DeletedRecord.status_pending.count # 1

LooseForeignKeys::ProcessDeletedRecordsService.new(connection: ChatName.connection).execute
LooseForeignKeys::DeletedRecord.status_pending.count
LooseForeignKeys::DeletedRecord.status_processed.count

Migration

up:

== 20211027064021 TrackDeletionsInCiRunners: migrating ========================
-- execute("CREATE TRIGGER ci_runners_loose_fk_trigger\nAFTER DELETE ON ci_runners REFERENCING OLD TABLE AS old_table\nFOR EACH STATEMENT\nEXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records();\n")
   -> 0.0031s
== 20211027064021 TrackDeletionsInCiRunners: migrated (0.0032s) ===============

== 20211027064156 TrackDeletionsInChatNames: migrating ========================
-- execute("CREATE TRIGGER chat_names_loose_fk_trigger\nAFTER DELETE ON chat_names REFERENCING OLD TABLE AS old_table\nFOR EACH STATEMENT\nEXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records();\n")
   -> 0.0012s
== 20211027064156 TrackDeletionsInChatNames: migrated (0.0013s) ===============

down:

== 20211027064156 TrackDeletionsInChatNames: reverting ========================
-- execute("DROP TRIGGER IF EXISTS chat_names_loose_fk_trigger ON chat_names")
   -> 0.0009s
== 20211027064156 TrackDeletionsInChatNames: reverted (0.0009s) ===============

== 20211027064021 TrackDeletionsInCiRunners: reverting ========================
-- execute("DROP TRIGGER IF EXISTS ci_runners_loose_fk_trigger ON ci_runners")
   -> 0.0008s
== 20211027064021 TrackDeletionsInCiRunners: reverted (0.0009s) ===============

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #343749 (closed)

Edited by Adam Hegyi

Merge request reports