Re: Rationale for aversion to the central database? - Mailing list pgsql-general

> On Sun, 8 Apr 2018 14:39:49 -0700
> Guyren Howe <guyren@gmail.com<mailto:guyren@gmail.com>> wrote:
> 
> When it comes to databases, I have universally encountered the
> attitude that one should treat the database as a dumb data bucket.
> There is a *very* strong aversion to putting much of any business
> logic in the database. I encounter substantial aversion to have
> multiple applications access one database, or even the reverse: all
> abstraction should be at the application layer.

That seems un-pragmatic to me. IMHO if any business logic needs
access to lots of data, it's best implemented by code that
resides in the database itself, close to the data. I once had a
job where, one night a week, I couldn't go home until a certain
program had been run and completed successfully. That meant I
wasn't going home until midnight. I realised that the person
that wrote it was (at least for the purposes of that program)
treating the database like "a dumb data bucket". Millions of
records were being selected, transferred over a network to
another host, effectively grouped and summarised, then each
resulting summary record was inserted into another table, one
stored function call at a time (with all the network round trip
times that that implies). It took 2-3 hours to complete. I
replaced it with a stored function that took two minutes to run
and I was able to start going home hours earlier. So, as you can
imagine, it would take an incredibly good argument to convince
me that business logic shouldn't reside in the database. :-)

I've always assumed (perhaps incorrectly) that not wanting
business logic in the database (and not using all of the
features that databases provide) was just a way to justify
programmers not having to learn SQL but it's just another
language and paradigm and programmers know so many languages and
paradigms anyway that I never understood why knowing SQL was a
problem. My assumption is probably wrong. And I guess as long as
the resulting software runs as quickly as it needs to, it
probably isn't an issue. If it's a widely held view, then it
must be workable.

I use stored functions exclusively, partly so that the business
logic is close to the data it needs to be close to for
efficiency, but also for security reasons. Users have no
permissions to select, update, insert or delete anything. All
they can do is execute stored functions that have been
previously created by the database owner who does have those
permissions. For a system that's accessible to the public, It's
a great way to guarantee that SQL injection can't do any harm
(even if all the other protections fail to work or are
bypassed). For a system that's only accessible to internal
staff, it's a great way to defend against their workstations
being infected by malware that goes looking for databases to
attack.

cheers,
raf