Re: Rationale for aversion to the central database?

Let's see.... There are two major issues when writing business logic in database:

1. Portability. Being tied to a single database engine is not always a good idea. When you write business logic in
database,you have to write and maintain your store procedures for every database engine you want to support. That can
bereally complicated, and will surely take pretty much time, as programming languages for different databases are very
differentfrom each other. And it's permanent: Every time you make a change to a store procedure, you must make that
changefor every supported database. 

2. Performance. I still don't have it clear, but, presumably, plpgsql(among others) may not have the same performance
asJava, Ruby, Python, C++, or any other programming language. Also, when your application runs outside of the database
server,having business logic on database will make your app use more resources from the database server, thus rendering
isslow for all other operations. This goes against separating database and application. 

However, there are some cases when you may want or need to use business logic on database: when you need to fetch large
volumesof data to produce some report. This is the case of some accounting reports in complex ERPs. The only advantage
storeprocedures have is they run INSIDE the database, so there's no TCP/IP overhead and no network latency when the
storeprocedure make a large query. Even running in the same host, fetching large volumes of data will always be faster
froma store procedure. 

Setting this considerable advantage of store procedures, I still try to avoid business logic programming on database.
Inthe very specific cases when I need to take advantage of this, I try to make it the most simple, more near to data
collectingthan business logic, so the application receives processed or summarized data, and processes it as needed. 

Regards,

Alvaro Aguayo
Operations Manager
Open Comb Systems E.I.R.L.

Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248
Web: www.ocs.pe

----- Original Message -----
From: "Guyren Howe" <guyren@gmail.com>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Sunday, 8 April, 2018 16:39:49
Subject: Rationale for aversion to the central database?

I am a Rails developer at a medium-large size company. I’ve mostly worked at smaller companies. I’ve some exposure to
otherweb development communities. 

When it comes to databases, I have universally encountered the attitude that one should treat the database as a dumb
databucket. There is a *very* strong aversion to putting much of any business logic in the database. I encounter
substantialaversion to have multiple applications access one database, or even the reverse: all abstraction should be
atthe application layer. 

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. And it is pretty reasonable to
adoptan aversion to sophisticated use of the database in that case. 

This attitude has just continued to today, even as many of them have switched to Postgres.

This is only a hypothesis. I am now officially researching the issue. I would be grateful for any wisdom from this
community.


Aside: it is rare to find a situation in life or anywhere where one widely adopted thing is worse in *every way* than
anotherthing, but this certainly was and largely still continues to be the case when one compares MySQL and Postgres.
Sowhy do folks continue to use MySQL? I find this mystifying.