Re: Rationale for aversion to the central database?

 
 
On April 24, 2018 07:27:59 am PDT, "Sam Gendler"
<sgendler(at)ideasculptor(dot)com> wrote:
 
 
On Sun, Apr 8, 2018 at 15:37 [1]g(at)luxsci(dot)net <[2]g(at)luxsci(dot)net> wrote:

 
 
On April 8, 2018 02:40:46 pm PDT, "Guyren Howe" <[3]guyren(at)gmail(dot)com> wrote:
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.

Perhaps I'm extreme. In my ideal world, developers might not even know
table names! I'm kidding ,sorta...

Thanks much for your comments,
Gerry
 

References

Visible links
1. mailto:g(at)luxsci(dot)net
2. mailto:g(at)luxsci(dot)net
3. mailto:guyren(at)gmail(dot)com