MySQL vs. PostgreSQL

[[deleted]]

"I would say they pretty much run on the same platforms. Although you can download binaries of MySQL for five more platforms than Postgres. (Of course you can simply compile either of them on most any OS) "

Seems like you have answered your own question there :-)

MySQL is for convenience, so they supply binaries for different OSes. PostgreSQL goes for quality so they supply sourcecode. All the serious database users run un*x so they don't need binaries anyway 🙂

In fact PostgreSQL does not run natively on windows (yet) it requires that you run a un*x emulator called cygwin. But again, no serious database user installs a database on windows unless it is MS-SQL.

Slashdot does use mysql, but they seem to have serious issues with it:
http://slashdot.org/faq/tech.shtml#te050

"Slashcode itself is based on Apache, mod_perl and MySQL. The MySQL and Apache configs are still being tweaked -- part of the trick is to keep the MaxClients setting in httpd.conf on each web server low enough to not overwhelm the connection limits of database,..."

"Fault tolerance was a big issue. We've started by load balancing anything that could easily be balanced, but balancing MySQL is harder. We're funding development efforts with the MySQL team to add database replication and rollback capabilities to MySQL (these improvements will of course be rolled into the normal MySQL release as well)."

Looks like what they want is PostgreSQL with replication, but instead they cling to mysql, probably because they have a ton of badly documented code that they fear to port to postgreSQL :-)

[ultraslacker]

I would say they pretty much run on the same platforms. Although you can download binaries of MySQL for five more platforms than Postgres. (Of course you can simply compile either of them on most any OS)

Compare mysql and postgresql under heavy load on *bsd and you will quickly gain appreciation for postgresql. Perhaps chris meant postgresql runs better on more platforms than mysql? I won't blame mysql completely for this one, but I see the process based model of postgresql as more suitable to my needs - and better groundwork for the future of postgresql, as I see it.

Mysql was meant to be a fast database, at the expense of ACID. Consider some elements of mysql - thread based, replication*, transactions, foreign keys, myisamchk - all these are signs of wrong headedness, bad judgements, poor instincts and will keep mysql from being regarded highly by dba's.

Interestingly, whenever these discussions come up, it seems most people who have used both weigh in on the postgresql side.

• True, postgresql needs replication, but not how mysql has implemented it!

[nnichols]

Now that I've come round from the ear bashing I received (thanx peeps), it was useful to make my completely rediculous comments directed at postgresql.

I have had a brief play with pg and done a very abbreviated translation of one of my apps over to pg. I'm intrigued and will be looking at it further.

Can one of you please suggest a bible for pg? A really good reference would be useful.

I apologise again for my previous, ignorant posts.

[ultraslacker]

PostgreSQL: Introduction and Concepts is a good primer and goes into some detail. It was written for 7.2.

And of course there is a wealth of documentation, tutorials, and reference materials at techdocs.postgresql.org

[jerdo]

Why is it when someone points out a flaw in an argument of "Postgres vs MySQL" the Postgres militants always come back with "Well Postgres is more powerful"? I never said Postgres wasn't more powerful, I just pointed out a flaw in the argument that stated Postgres runs on more platforms than MySQL when it does not. If you are going to convince DB newbies that Postgres is the better choice please at least respond to their queries instead of going of on some other tangent. These wild-tangent, mine's bigger than yours, arguments are what keeps great things in the background to lesser things (Linux -> Windows). Also, vincente, please don't ever consider MSSQL a real SQL database either. The arguments presented against MySQL can very easily be translated against MSSQL, as Microsoft thumbs their nose to quite a few SQL standards in MSSQL (believe me I've dealt with MSSQL and having to change column types to fit the Microsoft standard instead of the SQL is quite rediculous).

[[deleted]]

"Also, vincente, please don't ever consider MSSQL a real SQL database either. The arguments presented against MySQL can very easily be translated against MSSQL, as Microsoft thumbs their nose to quite a few SQL standards in MSSQL (believe me I've dealt with MSSQL and having to change column types to fit the Microsoft standard instead of the SQL is quite rediculous)."

I consider MS-SQL to be a 'real' database in the sense that you can build a proper application with it, it has all the features that a real database needs (unlike MySQL).

But ofcourse I would never recommend using it, the fact that it runs on windows is enough for that, and the strange MS-dialect of SQL makes my skin crawl too, but compared to MySQL it is a much better choice for those who have this strange urge to run databases under windows.

[csn]

What specifically makes Windows (2000, XP, ...) a poor choice as a database server?

[[deleted]]

"What specifically makes Windows (2000, XP, ...) a poor choice as a database server?"

There is nothing specifically wrong, it's a combination of many things. The reboot-to-activate stuff is wrong, you can't reboot a database server to install some feature. And there's the endless stream of security issues, the GUI that cannot be switched off and is allways using memory, cpu and potentially generating (fatal) errors.

The idea behind a good database server is that it is a database server and nothing else, the fewer other things it does, the less chance there is that those other things interfere with the database's core business: being a database.

[csn]

Oh right - that rebooting is really annoying. Especially with all the updates that seem to come out. Haven't rebooted Linux in months...

See what one of the PG hackers (Tom Lane) has to say about Windows:

http://candle.pha.pa.us/mhonarc/todo.detail/win32/msg00011.html

[Sxooter]

Originally posted by jerdo

