Add a new column (safe) - This operation will not block table and can be done safety. But exists some cases, which can lock your table.
Add a column with a default (unsafe) - Adding a column with a default requires updating each row of the table (to store the new column value). For big table this will create long running operation that locks it. So if you intend to fill the column with mostly non default values, it's best to add the column with no default, insert the correct values using UPDATE (correct way is to do batched updates, for example, update 1000 rows at a time, because big update will create table-wide lock), and then add any desired default.
Add a column that is non-nullable (unsafe) - same as add a column with a default. To make this operation without locking, you can create a new table with the addition of the non-nullable column, write to both tables, backfill, and then switch to the new table. This workaround is incredibly onerous and need two times more space than is a table takes.
Drop a column (safe) - Dropping a column is very quick, but PostgreSQL won't reclaim the disk space until you run a VACUUM FULL
Change the type of a column (unsafe) - It is not strictly unsafe for all changes. Changing the length of a varchar, for example, doesn't lock a table. But if column type change requires a rewrite or not depends on the datatype, in this case this operation requires updating each row of the table. As workaround, you can add new column with needed type, change the code to write to both columns, and backfill the new column.
Add a default value to an existing column (safe).
Add an index (unsafe) - Normally, PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table. PostgreSQL supports building indexes without locking out writes, as well. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.
Add a column with a unique constraint (unsafe) - This operation will lock table. As workaround, you can add column, add unique index concurrently, and then add the constraint onto the table.
Drop a constraint (safe)
Vacuum full (unsafe) - Resolve using pg_repack
ALTER TABLE SET TABLESPACE (unsafe) - Resolve using pg_repack
Source
UPDATE(correct way is to do batched updates, for example, update 1000 rows at a time, because big update will create table-wide lock), and then add any desired default.VACUUM FULLCONCURRENTLYoption ofCREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.pg_repackALTER TABLE SET TABLESPACE(unsafe) - Resolve usingpg_repack