Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do you make schema changes to a live database without downtime?

For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the email addresses to a new dedicated table, I'd have to change the schema and then migrate the email data across to the new table. How could this be done without stopping writes to the original table? Surely while data is written over from the old table to the new one, new data would continue to be written to the old table and be missed, right?

I guess this problem comes up pretty frequently but I can't find any standard solution for dealing with it.

This article deals with the problem but I didn't really understand step 3. He says to write to both tables, then migrate old data from the first table to the new one. How do you make sure you're only migrating old data?

(I use PostgreSQL on Heroku.)

share|improve this question

migrated from stackoverflow.com Jan 18 '12 at 22:48

This question came from our site for professional and enthusiast programmers.

2  
Facebook developed a tool to do this for MySQL. – Nick Chammas Jan 18 '12 at 23:27
2  
K. Scott Allen wrote about a system for managing schema versions here. I created DbUpdater, an open source tool for version-aware schema deployment. More here - http://www.tewari.info/dbupdater – ash Jan 22 '12 at 21:30
    
@NickChammas Thanks for sharing that. I have a lot of questions on it. Could you please suggest a more detailed tutorial, a video preferably, which explains the things like bit log, non clustered indexes, and answers questions like - 1. How will selecting data from source table into an outfile reduce load as compared to copying to destination table directly. 2. When will the copy phase end? These are only a few questions I have and I have only started reading it. – Sandeepan Nath May 13 at 18:58
    
@SandeepanNath - Sorry, I'm not that familiar with Facebook's tool and so can't point you to more resources. I read an announcement about it and posted my comment years ago, but I've never used it. – Nick Chammas May 13 at 19:10
up vote 18 down vote accepted

You almost have your answer already:

  1. Create the new structure in parallel
  2. Start writing to both structures
  3. Migrate old data to the new structure
  4. Only write and read new structure
  5. Delete old columns

As for step 3, use something like this (in one transaction):

Insert what is not there yet:

INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM   old_tbl
WHERE  NOT EXISTS (SELECT * FROM new_tbl WHERE new_tbl.old_id = old_tbl.old_id);

Update what has changed in the meantime:

UPDATE new_tbl
SET    data  = old.data
USING  old_tbl
WHERE  new_tbl.old_id = old_tbl.old_id
AND    new_tbl.data IS DISTINCT FROM old_tbl.data;

New data will not be touched, because it is identical in both places.

share|improve this answer
    
I have a few questions while trying to understand the scenario for which you proposed this answer - 1. Will the code changes be deployed along with the start of the db changes? 2. Why will there be a need to write to both the structures? 3. Why cannot the new structure be brought up first and then the existing data be migrated and then the code changes be deployed which will populate the new structure? 4. Why is there a need to find out what is not there (your 1st query)? Are you proposing insertion in multiple attempts? – Sandeepan Nath May 13 at 18:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.