Alter Table Without Locking the Table

ALTER TABLE without locking the table?

The only other option is to do manually what many RDBMS systems do anyway...

- Create a new table

You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it's not a lock...)

Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.

Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.

EDIT:

Some comments have been made about this limitation being a bit poor. So I thought I'd put a new perspective on it to show why it's how it is...

• Adding a new field is like changing one field on every row.
• Field Locks would be much harder than Row locks, never mind table locks.
  
  
  
• You're actually changing the physical structure on the disk, every record moves.
• This really is like an UPDATE on the Whole table, but with more impact...

Create a Primary Key on a large table (6Million records) without locking the table in PostgreSQL

Try to do it in two steps:

CREATE UNIQUE INDEX CONCURRENTLY pkey_name
   ON schema_name.table_name (field_pkey_name);

ALTER TABLE schema_name.table_name
   ADD CONSTRAINT pkey_name PRIMARY KEY USING INDEX pkey_name;

It will still take a long time (even longer), but the table will not be locked.

Related Topics