Available strategies to upgrade a database schema without downtime
-
There is a great book on database refactoring[^] that contains a comprehensive set or recipies on how to revise databases that are supposed to be always online and may have various clients that can't be upgraded at the same time. I guess this is a typical case with large databases and I would be surpised if Amazon stops their servers just to move a column from one table to another. The book describes necessary steps for such changes. Basically it's all about creating intermediate database schemas that would be used during transition period. For example, if we need to move a column from one table to another: Version 1. Table A columns: Name, Price Table B columns: Quantity, Date Let's say we move Price to table B: Version 2. Table A columns: Name Table B columns: Quantity, Date, Price The book suggests an intermediate version: Version 1_2. Table A columns: Name, Price Table B columns: Quantity, Date, Price Additional trigger that will synchronize "Price" columns between A and B. Version 1_2 can be used by both clients written for version 1 and 2. Software developers don't need to rush their upgrades, transition can last months and include several changes. This technique requires accuracy in version control management, but looks very good to implement non-interruptible database schema upgrade. I wonder if this is the only option available for data schema upgrade with no downtime. I can't think about anything else - it this how large data warehouses updata their databases?
Вагиф Абилов MCP (Visual C++) Oslo, Norway If you're in a war, instead of throwing a hand grenade at the enemy, throw one of those small pumpkins. Maybe it'll make everyone think how stupid war is, and while they are thinking, you can throw a real grenade at them. Jack Handey.
-
There is a great book on database refactoring[^] that contains a comprehensive set or recipies on how to revise databases that are supposed to be always online and may have various clients that can't be upgraded at the same time. I guess this is a typical case with large databases and I would be surpised if Amazon stops their servers just to move a column from one table to another. The book describes necessary steps for such changes. Basically it's all about creating intermediate database schemas that would be used during transition period. For example, if we need to move a column from one table to another: Version 1. Table A columns: Name, Price Table B columns: Quantity, Date Let's say we move Price to table B: Version 2. Table A columns: Name Table B columns: Quantity, Date, Price The book suggests an intermediate version: Version 1_2. Table A columns: Name, Price Table B columns: Quantity, Date, Price Additional trigger that will synchronize "Price" columns between A and B. Version 1_2 can be used by both clients written for version 1 and 2. Software developers don't need to rush their upgrades, transition can last months and include several changes. This technique requires accuracy in version control management, but looks very good to implement non-interruptible database schema upgrade. I wonder if this is the only option available for data schema upgrade with no downtime. I can't think about anything else - it this how large data warehouses updata their databases?
Вагиф Абилов MCP (Visual C++) Oslo, Norway If you're in a war, instead of throwing a hand grenade at the enemy, throw one of those small pumpkins. Maybe it'll make everyone think how stupid war is, and while they are thinking, you can throw a real grenade at them. Jack Handey.
That's an interesting question, and one that I've struggled to deal with. A few questions about the technique you mention the book suggests: When/how do you initialize the data (Table B.Price, from your example)? Is the answer as obvious as simply setting the Price field? More importantly, if the DB is live, how do you make the change while the DB is possibly being used? Again, it seems straight forward--add the field to the other table and create the trigger. But what if the field is not null? Do you make it nullable at first, initialize B.Price, then make it not null? And how do you test the trigger, so it doesn't fail the transaction? I'm assuming with a test DB first. But in more complicated scenarios, does the test DB really mirror all the scenarios of the live DB? Seems simple enough, but it often isn't. What about other triggers/stored procedures that will be deprecated when a.Price is finally removed? When are those updated, especially regards to ensuring that some transaction doesn't come in at the moment you're changing the trigger? These are simplistic questions with obvious answers, but I'm not convinced that it is a simple/obvious answer in some cases (not that I can think of any right now). Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
That's an interesting question, and one that I've struggled to deal with. A few questions about the technique you mention the book suggests: When/how do you initialize the data (Table B.Price, from your example)? Is the answer as obvious as simply setting the Price field? More importantly, if the DB is live, how do you make the change while the DB is possibly being used? Again, it seems straight forward--add the field to the other table and create the trigger. But what if the field is not null? Do you make it nullable at first, initialize B.Price, then make it not null? And how do you test the trigger, so it doesn't fail the transaction? I'm assuming with a test DB first. But in more complicated scenarios, does the test DB really mirror all the scenarios of the live DB? Seems simple enough, but it often isn't. What about other triggers/stored procedures that will be deprecated when a.Price is finally removed? When are those updated, especially regards to ensuring that some transaction doesn't come in at the moment you're changing the trigger? These are simplistic questions with obvious answers, but I'm not convinced that it is a simple/obvious answer in some cases (not that I can think of any right now). Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh SmithMarc, I am not trying to oversimplify the challenge. And by the way the author of the book reminded several times: test, test, test! Of course there must be rollback, but since the upgrade strategy is based on data redundancy and does not break compatibility with existing clients, risk of screwing up the production environment due to unsuccessful upgrade is not very high. Actually it is much lower than when you stop the whole system and must be up again with new data within limited time. It's also clear that during transition period some constraints have to be disabled. Nullability, foreign keys etc. They will be reenabled later when the transition period is over and database is in its real "version 2" state.
Вагиф Абилов MCP (Visual C++) Oslo, Norway If you're in a war, instead of throwing a hand grenade at the enemy, throw one of those small pumpkins. Maybe it'll make everyone think how stupid war is, and while they are thinking, you can throw a real grenade at them. Jack Handey.