Re: Re: PostgreSQL versus MySQL

Transactions are not for critical data such as credit card numbers. There are at least a couple of reasons for transactions and neither of the reasons I can think of are for storing critical data such as credit card numbers. Transactions are for updates to the database that must happen all together or not at all. For example, if you enter a sale into a database, you might have to enter the invoice and all the items purchased. The invoice with no purchased items makes little sense and purchased items with no corresponding invoice makes little sense. Without transactions, the database could be left in an incomplete state if records were inserted (deleted, or changed) that depend upon other records being inserted (deleted, or changed). Also, the transaction log is used by the database to bring it into a state of integrity in the event of a crash. The log can be rolled back to the last state that the database knew it was complete and had integrity. This is not usually something the database user/programmer does, though...it's more dba or system oriented. So transaction support is basically crucial for database integrity. The MySQL folks didn't even add support for transactions until just a couple of releases ago, and that was because many people demanded it. When they released it, it was (ummm...and still is) some kind of amalgamation of several products. I don't understand why MySQL would be better at handling table crashes and data corruption. Perhaps the fact that Jon simply spewed marketing hype (FUD) regarding transactions and what they're good for is an indication that the statement about MySQL being better at handling crashes and corruption is just more baseless marketing hype. None of the above means that MySQL is a bad or undesirable product. I sometimes use MySQL. It has come a long way in a very short amount of time, and they're doing fantastic things to it. However, one should not put it on a pedestal and exhalt it above all else. One might also not try too hard to compare MySQL to PostgreSQL. One of these products is a relational database management system. The other is a quasi-SQL-like-front-end-to-systems-of-indexed-files that has never concerned itself with things like standards conformance. Performance was addressed in another post. But I wish to remind all reading this that raw speed on a transaction or two is probably pretty nice, but being able to perform adequately under load is another matter entirely. One of these qualities is cool, but lack of the other quality is a disaster and could get you a pink slip or a torn up contract. One "answer" to the original post would be to use whichever product suits your needs. Both are highly acclaimed. Both have their strengths and weaknesses. Neither is a clear "winner" over the other. There are many reasons to select a platform...among them are things like: * your abilities * the abilities of the talent pool available to you * suitability to a task * features * security * scalability * portability And in Matt's case, it may be that MySQL is the one that should be used. But certainly unfounded hype is not among the reasons anyone should select a platform. Doug At 10:22 AM 4/7/01 +0200, Jon Valvatne wrote: >On the contrary, MySQL is much better at handling table crashes and data corruption than PostgreSQL is. What you may have heard is that due to lack of transaction support, critical data may be lost "in transit" from your application to the database, in the event of a system crash or a dropped connection. Well, guess what? MySQL now supports transactions in its latest release. Using transactions is probably not really needed though, unless your data is critical, such as credit card numbers. > >As for a full comparison between the two, I think the bottom line is that MySQL is slightly more light-weight, but easier to use and faster than PostgreSQL. So if you're looking for a database for a relatively noncritical web application, I'd say go with MySQL, especially since that's what you already have experience with. > >One of the upcoming features in MySQL I'm really looking forward to is query caching, which is a great feature for web applications. > >Jon Valvatne > >> Can someone outline the differences between the two? I am partial to MySQL >> from experience but want to get a good view of why one is better than the >> other. >> >> Also, I've heard that you will lose data with MySQL if a system failure >> should occur. >> >> Thanks. >> >> Matt >