Safe Operations For High Volume PostgreSQL
- 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
| 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 |
Originally taken from: Braintree Article.