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. 2. Pass the AR connection object to the Sequel db handler (is this possible? both use the 'pg' handler under the hood) 3. Remove Active Record and replace all my model layer with Sequel (and migrations, etc etc etc) 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. Tiago -- 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.
