Let's see.... There are two major issues when writing business logic in
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.
Open Comb Systems E.I.R.L.
Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248
----- Original Message -----
From: "Guyren Howe" <guy...@gmail.com>
To: "PostgreSql-general" <pgsql-gene...@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 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
This attitude has just continued to today, even as many of them have switched
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.