News for hackers. Stuff that matters.
Re:The Ten Rules of Schema Growth by dekhn on Tuesday October 31, 2023 @07:50PM (#38092887)
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.

Re:The Ten Rules of Schema Growth by codethief on Tuesday October 31, 2023 @07:54PM (#38092911)
> the change was broken into thousands of little changes

Could you elaborate on how this worked?

Re:The Ten Rules of Schema Growth by dekhn on Tuesday October 31, 2023 @08:39PM (#38093250)
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...

Re:The Ten Rules of Schema Growth by djbusby on Tuesday October 31, 2023 @08:13PM (#38093077)
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.

Re:The Ten Rules of Schema Growth by cdchn on Tuesday October 31, 2023 @09:06PM (#38093422)
>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.

Re:The Ten Rules of Schema Growth by smfjaw on Tuesday October 31, 2023 @10:32PM (#38093938)
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