Greetings, * Alvaro Aguayo Garcia-Rada (aaguayo@xxxxxxxxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
