[geekpowa]

I originally tried this with a project I am working on, moving as much of the logic as possible into the DB. But abandoned the effort quickly in part because the logic I need to implement was simply too complex and was better expressed & validated in a client side language.

plpgsql performance is a serious issue for anything non trivial.

debugging is slow and challenging.

General rules I apply with procs

* try to keep procs to SQL, not plpgsql. More performant usually and sometimes pg will inline these. But sometimes I've found forcing it as plpgsql is good too, as it effectively 'fences' the query planner just like CTEs used to. As supportive as I am with pg's philosophy on querying hinting, sometimes you need to press your thumb on the scales a bit.

* procs for very complex and subtle queries where performance is not such a concern but readabiltiy of client code or dependent queries is useful. Like a client side function, something whose inputs and outputs are easy to reason about and you can easily build client side regression tests to validate.

* consider using plpgsql proc instead of recursive CTE for things where CTE recursion is needed. I've found for some types of jobs, plpgsql outperforms a recursive CTE. I think bc query planning for recursive CTES is very difficult.

[bhaak]

In a previous job many moons ago I was tasked with maintaining an application that used an Oracle DB with stored procedures written in Java.

The server backend was naturally in Java as well so as soon as possible I ripped it out of the db and ran it on the backend.

The code ran twice as fast.

It might have been partly due to the JVM in the db being 1.3 whereas the server had 1.4 but still, nobody bothered to measure and just assumed the code in the db running faster because it was closer to the db.