Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL vs. MySQL comparison

Hemos posted more than 7 years ago | from the get-the-morning-starter dept.

390

prostoalex writes "Ever find yourself wondering which open source database is the best tool for the job? Well, wonder no more, and let your tax dollars do the work in the form of Fermi National Accelerator Laboratory publishing this unbiased review of MySQL vs. PostgreSQL. After reading it, however, it seems that MySQL ranks the same or better on most of the accounts." My poor sleepy eyes misread the date of posting on here; caveat that this is more then 15 months old.

cancel ×

390 comments

Sorry! There are no comments related to the filter you selected.

Foreign Keys (2, Insightful)

P(0)(!P(k)+P(k+1)) (1012109) | more than 7 years ago | (#17284914)

From TFA:

Having foreign keys [...] can all be very attractive in PostgreSql—if you need them and you will make any use of them.

Foreign keys are nice, I have to say; I implement them in mysql anyway, in spite of the fact that they're ignored for MyISAM.

Re:Foreign Keys (5, Insightful)

mwanaheri (933794) | more than 7 years ago | (#17284976)

Foreign keys are more than nice, they are essential. Unless, maybe you don't care about the integrity of your data or want to make the necessary checks in their application. The latter should keep their eyes down and their mouth shut if the talk is about 'speed' of any rdbms, off course.

Re:Foreign Keys (2, Interesting)

Tony Hoyle (11698) | more than 7 years ago | (#17285210)

Foreign keys don't speed anything up, they just add an extra layer of checks on your database. Your app should be checking itself anyway.

It's the subselects that get me - without them you have to jump through a lot of hoops. The sentence quoted basically translates as 'as long as you are only storing your CD collection and not doing anything serious with a database, then use mysql'.

TFA also fails to mention that mysql cannot be used in commercial development without paying $200 per client - which makes it more expensive than most other solutions (except maybe oracle, and even they have cheap licenses for some uses).

Re:Foreign Keys (1, Informative)

Sparr0 (451780) | more than 7 years ago | (#17285286)

MySQL has no restrictions on commercial development. They have restrictions on non-GPL distribution. Just like every other GPL-ed product on the planet. Nice try.

Re:Foreign Keys (5, Informative)

Tony Hoyle (11698) | more than 7 years ago | (#17285470)

Untrue.

The client library is GPL. That means you cannot create a commercial program that uses it without using the commercial licensed version. Which is $200 per client

You can't even create a library and not ship mysql - the mysql site is very clear that they consider distributing a program that *uses* mysql as being exactly the same as distributing mysql itself:

http://www.mysql.com/company/legal/licensing/comme rcial-license.html [mysql.com]

Typical examples of MySQL distribution include: ...
        * Selling software that requires customers to install MySQL themselves on their own machines.

Specifically:

        * If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries.

This makes mysql unusable for anything except large products. Our entire product only cost $70 for the single user version. No way in hell we're upping the price by $200 a copy.

Re:Foreign Keys (3, Insightful)

Smidge204 (605297) | more than 7 years ago | (#17285978)

I wans't aware that "commercial application" and "GPL licensed" were mutually exclusive. From the page you linked:

The Commercial License is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercially licensed customers get a commercially supported product with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source.

When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product.


Emphasis mine. In other words, You don't have to pay the $200 if your project is itself compliant with the GPL or similar license scheme.

"Comply with the GPL or pay us $200 to legally use our code or libraries" is not the same as saying "You have to pay us $200 if you plan to sell software you made using our code or libraries."
=Smidge=

Re:Foreign Keys (3, Insightful)

mwanaheri (933794) | more than 7 years ago | (#17285386)

>Foreign keys don't speed anything up, they just add an extra layer of checks on your database. Right, they even make the dbms slower. But the dbms certainly does it faster than the application you write. So, to rely on the checks being made in the application results in a waste of speed on the application side. If I don't care about the speed in the application, why make a fuss about the speed of the rdbms? By the way: I rather have things reliable than fast. Subselects are something I also heavily use (and thus mainly stay away from MySQL) although I think it's better to use views for queries performed more than once in a while. Probably one of the main reasons for the spread of MySQL is the fact that is is frequently pre-installed.

Re:Foreign Keys (2, Informative)

Tony Hoyle (11698) | more than 7 years ago | (#17285558)

An app can do its checks with full knowlege of the structure of data it's writing, and often it's just the comparison of a couple of integers anyway and have no impact on speed. You don't want to rely solely on the DB to do that... you end up having to handle a lot of nasty exception cases. Far better to avoid them first. Put foreign keys in, but definately don't make them your first line of defence.

Re:Foreign Keys (5, Insightful)

Branko (806086) | more than 7 years ago | (#17285760)

Your app should be checking itself anyway.

Actually it shouldn't (in this context). Typically, one database will have several client applications attached to it. If data consistency is not checked at DB level, then:

  • Bug in single application might compromise the data consistency of the whole system.
  • You must keep all of your applications precisely synchronized.
  • You are repeating the job of implementing the same consistency logic across all applications instead of implementing it only once - in database.
  • Implementing these sorts of checks can be difficult to do correctly at the application level in a concurrent environment typical for a DBMS.
  • Data consistency at DB level is directly supported by modeling tools, so you can plan for it and visualize it early enough to spot problems and communicate it to the other team members more easily.

Re:Foreign Keys (5, Insightful)

CaptainZapp (182233) | more than 7 years ago | (#17285256)

Foreign keys are more than nice, they are essential.

Bingo!

It doesn't cease to amaze me, when the Mysql croud argues that "you don't really need those pesky integrity stuff, it just slows down the database."

Guess what guys; You're dead wrong!

Any DBA worth his salary will enforce data integrity on the lowest possible level, which means constraints (however implemented) on the object level.

Sure, you can let your coders in Bengaluru ensure that the primary key is unique instead of just applying a unique index and the same goes for referential constraints between tables. You can implement them in the application just fine until somebody overlooks some minor detail in the code and you're royally fucked!

Again! Foreign keys or triggers are not "niceties". They are essential in implementing an industry strength database; period!

Re:Foreign Keys (5, Informative)

ShieldW0lf (601553) | more than 7 years ago | (#17285048)

This is unbiased? Give me a break.

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0?

MySQL is a great database, if you need clustering but not referencial integrity or ACID compliance, that is.

Re:Foreign Keys (1, Informative)

Anonymous Coward | more than 7 years ago | (#17285122)

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0?
That's because the comparison is dated Feb 2005.

Re:Foreign Keys (3, Informative)

Tet (2721) | more than 7 years ago | (#17285134)

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2

That'd be because the article was written in 2005. Unbiased? Maybe. Vague, unscientific and out of date? Definitely.

Re:Foreign Keys (1)

Linker3000 (626634) | more than 7 years ago | (#17285520)

Could it be coincidence that Digg had a 'dated' article on this earlier today - I can't check as Digg is currently down, but did Hemos 'nick' this from Digg?

I'd hate to think that Digg, Fark, Slashdot, Boing Boing etc. are nothing more than a big news circle-jerk now. (well, more than they are already!)

Re:Foreign Keys (2, Informative)

Phil John (576633) | more than 7 years ago | (#17285158)

MySQL is a great database, if you need clustering but not referencial(sic) integrity or ACID compliance, that is.

Is that the same referential integrity and ACID compliance afforded by using INNOdb as your table type in MySQL? ;o)

Re:Foreign Keys (1)

Eivind Eklund (5161) | more than 7 years ago | (#17285916)

No, it is the same referential integrity offered by
  1. Declaring all your foreign keys
  2. Marking all your MySQL tables as InnoDB, as otherwise (1) is silently ignored
  3. Ensuring that you have specific indexes added for all your columns used as foreign key targets (or was that sources, or both?), as otherwise (1) is silently ignored.
  4. Ensuring that your MySQL is compiled with InnoDB support, as otherwise (2) is silently ignored

In other words, the MySQL referential integrity is a very different referential integrity, one that you cannot trivially see from the table declarations, only by carefully investigating the surrounding conditions.

Eivind.

Re:Foreign Keys (0)

Anonymous Coward | more than 7 years ago | (#17285050)

If you're not using FKs.. Do you really need either? All you need is a file store right? What's sleepy cat doing?

I use PostgreSQL (0)

Anonymous Coward | more than 7 years ago | (#17285098)

Main reasons:

1. Very strict about data integrity. Entering "sdf" into a date field will throw an error in PostgreSQL, while MySQL (even in strict mode) will corrupt it to "0000-00-00" (v5 does throw an error, but it still enters the corrupt data).
2. Mature support for foreign keys. If you do not understand the concept, get the fuck away from a database.
3. Mature support for stored procedures.
4. Mature support for views.
5. Mature support for triggers.

Re:Foreign Keys (3, Insightful)

Brummund (447393) | more than 7 years ago | (#17285172)

Foreign keys aren't "nice", THEY ARE ESSENTIAL TO A RDBMS.

It is the same thinking that probably made the retards at MySQL AB make a datatype that accepts 30th February as a date. (At least did, a few years ago.) Why EVEN include a datetime datatype if it isnt capable of the SIMPLEST validations ever.

Yes, I'm fuming. Those MySQL retards has made a generation of programmers think they can do SQL when they manage to put crap into MySQL. Gahhh, I hope their puny webapps will haunt them down sometime.

(I was once searching for a simple webbased forum, and tested phpnuke. It had the following gem to display the 5 most recent articles in the database:

1. "SELECT * FROM ARTICLES ORDER BY ID DESC"
2. Retrieve all articles from the database
3. Then a for loop printing out the 5 first entries.

They basically transferred all data in the articles database everytime, just to iterate over the 5 first rows. Gahhhhhh)

Re:Foreign Keys (1)

Tony Hoyle (11698) | more than 7 years ago | (#17285362)

1. "SELECT * FROM ARTICLES ORDER BY ID DESC"
2. Retrieve all articles from the database
3. Then a for loop printing out the 5 first entries.

They basically transferred all data in the articles database everytime, just to iterate over the 5 first rows. Gahhhhhh)


Depends if it was cross-database or not, and whether that 'retrieve all articles' really did that or simply created the recordset/cursor.

SQL is a bit fuzzy once you get beyond simple selects... On one database you might want 'SELECT TOP 5 * ...' on another you might want 'SELECT ... LIMIT 5', or even 'SELECT ... WHERE ROWNUM6'. Or you could just use a forward only cursor - provided the DB backend doesn't try to read the whole thing into memory to implement it...

Re:Foreign Keys (1, Insightful)

walt-sjc (145127) | more than 7 years ago | (#17285370)

People coding shitty SQL is independent of their database of choice. MySQL is (IMHO) easier to install, configure, and use than postgres which just makes it more common to use, but MySQL is not responsible for shitty SQL in poorly written PHP apps.

Re:Foreign Keys (2, Insightful)

Brummund (447393) | more than 7 years ago | (#17285742)

A database allowing even simple datatypes to contain crap which is totally inconsistent with any calendar in use for the last 5000 years is responsible for some of the crap .

Re:Foreign Keys (1)

Brummund (447393) | more than 7 years ago | (#17285808)

Have you tried casting a returned column which contains a date like '1900-0-0 18:30' (contained in a datetime field) to DateTime in Java from the resultset?

Unless you cannot assume that a datetime contained in the RDBMS is in fact a valid datetime, you will end up with a huge mess.

But I guess that is why I don't work with MySQL or web programmers anymore. :=)

Re:Foreign Keys (1)

JustOK (667959) | more than 7 years ago | (#17285188)

Foreign keys are nice, I have to say;
I implement them in mysql anyway,

Couldn't finish with a rhyme on MyISAM?

Re:Foreign Keys (1)

Dunbal (464142) | more than 7 years ago | (#17285384)

Couldn't finish with a rhyme on MyISAM?

      You have to think like Dr. Seuss, MyISAM I am I am I am

Re:Foreign Keys (1)

lysdexia (897) | more than 7 years ago | (#17285868)

Although the nun smacked my hams
I gleefully clutched MyISAM

That pretty much scans, I think.

Re:Foreign Keys (1)

bubulubugoth (896803) | more than 7 years ago | (#17285280)

Using MyISAM databases is like using dbase with a sql wrapper, that kind of security u have.

I u want all tne neat features of a full rdbms, use InnoDB instead.

Compraing, for example Mysql MyISAM vs Postgress is a huge mistake, Mysql Innodb and MaxDB are almost a diferent RDBMS from Mysql.

Re:Foreign Keys (1)

hey! (33014) | more than 7 years ago | (#17285330)

This kind of entanglement of logical design (foreign keys) with phsyical design (ISAM) is precisely what a database management system is supposed to do.

It's just like separation of concerns in web site design. You want to leave graphic design to artists and programming to programmers, and to be able to vary each (within limits) somewhat freely.

The same SOC principle applies to database applications.

Foreign key constraints are declarative specifications of a behavior; either you need the behavior or you don't. If you need it, then the decision to use one or the other disk format should not force you to implement it in your program.

If a browser's implementation of CSS forced you to embed font tags in HTML, anybody'd see that's a broken CSS implementation. If a RDBMS forces you to enforce foreign key limitations in application code, it's equally broken.

No Digg (5, Informative)

AKAImBatman (238306) | more than 7 years ago | (#17284936)

1. There's no such thing as unbiased. Especially on a page that gives a fairly abstract review.

2. This article is 2 years old. Everything in its comparisons is out of date.

And it sucks anyway. (0, Redundant)

khasim (1285) | more than 7 years ago | (#17285018)

TFA lists "SQL standard COMPLIANCE" as a category and says about MySQL:
MySQL uses SQL92 as its foundation. Runs on countless platforms. ...

And what is the next category?
PLATFORMS

Which says:
There are binary distribution for most of the supported plataforms.

Why is "Runs on countless platforms" an item under "SQL standard COMPLIANCE" when "PLATFORMS" is also a category?

It looks as if the "reviewer" was trying to bulk up MySQL's characteristics. Even when it was totally inappropriate. A checklist of features would be more accurate in this case. With some evaluation of how well those features are implemented.

Re:No Digg (1)

TracerRX (775473) | more than 7 years ago | (#17285036)

Hehheh... Beavis He said "Digg" on slashdot

Re:No Digg (1)

suso (153703) | more than 7 years ago | (#17285080)

2. This article is 2 years old. Everything in its comparisons is out of date.

Its a government website and so it was written in Government Time (GVT). That means that February 2005 is actually about May of 2009. So clearly the author of the page can't be trusted.

Re:No Digg (0)

Anonymous Coward | more than 7 years ago | (#17285204)

If you knew the people who wrote the page (I do), you would have treated TFA as a joke when it turned up in a google search, and left laughing.. regardless of the date. ;)

They aren't people you want to be making advice for what software to be using. In fact, any software or software related suggestions you see come out of Fermilab you probably want to avoid.

One of the least professional IT departments I have ever had to deal with. Monkeys banging on keyboards would be an apt description.

(anonymous because I still need the job they give me)

Re:No Digg (1)

TrappedByMyself (861094) | more than 7 years ago | (#17285196)

Funny you should mention that. Guess what article appeared on Digg very recently...

Re:No Digg (5, Informative)

electroniceric (468976) | more than 7 years ago | (#17285212)

Just to continue on your good points, especially troubling is the fact that this article compares the then-unreleased MySQL 5 to the Postgres 7.x series. Nearly all the drawbacks to Postgres that this article highlights have been addressed in the 8.x series.

We run Postgres for our main business application and the main limitations are of two forms:
1) Depth of community
The Postgres community is great - very responsive and knowledgeable, but its size is a limitation in a number of ways. The ODBC driver is a bit of stepchild to the main project, and some key functions like dblink that address missing features like cross-database selects are relegated to /contrib, and rely on their individual authors for nearly all maintenance. This means that as a user you are more likely to bump up against the bleeding edge earlier than in communities where these outside-the-core projects are more supported.

For the same reason a key subset of its documentation is very sparse. Documentation for the core system is thorough, clear and concise, but anything in contrib or any projects like the ODBC or .NET drivers are much less like to have the same quality of documentaton. Postgres' extremely powerful GIST indexes are unparalled as a feature, but you need a background in theoretical CompSci to figure them out, thanks to limited documentation (note to aspire database index geeks - I would gladly buy a book on GIST aimed at proficient DBAs who are not giants of theoretical CS). Likewise its procedural languages: thanks to its architecture and open codebase, Postgres offers more server-side languages than any other database that I know of, but few of them have more than basic documentation, let alone the stacks of books you'd find with other procedural languages.

2) Postgres is very close to being a true enterprise contender (unlike MySQL, which is evolving that direction but distinctly further off), but lacks some key features like XML handling, a more comprehensible approach to result sets (anyone who's dealt with rowtypes and casting resultsets can attest to the steep learning curve), and a userbase that has put the product through the wringer. Now that some corporate heads are getting interested (e.g. Sun, Red Hat, EnterpriseDB) hopefully some of these shortcomings will be addressed in short order.

Don't let this outdated, apples to oranges comparison fool you: Postgres is a very solid and usable database.

Summary? (0)

Anonymous Coward | more than 7 years ago | (#17284940)

MySQL as good? You mean besides the weird MySQL-proprietary SQL stuff and the data integrity?

Re:Summary? (1)

Architect_sasyr (938685) | more than 7 years ago | (#17285066)

As opposed to TSQL or any of the other sorts? Or maybe the proprietry stuff in Oracle... which would be the first SQL...

Old news (5, Informative)

daffmeister (602502) | more than 7 years ago | (#17284944)

From the site:

"Last modified: February 15, 2005."

Re:Old news (5, Funny)

suv4x4 (956391) | more than 7 years ago | (#17285248)

Next article on Slashdot: 486 SX vs 486 DX

Or are they bots? (1)

Frosty Piss (770223) | more than 7 years ago | (#17285826)

More nice "editing" from Slashdot "editors". Or are they bots?

This is outdated and incomplete (1)

Tetard (202140) | more than 7 years ago | (#17284950)

MySQL does not have tablespaces, only recently support for views, subselects, transactions was added and triggers and stored procedures are still considered alpha. No bitmap indexes... This is by far not the best comparison I've ever seen.

Re:This is outdated and incomplete (0)

Anonymous Coward | more than 7 years ago | (#17285144)

triggers and stored procedures are both available in a stable release - 5.0

stability (2, Informative)

oliverthered (187439) | more than 7 years ago | (#17284958)

Having foreign keys, views, subselects, and transactions can all be very attractive in PostgreSql -
if you need them and you will make any use of them. If you don't need them or won't use them, then
you're probably better off with MySQL and its superior performance.


PostgreSql is more stable than MySQL, (and has better performance when saturated), shouldn't you take that into consideration?

Re:stability (3, Insightful)

TheRaven64 (641858) | more than 7 years ago | (#17285192)

According to TFA, 'MySQL does very good job even on the busiest sites,' while for PostgreSQL 'Random disconnects, core dumps and memory leaks are usual.' This flies in the face of my own experience and testing results I have seen. Under heavy load, PostgreSQL has a habit of slowing to a crawl, while MySQL just dies. How many web pages have you seen where the entire text was a PHP error saying it was unable to connect to the MySQL server?

Old and wrong (5, Informative)

ldapboy (946366) | more than 7 years ago | (#17284962)

postgresql has a native Win32 version, complete with an installer, service support and does not depend on cygwin.

Re:Old and wrong (3, Funny)

Anonymous Coward | more than 7 years ago | (#17285140)

Yeah but In 2005 it didn't!

You can't say it is "old" and "wrong" when it is wrong because it is old.

I don't think so (0, Troll)

ajaf (672235) | more than 7 years ago | (#17284968)

PostgreSQL seems to be a more "professional" database. Speed isn't everything, check on the article things like DATA INTEGRITY, SPECIAL server-side FEATURES, LOCKING and CONCURRENCY SUPPORT or LARGE OBJECTS.
I choose PostgreSQL for my applications.

Re:I don't think so (1)

TheRaven64 (641858) | more than 7 years ago | (#17285200)

Speed is a lot, but from what I've seen about the only thing MySQL beats PostgreSQL on in terms of speed is simple SELECTs (and then only if you aren't doing many INSERTs, since MySQL has very coarse-grained locking). If that's all you are doing, you would probably be better off with SQLite or even flat files.

Unbiased ? (2, Interesting)

UncleH (8863) | more than 7 years ago | (#17284974)

Just take a look at the description per item. I couldn't possibly call this unbiased in any way.

Out of date (0)

Anonymous Coward | more than 7 years ago | (#17284980)

Look at the bottom of the page "Last modified: February 15, 2005."

I didn't even get to the second item and found information that isn't valid. PostgreSQL installs and runs as service on Windows out of the box (not that I recommend running anything on Windows if you can help it).

Out of date? (0, Redundant)

zootm (850416) | more than 7 years ago | (#17285002)

Last modified: February 15, 2005.

Surely, given the speed that these suites tend to be developed, this comparison is tragically out of date by now? There were a few pieces of comparison which gave the impression that it needed to be updated ("Expect PostgreSQL 8.x to continue this trend."), and I'm fairly sure that both systems have advanced considerably in most, if not all, of the criteria specified.

Re:Out of date? (1)

zootm (850416) | more than 7 years ago | (#17285028)

Well, it appears that just about everyone else managed to type that comment quicker than me. Curses.

Re:Out of date? (1)

Tim C (15259) | more than 7 years ago | (#17285092)

So, would it be fair to say that your comment is out of date..?

Re:Out of date? (1)

zootm (850416) | more than 7 years ago | (#17285102)

I think more "belated"...

biased (1)

dheera (1003686) | more than 7 years ago | (#17285008)

who said everything done by the government needs to be unbiased? this isn't the court or the elections or something, this is them deciding on a piece of software to use. for a little crap decision like this they should pick whatever makes them most comfortable and crank out the data everyone wants.

i think mysql is better because of the name... "post-greeeeee-SQL" sounds awful.

Not similar to my experience (4, Informative)

seebs (15766) | more than 7 years ago | (#17285010)

I have been involved with a smallish ("hundreds") installation of Movable Type using a mysql backend.

One comment spammer can completely annihilate it.

One developer I talked to once did some testing. On one simultaneous connection, mysql was way faster. By five or so, they were close. At ten, PostgreSQL was definitely winning. At a hundred, he was simply unable to get a single MySQL server to complete the test successfully, let alone do it quickly.

The impression I get is that PostgreSQL uses more robust algorithms, with higher constant costs and lower quadratic costs. In any event, never had any problems.

As noted elsewhere, these comparisons are quite old...

But in any event, in my own experience, mysql is a lot easier to blow up by overloading than postgres is, at least if you have a lot of writes going on. For pure-lookup functions, it might do better -- but a lot of modern database apps are pretty compulsive about saving at least something every time someone touches them. (For instance, modern vBulletin saves last visits, threads seen, and so on; all of that adds up to a huge load on the database server.)

Re:Not similar to my experience (1)

Rich0 (548339) | more than 7 years ago | (#17285528)

I've been wanting to switch to postgres for a long time, but do you know what the main thing is that is holding me back?

THERE IS NO ODBC FOR LINUX (or equivalent).

Why should apps that use a db be linked against libraries that are db-specific? Why not make everything modular. Then developers can stick to ANSI SQL and let the user pick whatever database they want (mysql, posgres, oracle, sql-server, access, whatever).

Right now all of my apps support mysql, and a few support postgres. So either I run two database servers and maintain/upgrade/understand both of them, or I just run mysql and hope that the app devs are good since the DB won't protect them if they want to break their referential integrity.

Mysql has come a long way, though, and it concerns me less than I used to. Before I had to cringe whenever I went to their website and was treated to articles that amounted to "well, transactions are overrated anyway - they slow everything down". Well, sure, but what exactly is the point of using a database in the first place?

Re:Not similar to my experience (1)

Tony Hoyle (11698) | more than 7 years ago | (#17285602)

THERE IS NO ODBC FOR LINUX (or equivalent).

WTF? You *do* know Microsoft didn't invent ODBC???

There are at least 2 different ones for Linux that I can think of. Every commercial Unix either has ODBC or has it available as an addon.

Re:Not similar to my experience (1)

seebs (15766) | more than 7 years ago | (#17285722)

Totally agreed. There are a number of apps (WordPress is one of the most obvious) that are permanently tied to MySQL.

Many, of course, make use of the extra "feature" MySQL has. Remember, "embrace and extend" is only evil when Microsoft does it; when it's a product you can download free, being completely trapped in a system that someone else controls the development of is GREAT!

Re:Not similar to my experience (2, Informative)

walt-sjc (145127) | more than 7 years ago | (#17285538)

We use both PostgreSQL and MySQL for a large web-based application that does a reasonable mix of reads / writes (sessions / profiles are in MySQL so it gets MANY MANY writes.) Neither MySQL nor PostgreSQL has problems handling many many connections. Our load frequently hits around 1000 connections on Postgres and 4000 on MySQL on individual database servers (we replicate too.)

Obviously you need to tune your environment (there are a plethora of options including table types which can impact things a LOT) to the load, so if you are running into problems at 100 connections, something is wrong.

Re:Not similar to my experience (2, Insightful)

hey! (33014) | more than 7 years ago | (#17285546)

Somewhere in here, there's a tortoise and hare analogy trying to get out.

It seems to me that if you step back from the details, there is a fundamental difference in style between the two systems that could be summarized thus:

Postgres: emphasizes completeness, correctness, and conformance.

MySQL: emphasizes immediate practicality.

One style is not intrinsically better than the other. Given time their results may begin to converge, which I think is starting to happen. However, I am not surprised that many people are starting to give Postgres a second look after having dismissed several years ago. The Postgres strategy is a long term one. Early adopters of Postgres were a minority with a particular interest in the relational model and for whom conformance was a relatively high priority. Pragmatists who wanted to cherry pick a few of the model's most important advantages were drawn to MySQL.

Re:Not similar to my experience (2, Informative)

Wdomburg (141264) | more than 7 years ago | (#17285748)

For all it's faults, MySQL does scale with a largely read-only data set. We currently have twenty-eight production servers running and about twenty development and testing machines. On the busiest servers we're pushing somewhere in the neighborhood of 4000 queries a second sustained.

Write performance can certainly be an issue, but it depends largely on the application and the table backend. For example, if you can avoid doing deletes on a MyISAM table INSERTs get appended, allowing concurrent reads.

I've not looked at how MT uses the database, but blog software would imply large variable length writes. Definitely not an ideal application for MySQL.

I want to see more databases - Firebird, Derby (2, Insightful)

Anonymous Coward | more than 7 years ago | (#17285020)

Glad to see the comparison, but I would really like to see is a comparison that includes the new 2.0 release of Firebird. Their new release is impressive, but I dont know how the features pan out with MySQL or Postgres. Including Derby would also be nice.

The most important factor to me in any comparison is the licensing agreement. I like a very open agreement and the MySQL license requires you to release the source code to your product in some cases, or you have to purchase a license from them.

MySQL is ridiculously easy to configure (2, Insightful)

MikeRT (947531) | more than 7 years ago | (#17285038)

You have to give the MySQL guys credit for the fact that it is an incredibly easy product when it comes to configuring it for your needs. For me, out of college, going to Oracle was a culture shock because the process of configuring Oracle was so convoluted and drawn out for simple stuff. I know that Oracle and PostgreSQL can be much more powerful than MySQL, but there is something to be said for how easy it is for a developer to install MySQL and just start working with it.

Re:MySQL is ridiculously easy to configure (5, Insightful)

Schraegstrichpunkt (931443) | more than 7 years ago | (#17285106)

You have to give the Notepad guys credit for the fact that it is an incredibly easy product when it comes to configuring it for your needs. For me, out of college, going to Vim was a culture shock because the process of learning Vim was so convoluted and drawn out for simple stuff. I know that Vim and Emacs can be much more powerful than Notepad, but there is something to be said for how easy it is for a developer to install Notepad and just start working with it.

Re:MySQL is ridiculously easy to configure (2, Insightful)

MP3Chuck (652277) | more than 7 years ago | (#17285346)

Part of me sees the point you're making, but another part of me say "Yea, and ... what?" Does Notepad, embarrassingly simple though it may be, not still have appropriate uses?

Re:MySQL is ridiculously easy to configure (4, Funny)

value_added (719364) | more than 7 years ago | (#17285430)

Part of me sees the point you're making, but another part of me say "Yea, and ... what?" Does Notepad, embarrassingly simple though it may be, not still have appropriate uses?

Short answer: No.

Longer answer: None at all.

Re:MySQL is ridiculously easy to configure (1)

MrNemesis (587188) | more than 7 years ago | (#17285426)

Maybe your notepad.exe is a different one from mine, but I can imagine very few bona fide developers (apart from the very newest) using notepad, since IMHO it's unusably bad for all but the simplest of stuff. No syntax highlighting and annoying cursor placement issues being two of the most obvious problems. There are a million and one text editors superior to notepad available for win32 that are just as esy to use fr base functionality.

OTOT, no-one would think of comparing vi/emacs/whatever to notepad. A more valid desription would be, say, kedit and notepad.

Re:MySQL is ridiculously easy to configure (2, Insightful)

multipartmixed (163409) | more than 7 years ago | (#17285476)

Dude, his post is 100% on the money.

The problem is that you apparently missed his point entirely.

Re:MySQL is ridiculously easy to configure (1)

MrNemesis (587188) | more than 7 years ago | (#17285844)

That'll teach me not to read the parent post properly... damn thing was marked "informative" when I read it. Good job I made a fool of myself instead of moderating ;)

Re:MySQL is ridiculously easy to configure (1)

jalefkowit (101585) | more than 7 years ago | (#17285480)

I don't think your example cuts the way you intend it to.

  1. For a lot of tasks, Notepad is all the editor you need (where "editor" is defined as a non-WYSIWYG text processor). For making a quick change to a config file, Notepad works just as well as Vim does.
  2. In Notepad, you open the program and start typing. Very intuitive for new/untrained users. In Vim, you can't start typing until you figure out how to turn on Insert mode. Very un-intuitive.
  3. Notepad on Windows is like Vim on *nix: it's the only editor that is always guaranteed to be there.

So: Notepad isn't as powerful as Vim/Emacs, but that doesn't mean it's not useful -- just that its utility is mostly for basic, low-end tasks where overall power is less important than a quick time-to-completion.

And come to think of it, you could say the same thing about MySQL vis a vis PostgreSQL -- though the MySQL developers have done a better job at scaling up their product than Microsoft has with Notepad.

Re:MySQL is ridiculously easy to configure (1)

walt-sjc (145127) | more than 7 years ago | (#17285646)

Nice troll. Comparing notepad to emacs is not the same as MySQL to PostgreSQL. Not even close.

I think the point that the OP is trying to make is that PostgreSQL can be more difficult to install and use for no damn reason. As someone who uses both MySQL and PostgreSQL, the Postgres guys could (should) take some of the simplicity ideas of MySQL and incorporate them into PostgreSQL which would be a Good Thing. Most of the things that make MySQL easier to install / configure / use have NOTHING AT ALL to do with the actual back-end features of the database.

Re: PostgreSQL is easy too (2, Informative)

ajaf (672235) | more than 7 years ago | (#17285154)

You don't need to setup anything to run it for the first time, only if you want to play with performance, you can start to modify parameters as memory, max connections, etc. PostgreSQL is easy and powerful, just give it a try.

Re:MySQL is ridiculously easy to configure (1)

Tony Hoyle (11698) | more than 7 years ago | (#17285266)

Oracle Express on Windows is dead simple. Click 'setup'. It creates the default tablespaces for you and there's a nice web frontend for creating tables etc.

Perfectly fine for learning it. You can start on the real configuration later.

Happy 2007 (0, Redundant)

Alworx (885008) | more than 7 years ago | (#17285046)

The article is dated Feb 15, 2005.

Since then things have changed a lot, especially regarding Win32 service support, ALTER TABLE features, stability and security.

I suggest we scrap this post altogether

There are several problems (4, Informative)

kahei (466208) | more than 7 years ago | (#17285058)


1 -- This article is years old.

2 -- This article is posted solely to stir up (repetitive) discussion.

3 -- This article pretends that MySQL is a real database, even though in order to do so it has to make gigantic leaps like considering data integrity to be not really all that important in a database.

4 -- This article trolled me.

I'd rather (2, Insightful)

Klaidas (981300) | more than 7 years ago | (#17285078)

I'd rather have a new (not THAT old) comparison between Oracle and MySQL

Re:I'd rather (0)

Anonymous Coward | more than 7 years ago | (#17285702)

I'd rather have a new (not THAT old) comparison between Oracle and MySQL

Why? That's a bit like comparing some ricer's honda to an indy car. Oracle is in a different league.

Oracle vd PgSQL (1)

muyuubyou (621373) | more than 7 years ago | (#17285922)

Why MySQL? it's not in the same league at all. If you are in the market for Oracle, you definitely are not in the market for mySQL.

Oracle vs PostgreSQL would make a lot more sense. 8 series of course.

Outdated and Silly (3, Informative)

ClayDowling (629804) | more than 7 years ago | (#17285094)

It's been a long time since any of their PostgreSQL statements were true. It's a very happy native windows service with a nice installer, and the administrative interface is very easy to use. Let's try posting current reviews of software, rather than reruns from a year or two ago.

I cannot believe... (0)

Anonymous Coward | more than 7 years ago | (#17285108)

I can't believe they even let this post survive. It's old, and it simply isn't fair for either platform's updates and new features. Just delete this crap...

Doesn't mean a thing and this is why ...... (2, Insightful)

Stumbles (602007) | more than 7 years ago | (#17285136)

February 15, 2005

What about clustering? (2, Insightful)

cecchet (931266) | more than 7 years ago | (#17285150)

Clustering and High-Availability aspects are not mentioned at all.

MySQL speed will really depend on the database engine you use (MyISAM or InnoDB do not perform the same!). PostgreSQL performance is pretty much consistent across platforms.

On the HA side, PostgreSQL has maybe less options: Slony/I (http://gborg.postgresql.org/project/slony1/ [postgresql.org] ) for master/slave or Sequoia (http://sequoia.continuent.org/ [continuent.org] ) for multi-master.
MySQL offers MySQL replication (http://dev.mysql.com/doc/refman/5.0/en/replicatio n.html [mysql.com] ) for master/slave, MySQL cluster (http://dev.mysql.com/doc/refman/5.0/en/mysql-clus ter.html [mysql.com] ) for those who want to switch to a new storage engine (NDB) or Sequoia (URL:http://sequoia.continuent.org/) for multi-master with transparent failover.

MySQL replication/clustering needs work too... (1)

jonathan_lampe (943581) | more than 7 years ago | (#17285938)

I've worked with MySQL replication for about three years and I'd have to say that feature isn't quite done. Some of the most annoying features:
    - Manual cleanup of binary change files is sometimes required.
    - Databases in "slave" mode can still be updated by local applications; you can't configure them to only log changes from the "master" database.
    - Automated master-slave role-switching requires you to write some code.
    - The whole "master/slave" terminology. I know it's meant as just computer-speak and the MySQL team is European, but I work in America; it can be offensive in some situations.

MySQL clustering is also only available for a few of the OSs regular MySQL supports; it's not a universal option for all platforms.

Interested to death (1)

tttonyyy (726776) | more than 7 years ago | (#17285152)

I know slashdot is for nerds (and I happen to use mysql databases myself), but honestly - an old article comparing databases? Must... keep... eyelids.. open...

Crap! (3, Informative)

CaptainZapp (182233) | more than 7 years ago | (#17285174)

MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation.

I call pure, unadulterated crap on this one.

One of the major new features in Postgresql 8 was native Windows support. It runs just fine as a service.

This comparision is either very old news, incompetence in action, or, um! strongly biased.

LAMP (1, Interesting)

Anonymous Coward | more than 7 years ago | (#17285354)

Out of LAMP, the only component that doesn't suck goats is Apache. Linux is a total mess (2.6 is unstable to the point of being useless), MySQL can barely be called a database (absolutely key functionality missing, error reporting extremely weak) and PHP is a security joke (unless you add in Suhosin).

The low entry bar to PHP coding and the fact that most of the bargain bin webhosts offer a poorly configured MySQL install as part of a $1/mnth plan only serves to perpetuate this horrid combination of software. What is worse is the way in which it has seeped into corporate applications. I hate you all.

Why we moved from MySQL to PG (5, Interesting)

punker (320575) | more than 7 years ago | (#17285390)

This almost seems like the same comparisons we've been hearing for years.
1) Postgresql is more full featured than MySQL
2) MySQL is faster in a read-mostly environment
That's pretty much the same as the anecdotal arguments have been for years.

          In my job, we moved from mysql to postgres several years ago (around PG 7.0). At the time, we needed to make the move for performance reasons. We are in a read-write system, and MySQL's locking was killing us (this was before InnoDB was well established). The features are better too, as our developers were used to having data integrity features, server side programming, and all of the SQL92 constructs available. We also learned a bit about PG performance, which I'll share.

1) Run EXPLAIN ANALYZE on everything. Postgresql is touchier about query performance than MySQL was. This just needs to be a habit if you're using PG. (You really should do performance analysis no matter your DB. It's just a good practice). The biggest gain will be making sure you're using index scans rather than sequential scans.

2) Use persistent connections. Everyone likes to point out the forking issue with PG vs. MySQL's threaded. PG's connection handling is slow, there's no doubt about it. But there's an easy answer. Just limit how often you connect. If you can keep a connection pool, and just reuse those connections, you'll save this big hit.

3) Full vacuum and reindex regularly. We've found the docs to be a bit off on this. It indicates that you should run these occasionally. If you're in a read-write system, a full vacuum on a regular basis is very important. It really doesn't take that long if you do it regularly. Also, we've had trouble with indexes getting unbalanced (we see 50->90% tuple turnover daily). This has gotten better, but it doesn't hurt to let your maintenance scripts make things ideal for you. So, we run a full vacuum and reindex of our tables nightly through cron.

4) Get your shared memory right. PG's shared buffers is probably the most important config attribute. It controls how much of your DB is memory resident vs disk resident. Avoiding disk hits is a big deal for any DB, so get this right. If you can fit your whole DB in memory, then do it. If not, make sure your primary tables will fit. The more you use the shared memory, and the less you have to page data in/out, the better your overall performance will be.

Most DB systems seem to be read-mostly, so I can understand the performance comparisons focusing on that. In our read-write system though, the locking was the biggest issue and it tilted the performance comparison toward PG.

Re:Why we moved from MySQL to PG (1)

giuntag (833437) | more than 7 years ago | (#17285724)

Dude, I thought this was a guide on optimizing ORACLE performance!
Its sums up neatly all the advice I keep giving to all the noob contractors that develop on sqlserver and then kinda start having nightmares when they are told we will install on oracle in production (and their app gets suddenly 5x slower...)

Re:Why we moved from MySQL to PG (4, Informative)

tcopeland (32225) | more than 7 years ago | (#17285904)

> So, we run a full vacuum and reindex
> of our tables nightly through cron.

I've found that just enabling autovacuum seems to keep things in order. And you can tweak it for individual tables [postgresql.org] if you're so inclined.

Of course, MySQL is effectively two products... (3, Insightful)

itsdapead (734413) | more than 7 years ago | (#17285424)

MySQL/MyISAM is the one with the massive legacy code base, the one that your open-source blogging software uses and probably the one that your web host supports. It beautifully hits the "sweet spot" for data-driven web sites with infrequent and simple updates, where trading integrity for "read only" performance is sensible. It does not even purport to compete with PostgreSQL on features - but it does offer fulltext searches, again

MySQL/InnoDB is the one that offers transactions, foreign keys etc. (ISTR it doesn't do fulltext indexes, though) - this is the "version" that bears comparison with PostgreSQL. I wonder how its user base compares?

(OK - you can mix InnoDB and MyISAM tables in a single database, but you can't use InnoDB if your web host hasn't installed it - heck, one provider I use is still on MySQL V3.23)

Flamewars have tended to pit PostgreSQL against a mythical database with the performance of MyISAM and the features of InnoDB...

As for the GUI software, the MySQL GUI Admin/query browser stuff is shinier than PgAdmin3 - but the MacOS version of the former is a complete crashfest! Neither of them steps up to the plate of providing a FOSS equivalent of (the good bits) of MS Access.

Re:Of course, MySQL is effectively two products... (1)

LizardKing (5245) | more than 7 years ago | (#17285836)

As for the GUI software, the MySQL GUI Admin/query browser stuff is shinier than PgAdmin3 - but the MacOS version of the former is a complete crashfest! Neither of them steps up to the plate of providing a FOSS equivalent of (the good bits) of MS Access.

If you're talking about the MySQL GUI clients that are written in C++, then I strongly suggest you take a look at the source code. About eighteen months ago I tried to port the new clients to NetBSD as the older client wouldn't work with MySQL 4.1 (MySQL AB seem to rewrite their GUI clients from scratch every couple of years). Never have I seen such poor C++ coding in a fairly high profile project. I gave up trying to port them in the end (too many Linuxisms) and wrote a simple client in Java.

Your US at Work (1)

Doc Ruby (173196) | more than 7 years ago | (#17285572)

" let your tax dollars do the work in the form of Fermi National Accelerator Laboratory"

I don''t begrudge the world the science (and other investigations) that Americans pay for. But that summary should read "let US tax dollars do the work".

I wonder how many of Slashdot's foreign readers who usually rail against US-centric language in posts here will complain about how they get a free ride on this research.

15 months old... (1)

Smoking (24594) | more than 7 years ago | (#17285580)

Seeing the current rate of development of both MySQL and PostgreSQL
this can only be a bad joke...

Ok, here is another outdated test (3, Informative)

xyvimur (268026) | more than 7 years ago | (#17285584)

Ok, this is yet another outdated report comparing three mainstream RDBMS'es - MySQL, PostgreSQL and ORACLE. It was done for yet another physical experiment - for choosing the proper system for storing data about the construction process of one of the LHC detectors - ALICE.
And this report is at least professional, which cannot be said about the one mentioned in the article.
http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczy k/db_compare/db_compare.html [dcdbappl1.cern.ch]

more recent benchmarks (4, Interesting)

darekana (205478) | more than 7 years ago | (#17285728)

http://tweakers.net/reviews/657 [tweakers.net]

They compare PostgreSQL 8.2 vs MySQL 4.1.20 and MySQL 5.1.20a.

Re:more recent benchmarks (1)

tcopeland (32225) | more than 7 years ago | (#17285856)

> They compare PostgreSQL 8.2 vs MySQL 4.1.20 and MySQL 5.1.20a.

Mod parent up and all that. We're using PostgreSQL 8.2 [blogs.com] for a small (18 million records) database and are pleased as punch with it. And here, too [getindi.com] .

a more up-to-date comparison (2, Informative)

brentlaminack (513462) | more than 7 years ago | (#17285892)

I did a presentation [laminack.com] at the Atlanta Unix Users' Group [auug.org] this month that is a more up-to-date comparison. It's available in Open Office [openoffice.org] format. You can also get to it from my home page [laminack.com] . I did a similar talk almost four years ago. My conclusion is that MySQL has closed the feature gap with PostgreSQL in recent years. I still give PostgreSQL the edge in features, and MySQL the edge in out-of-the-box untuned performance. I also discuss replication and clustering.

Use "than," not "then," in the blurb at the top! (0)

Anonymous Coward | more than 7 years ago | (#17285966)

I hate it when people misspell/misuse these words.
Load More Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>