On Friday, October 30, 2015 at 2:50:19 AM UTC-7, Janko Marohnić wrote:
>
> Hi Jeremy,
>
> We have a fairly large database in our application (about 5 million rows),
> so when we're doing data migrations via a UPDATE statement, we have to do
> it in batches so that Postgres doesn't lock the table for the rest of the
> application. I was just wondering what's the best way to do it with Sequel
> and Postgres?
>
> Currently we take pages of 1000 by an indexed ID column, but I think it
> gives Postgres more work, because it always has to find the next page "by
> hand", since it doesn't know where it last took off. I was thinking
> Postgres cursors, that I just move it forward by pages, but I'm not that
> familiar with how to use them for updates. Is it possible to do something
> like that in Sequel?
>
If you can run a single UPDATE statement for multiple rows, and just don't
want to do the whole table at once:
ds = DB[:table]
m = ds].max(:id)
(m/1000).times do |i|
ds.where(:id=>(i*1000)...((i+1) * 1000)).update(...)
end
That should work OK from a performance standpoint unless the id column is
sparse.
If you need to do a separate update query per row, maybe you can use the
pagination extension with each_page, and use a transaction around the
separate UPDATE statements for each page.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
