PHPBuilder - MySQL and PostgreSQL Compared Page 3

RSS Twitter

MySQL and PostgreSQL Compared - Page 3

by: Tim Perdue
July 30, 2000


The numbers for MySQL ring true with what most people already know: it's a fast, although lightweight database that will probably serve well for the vast majority of web sites. However, if you plan on having a high-traffic site (say, greater than 500,000 pages per day), then forget MySQL as it can tend to fold up and die under load. Anyone who has ever visited slashdot can attest to the fragility of its setup (mod_perl and MySQL).
But again, the vast, vast majority of web sites fall well under the 15-pages per second demonstrated by MySQL here. If you ever surpass a sustained 15 pages per second, you'll be delighted to fork over the cash for a bigger server or an Oracle license.


Obviously, the advantage MySQL has over Postgres is performance. It also has some more powerful admin tools included in the distribution (mysqladmin allows you to watch processes and queries in-progress), like hot backup, a file corruption recovery tool and a couple others.
I'm also a fan of MySQL's command-line tools. You can see database and table structures using describe and show commands. Postgres' commands are less obvious ( \d to show a list of tables for instance).


The first thing you hear from hard-core database gurus is that MySQL lacks transactions, rollbacks, and subselects. You'll really miss transactions if you're trying to write a banking application, accounting application, or trying to maintain some sort of counter that needs to increment linearly over time. Forget attempting any of those with released versions of MySQL (it should be noted that the unstable 3.23.x series of MySQL now includes transaction support).
For many, if not most, web sites out there, MySQL's limitations can be overcome with a little elbow grease on the part of the developer. The primary feature you'll miss in MySQL is powerful subselect syntax that is present in almost every other production database. If I had a nickle for every time I could've used subselects in MySQL, I'd be able to buy a case or two of beer. In other words, this missing feature can be a pain in the neck, but it can be overcome.


MySQL loses points in the long-term stability department. Simply put, MySQL gives up the ghost randomly and for no obvious reason after running for semi-long periods of time (say 30-60 days). Many developers will compile MySQL "statically" for just that reason, and doing so has helped some people.
That problem again can be overcome with a good pager or a simple crontab entry that kills and restarts MySQL monthly. Not that I find that at all acceptable, but it is a solution.
Where MySQL loses points in the daemon robustness department, it makes up for it by apparently never corrupting its data files. The last thing you want is your precious data files fouled randomly, and MySQL does well here. In over a year of running MySQL, I haven't ever seen a single case of database or index corruption. In the same timeframe, I have done 2 or 3 recoveries of a couple different Postgres databases. (Regardless, backups are always your best friend, as shown by the database fiasco here on PHPBuilder.)

« Previous Page
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Tim Perdue



(Maximum characters: 1200). You have characters left.