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 be really complicated, and will surely take pretty much time, as programming languages for different databases are very different from each other. 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 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 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 logic on database: when you need to fetch large volumes of data to produce some 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 there's no TCP/IP overhead and no network latency when the store procedure make 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 avoid 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 near to data collecting than 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(at)gmail(dot)com>
To: "PostgreSql-general" <pgsql-general(at)postgresql(dot)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 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.

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 adopt an 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 another thing, but this certainly was and largely still continues to be the case when one compares MySQL and Postgres. So why do folks continue to use MySQL? I find this mystifying.