how to quickly mass update sequential numbers in postgres

Question

boss wants sequential order numbers for each merchant, starting at 1000.

Right now I'm looping through each merchant (using ruby), and updating the orders like this:

#running all of this in a migration
add_column :orders, :order_seq, :integer


Merchant.find_each do |merchant|
  order_seq = 999
  merchant.orders.order(:ordered_at).find_each do |order|
    order.update_column(:order_seq, order_seq+=1)
  end
end

I was planning to run this during a migration to set all the existing orders to have the sequential numbers populated according to their ordered_at date. I tested this on a fork of the production database and it will take average 80 ms per order update. With close to a million order records, this will incur far too much downtime.

Is there a faster way to do this with native postgres? This would be a one time migration that needs to be run once and there is nothing else concurrently going on.

I'm not a postgres expert, but is there a way to use a window function using 999+row_number() over each merchant_id and save that row_number back into order_seq column?

EDIT:

Using @Gorden-Linoff answer, but slightly modified. I realized I didn't need to use partition over merchant_id because there were only some active merchants that needed this, not the entire table. In addition the update needed to be on the orders table, not the merchants table, and the where clause can just use id not merchant_id and ordered_at.

Final solution:

  Merchant.active.find_each(batch_size: 100) do |merchant|
    statement = "update orders set order_seq = o.seqnum + 999 " +
      "from (select o.id, row_number() " +
      " over (order by ordered_at) as seqnum from orders o where o.merchant_id = #{merchant.id}" +
      ") o where orders.id = o.id"
    ActiveRecord::Base.connection.execute(statement)
  end

The result is that this operation takes 10 minutes to process 200 merchants. The old method processed about 10 merchants in 1 hour.

Answer 1

I think you can do this with native Postgres using an updatable subquery:

update merchants
    set order_seq = m.seqnum + 999
    from (select m.*, row_number() over (order by ordered_at) as seqnum
          from merchants m
         ) m
    where merchants.merchant_id = m.merchant_id and
          merchants.ordered_at = m.ordered_at;

EDIT:

If you want it to start over for each merchant id, then just use partition by:

update merchants
    set order_seq = m.seqnum + 999
    from (select m.*, row_number() over (partition by merchant_id
                                         order by ordered_at
                                        ) as seqnum
          from merchants m
         ) m
    where merchants.merchant_id = m.merchant_id and
          merchants.ordered_at = m.ordered_at;