Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Safe Postgres Operations on High Volume Tables

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment