One advantage to using logic and functions in the db is
that you can fix things immediately without having to
make new application builds. That in itself is a huge
advantage, IMO.
I doubt most of us would consider this any
kind of advantage outside of the momentary temptation to do
it when an app is completely broken and needs to be up in a
hurry. Application changes, whether in the dB or in
application logic, need to be tested, and they need to be
revision controlled and released in a manner that can be
easily rolled back in an automated manner. The fact that
putting logic in the database can effectively allow
developers to make unreleased changes to production apps is
specifically one of the problems that I am trying to avoid
when I keep most logic in the app instead of the dB. It’s a
whole lot harder to make arbitrary manual changes to code in
the app, whether interpreted or compiled, if it is running
inside a container that cannot be updated. Even if you go in
with a shell and update an interpreted file, the next time
that container is launched the change will be lost, which is
usually sufficient motivation to keep devs from doing that
kind of thing.
I’ll put some things in the db, either for
performance or because I want that logic to be built into
the data and not be part of the application, but I choose
those contexts carefully and I write them in as portable a
manner as possible. And for those who say migrations don’t
happen, I’ve certainly been through a few, usually as part
of an acquisition or the like, but sometimes simply because
another dB server better meets our needs after a time. And
migrating stored procs can be really difficult. Such code
usually has less complete unit and integration tests, which
makes validating those changes more difficult, too.
But the biggest reason is that databases often
have to scale up rather than out, so keeping as much logic
in the application code allows my scaling requirements for
the dB server to be as minimal as possible. Sure, there are
workloads where pushing raw data across the wire will be
more work than processing it in the dB, and in those cases,
I may do that, but I consider it premature optimization to
just assume that is necessary without hard evidence from
production examples to suggest otherwise.
Finally, there’s the consistency argument. I
want to find all of the logic in one place. Either entirely
in the source code or entirely in the dB. Having to trace
things from the code to the dB and back again can make it a
whole lot harder to see, at a glance, what is happening in
the code. Having logic in the dB also means it can be
difficult or impossible to have two releases talking to the
same schema version at the same time - so canary builds and
rolling deployments can be difficult. Of course, schema
changes can cause this problem, regardless of whether there
are stored procs, but the more of your logic that lives in
the db, the more likely it is that your releases will
conflict over the db. So I’m more likely to be able to do a
rolling release if I keep the db as a dumb data store and
keep logic in the application code.
=======
I could have worded that better but I think that we're
coming at it from different directions. You think of your
application as the "master" operator. I think of a PG db as
the "master", not a slave. I believe that we shouldn't
_have_ to use an external application for the database to be
useful and coherent. I like to think of external
applications as subservient to the db and not the other way
around. Yeah, I know, probably not a popular viewpoint.
Sorry, I don't really understand why it would be so hard to
migrate, say pl/pgsql functions. You can maybe expect to
write some likely convoluted application code, though. :)
Reusable functions in the db that are solid also means that
developers don't have to reinvent the wheel in whatever
language and debugging also becomes simpler.
