How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?

I know the correct way is to backup everything schedule downtime and do then do the changes.

But... does any current database system support doing these things "on-line" without stopping anything? (maybe just delaying the queries that reference a column that is just being changed/deleted)

And what does it happen when I just do an ALTER TABLE... on a live running database? Does everything stop when this happens? Can data get corrupted? etc.

Again, I'm mostly referring to Postgres or MySQL as these are what I encounter.

(And, yes, anytime I had to do this before I did it "the right way", backing things up, scheduling downtine etc. ...but I just want to know if it's possible to do this sort and things "quick and dirty" or if there is any DB system that actually has support for "quick, live and dirty" schema changes)

Someone just suggested Online Schema Change for MySQL from Facebook script (with a tutorial here and source here)... seems like a nice way to automate a the set of "hacky" ways to do it... has anyone ever used it in something resemblig production?

  • 3
    Note: the "correct way" specificified is relative to MySQL and not to PostgreSQL. The "correct way" in PostgreSQL is typically very easy, though it can be involved. Use of pg_reorg can help with the more difficult scenarios.
    – Sean
    Oct 19, 2012 at 5:17
  • I would have loved to have a detailed video on this, with someone explaining as many strategies as possible. May 13, 2016 at 10:16

6 Answers 6


When you issue an ALTER TABLE in PostgreSQL it will take an ACCESS EXCLUSIVE lock that blocks everything including SELECT. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE, CHECK or FOREIGN KEY constraints need expensive full-table scans to verify, etc.

If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.

Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t and you want to change column customercode integer NOT NULL to text because the customer has decided all customer codes must now begin with an X, you could write:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:

ALTER TABLE t ADD COLUMN customercode_new text;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;

This will only prevent writes to t during the process; the lock name EXCLUSIVE is somewhat deceptive in that it excludes everything except SELECT; the ACCESS EXCLUSIVE mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE, but at worst you'll just have to do it again.

You can even avoid that lock and do the whole thing live by creating a trigger function on t that whenever an INSERT or UPDATE comes in, automatically populates customercode_new from customercode.

There are also built-in tools like CREATE INDEX CONCURRENTLY and ALTER TABLE ... ADD table_constraint_using_index that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.

The pg_reorg tool or its successor pg_repack can be used for some table restructuring operations as well.

  • 1
    The key thing in what @Craig said was, "if it oesn't require re-writing." Using an ALTER TABLE t ADD COLUMN i INT is a fast operation (typically <1ms) once the lock is acquired. Acquiring the lock can queue up connections, however, so it's not "free" ... though it's world's better than what you have to do in MySQL. Adding a NOT NULL constraint is more difficult and not for the feignt of heart.
    – Sean
    Oct 19, 2012 at 5:16
  • It seems to be consensus that pg_repack is the improved successor of pg_reorg. Apr 9, 2013 at 23:47
  • Good answer, regarding adding a column with a default (or computed) a less "blocking" way is to create a entire new table, block the old table to insert/update/delete but allowing selects and populate the new. Finally issue a brief exclusive lock on old table for select, delete it and rename new to old. Depending on your scenario you can even start to populate the new without blocking inserts in the old and issue that exclusive lock just while solving the diff (hopefully just inserting a few new records)
    – jean
    Jan 29, 2018 at 13:04

Percona has comes up with its own tool for performing online schema changes

The tool is called pt-online-schema-change

It involves triggers, so please read the documentation carefully.

According to the Documentation, the major operations done are

  • Sanity checks
  • Chunking
  • Online schema change
    • Create and alter temporary table
    • Capture changes from the table to the temporary table
    • Copy rows from the table to the temporary table
    • Synchronize the table and the temporary table
    • Swap/rename the table and the temporary table
    • Cleanup
  • thanks, seems like a "soldified" version of Facebook's approach that I could trust more...
    – NeuronQ
    Oct 18, 2012 at 16:12
  • 1
    pt-online-schema-change is definitely the preferred way of doing this if you are running your own MySQL server. As of Percona Tools 2.2, (sadly) they do not support RDS/Aurora on AWS. pt-online-schema-change inserts a trigger on the source table to copy rows (low priority for MyISAM) to the destination table_temp and does a single quick locking drop and rename at the end when all rows are in sync between the source and destination tables.
    – phpguru
    Mar 21, 2016 at 18:46

Shutting the system down and doing all changes at once may be very risky. If something goes wrong, and frequently it does, there is no easy way back.

As an Agile developer, I sometimes need to refactor tables without any downtime at all, as those tables are being modified and read from.

The following approach has low risk, because the change is done in several low-risk steps that are very easy to roll back:

  • Make sure that all the modules accessing the table are well covered with automated tests.
  • Create a new table. Alter all procedures that modify the old table, so that they modify both old and new tables.
  • Migrate existing data into new structure. Do it in smallish batches, so that it does not seriously impact the overall performance on the server.
  • Verify that the migration of data succeeded.
  • Redirect some of the selecting procedures from the old table to the new ones. Use automated tests to make sure that the changed modules are still correct. Make sure their performance is acceptable. Deploy the altered procedures.
  • Repeat the previous step until all the reports use the new table.
  • Change the procedures that modify the tables, so that they only access the new table.
  • Archive the old table and remove it from the system.

We have used this approach many times to change large live production tables without downtime, with no issues at all.

  • 4
    great... but that's exactly the type of "pain" that I'm looking to avoid :)
    – NeuronQ
    Oct 18, 2012 at 16:14
  • 1
    @NeuronQ "There is no easy way back" - there is in Postgres: simply put everything into a transaction and rollback if anything goes wrong. Sep 25, 2018 at 16:14

