Oh man, that migrations point. I remember at my last company needing to handle this. We had some (huge) read-only tables that were referenced by other tables an

Oh man, that migrations point. I remember at my last company needing to handle this. We had some (huge) read-only tables that were referenced by other tables and we needed up update the read-only tables in batches from raw mysql dumps. The keys in the dumps were stable, so we could swap out the table and the references would still be valid. Since the referenced tables were huge, they took a long time to upload the batches, and we needed a migration that was ACID and fast. The process ended up looking like this: 1. Create a `foo_load` and a `foo_unload` database, dropping any that already exist. 2. Populate `foo_load` with all of my new data. 4. Rename `foo.table` to `foo_unload.table` and `foo_load.table` to `foo.table`. This is atomic, fast, and updates references to point to `foo_unload.table`. At this point the application reads from the new data, but referential integrity checks still verify against the old data. 5. Update each reference to `foo_unload.table` to point to `foo.table` using ALTER TABLE DROP FOREIGN KEY, ADD FOREIGN KEY. Again this is atomic and runs quickly. 6. Drop the `foo_unload` database. Now this process is slow, but the switchover is 2 atomic steps and requires locking the tables for only a few milliseconds. The process is safe as well, if it crashes at any step, running the script again will cause it to safely recover. Well, with one more step zero: 0. If `foo_unload` exists, do the same rename step as step 5. This solution worked for us for a few years. Eventually we swapped out the whole architecture around these tables and ended up with a more traditional ETL flow that didn't involve moving round huge mysql dumps.