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

On 2018-04-08 14:39:49 -0700, Guyren Howe wrote:
> I am a Rails developer at a medium-large size company. I’ve mostly worked at
> smaller companies. I’ve some exposure to other web development communities.
>
> 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.

Like Tim I would like to make a case for finding a middle ground. The
sweet spot may be different from project to project and it is certainly
different for different teams or companies.

I'll try to outline what I like to solve within the database and what I
like to put into the application, and why:

Everything which is easy to specify *declaratively* goes into the
database: Tables (of course), views, constraints, etc. These are
basically type declarations for the data which is stored in the
database, so it belongs there.

Simple stored procedures and triggers go into the database, too. They
should make using the database easier and should not surprise the user.
This is mostly optional.

Anything I would consider "application logic" (when the user does X,
then the system should do Y) goes into the application. There is often a
distinction between a backend (or data access layer) and a frontend. The
backend could theoretically be within the database and be called via
stored procedures, but I find it much easier to test and debug code
outside of the database (this may be just my lack of knowledge about
available tools).

In my applications I use SQL heavily. RDBMs are good at processing
queries, so use them for that. If all you want is a key-value store,
don't use PostgreSQL. I'm not very fond of ORMs. I know what I want to
do and can express it in SQL. An ORM makes me translate that into a
different (and usually inferior) query language, which is then
translated back into SQL. That doesn't make things easier for me.


> My best theory is that these communities developed at a time when Windows was
> more dominant, and just generally it was *significantly* easier to use MySQL
> than Postgres for many, particularly new, developers.

I come from Oracle, not MySQL, But I have also used MySQL, and I guess
the very wide gap in capabilities between Oracle and MySQL made me
cautious about putting too much into the database. There is also the
expectation that you should be able to use a different database engine
(SQL is a standard, right?) just like you should be able to use a
different C compiler, but in practice that never works. And of course I
wasn't very impressed with PL/SQL. (PostgreSQL gives you a much wider
range of languages for stored procedures than Oracle, but PL/PerlU still
isn't quite the same as Perl (And I suspect it's the same for Python).

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>