Yes, many modern databases will allow you to just add a column or change the characteristics of a column, like adding or removing nullable.

If you drop a column, data will be lost, but there is not much fear of corruption.


The Percona tool uses triggers to aid in it's altering, and it doesn't play nicely if your table already has existing triggers. I ended up having to write one that actually handles existing triggers well, since they're super important to our database https://github.com/StirlingMarketingGroup/smg-live-alter


To address the question about what happens with an ALTER TABLE statement, it depends on the extent of your changes. In specific cases, if you add a new column, at least in MS SQL Server, the engine will create a temporary copy of the table, while it creates the new table definition, and then inserts the data back in there. For the duration of the change, the table thus would be inaccessible to users.

An example of the specific operations for MSSQL server is here: http://support.microsoft.com/kb/956176/en-us

I would assume that other RMDBs have similar methods, though the exact implementation would be something you would have to verify with the vendor documentation.

  • -1 This is completely wrong for SQL Server: "If you add a new column, at least in MS SQL Server, the engine will create a temporary copy of the table, while it creates the new table definition, and then inserts the data back in there"
    – A-K
    Oct 18, 2012 at 14:09
  • @AlexKuznetsov - I figured the preceding line, as well as the link with some of the cases listed would clarify this does not always happen. I amended the sentence to better reflect this.
    – SchmitzIT
    Oct 18, 2012 at 14:17
  • 1
    You are mentioning the behavior of the GUI, SSMS, not the behavior of SQL Server itself. Following your link, the advice is to use T-SQL directly to make DDL changes. SSMS is not a very good tool for changing DDL.
    – A-K
    Oct 18, 2012 at 15:05
  • @AlexKuznetsov - I read the article as saying that there's risks involved, but not as a discouragement. Anyway, I didn't link the article for the GUI bit, but as an indication of some of the operations that lead to an ALTER statement leading to the creation of a temporary table due to changes in the underlying data structure. I haven't tested whether the exact same thing applies when issuing the statement directly from T-SQL, but I would think the process is pretty similar and that SL Server does the legwork behind the scenes.
    – SchmitzIT
    Oct 18, 2012 at 16:18
  • You can start Profiler, execute ALTER TABLE statement directly, and see what is happening. Then you can alter a table via a dialog box, and see for yourself the commands being executed.
    – A-K
    Oct 19, 2012 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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