Great comments
|
| Author | Content |
|---|---|
| Khamul May 11, 2012 12:35 PM EDT |
This article is good just for the comments educating the article author. It's funny how many people dump on SQL databases because they come from the 70s, when their performance and reliability is unmatched. |
| caitlyn May 11, 2012 12:43 PM EDT |
I agree with your comment about SQL databases, Khamul. However, I don't think the comments on the article are so great. It seems to be filled with PostgreSQL fans dumping on MySQL. MySQL, as part of a LAMP stack, is the defacto standard for web applications. I lost a lot of respect for PostgreSQL years back when they failed to preserve backwards compatibility from one major version to the next. That broke an open source GIS application I wa s supporting for a government Agency at the time and the net result was that the server in question was frozen at an outdated version of Red Hat until the problem could be resolved. That did not happen for quite a long time and the PostgreSQL developers never addressed the issue. |
| Khamul May 11, 2012 5:13 PM EDT |
Ok, I'm not an expert on these things as I just know what I've read, but PG seems to have a very good reputation for reliability above all else. MySQL, OTOH, doesn't even seem to be ACID-compliant from what I've read about it. Close, but not quite, and if data integrity and reliability is paramount, then close doesn't cut it. MySQL seems to have been geared for performance above reliability, as it's not such a big deal if a web application loses some data such as session information, and for high-volume web servers speed is important (which has also driven the trend/fad towards NoSQL databases). I've never seen anyone complain about PG's reliability. It probably also helps that PG is much older, having been started in the mid-80s. I see nothing wrong with people dumping on MySQL in favor of PG if it's better for the job. In this case, the article wrote specifically what they were using it for, and the comments weren't just mindlessly dumping on MySQL, many of them were specifically recommending that he use PG's "hstore" feature as it appears to be designed explicitly for that task. That looks like a case of "using the proper tool" to me. Why go to a lot of trouble to get MySQL to do something it wasn't designed for (as the author seemed to have to do, according to his text), when PG has a feature that does exactly that, out of the box? As for backwards compatibility, from what I've read about it, that doesn't seem to be promised by the PG developers at all. In fact, it's specifically said that the binary files are NOT compatible between releases, and that you need to use pg_dump or similar means to transfer data when you upgrade to a new major version. |
| caitlyn May 11, 2012 7:22 PM EDT |
Quoting:Close, but not quite, and if data integrity and reliability is paramount, then close doesn't cut it. MySQL seems to have been geared for performance above reliability, as it's not such a big deal if a web application loses some data such as session information, and for high-volume web servers speed is important (which has also driven the trend/fad towards NoSQL databases).I don't think you can tell my eCommerce customers that it's not a big deal if their sales records are lost or if an order disappears. In my experience MySQL is highly reliable and my customers, many of which are Windows refugees, love the reliability that a LAMP stack offers. Quoting:As for backwards compatibility, from what I've read about it, that doesn't seem to be promised by the PG developers at all. In fact, it's specifically said that the binary files are NOT compatible between releases, and that you need to use pg_dump or similar means to transfer data when you upgrade to a new major version.That is precisely what broke a major app for the Agency in question and why they ultimately migrated to MySQL. No backwards compatibility is a HUGE red flag for most business applications and is the main reason I do NOT recommend PostgreSQL to anyone for much of anything. If you can't keep your apps updated and secure without breaking a mission critical business app then the product becomes useless. |
| dinotrac May 11, 2012 8:08 PM EDT |
@khamul -- It IS a consideration if you want to keep an up-to-date release (security, features, etc) and have a very large database. I know at least one company with terabytes of PG data, and having to upgrade the binary data store would be painful. Possible? Yup. But painful. PS -- I'm actually moving from MySQL to PostgreSQL, but that doesn't mean I don't appreciate Caitlyn's pain. |
| Khamul May 11, 2012 8:27 PM EDT |
Backwards compatibility is overrated; do you also criticize the Linux kernel developers for not having a stable binary ABI and API and not supporting proprietary drivers? Locking yourself into one binary format is an easy way to limit your performance; one big complaint about PG years ago was that it was slower than MySQL. They've greatly improved performance since then by most accounts, and that probably wouldn't have been possible if they had had to maintain binary compatibility. Besides, from what I see, the PG team still maintains the older major versions (7, 8) alongside the newest version (9), much like the Linux kernel team maintains certain older "stable" releases, so unless you really need some new feature or performance, you can just stick with the older stable version you're on. There's no reason to rush to get the latest thing; the only real reason to update is for security fixes, but those shouldn't break your binary compatibility when you're staying on the same major version (e.g., 8.4.1 -> 8.4.2). If you're trying to migrate from one OS version to another and to a newer PG major version along with it, what's the big deal with just doing a full dump and restore? It's not like you're pressed for time if you're already going to the trouble of changing to a totally new OS version. |
| caitlyn May 11, 2012 8:47 PM EDT |
I'm glad that the PostgreSQL are now maintaining previous versions. They didn't when the incident I described happened. Having said that, I think backwards compatibility not only isn't overrated, but I think its importance cannot be stressed enough. You comparison to the Linux kernel is an apples to oranges, or perhaps even an apples to bricks comparison. We're not talking about proprietary anything when we're discussion PostgreSQL. We're talking pure Open Source and incompatibilities between one version and the next of the same product. Imagine if the 3.0 kernel had broken each and every existing Linux app. That's the level of incompatibility I'm talking about and to me it is both unacceptable and unforgivable. |
| skelband May 11, 2012 9:26 PM EDT |
There's a lot of nonsense spoken about relational databases on the web. There's so much "NoSQL" bashing by relational zealots I've stopped listening to them. The issue for me (as a long time database developer in the past) is that for some implementations, there are better solutions than the relational model. Relational databases still have a place where ACID properties are critical. The problem is that the relational model has been pressed into service in so many situations where it is inefficient and you have to bend over backwards to make it fit. You either end up with a hopelessly painful model or a very inefficient one. The main reason that these alternatives have picked up more recently is that for a lot of implementations (such as search engines, social networks) you don't need ACID guarantees but what you do want is both scalability and speed. One thing that relational engines are not particular good at it scaling to the massive sizes that we see in these situations. Instead of scaling being a fundamental part of the model, you have to resort to (what are quite frankly hacky) methods such a partitioning and consolidation techniques to make the thing workable. To me, the whole NoSQL revival is a realisation that one-size-fits-all is neither necessary nor workable. My 2c. |
| skelband May 11, 2012 9:27 PM EDT |
BTW, we use PostgreSQL here and I fit it extremely reliable. My experiences with MySQL were pretty painful and corruptions were very common. |
| mbaehrlxer May 12, 2012 1:29 AM EDT |
there is quite a difference between having compatibility for programming APIs vs. dataformats.
programming APIs are used, and if they change, you need to change the code. and even though this can be a pain, if it is Free Software you have access to the code. the change can be tested and you can make sure it works before applying it. but if you have to change gigabytes or even terabytes of data because of a change in the storage format, then that can be prohibitive. worse, imagine you archive some data and don't touch it for a few years. then try to read it. better hope that the old format is still supported. think about old word documents. same problem. it is one of the things that gives non-free software its bad name. Free Software can do better. compare to upgrading your filesystem. ext2 -> ext3 -> ext4 all upgrade in place. that's better. greetings, eMBee. |
| dinotrac May 12, 2012 10:40 AM EDT |
Quoting:do you also criticize the Linux kernel developers for not having a stable binary ABI and API and not supporting proprietary drivers What @eMBee said. Having to migrate terrabytes of data to a new format is a vastly different from upgrading drivers, software versions, etc. |
| Steven_Rosenber May 12, 2012 10:59 AM EDT |
My problems with MySQL have solely been due to poor applications that interface with it. |
| Khamul May 12, 2012 2:10 PM EDT |
An office document is a little different from a database. No one cares if a Word document takes an extra 0.2 seconds to open because its file format isn't optimized for performance, but for a large database it'll make a big difference. Which do you want, easy migration or fast performance? You can't have both. It's exactly the same as the Linux kernel APIs; forcing a fixed API causes all kinds of problems when people try to do something new. Even Oracle databases aren't compatible across major versions: http://docs.oracle.com/cd/E11882_01/timesten.112/e21632/upgr... PostgreSQL even has documentation detailing how to migrate data easily: http://www.postgresql.org/docs/8.4/static/migration.html What do you think is going to happen when a database adds a new data type that wasn't planned for in an older release? Either you change the binary format, or you figure out some way of shoehorning the new data type in, which leads you to cruft and terrible performance. Or, you could just make the database store all its data in XML files... yeah, that'd be great for performance. Worse, this whole thing is a bit of a red herring, because even MySQL isn't compatible between major releases! http://dev.mysql.com/doc/innodb/1.1/en/innodb-file-format-co... A quick read of this seems to show that InnoDB does have a mechanism to allow use of database files created with older releases, however, it hamstrings you and basically locks you out of using new features. |
| mbaehrlxer May 12, 2012 2:30 PM EDT |
khamul: good points, except for the oracle one, that they don't do it doesn't mean it's good not to do it. also i suspect that oracle users are less eager to upgrade because those upgrades cost money too... and for being locked out from new features for using an old format, if i can't upgrade then i am equally locked out, so backwards compatibility is not any worse than not being to upgrade at all. at least i can run the version with the latest bug and security fixes... that migration documentation may look easy, but even only needing a single command doesn't help if that takes some hours and some terabytes of diskspace to process all the data, leading to hours of potentially unacceptable downtime. the alternative solution that is more reasonable (to minimize downtime) is replication, which is not documented there and presumably not as easy. greetings, eMBee. |
Posting in this forum is limited to members of the group: [ForumMods, SITEADMINS, MEMBERS.]
Becoming a member of LXer is easy and free. Join Us!