Re: Are stored procedures/triggers common in your industry - Mailing list pgsql-general

On Wed, Apr 20, 2022 at 12:18:23PM -0700, Guyren Howe <guyren@gmail.com> wrote:

> I’ve really only ever worked in web development. 90+% of web
> developers regard doing anything at all clever in the database with
> suspicion.
>
> I’m considering working on a book about implementing business logic in
> Postgres, and I’m curious about how common that actually is.

I'm used to putting all business logic in the database
(after choosing a great FLOSS database that you'll
never want to migrate away from - like Postgres). And
I've never regretted it (in decades of doing it).

One of the main reasons is speed. I once had a job
where a program selected data out of a database,
dragged it over a network, effectively grouped it into
summaries, sent the summaries back over the network,
and inserted them back into the database one at a
time(!). Replacing it with a stored procedure changed
it from taking 2-3 hours to 2 minutes. And that was a
place that already made heavy use of stored procedures,
so I don't know what went wrong there. The point is
that whenever a lot of data activity is needed, it's
much faster when it's done where the data lives.

The other main reason is security. The database can
provide an effective "firewall" between the data and
the client. I never liked the idea of trusting
arbitrary SQL sent from the client. It means you have
to trust every single client application and every
single user (even the ones with good intentions that
produce bad queries in some reporting software and
throwing it at the database and bringing it to its
knees) and every single developer (who might not know
SQL and relies on ORMs that trick them into thinking
they don't need to). But when the clients are only
permitted to execute security defining stored
procedures that have been loaded by the privileged
database owner, you know exactly what code can run
inside the database. SQL injections become impossible
no matter how many bugs and flaws there are in the
client software or its supply chain.

Another good but less critical reason is that when you
use multiple languages, or you migrate partially or
completely from the old cool language to the new cool
language, you don't have to replicate the business
logic in the new language, and you can eliminate the
risk of introducing bugs into mission critical code.
The existing business logic and its test suite can stay
stable while all the bells and whistles on the outside
change however they like.

There are other nice benefits but that's enough.

I think it's safe to disregard the suspicions of the
90+% of web developers you mentioned. The requirements
that they have for a database might be quite
undemanding. Most individual actions on a website
probably don't result in a lot of data activity (or
rather activity that involves a lot of data). The CRUD
model is probably all they need. So their views are
understandable, but they are based on limited
requirements. However, I still use stored procedures
for everything on websites for security reasons.

Everyone's mileage varies. We're all in different places.

cheers,
raf