Ecto migration with default value from another column in same table

Hi,

I’ve a few million records, and I’m trying to add a column, uuid, to their table that has a default value of each row’s id field. Is this possible? And if not, would it be possible to give it a default value that increments?

My aim is to have unique values in the new UUID column so I can then add a unique index.

Thanks in advance.

Probably it will be best to add the column with a default value of gen_random_uuid() and then that way they are actually UUIDs.

1 Like

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

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

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

1 Like

Will do, does this just reduce the overall size of the DB?

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.

4 Likes

OK that sounds like a pretty tangible benefit, have changed, thanks again.

@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.

2 Likes

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

1 Like

Hi @benwilson512 @marciotrindade, could you suggest a book on designing(data modelling) and working with databases(especially Postgres)? I would like to get better at dealing with DB’s in an efficient way.

Thank you both of you & have a great day!