Not necessarily and if it does, then the solution is to add that logic into the low level libary. So yes, you need at least one guy who can do this.
Just to be clear, I'm not saying it's impossible to strike the balance where it makes sense. I'm just saying I've yet to see it work, and I would, therefore, not assume such a balance is given by default, or even easy to achieve.
In my experience, that means that 80-90% of the developer effort goes into getting the FFI bindings to not be terrible. (In fairness to things like python, most of my FFI experience is with Java, which is notoriously bad at such things)
And with wasm (or even webGPU) one can do now the same with javascript and the web. Some logic in scripting is fine, but the heavy calculations should be lower level if one is aiming for performance.
From personal experience, I don't believe this works in practice.
It works well for web or other server-side tasks where your database is doing the "heavy lifting" computationally.
I believe you, though, when you say that you've seen failures of this paradigm.
It's pretty common to see issues where an inexperienced programmer is doing a bunch of data manipulation in 200 lines of Python/Ruby/PHP/whatever that could have been accomplished more easily, more performantly, and with more atomicity in 10 lines of SQL. I wouldn't say that means it's a busted paradigm. I don't consider bad implementations of a particular paradigm to equal refutations of that paradigm.
I do appreciate that there are probably arenas (scientific computing?) where maybe this approach doesn't work at all.
From personal experience, I don't believe this works in practice. Invariably, some level of logic is implemented in said scripting language, and it becomes a huge bottleneck. Counterintuitively, when you have a central thread dolling out work to many subroutines, the performance of that thread becomes more critical as opposed to less.