Comment by rosser

One of my favorite idioms lately for doing bulk updates without incurring lock contention is to chain CTEs, like so:

  with candidate_rows as (
     select id
       from table
      where conditions
      limit 1000
        for update nowait
  ), update_rows as (
     update table
        set column = value
       from candidate_rows
      where candidate_rows.id = table.id
  returning table.id
  )
  select count(1) from update_rows;

...and loop on issuing that query until the "count(1)" returns zero some for number of iterations (three works pretty well).

Want to add a column to your "orders" table and populate it without blocking concurrent writes for as long as it will take to rewrite a multi-million row table? Want to re-hash user passwords using something stronger than MD5, but not prevent users from ... you know, logging in for the duration?

CTEs are all that and the bag of chips.