Safe Postgres Operations on High Volume Tables

safe.md

Originally taken from: Braintree Article and expanded on by me.

Safe

• Add a new column
• Drop a column
• Rename a column
• Add an index concurrently (Example), Note: it will still take a long time to run the migration, but it won't write-lock the table.
• Drop a constraint (for example, non-nullable)
• Add a default value to an existing column

Unsafe

Operation	Work Around
Add an index	Add the index using the CONCURRENTLY keyword
Change the type of a column	Add a new column, change the code to write to both columns, and backfill the new column
Add a column with a default	Add column, add default as a separate command, and backfill the column with the default value
Add a column that is non-nullable	Add a default, enforce non-nullable in code. Backport all null columns in a separate process, when fully backported, add NOT NULL constraint. This takes a short amount of time to run as it has to scan the table once, but usually isn't TOO bad.
Add a column with a unique constraint	Add column, add unique index concurrently, and then add the constraint onto the table
VACUUM FULL[3]	We use pg_repack instead

Rename table and rename database are also safe from my experience.

Needs Updating; As of Postgres 11.

Add a column with a default	Add column, add default as a separate command, and backfill the column with the default value
Add a column that is non-nullable	Add a default, enforce non-nullable in code. Backport all null columns in a separate process, when fully backported, add NOT NULL constraint. This takes a short amount of time to run as it has to scan the table once, but usually isn't TOO bad.

Renaming a column isn't safe. It triggers ACCESS EXCLUSIVE lock.

As per PG (11+) docs https://www.postgresql.org/docs/11/sql-altertable.html, renaming a column doesn't acquire a lock.