I think a lot of the problems come from the fact testing stored procedures ventures into e2e testing land. You have to stand up infra in order to test them. There's not really been a simple way to unit test stored procedures as part of your application codes testing framework.
(Aside: I think this is something PGlite helps with if your in Postgres land)
PostgreSQL server is a single process that starts in under 100ms on a developer's laptop.
In the company I work for we use real PostgreSQL in unit tests — it's cheap to start one at the beginning of a suite, load the schema and go, and then shut it down and discard its file store.
I keep thinking of moving that file store to tmpfs when run on Linux, but it's nowhere near the top of the performance improvements for the test suite.
So: no more mocks or subsitute databases with their tiny inconsistencies.
My team has found a lot of success using testcontainers for testing Go and Java applications integrating with Postgres. They feel more unit-testy than e2e-testy.
Admittedly I’m only talking about selects, inserts, updates, views, etc. not stored procedures. But having worked in codebases with far too many stored procedures in the past, I think there might be a marginal improvement.
For what it’s worth, I fully agree that the main problem with using store procedures is testability and debugability.