Thx for the reply! I've managed to deploy a solution to our test servers with the option 1. So, the number of db connections will be N as in N=number of workers + 1 specific sequel db connection. This will be used by every worker, but just to build SQL, so I guess there won't be a lot (if any) of socket usage concurrency issues. It left me a bitter taste in my mouth, as I now have the feeling I'm using sequel as a much better Arel, and I've started calculating costs of migrating the app to sequel-rails, and actually use a proper tool for the whole stack.
The migration path entails some concerns though. I assume you were going to say "roda" (I really like it, btw), but the "issue" regarding using these specialized non-rails tools is that you'll need developers who have some experience and can mitigate the learning curve. The advantage of rails is that any code school and "agile" course teaches it, not a lot of extra-training to do. I'm glad to pay the cost for sequel now, just not sure what are the long term costs of replacing the whole stack in terms of maintenance. I think roda and other frameworks need more adoption and visibility so that these costs are not as high, and right now, I don't see this happening. But hey, I could have said that from sequel last week and here I am talking about replacing active record. Here's to hoping that the situation changes and people appreciate these tools more. All the best and thanks for the work! sexta-feira, 22 de Abril de 2016 às 11:32:33 UTC+2, Tiago Cardoso escreveu: > > 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.
