Pros:

  - Can query DB multiple times, conditionally, without making multiple trips to DB, since your code for a certain procedure is all in the DB.
  - Procedures are accessible using any DB client.

  - Version control of these procedures is not as nice as normal code. Graphile Starter has some tools for snapshotting the DB schema that help, but the DX is still not great.
  - Scaling your DB is more costly than scaling compute, so from a cost/scaling perspective this might not be the best idea.

Maybe I'm just stuck in the olden days and haven't yet embraced the brave new world where no one can run a full local instance because it depends on queues and storage backends and whatever else supplied by a cloud vendor. But even in my little world, I feel like I experience this with overly-smart Jenkins pipelines that can't really be executed except in production or an expensive-to-maintain clone of production.

Having views instead of tables (hiding the actual implementation details), having procedures to interface with underlying tables and having procedures to ensure data integrity for incoming changes is a great use for procedures.

Too often, I see databases used as simply storage boxes, when they are capable of being much more.

> PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.

I've been looking for something like this for a long time?

Procedures can be triggered by inserts/updates/deletes. This means logic can be replayed. There might be a table "IncomingActions" that triggers a set of procedures. Procedures might sanitize values on updates, update various specific materialized views.

Thinking about it generally, consider the db as storing system state, and a set of actions that updates the state. So the db is a sort of state machine, and the triggers/procedures define the reachable state space. For example, there might be FK constraints that forbid clients to insert into the table "Orders". Instead, clients could insert into "OrderRequests" which triggers a set of procedures that ensures the entry in "Orders" is legal. In this way, an ORM app could be allowed to create Orders without needing to call procedures, or knowing anything about what makes "Orders" legal. We also get a log in "OrderRequests".

Error handling is ugly though.

    Chad F

    Sam L

    Max D

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.

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.

And then forms, and then reports. Hail the return of FoxPro!