We used something similar so far, and I felt like it was too slow, but it turned out I was wrong, I just didn't give it enough time to estimate the performance.
Thanks! On Fri, Oct 30, 2015 at 4:37 PM Jeremy Evans <[email protected]> wrote: > 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 a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/ZHsIv1wa6rI/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
