MySQL And PostgreSQL Compared

unicron writes: "PHPBuilder has got an article MySQL and PostgreSQL Compared. " Everyone who has used these DBs knows the differences between them, and now that licensing isn't one of them, let's try to talk about where each excels and the other fails. I know people get almost as religious about their DBs as they do about OSs and programming languages, but let's try to get somewhere here and not just needlessly flame and rant, mmkay?

Comments

Tool for the Job

[Greyfox]

DBase III wasn't a very good database program, but in its heyday millions of people used it and it got the job done for them. Even relatively inexperienced users could make use of it and write simple programs to manipulated their data. Even though it sucked, it was the right tool for a lot of jobs at the time.

Compared to DBase III, both of these tools are excellent. I wish I'd had either one a decade ago when I started work doing clipper programming for a dog track related publishing company.

For the dog track application I would have preferred Postgres; the rollback support would be pretty compelling for an application like the one we were doing. For something where I was just kicking around a database (Which I've also done a lot of) MySQL would be perfect. MySQL would be ideal in something like the RHS Orchid Registry, for instance.

If application bigotry keeps you from choosing the right tool for a job, you will be a less valuable resource to those who employ you. Not too many people seem to "Get" this. People are often surprised that I will, on occasion, suggest that Microsoft products are the best tool for what they're trying to do. Usually those people asked me expecting a "Windows sucks use Linux" spiel, but if I think their situation warrants it (Inexperienced user, just wants to browse the web, word process and send E-Mail or wants to play games at all) I'll tell them to use Windows.

Re:You know...

[wik]

The article was listed as a column, not a full-disclosure benchmark report. I'll let the author slide on those points, but here are some things to think about when running a benchmark:

• He gave a very vague description of the layout of his database and the nature of the queries. Without that, it is impossible to reproduce his results.
• He did not describe his client/driver configuration, for all we know, they could have been programs running on the same machine as the DB server
• For database benchmarks such as TPC-C (you can see the stringent reporting specifications at www.tpc.org) it is common to allow the driver program to warm up the database for a long amount of time (45 - 60 minutes seems to be common now, depending on the size of the database) then allow the database to reach steady state after at least 20 minutes. He does not mention whether he gave time for the database to stablize. Transient numbers are not a good indication of actual system performance.
• His reported statistics are lacking. Saying that PostgreSQL is 2 - 3 times faster than MySQL leaves a 50% uncertainty in his measurements. At times, he reports 2 - 3, and others he reports simply 3. In fact, I don't see any solid measured numbers for queries/second, transactions a second or some other metric.

Granted, most websites running either of these software packages are probably not going to see very heavy loads, but if you're deciding between two vendors, it's good to know exactly *what* you are comparing.

Re:Flame

[wik]

Try the TPC-C reports here. These are not specifically comparisons between vendors, but they are all reports of systems running a common benchmark. The reported benchmarks all went through approved TPC audits before being released. (On an aside, Microsoft just withdrew their results on SQL Server 2000 because they failed the audit, it's on ZDNet somewhere, I don't have the link).

Re:8K limit ?

[paulrussell]

> my sites all have whole articles, pictures, etc... into a MySQL database.

(Warning: DBA in a previous life - expect pain ;)

I really wouldn't suggest placing text articles, images or other large objects into a database tuple. Modern databases (including PostgreSQL; not sure about MySQL) have support for 'BLOBs' (Binary Large Objects), which allow you to associate very large files and objects with a particular tuple.

The BLOBs are stored as part of the database, but not within the relation itself. This works a *lot* faster than putting the images or text inline with the tuple, and is I suspect partly why the folks at PostgreSQL haven't yet fixed the 8kb limit.

He missed Stored Procedures!

[Ami Ganguli]

The reviewer mentioned Postgres triggers, but didn't make it clear that triggers use a more general feature called stored procedures.

By coding lots of low-level functions as stored procedures that are stored _inside_ the database I can drastically reduce the number of database calls my application has to make.

The benchmarks he used can't show this (because he said himself that he doesn't use these features) but proper use of stored procedures should improve performance drastically while simplifying application code.