My team is trying to figure out how to ALTER a large table in production which has a couple large related tables (via fk constraints)
We have done some testing with the rebuild_constraints option and found that during the rebuild constraints portion of the migration that the table locking would not be acceptable for running in our production environment
We are also aware of the drop_swap option which should allow us to get around this but we are nervous of course about the warning that failures to rename the table could mean you lose the table.
One option we have found that appears to sidestep this is to pass --set-vars foreign_key_checks=0 to the command. As we understand it this would mean:
No guarantee of data consistency (as with the drop_swap approach)
However no risk of being stuck in limbo if the rename fails
Just curious to see if anyone has experience with this type of approach or can see better alternatives? I’ve poked around on the forums and online and have not seen references to using foreign_key_checks=0 along with rebuild_constraints.
Happy to provide more info / context if required and very much appreciate any experience / insight the community here might have!
To be honest I wouldn’t recommend to disable the FK check unless you are planning to check data relationships after the operation and know how to deal with potential invalid constraints.
Another approach that I would suggest is, if possible, run the ALTER directly but doing it first on the replicas and for the Primary execute a failover and when the older primary is now a replica, execute the alter there. The “risk” of the failover are potentially less and somehow more easy to manage
We did go ahead and run this actually and everything went smoothly! We did run some queries to verify the FK relationships were intact after the swap was completed and everything is looking good.
I see what you mean about the replica failover approach… basically imagine that is the most general approach that could pretty much allow you to make any type of DDL change safely right? In this case we’re looking for something that we can run pretty easily without too much overhead.
Would really appreciate if we could confirm two quick points actually. Is it true that this type of approach is like the drop_swap strategy without the risk of being stuck without the table if the swap fails? Also realize many variables at play but would we expect that the risk of FK constraints being broken to be pretty low? It would only be possible during the swap of the new table with the old am I correct? (Since the disabling of the fk constraint only applies to this specific operation)