It very much depends on the details of your requirements.
If you have sufficient free space (at least 110% of
pg_size_pretty((pg_total_relation_size(tbl))) on disk and can afford a share lock for some time and an exclusive lock for a very short time:
Create the new table including the
uuid column with a column default.
CREATE TABLE tbl_new (
tbl_uuid uuid NOT NULL UNIQUE DEFAULT uuid_generate_v1()
, ... -- rest of you columns
I am using a function from the additional
uuid-oss module here.
The rest goes into one transaction.
Lock the table against concurrent changes in
SHARE mode (still allowing concurrent reads).
Copy the whole table while letting the column default in the new table populate the new column - possibly ordering the rows in some favorable fashion while being at it.
If you are going to reorder rows while being at it, be sure to set
work_mem as high as you can afford (just for your session, not globally).
Add constraints, foreign keys, indices, triggers etc. to new table. When updating large portions of a table it is much faster to create indices from scratch than to update iteratively.
When the new table is ready, drop the old and rename the new to make it a drop-in replacement. Only this last step acquires an exclusive lock on the old table for the rest of the transaction - which should be very short now.
It also requires that you delete any object depending on the table type (views, functions using the table type in the signature, ...) and recreate them afterwards.
-- SET work_mem = '???? MB';
LOCK TABLE tbl IN SHARE MODE;
INSERT INTO tbl_new (<list of existing columns>)
SELECT <list of existing columns>
-- ORDER BY ??
-- add constraints, indices, triggers, ...
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME tbl;
-- recreate views etc. if any
-- RESET work_mem;
This should be fastest. Any other method of updating in place has to rewrite the whole table as well, just in a much more expensive fashion. You would only go that route if you don't have enough free space on disk or cannot afford locks on the whole table.
What happens to concurrent writes?
Other transaction (in other sessions) trying to INSERT / UPDATE / DELETE in the same table after your transaction has taken the SHARE lock, will wait until the lock is released or a timeout kicks in, whichever comes first. They will fail either way, since the table they were trying to write to has been deleted from under them.
The new table has a new table OID, but concurrent transaction have already resolved the table name to the OID of the previous table. When the lock is finally released, they try to lock the table themselves before writing to it and find that it's gone. Postgres will answer:
ERROR: could not open relation with OID 123456
123456 is the OID of the old table.
If you cannot afford that to happen, you have to keep your original table.
Update in place (possibly running the update on small segments at a time) before you add the
NOT NULL constraint. Adding a new column with NULL values and without
NOT NULL constraint is cheap.
Since Postgres 9.2 you can also create a
CHECK constraint with
The constraint will still be enforced against subsequent inserts or updates
That allows you to update rows peu à peu. And finally add the
NOT NULL constraint. You can then remove the
NOT VALID CHECK constraint.
ALTER TABLE tbl ADD CONSTRAINT tbl_no_null
CHECK (tbl_uuid IS NOT NULL) NOT VALID;
-- update rows
ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL;
ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;
Prepare the new state in a temporary table,
TRUNCATE the original and refill from the temp table. You'd still take a
SHARE lock before preparing the new table to prevent losing concurrent writes.
Details in this related answer on SO or this one.