2 ms·

Many of these rules are things that only became obvious after I worked on an extremely large production database where downtime was counted in millions of dolla

by dekhn 3y ago

Many of these rules are things that only became obvious after I worked on an extremely large production database where downtime was counted in millions of dollars an hour, we didn't know who all our users were, some unknown users were load-bearing, and we had a bunch of postmortems describing historical incidents. 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.

codethief 3y ago
> the change was broken into thousands of little changes Could you elaborate on how this worked?
- djbusby 3y ago
  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.
- dekhn 3y ago
  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/AdministratorsGuide/TrickleLoading/TrickleLoadingData.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Ad...
cdchn 3y ago
>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.
smfjaw 3y ago
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

by dekhn 3y ago

codethief 3y ago

> the change was broken into thousands of little changes Could you elaborate on how this worked?

djbusby 3y ago
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.
dekhn 3y ago
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/AdministratorsGuide/TrickleLoading/TrickleLoadingData.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Ad...

cdchn 3y ago

>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.

smfjaw 3y ago

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