eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
In terms of media coverage, open-source databases are having their day in the sun. The most recent tidbit: While the 500+ respondents to Evans Data Corp.s recent Database Development Survey report that Microsofts SQL Server and Access still predominate, MySQL usage by the group surveyed is growing as fast as dandelions in the spring. The survey showed that respondents use of the open-source database grew more than 30 percent last year, compared with a much more modest 6 percent growth in the use of the two Microsoft databases. With LinuxWorld just around the corner, theres only going to be more hubbub around open-source databases—particularly given that practically all the big database and database tool vendors will be there, including IBM, Oracle, Microsoft, BMC and Computer Associates.
Given all the excitement around open-source databases, now is a good time to take a look at which of the two most popular options—MySQL and PostgreSQL—is a “better” choice. Of course, theres no quicker way to get third-degree burns from a flame war than to imply that one database is “best.” Each has their own set of devotees, and both groups are vocal and devoted, and both can give ample reasons why their choice is the right choice.
As pointed out (in a great article thats full of details concerning the relative strengths of the two databases on the basis of features, support, ease of use, stability, speed, existing skills and licensing) by Ian Gilfillan in Database Journal, “best” is a loaded term. “What is best in one situation is not best in another,” Gilfillan writes. “Therefore, the correct answer would be neither is best, and both have their place.”
Amen, brother.
Starting with an objective mind, it can be illustrative to look at what opinionated people have to say both for and against both of the databases. In addition to checking out zines like Database Journal, Ive been haunting Slashdot forums and the blog of MySQL expert Jeremy Zawodny to get a sense of who likes which open-source database for what reasons. Heres a sampling of what Im gleaning:
Next page: Whos winning the features war?
Features
Features. Its a common mantra that PostgreSQL beats MySQL in the features war. Oft-cited is MySQLs lack of stored procedures, subqueries, subselects, cursors and views, and object-oriented table inheritance, for example. Ma Siva Kumar, an entrepreneur who started up a business to develop ERP systems for the leather industry in India, recently told me that his choice to go with PostgreSQL was determined by MySQLs lack of support for foreign keys and stored procedures.
Fair enough—MySQL for a long time left out capabilities that are considered by many to be crucial in a robust database. But its been improving on the features front in leaps and bounds for the past few years. For example, MySQL 4.0 picked up the InnoDB storage engine as a standard feature of the server. InnoDB allows ACID-compliant transactions that enable critical transactions, as opposed to the default MyISAM table type, which is fast but not as useful. (Click here for another article by Gilfillan that gives a roadmap of other MySQL 4.0 features that might surprise you.)
Theres more coming, too, in MySQL 5.0, the alpha code that was announced today. Stored procedures support based on SQL:2003, a common standard for syntax, data structures and retrieval processes of SQL databases, is the big news here. The new functionality will also integrate server-side cursor support.
Has MySQL caught up to PostgreSQL yet, features-wise? Many think not. A typical comment, contributed by Kevin_Stevens and trolled from Slashdot: “Just about every database professional I have met, if they had a gun put to their head by someone and had to set up a free database, they would choose PostgreSQL. MySQL has made some strides, but its just not PostgreSQL.” Better yet, read the full thread.
Next page: Which database is faster?
Speed
Performance. Users/testers of the two databases say that when the data model is complicated enough, PostgreSQL is faster than MySQL. Indeed, updates in PostgreSQL 7.4 included an impressive list of performance enhancements. To wit:
Several major performance enhancements have been added in Version 7.4, enabling PostgreSQL to match or exceed the speed of other enterprise database systems. These included:
- Hash aggregation in memory to make data warehousing and OLAP queries up to 20 times faster;
- Improvements in subquery handling by the planner resulting in up to 400 percent speed increases in some complex queries;
- New script to set more reasonable postgresql.conf defaults for shared buffers, yielding better “out of the box” performance;
- New wire protocol (Version 3) increases the speed of data transfers;
- Enhanced implementation of functional indexes allows better indexing on custom data types and composite fields.
On the other hand, MySQL proponents say that, if configured properly, MySQLs MyISAM tables are indeed lightweight and make for a faster database.
Next page: Which database has better support?
Support
Support. MySQL has a much vaster community to lend support than does PostgreSQL. But whats more important, a huge number of people to turn to for answers or a concise list of responsive and knowledgeable community members? While some have charged the PostgreSQL community as being unfriendly, most PostgreSQL users point to fast, expert help thats gotten them out of a pinch on more than one occasion.
“I keep in touch with the discussions taking place at the mailing list pgsql-general to know the issues faced by the others and how others are helping them,” Siva Kumar told me. “I remember one instance when a production-critical issue was sorted out by the community in a matter of hours.”
And after all, when push comes to shove, thats what matters: that there are community members, be they one or 1,000, who have the answers and the time to help you out.
Next page: A few words on stability, ease of use, existing skills and licensing.
Stability, ease of use,
etc.”>
There are other grounds on which to compare the two open-source databases, including ease of use, stability, existing skills and licensing. As with the other categories I went into above, much of the feedback youll get on ease of use and stability is subjective. Opinions on stability vary according to particular users applications and environments. Opinions on ease of use depend on what other database(s) a given user is familiar with. Those whove worked with Oracle, for example, report that they feel at home and comfortable with PostgreSQL. Users of smaller, desktop or dedicated Web server databases such as Foxpro tend to be more comfortable with MySQL. Thats a generalization, but its based on a good amount of confirmation gleaned from users and from forums.
When it comes to existing skills, chances are better that youll find MySQL skills in your organization than that youll find familiarity with PostgreSQL. MySQL wins, again, by sheer numbers.
MySQL is licensed under the GNU GPL (General Public License) but offers an alternative commercial license for those who dont want to be restricted by that license. PostgreSQL uses the BSD license, which stipulates that the credits have to be maintained but beyond that you can use it pretty much as you see fit.
And in summary, not to be lazy, but I just cant think of a better way to close the topic than by reprinting a blog entry written by Zawodny:
“[MySQL] does many things [other databases] do not and doesnt do things they do,” Zawodny writes. “It satisfies different needs. … I care about how MySQL fits the needs of people I work with. Often it does. Sometimes it doesnt. And I have little problem figuring out the difference. But, hey, if you feel like bitching MySQL, I wont try to stop you. … But the recovering Catholic in me knows that youll probably go to hell for it.”
Again I say, hallelujah and amen. On that note, Ill say best of luck in scoping out open-source databases, and I hope to see you at LinuxWorld.
Let me know why you love or hate MySQL or PostgreSQL. Write me at [email protected].
Database Center Editor Lisa Vaas has written about enterprise applications since 1997.