What can ALTER TABLE do, and what does each operation cost?
It can add columns, drop columns, rename columns, change the type of a column, and add or remove rules. The cost of each one is very different.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'open'
);
-- Add a column. Cheap in the modern engines.
ALTER TABLE tickets ADD COLUMN assigned_to INTEGER;
-- Change the type of a column. This rewrites the whole table.
ALTER TABLE tickets ALTER COLUMN title TYPE VARCHAR(320);
-- Add a rule. This checks every row.
ALTER TABLE tickets ADD CONSTRAINT title_not_blank CHECK (title <> '');
So what does each one cost? Four things to know.
Adding a column is cheap in the modern engines, because they write down that the column is missing rather than filling it in. It takes a very short lock. In the older engines it rewrites the table, so on a big table it is slow.
Changing the type is always expensive, because the engine has to work out the new value for every row and write the whole table again. It holds a lock for as long as that takes, and on a big table that can be an outage.
Adding a rule has to look at every row to see whether it breaks the rule. It holds a lock while it does that, so it can be a long wait on a big table.
Dropping a column is usually cheap, because the engine marks it as gone and stops reading it. It is only expensive if you ask for the space to be given back, which is a separate operation.
operation what it costs how long it locks
---------------------- ------------------------ ------------------
add a column little or nothing briefly
drop a column little briefly
rename a column little briefly
change a column type rewrites every row a long time
add a check rule reads every row a long time
add an index builds the whole index a long time
The next question in this topic looks at how to make these changes while the application is still running.