Tips to minimize table lock for DDL on large tables for PostgreSQL

Here are some tips to minimize table lock for DDL on large tables:

• NOT VALID for FOREIGN KEY/CHECK CONSTRAINT
• CREATE INDEX CONCURRENTLY
• UNIQUE/PRIMARY KEY CONSTRAINT using pre-created index

Examples:

mytest=# alter table t add constraint t_fk foreign key (id) references p(id) not valid;
ALTER TABLE
Time: 4.706 ms

mytest=# alter table t validate constraint t_fk;
ALTER TABLE
Time: 279.145 ms

mytest=# create unique index concurrently t_pk on t(id) ;
CREATE INDEX
Time: 201.014 ms

mytest=# alter table t add constraint t_pk primary key using index t_pk;
ALTER TABLE
Time: 4.386 ms