Loose foreign key definition experiment
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #343749 (closed)