In practice the biggest issues with it are that the tooling is significantly behind the state of the art of mainstream dev tooling, both for working engineers and ops. It's also not built with this with style of working in mind, so you accumulate a lot of little struggles that are hard on engineering morale and confidence in the deployed system. DBAs have their own practices, but admin is a different discipline from dev and they aren't always directly compatible.
The other problem is that for most modestly sized money-making applications, the database is the single most resource-intensive and expensive necessary part of the stack. Putting a bunch of business logic in there will never help that, and query optimization is a difficult to acquire skill. You pretty much always end up with just a couple of people who can talk the query planner down off a cliff when you get into the shit.
https://postgrest.org/en/stable/ecosystem.html#client-side-l...