Oh, and just to play devils advocate. Sxooter, I think chriskl points out a couple of things that are needed in Postgres that might already be available in Oracle, most prominent the integrated replication. And correct me if I'm wrong but doesn't slashdot use MySQL? (At least it did as of April 2002, and this is a reference to this comment "I.e. you don't really care whether your websites stay up under Kuro5hin / slashdot load levels")

"Integrated" does not necessarily mean better replication. While replication for postgresql is currently available in several free and commercial forms, the fact that it's not integrated is hardly an issue. Is it available? yes. does it work reliably and well? yes. Is it hard to install? no.

My main point wasn't that there weren't things that postgresql needed to have before it would be a full match for Oracle, but that the guy saying Postrgresql was a pale imitation of Oracle had no clue what he was talking about, he was just flaming and likely couldn't tell the difference between asynchronous replication and syncrhonous replication. I.e. he was using what little knowledge he'd got in a magazine.

So, the integrated part of that means little to me, as those other criteria I pointed out are far more important. Is the fact that the replication is integrated worth $40,000 a year per CPU to most folks?

The real thing Oracle has that Postgresql would need to compete with it is load balancing clusters. And that is still a ways away. If you don't need load balanced clutsters, there isn't much you can do in Oracle you can't do with Postgresql.

My point about slashdot and kuro5hin was that they CAN'T handle their load. They crash all the time, and have had to buy truly huge database servers to get stable operation from MySQL. MySQL has serious issues under heavy parallel load that don't show up until you're committed to it as your database.

Sourceforge is a great example of how great postgresql can do. It was actually much faster, in my opinion, when it was running on postgresql before they switched to DB2 in a marketing led maneuver last year. It took almost six months for the search engine part to start working halfway decent, and it's still slower doing searches than it was on postrgresql, and it still doesn't match nearly as many terms. I'm guessing that had to cut down on the size of the keyword catalog to get decent performance.

[Sxooter]

Originally posted by csn
What specifically makes Windows (2000, XP, ...) a poor choice as a database server?

I think there are many things that make all flavors of Windows a poor choice for a database server. One is that it tends to fail under heavy load.

The next reason is that windows has a heavy process / light thread design, which means that it expects to run a couple of heavy processes each running dozens of threads.

Multi-threaded is a great way to gain performance, but unfortunately, it can lead to a rather fragile program environment, since one crashed thread will usually kill all it's brother threads.

The next reason is that if you open a file in windows and let the kernel buffer it, you can't force a flush of that file from the kernel buffers. You can force one from your own program buffers, but the kernel doesn't guarantee to actually flush buffers when you fsync.

So, if you want reliable buffering, YOU get to do it or you can let the kernel do it and hope the OS never crashes.

There's been a thread from Tatsuo on the pgsql-hackers list lately about power off failure testing on Windows failing. Basically, what it's come down to is that in order to get reliable performance on windows with a power off failure, the hackers have to keep a list of all dirty files in an array and flush them individually to get buffered performance WITH reliability. In any flavor of Unix, a simple fsync() is all you need.

And the last reason I consider Windows not worthy of hosting a database is that it changes too much from version to version, and you HAVE to upgrade to a new version every 5 years or so to stay supported. If it was just one or the other I wouldn't mind, as either condition wouldn't be so onerous, but the two together make for serious maintenance issues of legacy application databases, driving up TCO.

[csn]

Anybody see this article?

http://www.cnn.com/2003/TECH/ptech/03/12/fortune.ff.open.source/index.html

Scott McNealy thinks Google and Yahoo use MySQL for everything!

[csn]

Great points about Windows - plus I guess you could throw in the license costs of OS(s) and database(s).

[csn]

MySQL 4 has finally emerged from beta 😛

http://www.mysql.com/downloads/mysql-4.0.html

[Sxooter]

Originally posted by csn
Great points about Windows - plus I guess you could throw in the license costs of OS(s) and database(s).

True. While those don't usually add up to much if running MySQL or Postgresql, where you don't pay by the connected user, I could easily see the next flavor of windows require licensing for each incoming TCP/IP connection you handle.

The last reason I wouldn't run a database or any other critical app on Windows is I don't trust Microsoft. If they want to get into the market you're in, do you really think they'd hesitate to use their internal knowledge of Windows against you?

[ultraslacker]

Scott McNealy thinks Google and Yahoo use MySQL for everything!

And he is wrong on both. AFAIK Google uses a distributed filesystem for the data.

[csn]

Originally posted by Sxooter
True. While those don't usually add up to much if running MySQL or Postgresql, where you don't pay by the connected user, I could easily see the next flavor of windows require licensing for each incoming TCP/IP connection you handle.

The last reason I wouldn't run a database or any other critical app on Windows is I don't trust Microsoft. If they want to get into the market you're in, do you really think they'd hesitate to use their internal knowledge of Windows against you?

I wouldn't trust MS either - either as a competitor (like Netscape) or customer (they always seem to be tinkering with their licensing terms). Plus there's the open vs. closed source factor. You're able to examine the code and make your own changes, not wait on MS (or some third-party if they're able) to maybe implement stuff.

[chriskl]

Wow - this thread sure has blown out since I made my post!

With regards to the number of platforms issue, PostgreSQL runs on the PlayStation 2 - nuff said! 🙂

Chris