[repsilat]

There are simple tricks to make those queries not kill performance. Here is a dumb proof-of-concept I made a few months ago: https://github.com/MatthewSteel/carpool

The general idea is combining queries from different HTTP requests into a single database query/transaction, amortising the (significant) per-query cost over those requests. For simple use-cases it doesn't add a whole lot of complexity, can reduce both load and latency significantly, and doesn't lose transactional guarantees.

Not 100k/sec writes on my laptop, mind you :-).

[Perseids]

Since looking into modern concurrency concepts I've always thought such (in my opinion obvious) batching should be part of sophisticated ORM frameworks such as Rails' Active Records. Alas, their design decisions always seem to cater for making the dumb usages more performant (sometimes automagically, sometimes adding huge layers of cruft) than rewarding programmers who are willing to learn a few concepts by creating interfaces with strong contracts with better safety and performance.

E.g. please give me guidance on how to better structure my database model so that it doesn't effectively end up as a huge spaghetti heap of global variables. My personal horror: updating a single database field spurs 20 additional SQL queries creating several new rows in seemingly unrelated tables. Digging in I find this was due to an after_save hook in the database model which created an avalanche of other after_save/after_validation hooks to fire. The worst of it: Asking for how this has come to be I find out that each step of the way was an elegant solution to some code duplication in the controller, some forgotten edge case in the UI, some bug in the business logic. Basically ending up with extremely complex control flows is the default.

So of course, if your code has next to no isolation, batching up queries produces incalculable risks.

/rant, sorry.

[repsilat]

I agree that with that kind of complexity (or with the belief that that kind of complexity is inevitable) it isn't a great idea. You lose isolation, and if you can't predict which rows will be touched you're hosed.

One mitigating factor, this sort of optimisation should be applied to frequent queries more than expensive queries. In some use-cases the former kind may be simple ("Is this user logged in?") even if the latter is not.

And on keeping that complexity down: the traditional story has been "normalise until you only need to update data in one place," but often requirements don't line up well to foreign-key constraints etc. The newer story can work, though: "Denormalise until you only have to update in one place, shunt the complexity to user code, and serialise writes." It's anathema to many, but it is becoming more common (usually in places that don't use RBDMSs though.)

[chug]

Looks interesting! You mentioned in the docs that it would be simpler once abstractions develop and that made me realize it's similar to facebook/dataloader, just used across requests instead of batching up all of the queries per request. It's also of course a generalized form of it that represents batching a parametrized method more so than just batching retrievals by some kind of unique key. It may be able to serve as something to lift API ideas from though. Like some kind of BatchedTask that has an execute() method that takes an array of args then batches those into an array of array of args for the underlying batched implementation.

https://github.com/facebook/dataloader/blob/master/README.md

[repsilat]

Ah, thanks for the link, I'll definitely check it out.

[vasilia]

It may help if you use a single data source. But we are using more than 2000 data sources and all they distributed/replicated in different data centres across different countries.