The use case is what I mentioned in the article: Easier development and deployment of open-source, self-hosted apps. This paradigm makes things a lot easier both for the user and the volunteer developers.

It simplifies the 3-tier architecture (DB, server, client) into effectively 2-tier because the server environment is replaced by a standard binary (PostgREST) with zero hand-written code. This is 33% improvement in language/framework complexity if nothing else.

If needed, one can also write postgresql functions/triggers in Javascript/Python/etc for productivity - although this doesn't seem to have taken off yet.