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

  • 3
    Facebook developed a tool to do this for MySQL. Jan 18, 2012 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, 2012 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. May 13, 2016 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. May 13, 2016 at 19:10
  • Facebook's OnlineSchemaChange tool, linked by @NickChammas above, has a pretty good explanation here: How OSC works
    – djvg
    Feb 25, 2021 at 8:51

1 Answer 1


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.

  • 1
    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? May 13, 2016 at 18:42
  • 3
    @SandeepanNath, to answer question 3 in your comment: because if you (a) bring up new structure, (b) migrate data to it, (c) change your code to write data to the new structure instead of the old, then all data changes made between step b and step c will only exist in the old structure. The question was how to make schema changes without downtime. Read this answer again, carefully.
    – Wildcard
    Oct 17, 2017 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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