Re: Rationale for aversion to the central database?

Greetings,

* Alvaro Aguayo Garcia-Rada (aaguayo(at)opensysperu(dot)com) wrote:
> 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.

The portability claim tends to be both a false one and often, when
realized, results in a solution where you aren't using the database for
anything complicated and you'd be better off with a much simpler data
store. You also don't actually offer any justification for the claim
that being tied to a single database engine is not always a good idea-
why is that? With commercial databases it tends to be because you are
at the behest of some very large commercial company- but that isn't an
issue with PostgreSQL. The next argument may be that the project might
go away and force a move to another database, but PG's track record is
hard to beat in this area given the history and the number of people
working to make it better and keep it maintained year after year.

Ultimately, you really should be thinking of the database as the
language of your data. You wouldn't write your app in multiple
different programming languages, would you? What if Python suddently
goes away as a language, or Java does? Would you write every piece of
software twice, so that you can flip over to using Python instead of
Java on a whim?

> 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.

No, plpgsql doesn't have the same performance characteristics as Java,
Ruby, Python, C++, or many other languages- but that's why it isn't the
only language which is supported in PostgreSQL. You can write back-end
functions in another of those languages, plus quite a few others
including Javascript, R, Perl. As for the question about if it'll
actually result in the database server being more taxed or not- that
really depends. Aggregates are a great example- is it going to be
cheaper for the database to run 'sum()' across a data set and give you
the result, or for it to take every row from that table and ship it to
a client? There are certainly examples which can go the other way too,
of course, but it's really something to think about on an individual
basis, not to make a broad stroke decision about, particularly when
you're arguing that you'll get better performance by moving the code
away from the data, that tends to not be the case.

> 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.

This is what I'm getting at above, but I would caution that looping over
a table in a stored procedure is generally much less performant than
finding a way to express what you want in SQL.

I'm afraid that the other advantages of doing more in the database
aren't really being considered in your arguments above either- things
like having constraints all checked in one place, regardless of the
application, and the ability to have interfaces defined which multiple
applications could operate against and know that they're all going to be
getting back the same, consistent, results from the database since it's
the same code underneath. Some of that can be done by sharing code
between the applications, of course, but there's a great deal more risk
there (what about when the applications need to change something that's
done in that shared code, so they essentially fork it..? Or the
applications have to be upgraded at different times, or a variety of
other situations which could lead to that common code diverging, or even
when the applications aren't written in the same language...).

Just some food for thought.

Thanks!

Stephen