This is one of those questions that look fairly simple but in practical terms can be quite complex, building on what ben said,
step 1
- add the new column
step 2
- change either the db or app to be able to automatically save the new values as well as the old ones
- update all existing rows to have the new column filled
step 3
- roll out a deploy to a new version of your app that only uses the new column in its schema definition and can translate requests using the old schema into new ones
- if you have a frontend as well do the same there
step 4
- if you created a db trigger on 2, drop it here too
- delete the old column
This would solve the question even if you have many nodes deployed but it really depends on - is it ok if some request errors out while we’re doing this? Or it’s of the uttermost importance that not a single request is botched. Depending on the answer for that you can make it easier or more complex.
note on step 2
can be either at the app level (changing the code paths that save those records to now save also the new col) or at the db level with a trigger that auto populates the new column based on the value of the old one whenever an insert or update is done for that table. This is needed because if someone would save a record between you updating the existing rows and you having finished the deployment of the new app version, it would still only save the old column for those requests.
note on step 3
gets more complex if you’re using a spa client, as they won’t receive the new “bundle” until they refresh their page. So even if you do all these steps they will be stuck on a version of code that expects the old schema and functions with the old schema alone (forms, etc). The best UX is to somehow display a warning to the user telling them they should refresh (like adding a warning/info when trying to save a form and you detect that the form only has the old column and not the new one). With html only (no spa clients) you can just redirect or serve the new version on submission.
It kinda boils down to what you need to guarantee while doing this. Sometimes it’s ok to go YOLO.
