HOW-TO: Avoid Dangerous Database Migrations

This is unsafe.

This will cause the entire table to be re-written to disk.

Safe alternative:

1. ALTER TABLE table ADD COLUMN column INT;
2. Set the value on existing rows in batches:
   UPDATE table SET column = 0 WHERE id >= 0 AND id < 1000;

Since PostgreSQL 11 this is safe.

This is unsafe.

This will cause the entire table to be re-written to disk.

An ACCESS_EXCLUSIVE lock is acquired during this.

This blocks all queries.

Alternative: make it NULL first—still slow because a full table scan is required (faster than a table re-write).

There is no way to do this without downtime.

Since PostgreSQL 11 this is safe.

This is unsafe.

This will cause a full table scan.

An ACCESS_EXCLUSIVE lock is acquired during this.

This blocks all queries.

The closest alternative is to create table constraint instead.

The downsides are: 0.5-1% performance hit, and the NOT NULL is fairly invisible.

1. SET statement_timeout = "3000";
SET lock_timeout = "3000";
ALTER TABLE table ADD CONSTRAINT ck_constraint_name CHECK (column IS NOT NULL) NOT VALID;
2. SET statement_timeout = "0";
SET lock_timeout = "0";
VALIDATE CONSTRAINT ck_constraint_name;

Upgrading to PostgreSQL 12 allows the suboptimal constraint to be replaced.

This is unsafe.

An ACCESS_EXCLUSIVE lock is acquired during this.

This blocks all queries.

A full table scan is required which takes a long time.

Since PostgreSQL 12 it is possible to use an existing constraint to help.

Safe alternative:

1. SET statement_timeout = "3000";
SET lock_timeout = "3000";
ALTER TABLE table ADD CONSTRAINT ck_constraint_name CHECK (column IS NOT NULL) NOT VALID;
2. SET statement_timeout = "0";
SET lock_timeout = "0";
VALIDATE CONSTRAINT ck_constraint_name;
3. ALTER TABLE table ALTER column SET NOT NULL;
4. SET statement_timeout = "3000";
SET lock_timeout = "3000";
ALTER TABLE table DROP CONSTRAINT ck_constraint_name;

This is unsafe.

A SHARE lock is acquired for this operation.

This blocks writes on the table.

A full table scan is required which takes a long time.

A good alternative is to create the index concurrently.

The downside is that more total work is performed and it takes longer overall.

There are other caveats in the PostgreSQL documentation, but I feel they are unlikely to be a problem.

1. SET statement_timeout = "0";
SET lock_timeout = "0";
CREATE INDEX CONCURRENTLY name_idx ON table (column);
2. Check the index was created successfully:
   \d table
   Invalid indexes are marked with INVALID.
3. If it was unsuccessful, try again after dropping the index:
   DROP INDEX name_idx;