pull down to refresh
100 sats \ 2 replies \ @WeAreAllSatoshi 2 Oct \ on: Make massive DB migrations fast II: batch, unlogged tables, defer constraints devs
I found this very insightful, thank you.
When deferring the constraints to after the bulk inserts, don't you run the risk of having invalid data inserted and therefore applying the constraints has the potential to fail? I know in theory the migration would handle it, but in practice it would seem possible that there are some bad records that could cause an issue?
Also, how have you handled running migrations like this live in production while users are using the app, potentially creating data that needs to be included in the migration? Do you run it again after the app code is deployed to ensure you "got" all the records needing migration?
When deferring the constraints to after the bulk inserts, don't you run the risk of having invalid data inserted and therefore applying the constraints has the potential to fail?
Yes, but the migration is faster to fail than the alternative, so you can iterate to fix the cause of the failure faster. If you use constraints as part of your migration logic though, i.e.
UPDATE ... ON CONFLICT, this might be a problem. In that case I'd add that constraint early then defer the others retaining proportional performance benefit.Also, how have you handled running migrations like this live in production while users are using the app, potentially creating data that needs to be included in the migration? Do you run it again after the app code is deployed to ensure you "got" all the records needing migration?
I haven't done this kind of migration online before, but if I had to I'd either take explicit locks on tables while they're being migrated or run two migrations (once to get most of it, switch over any clients to use the new schema, then run another migration to get any lingering rows).
In this case, we'll have a maintenance window on some dead weekend day. Even then, having the migration take an hour when it might fail was way too slow.
reply
As usual, thank you for the thoughtful response!
reply