☚ Back to Book

HOW-TO: Avoid Dangerous Database Migrations

For PostgreSQL versions 9.5 - 12

Database migrations can seem safe on tables with little read and write activity.
However, in production the exact same migration can be deadly for busy tables.

This is only a HOW-TO guide; a more detailed understanding can be obtained from the references.

Consult the table of contents before creating an automated migration.

General Advice...
  1. Deployments should do the following things in order:
    1. Migrate the database successfully
    2. Then, and only then, deploy the new code
  2. Application code should be forwards-compatible with the new schema.
  3. Database schemas should be backwards-compatible with the old code.
  4. Use timeouts for all automated migrations:
    SET statement_timeout = "5000";
    SET lock_timeout = "2000";

Create a NULL Column with a DEFAULT

ALTER TABLE table ADD COLUMN column INT DEFAULT 0;

(Setting a default value after adding the column is different—it won't apply the default to existing rows.)

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.

Create a NOT NULL Column

ALTER TABLE table ADD COLUMN column INT DEFAULT 0 NOT NULL;

(A default value is required.)

This is much better than: NULL ➜ Backfill Default ➜ NOT NULL.

It is literally better to use an incorrect default value.

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.

Make a NULL Column NOT NULL

ALTER TABLE table ALTER column SET NOT NULL;

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;

Create an Index

CREATE INDEX name_idx ON table (column);

CREATE UNIQUE INDEX name_idx ON table (column);

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;

Create a Constraint

NOT VALID, then VALIDATE CONSTRAINT

Not tried.

Create a Foreign Key

Not tried.

Requires lock on foreign key target.

Drop a NOT NULL Column

Problem with ORM reading and writing field.

Safe alternative: make null first, then follow drop a column.

Drop a Column

Problem with ORM reading and writing field(?)

Problem with ACCESS EXCLUSIVE lock (waiting issue)

Rename a Column

...