Smaller size is the first order impact (the string representation is twice as big as the binary representation) but at large scale the smaller size directly translates into improved query performance because the indices are smaller and thus faster to traverse.

Thanks for your help. I ended up with this which works as intended:

  add :uuid, :string, null: false, default: fragment("gen_random_uuid()")

@MarkMekhaiel If it is a table with some records (a lot of them), my recommendation is to do it with more than 1 migration to avoid having your table locked.

My suggestion is:

1. Create a new UUID column that does not have a default value and can be null.
2. Run a script to generate value for empty records in a batch of some records until all of them are updated.
3. In the same migration (using the same transaction, yes Postgres uses transactions for DDL) run your script again and change the UUID column to be not null and having gen_random_uuid() as a default value.

That way you can avoid changes in a table with a lot of records and lock the table during the generation of those values.

I would strongly suggest using the binary_id type which will much more compactly store the underlying UUID.

Thanks for the advice, I ended up copy and pasting from Backfilling Data · Fly - batching every 2500 and throttling inbetween with a temp table to store the ID’s of completed records