Skip to content

Instantly share code, notes, and snippets.

@bryanrite
Last active Sep 25, 2021
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
@ioquatix

This comment has been minimized.

Copy link

@ioquatix ioquatix commented Aug 13, 2018

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

@stephenreid

This comment has been minimized.

Copy link

@stephenreid stephenreid commented Nov 21, 2019

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