Altering db schema in production environment

Question

I have some questions on db schema changes in production environment without taking the database server and the webapp offline.

Q1- I want to be able to make changes to database schema (adding, removing columns, normalizing tables etc) in my production environment without taking the db server and my webapp offline. What are some of the best practices to achieve this? Also are there any free tools that deal with this problem. I am using PostgresSQL.

Q2- While the db is being upgraded, will the app servers in production work against the old db or the new db. Or is there usually some sort of a hybrid using perhaps a strategy pattern in the app? What are some of the best practises for this? When would it be safe to point the appserver to the upgraded db. Will there be some sort of downtime involved or can that be avoided altogether somehow?

Thanks.

Answer 1

What I do is to build and test a script with the changes in a development server. It must upgrade the db in a totally automated and reproducible way. That includes running it with the same user with the same permissions with which it will be run in the production server. That said this question is too broad and should be asked in https://dba.stackexchange.com/

Answer 2

It really depends how big, complicated and active your database is. Bear in mind that while you are making schema changes, many of the tables will be read-only or completely inaccessible until the changes are completed. Also bear in mind that the reverse is true - your schema changes will need to acquire exclusive locks on entire tables and possibly all their relations, which may take a long time or even be impossible to acquire if the application gets in the way. Theoretically this can result in circular locks which will freeze the whole DB. Basically, if there are a lot of reads and writes going on and changes are going to cause significant disruption to your application, then you should schedule downtime for it instead.

Then you have to account for the changes your application will require. Will changing the schema require changes in the application too? Is your application small enough or modular enough that you can make those changes in increments as you change the database? Ultimately, making changes in production is really hard. Unless your app is designed for it from the ground up, it's almost universally not worth the extra effort required for the sake of some small downtime.

The other option to minimise downtime is to build a new database with an improved schema and a new app server connected to it, then build a widget to sync the data between the new database and the old one. When you are ready to deploy you simply direct traffic to the new app server. Again this is a tradeoff between how hard it is to create the system and how hard it is to accept some downtime. There are very few applications that cannot tolerate an hour or two with a "Scheduled Maintenance" message starting 2am on a tuesday night.