> the change was broken into thousands of little changes
Could you elaborate on how this worked?
Literally, there were many thousands of "UPDATE WHERE" each targeting a different row, and the file was sharded, and then some process would read through a shard and apply statements one at a time with a delay. It would export stats (# of updates, # of errors) and could be easily paused/resumed.
We called it trickling, see https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Ad...
Not OP but here what we have is some routine to get a fact from the DB (eg Customer->getOrders()) and the routine is schema aware. On v0 it queries a table with one rules, on v1 it's a different slightly different query. So, one small schema mod to eg add a column. Roll out. Another schema mod to populate, possibly a few days later, then roll out queries over a few days, then, later slowly remove the dead parts of the schema and then later drop the legacy code using the old columns. The process can take weeks. And some throwaway code (scaffolding I say) has to pass through the system. I recently was able to clean up and old view that took months to process through (we're a small team)
One recent one was moving from bitflags in an int column to flags in a jsonb column. Tedious.
What makes it work? Testing, testing, testing and management that gives time for that process.
>Instead of changing all the rows in a single big transaction, the change was broken into thousands of little changes that were rolled out over a series of days.
Having been in a situation where a large schema update required migrating data in a transaction, which then broke for some data, but the data was so big that rolling back the transaction caused the database to become totally hosed, I can see this technique being very attractive.
I really really like partitioned data formats like Parquet/Delta etc that allow this for this reason. Often do stuff like this in our data warehouse but hadn't thought of 'psuedo partitioning' the data like this on an operational DB
I don't think the article mentions it, but one other technique we used- which still is a big question mark to me- is that we "trickled" changes to the database. Instead of changing all the rows in a single big transaction, the change was broken into thousands of little changes that were rolled out over a series of days. The reason for this is that if there is an unexpected problem, you have more time to stop the change and mitigate the damage.