Mocking away DB is both an inefficient way to work and lets the tests cover less. I will never write software without running tests against a real DB again.
Our setup totally would let you test anything in stored procedures (although we do not use them much).
Now I have a test database, which I create from a version-controlled schema.sql, and each test creates its own fixtures in the test db. Works way better.
In practice though, the logic your application uses is going to generate the same queries anyway. So I’m skeptical that using stored procedures increases the workload of the database.
If you need to scale your database, you can do it with any standard replication strategy, and just make sure to bring the functions to the replica too. If you package them in extensions, this is easy.
To be clear, you’re not answering web requests from the database. There is still an application in between; it’s just much thinner, or can be some abstraction like postgrest or postgraphile. You scale that too.
Performance and arbitrary scaling aren't problems that always occur together. Not every app with performance concerns is a mass audience consumer app.
If performance is so critical, whatever you gain by moving everything to the database is lost by the fact that databases are harder to scale. You may be able to get by with a single instance a little longer, but when eventually you don't anymore, you've now worked yourself in a corner. It's an 80/20 game anyway, so just use an occasional stored procedure when it's really worth it.
For data integrity, I've always found databases not expressive enough. Sanity checks, okay, but you can't capture a non-trivial domain in some custom data types, check constraints and foreign keys. Even if you introduce stored procedures that will be responsible for keeping everything proper, you need to go crazy with permissions to block circumventing those. Might as well build your own API then. (I do find it difficult where to draw the line when it comes to what to enforce in the database still.)
"But you don't need another API! Just use the database as one!" Then I ask how they do testing, and the answer basically is: "We don't make mistakes or we find out about them (in production) soon enough." That pretty much ends the discussion for me. Surely there must be ways to devise a basic testing framework for stored procedures, but why bother? I don't want to spin up a database just to test some logic. Never mind testing, what about refactoring?
From a theoretical point of view, I can see the potential, but practically...? The tooling isn't there, and perhaps that's for a reason. Maybe databases are just not supposed to be used that way. Despite the extra functionality they offer, I treat them mostly as a data store.
What am I missing? I would love to be proven wrong.