Slashdot Mirror
PostgreSQL vs. SAP?
Johann asks: "As my friend and I embark on building a large web site using open source development tools, I planned on using PostgreSQL. I was reminded that another 'enterprize' database is now released under the GPL - SAP DB.
Since there have been countless Pg vs. MySQL comparisons on Slashdot, I wanted to ask: how does SAP DB compare technically to Pg?"
one big thing to consider is the interface to the db you'll be using. i chose postgres for a large server-side java web-application, and while i have zero complaints about postgres the database, the jdbc postgres interface is a complete mess. if you're doing anything beyond the most basic operations, you'll find a _lot_ of the jdbc-2 spec completely or partially unimplimented (and these shortcomings are, of course, undocumented). i've put far too many hours pouring through jdbc driver code in the last year with postgres. though, i give the developers credit for making steady progress in the last year, it just isn't there yet.
like you, i've been pondering the switch to SAP-DB - from looking through the source, their jdbc implementation seems to be very complete. the only problem i've run into with SAP is the lack of readable documentation... the manual seems to have lots of information, but it isn't exactly developer-friendly.
if this is an "enterprise" level application, i see little choice but to dive into SAP and figure it all out - otherwise i think you'll run into bigger problems down the road.
I love PostgeSQL - it's worked wonderfully for us, but....
In all seriousness, if you use SAP DB, you could probably raise your rates just due to the SAP name alone. The name 'SAP' is also good for covering your ass when things get difficult.
SAP and Oracle have expensive reputations and you can charge accordingly. We do it all the time, for some of our nastier customers, when anything with the word 'Cisco' on it need care.
It depends on the relationship you have with your custome, wether you go that route, but it's worth considering.
Moneyed corporations, non-working 'poor' and criminal prisoners are turning productive citizens into tax-slaves.
Also Slashdot now numbers each and every post uniquely. Contrary to popular myth, this was not done to reduce FPs. When they needed two fields (the story number and the post-within-story number) for their primary key, they took a nasty performance hit.
So if we use Slashdot as an example (and I have to admit it's the only big MySQL application I know much about), then we have to conclude that MySQL's much-touted performance lead goes away if your database has more than the most trivial structure, or if your database gets very large.
I know you seem pretty sure that you want one of those two (Postgres or SAP), but I don't think you should discount MySQL.
Introduction of a third option. Makes the audience feel powerful and privileged.
MySQL is considered (even by its detractors...and there are a lot of them!) to be much faster than the competition.
In this world of benchmarks, is there any need to talk about one database being "considered" faster than any others? Sneaky.
While other RDBMS makers go on about "tradeoffs,"
Well, the "tradeoffs" that they were going on about were little unimportant things like transactions which, up until recently, MySQL didn't support.
the MySQL team has put their money where their mouths are and delivered a database that makes speed the top priority.
Use of language to suggest the MySQL boys are a bunch of good stand-up guys all around.
This is vital in the enterprise.
According to...? Sneaky.
Furthermore, the latest MySQL releases have full support for transactions and complete ACID compliance.
Once again, only a very recent phenomenon.
MySQL also supports a greater and more useful subset of the SQL99 standard than either Postrgres or SAP.
"More useful"? According to...? Sneaky.
I am by no means a MySQL zealot (though there are plenty who are, and you won't have to look far to find them)
Ah yes. This person is an everyman. A reasonable person. You can tell because he talks about the marginalized whackos and does not identify with them.
Just ignore the fact that he's promoting the same software the marginalized whackos would.
but I do think you should take all the options into account.
And Slashdot uses MySQL. Could you even ask for a more shining recommendation?
Appeal to Authority. Sneaky...
STORED PROCEDURES?
You can't do everything in one line of SQL. Not in a timely fashion anyways. Aside from the speed of the database, you should also consider the speed of development.
THERE IS NO DATA. THERE IS O
something strange going on here ;-)
http://www.sapdb.org/history.htm
Yes, you can now have transactions and foreign keys, but to get them you'll be using InnoDB tables, which don't offer up any significant speed advantage. They run at slightly slower than postgres speeds, in my testing.
MySQL sounds really good, especially if you haven't worked with real databases before, so you don't know what they're supposed to be able to do, but in the end it's not. Unfortunately, telling mysql advocates this is like convincing a Best Buy employee that overall PC performance cannot be compared by looking at the CPU clock rate.
Stored proc can be useful but you have to remember that :
a) They are not portable at all. Moving to another database means rewriting all of them.
b) They tend mix business logic and data which is a big no-no as far as three-tier architecture goes
For these two reason I think everyone should restrict stored procedure to trivial repetitive things and keep the complex logic in a separate layer (outside the database that is)
I'm sorry that most of the responses haven't answered your question. I'll try to partially answer it, but I bet you already know all of this. I really like PostgreSQL and use it. If I were wanting to get a really big web site together, I'd try SAPDB, or just go with either DB/2 or Oracle.
My info about SAPDB is research-based, as I did a lot of that. Please feel free to correct me, if I'm wrong, in any way. MySQL has steadily improved, so a couple of items, below may be out of date.
I see that SAPDB is really an Oracle killer. It is a true enterprise-ready DB. If you want all the features of a big DB, such as replication, partitioning, etc., use SAPDB. You can get true commercial support, etc. Read their PDF's and be impressed.
However, you might want to know why I chose PostgreSQL over MySQL and SAPDB:
1.) PostgreSQL is really ACID compliant, as is SAPDB. MySQL, on the other hand hasn't yet proved itself in this area. Give MySQL a few more months, though, and we'll see, with version 4.1.
2.) Hosting companies support MySQL really well, and PostgreSQL only partially well. I have yet to see SAPDB as a hosting offering. Oracle is rare, too, but expensive.
3.) All three are free, in both senses of the word, while DB/2, Sybase, Oracle, etc., are not. (No preference in this area.)
4.) There is an enormous userbase for MySQL, and a sizable one for PostgreSQL, so I can get help from peers 24 by 7. SAPDB, unfortunately, does not have much in this area.
5.) PostgreSQL 7.1.x+ is supposed to scale better in many ways compared to MySQL 3.xx+. Many benchmarks seem to bear out the fact that PGSQL annihilates MySQL 3.xx, after about 5-10 or so web users. PGSQL seems to beat Oracle up through and beyond 100 simultaneous web users. I cannot find any benchmark on SAP, anywhere.
6.) Installing PostgreSQL and MySQL are easy, easy, easy. It's not so easy with SAPDB. While I'm no neophyte, when you consider remote hosting issues, I hope to have a system I can quickly rebuild if hardware dies.
7.) PostgreSQL has enough enterprise-ready features for my web site. MySQL did not (and probably still does not). SAPDB is almost overkill. Views, triggers, foreign keys, constraints of various kinds, stored procedures, versioning, hot backups, etc., are all available in PostgreSQL and SAPDB. One responder indicated that programming time is more important than benchmarks -- amen, preach it, brother. Your time programming and tweaking are far more expensive than hardware and most software.
8.) After working with PostgreSQL and MySQL, I saw that care and feeding (DBA work, in particular) were very simplified, straight forward, and quick. SAPDB smacks of Oracle, in terms of tweaking, complexity, etc. (I was an Oracle DBA for two years; a RedBrick DBA; worked for Informix; and have administered MySQL, PostgreSQL, and even lowly Access.) I haven't actually tried SAPDB, because it looks like a major investment of time.
9.) Books, web sites, and other literature are readily available for MySQL and PostgreSQL. SAPDB's included documentation is most excellent, however, followed by MySQL's included docs. PostgreSQL suffers in this area -- buy the books.
10.) As I scale up, I probably will have to consider something other than PostgreSQL, like SAPDB, DB/2, or Oracle. I refuse to look at Sybase or especially it ancestor, SQL Server. On the other hand, PostgreSQL is semi-tunable and the development team plans to be adding replication, etc., in the coming months. I'll have to wait-and-see. If SAP ERP can be hosted on SAPDB, then, well, it'll scale, no question. I'd contact the author of BinaryCloud for more objective info, here. http://www.binarycloud.com/
11.) Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above. I'm sure the same is true about SAPDB, and more so, since it's got a real business and an ERP behind it.
12.) PostgreSQL, like MySQL, has a user-friendly SQL command line, with readline support, etc. I don't know about SAPDB, but I expect it to be less so, like Oracle's SQL*Plus. Can somebody help me out, here?
In conclusion, my first choice, for now, is PostgreSQL, my second would be SAPDB, my third is actually MySQL, followed by the commercial products. Again, I've never used SAPDB, but I hope to, in the near future; it seems "too big" for my needs, now, and information, outside of sapdb.org is scarce. I hope the community really gets around it, soon, so we can have a more objective look at the product. We need support groups and books available at our local book stores.
SAPDB looks absolutely excellent, while PostgreSQL looks good. MySQL has potential to be a business/enterprise-ready product, in a couple of years. I like the fact that SAPDB uses ODBC/UDBC for its native calls, like SQL Server, Access, and DB/2. MySQL, PostgreSQL, Oracle, and the like, require drivers to translate calls back and forth, slowing things down and adding complexity, for ODBC, UDBC, and JDBC.
For PostgreSQL, I gotta say, the thing that really impressed my was versioning, which makes transaction support and hot backups easy, while keeping performance very, very high.
Try out both databases. Use PGSQL today and SAP tomorrow, as you grow into it.
I also failed to have you look at Firebird/Interbase -- I wasn't thinking. Firebird might be a PostgreSQL killer. I have actually seen a web host, or two, with Interbase/Firebird support.
Almost all of the PostgreSQL/SAPDB advantages are available to Firebird/Interbase.
My list:
PostgreSQL (for now)
SAPDB or Firebird (future growth)
MySQL 4.1+ (maybe, maybe not)
I have done less research about Firebird than SAPDB. All 4 give a great deal of choice. Firebird is based off of Interbase and has a userr community probably larger than SAPDB. SAPDB still kills Firebird/Interbase and PostgreSQL in terms of enterprise features.
Um, what "move"? SAP DB is GPLed for years. It isn't the only once-commercial-now-free (or even Free?) database either, check out Interbase.
Programming can be fun again. Film at 11.
I agree on about everything but point 11 :
Warning, total subjectivity: something about PostgreSQL seems "clean," compared to MySQL. I can't say what it is, but there is a big lack of business-likeness to MySQL, other than what I listed above
I'm following pretty closely both releases and frankly I've the exact opposite feeling. While MySQL adds features one by one, Postgresql seems to absorb lots of stuff at each releases. While this is great feature-wise, it can lead to pretty nasty bugs and instabilities.
Just look a the latest release for example (from postgresql history) :
"it fixes a critical bug in v7.2: sequence counters will go backwards after a crash "
That bug is really critical (can you imagine the state of your datas when counters go backward ?), but it took 2 months to be discovered and fixed. On the other hand the MySQL history list only very minor bug fixes, most of them that you are highly unlikely to meet and that are of little effects. Actually the MySQL developpers make the effort of documenting every petty bug fix, which is a very professional thing.
- Platform independence: SAP DB supports Windows better than PostgreSQL does. They both have good support for unices of various flavors.
- Concurrency: Both databases support ACID transactions. PostgreSQL uses multi-version concurrency control so reads and writes won't interfere with each other, while SAP DB uses row-level locking. In this respect, PostgreSQL v. SAP DB is similar to Oracle v. DB2.
- Curriculum vitae: PostgreSQL originated at Berkeley, and has very cool features like MVCC and functional indexes. SAP DB descends from Adabas D, which was marketed as the PC-sized little brother of Adabas for mainframes, and SAP's focus is on providing robust support for business applications such as SAP's customer-relations and supply-chain products.
- Developers: PostgreSQL is developed by a global community of volunteers. SAP DB is developed by a team of employees (FAQ says 100) in a major software company. Development of both products is active.
You can do good work with either product.MySQL is targeted at a particular feature set, and within that feature set, is fast as all heck.
But it is not faster than Postgre or others in all circumstances, and is incredibly slower in many that bear importance to enterprise useage. MySQL provides responses with extremely low latency when there are few concurrent connections. This however, does not scale. It also tends to suffer under certain mixes of statements, or when using particular features.
If you need what I would tend call a "more safe flat file" MySQL is perfect. MySQL also is perfect for some other situations due to it's popularity.
If you need a relational database, potentially large, and that sustains a high number of concurrent connections, PostgreSQL is probibly your best option. While I've not tested the latest flavors of MySQL and Postgre, at the time I did do a comparision, even in 90% simple read queries MySQL's throughput fell below Postgre's after around 10 concurrent connections were reached.
Look, I'm not a Postgre zealot either, but I do feel that many MySQL supports are not familiar with the rest of the database world, that MySQL is all they know, and they try and pull it out as a silver bullet answer to ever problem.
And as others have pointed out, Slashdot is a poor example, they've written a great deal if middleware to deal with MySQL. If writing middleware is something you wanna do, go for it.
The biggest criticism I could level at PostgreSQL is that replication isn't fully implimented yet. This really holds it back from scaling out, again, something important for the enterprise today.
When you read benchmarks, you should never stop with the numbers. You should look at exactly what the benchmark does, and ask how it relates to what you want to do. Is the benchmark task at all similar to your task? Was the system tested at the same scale you need it to work at?
RE: Docs. There are two books about PostgreSQL online. Buying them is certainly encouraged, but not necessary!
That's assuming you use the innodb tables which are not the default normally. Also the foreign key stuff only allows you to set things to null or delete the row. You can't have mysql stop the sql statement with an error or change the value to something besides null.
"When you sit with a nice girl for two hours, it seems like two minutes. When you sit on a hot stove for two minutes, it
Row level locking may be in mysql now, i gladly admit that i don't follow the development of it, but that's standard in pretty much every database made since 1996 or so, it's not special. if anybody tells you that most databases don't do it, they're wrong.
I never said foreign key constraints aren't possible, I said to use them you have to use InnoDB tables, which aren't fast. I said check constraints aren't implemented, because they aren't. In MySQL CHECK is a no-op which always returns true, whether the condition is true or false.
"on cascade" triggers aren't what i meant by triggers. A real trigger lets me do something like create a rule saying 'on an update to table foo, write some other shit in table bar', or 'if the update to table foo attempts to change the userid, kill the transaction, and write something to a log'.
And dozens of tables doesn't imply a complex database structure, it merely implies that there are dozens of tables. a few gigs of data also does not imply a complex database structure, or something that should be particularly challenging for the database to handle quickly either.
MySQL has more limitations than that too, it can't even have the default value for a field be variable. You can't do something like: expires TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + '2 days' because that's not a constant.
as far as quirks and limitations of interbase, i can't say anything because i've never used it heavily.
As for postgres, yes, there are some things it can't do, but far, far, far fewer than mysql. PL/pgSQL is no match for PL/SQL but it beats the shit out of mysql's procedural language (you know, the non-existant one). It has some annoying limitations, like the fact that you can't use the return value of a function as a CHECK constraint, but to find the limitations of Postgres (and believe me, I'm good at finding the limitations of Postgres) you need to be attempting to do fairly advanced and complicated things. To find the limitations of MySQL, you just need to take somebody who learned SQL on a real database and ask them to solve a simple problem. Odds are good they'll do something that MySQL does not support.