Date: Sun, 8 Apr 2018 16:57:44 -0500 (PET)
From: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
To: Guyren Howe <guyren@gmail.com>
Cc: PostgreSql-general <pgsql-general@postgresql.org>
Message-ID: <2020789773.14648.1523224664181.JavaMail.zimbra@opensysperu.com>
In-Reply-To: <7e56cc47-ae9c-43ee-9984-77df3a485d2e@Spark>
References: <7e56cc47-ae9c-43ee-9984-77df3a485d2e@Spark>
Subject: Re: Rationale for aversion to the central database?
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: Rationale for aversion to the central database?
Thread-Index: 6OzDr71QO+WmfYgRKn0dv03aiw0FXg==
Precedence: bulk

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

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 mai=
ntain your store procedures for every database engine you want to support. =
That can be really complicated, and will surely take pretty much time, as p=
rogramming languages for different databases are very different from each o=
ther. And it's permanent: Every time you make a change to a store procedure=
, you must make that change for every supported database.

2. Performance. I still don't have it clear, but, presumably, plpgsql(among=
 others) may not have the same performance as Java, Ruby, Python, C++, or a=
ny other programming language. Also, when your application runs outside of =
the database server, having business logic on database will make your app u=
se more resources from the database server, thus rendering is slow 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 log=
ic on database: when you need to fetch large volumes of data to produce som=
e report. This is the case of some accounting reports in complex ERPs. The =
only advantage store procedures have is they run INSIDE the database, so th=
ere's no TCP/IP overhead and no network latency when the store procedure ma=
ke a large query. Even running in the same host, fetching large volumes of =
data will always be faster from a store procedure.

Setting this considerable advantage of store procedures, I still try to avo=
id business logic programming on database. In the very specific cases when =
I need to take advantage of this, I try to make it the most simple, more ne=
ar to data collecting than business logic, so the application receives proc=
essed 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=E2=80=99ve mostly =
worked at smaller companies. I=E2=80=99ve some exposure to other web develo=
pment communities.

When it comes to databases, I have universally encountered the attitude tha=
t one should treat the database as a dumb data bucket. There is a *very* st=
rong aversion to putting much of any business logic in the database. I enco=
unter substantial aversion to have multiple applications access one databas=
e, or even the reverse: all abstraction should be at the application layer.

My best theory is that these communities developed at a time when Windows w=
as more dominant, and just generally it was *significantly* easier to use M=
ySQL than Postgres for many, particularly new, developers. And it is pretty=
 reasonable to adopt an aversion to sophisticated use of the database in th=
at case.

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

This is only a hypothesis. I am now officially researching the issue. I wou=
ld 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 another thing, but this certainl=
y was and largely still continues to be the case when one compares MySQL an=
d Postgres. So why do folks continue to use MySQL? I find this mystifying.