What is Digg?70 Comments
- burke, on 10/12/2007, -2/+23MySQL is usually benchmarked as more efficient for simple queries, so I don't think it's quite as straightforward as that. I do agree that feature-wise, Postgres blows MySQL out of the water.
- marchaos, on 10/12/2007, -0/+18There is a serial type in PostgreSQL that does pretty much what an autoincrement field in MySQL does.
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
You will find that sequences give you more power anyway. - headzoo, on 10/12/2007, -3/+18Which is why a "dated" chart comparing computer technologies is kind of worthless. The technologies are evolving too rapidly.
- saranagati, on 10/12/2007, -2/+16try creating a sequence that multiple tables could use in postgresql and then in mysql and you can see why most people will prefer the former.
- floguy, on 10/12/2007, -1/+14An auto-incrementing field in a table in postgresql is just called a serial. Easy as that.
- tangledweb, on 10/12/2007, -2/+15Regardless of its original merits, a comparison that is two years old is useless when both products have had a major release since then.
- gyrmnix, on 10/12/2007, -0/+12MySQL is dual licensed. You can use it for free for commercial or personal use. If you make changes to the code and keep the source closed, then you have to pay the licensing fees.
PostreSQL, on the other hand, is under the BSD License, which allows you to use/modify/copy the software as long as you keep the copywrite notice intact. - tangledweb, on 10/12/2007, -0/+11PostgreSQL is under a BSD license.
MySQL is available under a GPL license, or under a commercial license.
If you want to do something that would be legal under the GPL then it will make no difference to you. If you want to do something the GPL does not allow, such as distribute a modified version of it but not open source your parts of the code, it would cost you money with MySQL.
The GPL does not ban "business use". - ejdmoo, on 10/12/2007, -2/+12"One of the problems is that PostgreSQL doesn't run properly on NT as a service by default, you need something like firedaemon to start it."
I'm pretty sure that's no longer the case with PGSQL 8... - joe90210, on 10/12/2007, -11/+20this is a worthless comparaison, PostgreSQL is vastly superior
- kerplunk, on 10/12/2007, -2/+11The comparision between MySQL and PostgreSQL isn't like a normal comparison. Why? Because both of these products are amazing.
- ChannelCat, on 10/12/2007, -1/+10I ported my web site's database from MySQL to PostgreSQL and the result was a slower database with a few cool features. Auto vacuum was convenient, but that was about the only thing I used that PostgreSQL offered over MySQL. At the time I tried both DB servers, the site was receiving about one million hits per day and running on two servers, one for web requests and one for the database and Memcached. After configuring the site to run as fast as I could get it going with PostgreSQL, it was able to handle a fair amount of the requests it was receiving within a reasonable amount of time. When the database grew larger, and some tables held 20 million + rows, PostgreSQL started failing to produce the same performance MySQL could, and as a result, page request time blew sky high.
My main concern is how fast a user retrieves the page they request. Features aren't as important to me =/. I spent a good amount of time configuring the database servers and optimizing my queries to produce the best results I could, but in the end I chose MySQL for its performance.
I can't call it fact, because I don't have numbers, but IMO I'd say MySQL is more practical when your database is your bottleneck. - q3ctf4, on 10/12/2007, -2/+11Yes, Microsoft Access.
"When humor goes, there goes civilization." - tehmoth, on 10/12/2007, -3/+11@blindot: yeah, whenever digg is slow, I always know where to lay the blame. Poor indexing and/or database design from those @digg combined with MySQL's inherent crappiness for anything beyond SELECT * FROM TABLE ORDER BY CLUSTEREDINDEXFIELD;
- burke, on 10/12/2007, -2/+9Making FreeBSD "FAPP"?
- dublindog, on 10/12/2007, -1/+8There is another page at this same site that does a 3 way comparison of Oracle - MySQL and PostgreSQL;
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html - hambend, on 10/12/2007, -3/+9Also note: PostgreSQL has way better GIS support.
- marchaos, on 10/12/2007, -1/+7The configuration of PostgreSQL out-of-the-box is fairly abysmal for performance, which is why people coming from MySQL find it slower, as they generally aren't aware that it requires tuning. Once you tune the settings for the application at hand, I generally have found that PostgreSQL will outperform MySQL in high concurrency applications.
Here's an interesting benchmark that also seems to suggest this:
http://tweakers.net/reviews/657/6 - CHAD3814, on 10/12/2007, -2/+7We recently migrated from PostgreSQL 8 to MySQL 5. While MySQL is slightly faster out of the box then PostgreSQL, we had spent a couple years tuning Pg. The main issue, is you can only grow so much until you need reliable replication. After our transition, everything is working slightly faster then PostgreSQL and with solid ms-response time replication.
- sc0tt, on 10/12/2007, -2/+7I remember reading this a while ago, pretty decent comparision, a few others i read at the time were:
http://www.devx.com/dbzone/Article/20743/1954?pf=true
http://www.databasejournal.com/features/mysql/article.php/3288951
http://www.geocities.com/mailsoftware42/db/ - marchaos, on 10/12/2007, -0/+5@tehmoth: There are replication solutions for PostgreSQL, ie. slony.
http://gborg.postgresql.org/project/slony1/projdisplay.php - tehmoth, on 10/12/2007, -0/+5in 7, the cygwin port did enable you to run Postgres as a service without firedaemon, of course you had to do some unpleasant manual configuration and rely on the cygwin dlls. That has been removed in 8,
- lqqkout4elfy, on 10/12/2007, -0/+5Now another HUGE one for my datawarehousing needs that postgres supports and mysql doesn't is FULL OUTER JOINs. MySQL's dev version (5.1 at this writing) has a new partition scheme that postgres has had for a while now.
- marchaos, on 10/12/2007, -0/+5@ChannelCat
Yep, the performance increase was significant from 8.0 to 8.1. PostgreSQL has had a recent release (8.2), which again promises significant performance improvements over its predecessor. - mointrigue, on 10/12/2007, -1/+5Isn't this all just a silly argument? Both projects are rapidly growing to cover their initial weaknesses, and their feature sets are converging. Everyone you ask has their own "benchmark" to prove that their choice is the "right" one. I'm just grateful that when I do a project that I have two awesome products to choose from.
- ezrider0, on 10/12/2007, -0/+3This article is very old, can the digg.com users please stop taking things from slashdot and posting them here. This article has also been dugg at least twice in the past.
- Jack9, on 10/12/2007, -0/+3This is a clearly biased article, favoring MySQL. While I am a proponent of MySQL, it's unfair to call this unbiased. There's PostgreSQL criticisms in the MySQL feature lists. This is inaccurate.
- tobtoh, on 10/12/2007, -1/+4It may have been decent back when it was written, but some two years later this article is out of date and completely irrelevant.
- Teaboy, on 10/12/2007, -1/+3yay for reply bugs.
- Giga, on 10/12/2007, -3/+5@Dhalgren
I use version 5... I wouldn't be surprised if others do too... - DickBreath, on 10/12/2007, -0/+2@tangledweb
> The GPL does not ban "business use".
Agreed, it doesn't. But MySQL, effectively, does.
MySQL licenses its drivers (ODBC, JDBC, etc.) as GPL rather than LGPL.
I've called and spoken with them about this. Their position is that you cannot use a non-GPL (eg. most commercial) application with MySQL, not because of the database engine, but BECAUSE OF THE DRIVERS.
On Windows, for instance, the only way you get a non-GPL'd ODBC driver is to buy the commercial version of their database server. - dioxide, on 10/12/2007, -1/+3I guess this is becoming truly off topic, but I'd like to point out that not having nulls until version 2 came out doesn't mean much. MySQL was missing more than it's share of common features up until version 5, and they managed to wait until 5.1 to implement events. I will agree that MySQL and PostgreSQL aren't really comparable. Not because Firebird is in its infancy, but the other way around.
Firebird does seem to be much more of a single user rdbms, whereas the other two were designed to be shared by many users. On the other hand, Firebird is much more SQL compliant. Firebird doesn't cost anything to use commercially, MySQL requires licensing in the business environments. Firebird is fast. One of my major gripes with it is its nasty lack of admin tools. Even the CLI tool is garbage.
I could go on, but nobody wants to hear what I have to say about it.
-tommy - codelogic, on 10/12/2007, -0/+2Ease of use?
Please, unless you're setting a database for some personal web site or to learn SQL, ease of use isn't even in the picture.
Availability?
Postgres is hard to get? Try http://www.postgresql.org/download/
And, these are far from the 'most important' aspects when running a high bandwidth database site. - ChannelCat, on 10/12/2007, -1/+3@marchos: Those results are crazy awesome, though I wish they showed what they did to produce them, since they were mostly testing CPUs instead of doing a MySQL vs Postgres shootout, from the way it looks. I used Postgres 8.0 and MySQL 4.1. Has Postgres come a long way in performance since then?
@tehmoth: I give equal time in configuring what I use and optimizing queries by watching them as they are executed. I'm not saying that anyone that uses PostgreSQL "must suck," and don't mean to offend anyone. All tests are prone to error, but calling me a poor craftsman because I didn't produce the results you want is just plain insulting :(. - payrok, on 10/12/2007, -0/+2The article is two years old. It bears no relevance to the newest version of either DB. This is a biased post of stale data that has no relevance to the current products of either DB. Worthless.
- coryking, on 10/12/2007, -0/+2No, because one isn't even a real database. Better to compare MySQL to Access. More interesting is PostgreSQL vs Oracle.
- andrewry, on 10/12/2007, -0/+1Yeah, I use MySQL 5.0 on my local setup. Shame my host hasn't updated to it yet, but 4.* works well with my scripts.
- grumpyrain, on 10/12/2007, -0/+1Damn I hate the digg comment parser. You would think they would be capable doing the escaping of less than and greater than symbols. Ran out of time in correcting it.
"Let A and B be to fields. If A is null, and B = 5, then ...."
Lost about a paragraph there, and yes, spelt two wrong. Lets try again.
Let A and B be 2 fields.
If A is null and B = 5 then A != B should return null according to SQL standard.
Because I can't be bothered escaping all the time, I will use != instead of the usual Firebird PSQL syntax. Many people do this sort of thing in triggers.
IF (OLD.BAR != NEW.BAR) THEN
BEGIN
DOSOMETHING;
END
When they run a query like:
update FOO set BAR = 5 where BAR is null;
Many developers are used to the 'non-standard' null treatment (ironic because the non-standard is probably more common).
OLD.BAR is null
NEW.BAR = 5
but the clause (OLD.BAR != NEW.BAR) does not return true it resolves to null and so would go into the else if it existed. (technically what it should do)
Firebird 2 introduces a new null feature.
IF (OLD.BAR IS DISTINCT FROM NEW.BAR) THEN
BEGIN
DoSomething;
END
Which is the behaviour with respect to nulls that many people expect to see.
You can still write your trigger in Firebird 1.5, even Interbase 6 or earlier. It would just look like:
IF ((OLD.BAR != NEW.BAR) OR
(OLD.BAR IS NULL AND NEW.BAR IS NOT NULL) OR
(OLD.BAR IS NOT NULL AND NEW.BAR IS NULL)) THEN
BEGIN
DOSOMETHING;
END
With Firebird 1.5 or more recent, you can also shortcut it with coalesce assuming there is a particular impossible value, eg 0:
IF (COALESCE(OLD.BAR, 0) != COALESCE(NEW.BAR, 0)) THEN
BEGIN
DOSOMETHING;
END
Firebird 2 also adds the ability to pass and return null parameters from user defined functions.
There are good reasons to use Firebird, and good reasons to use both of these other engines, depending on your problem. But to say Firebird 1.5 does not support nulls is inaccurate. Interbase 6 and earlier supported nulls. - nitsuj, on 10/12/2007, -2/+3Wins for who? Some newbie on shared hosting?
- ChannelCat, on 10/12/2007, -1/+2Disregard that, title confused me.
- joshman5k, on 10/12/2007, -2/+3What is the licensing differences? I think MySQL has a dual license. Where you might have to pay if you are using it for business.
Does PostgreSQL? - ChannelCat, on 10/12/2007, -2/+3I did give it ample time and configuration, but I couldn't get it to perform faster. I had a few people look at my configs, none of them gurus, but they knew their way around PostgreSQL. It could be because my site used 99% selects, but like I said, I gave both MySQL and PostgreSQL months of tweaking before deciding which way to go.
BTW though, if you could check that link and relink me to what you meant to originally, I'm interested in seeing some good benchmarks. That link leads to CPU performance benches. - DickBreath, on 10/12/2007, -0/+1@gyrmnix
MySQL is very specific that you cannot use it with commercial (non-GPL) apps. Not because of the database engine, but because of their drivers.
Now if you have a way to use your commercial app with MySQL, using some other non-GPL driver, that did not come from MySQL, then more power to you.
There also migtht be indirect ways of using commercial apps. For instance, a hypothetical PHP app which is commercial, runs on PHP, which has its own connectivity to MySQL.
BTW, there is no such thing as "copywrite". It is "copyright", as in a "right" to make copies. - Giga, on 10/12/2007, -2/+3MySQL
- coryking, on 10/12/2007, -0/+1Only true because you probably never took the time to rewrite your millions of MySQL style queries. Postgres is only slow when you call it a million times with a bunch of stupid SELECT * FROM blah WHERE blah=?. Since that is about as complex of a query as you can use in MySQL, of course your performance sucks on a real database.
- codenexus, on 10/12/2007, -3/+4There is a very good reason that most use MySQL over PostgreSQL. Nothing to do with speed or features. The reason is market share. My feeling is that those that choose to use MySQL will start life looking at open source program code and honing their teeth on this. There is something to be said also for what is installed on hosting companies by default.
- esconsult1, on 10/12/2007, -1/+2Just moved my Rails site to Postgresql 8.1 from MySQL. The site is way faster and more stable now under bigger loads. One issue with Postgresql is the cost of making a connection. With Rails in production mode, there's a persistent connection, so that's no longer a factor. 8.xx has auto-vacuuming down pat so that's no longer something that we have to worry about, and for now, we can throw lots of mongrels at it without thinking too hard.
Sure, in a few simple cases, MySql was faster, but we find that with the joins we're doing, postgresql performs way better, with a much smaller load on the server. Biggest wins:
* Stability
* Things don't crawl if we have to index a field, or drop an index
* Faster queries that use joins
* Easy setup and tuning (mainly setting decent shared memory)
* Good searches using Tsearch (better than MySQL fulltext search which sucks)
We'll continue to use MySQL for other things, but for this particular application, Postgresql is better. - Sartan4455, on 10/12/2007, -0/+1Are people crazy? Do people still continue to think that reviews of software 2 years old(or even 6 months old) doesn't mean squat. This is like comparing MacOS9 to Windows even though MacOSX is around.
- haackers, on 10/12/2007, -1/+1how about yourMOMSQL
- Dhalgren, on 10/12/2007, -4/+4But how many people are actually using mySQL 5, and not still using 4?
-
Show 51 - 70 of 70 discussions
Browsing Digg on your phone just got easier with our enhancements to the 
© Digg Inc. 2009
— Content created and posted by Digg users is