On Friday, April 22, 2016 at 12:32:33 PM UTC+3, Tiago Cardoso wrote: > > Hi Jeremy, > > > We've recently had some issues inserting/updating multiple elements in our > vanilla rails/pg app (yes, with active record), in that per record, we'd > try to find or create a new instance, pass the results, save in db, and > execute all callbacks on the AR side (bubbling up timestamps, update those > counter caches, yadayada). Fairly vanilla, unbounded number of queries, > very low performance (depending of the number of multiple records, but > sometimes I'd get times of 60 seconds (!) for job completion). As this was > consumption of internal service, it wasn't apparent for the user, reason > why we ignored the performance. > > Our main drawback was the lack of good support in postgres for upserts, > that is, until 9.5 came along. We were obviously excited, benchmarked some, > and we'd get in theory a performance uberboost (where, for N records, we'd > have 2 queries associated, 2N, we would bring this number down to 1!). > Our bottleneck therefore became... Active Record. We searched for support > for "ON CONFLICT" using idiomatic AR/AREL, and couldn't find a thing. I > reasoned that, either we'd have to use raw SQL, or we could just use sequel > on top of Active Record to build the query. > > So I iterated on a solution using AR everywhere except in the multi-upsert > query, where I used sequel, and voilà. > > AR > instantiate all relevant parent model, lock the table row #=> parent = > ParentClass.find(id) ; parent.lock!(true) > SEQUEL > build all the attributes necessary > build multi upsert query and trigger #=> > DB[:children].insert_conflict(... > AR > reset counter caches, update timestamps #=> > ParentModel.reset_counters(... ; parent.touch > > > Speedier, and the memory consumption was also way lower, as no model > instances were instantiated. > > Thanks Sequel! > > > Now to the reason why I wrote this post: I don't think it's correct to do > this as described above, as AR model locks the table row, and the multi > insert is done with the sequel db handler, that means, two separate > connections to the DB. The way I see it, I have these possible solutions: > > > 1. Use the Sequel layer only to build the SQL, and then pass it to the AR > connection. >
This is probably the easiest short term solution. As you mentioned later, multi_insert_sql is the method. I'm guessing the reason it connects to the database is to know how to correctly escape the strings (it uses a connection-specific string escaping method). I doubt there will be much impact in a multi-threaded app, but you'd have to see. > 2. Pass the AR connection object to the Sequel db handler (is this > possible? both use the 'pg' handler under the hood) > This is not possible. Sequel uses a Sequel-specific PG::Connection subclass. Some people have made hacks that allow ActiveRecord to use the Sequel connection pool, but it's not something I'd feel comfortable doing in my application. > 3. Remove Active Record and replace all my model layer with Sequel (and > migrations, etc etc etc) > This is definitely the best long term solution, but as you mentioned, it's probably not worth it unless you want to rewrite the app. I can recommend a good web framework if you plan on doing that. :) > 3 is out of the question, as we don't have the resources, and replacing > ORM is not an easy task. I'd envision rewrite the app with sequel, but > certainly not with rails. 2 might be something which is not trivial and > allowed by the public API (haven't researched it). 1 seems the most > feasible, provided that Sequel supports this. > > So, the question being: can I use Sequel as a better Arel? > > Thanks for all the awesome work! Sequel is a life saver. > 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 https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
