When adding a new column and assigning values to it, you may be tempted to write something like: (I'm using postgres)
-- Add cost column ALTER TABLE products add COLUMN cost DECIMAL; update products set cost = 0.00; ALTER TABLE products ALTER COLUMN cost SET NOT NULL;
These three queries are not the fastest way of doing this. It requires several passes through the table on disk, the first time it adds the column, assigning null to each row. The next time it goes through them all changing the value to be "0.00". It then sets the table to be not-null.
It can be done a lot faster by assigning the default value at the same time as adding the column.
-- Add cost column ALTER TABLE products add COLUMN cost DECIMAL NOT NULL DEFAULT 0.00; ALTER TABLE products ALTER COLUMN cost DROP DEFAULT;
the second line here can even be omitted if you don't mind the default still being there, not that it takes a long time.
Similarly I saw recently one where all the existing data had to have a different value from the new default. This was implemented as:
ALTER TABLE products ADD COLUMN new_flag BOOLEAN DEFAULT false; UPDATE products SET new_flag=true;
It was made significantly faster by changing it to
ALTER TABLE products ADD COLUMN new_flag BOOLEAN DEFAULT true; ALTER TABLE products ALTER COLUMN new_flag SET DEFAULT false;
which does the same thing, but only scanning through the table once.