Originally taken from: Braintree Article and expanded on by me.
- 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
|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
||We use pg_repack instead