For information, those kind of query modifying all the table's records cannot really be done that way (well i guess, given a "certain" threshold of data).

With discussing with @olasd, it was done on range with something like (from the db server itself):

i=0; while (( i > 90000 )); do min=$((i * 1000)); max=$(( (i+1) * 1000)); echo "[$min,$max]"; psql -p 5432 -c "update origin_visit set type = origin.type from origin where origin_visit.origin = origin.id and origin_visit.type is null and origin_visit.origin >= $min and origin_visit.origin < $max" softwareheritage; i=$(( i + 1 )); done

Note:

• we have roughly 90M origins, thus the upper bound mentioned (given a multiple of 1000 which is the interval range used).
• i did use the pbouncer (that's one more check it works ;)

It started yesterday around 1pm and it got finished this morning (i started 2 kinds of those script, 1 ascending, the other descending).
I had to make an extra pass to ensure there were no more holes (there was, either possibly because i stopped it to improve it with logs, either because the loaders were not completely up-to-date, the client swh-storage package on workers was not for example, i'm not sure).

In any case, migration done btw.