What we would do when having to update a large table is do it in multiple
transactions. Basically:
1. create sequence s1;
2. alter table t1 add column c1 integer default nextval('s1'); -- Ensure
new values don't need changing anymore
3. In batches of a significant but not huge amount of rows: update t1 set
c1=nextval('s1');
4. Create index
5. Convert the index into pk index
We use niceupdate (home brew tool) for this, it may suit your needs:
https://github.com/zalando/acid-tools/wiki/Niceupdate
Regards,
Feike