MySQL Alter Table – How to Alter Table with Casting

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

• Script 1 can be executed on any slave that do not have binary logs enabled
• Script 2 can be executed on any slave that does have binary logs enabled
• Script 3 can be executed on a master or anywhere else

Give it a Try !!!

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;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

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.