The Hunt for the Right Open Source Database
Novell Cool Solutions: Feature
Digg This -
Posted: 16 Oct 2005
By Lance Obermeyer, Ph.D, Director of Products, Pervasive Software, Inc.
The open source database market has rapidly expanded over the past decade, becoming a rather competitive arena. This growing competition means there are options for corporate IT departments wanting powerful database systems to run their enterprises or on which to build business applications.
Choosing the right product for a particular environment can be challenging unless you know the fundamental facts. Two of the more open source databases are PostgreSQL and MySQL. Both have loyal followers and many comparable features, but also differ in important ways. Because proprietary closed-source databases from providers like IBM, Oracle and Microsoft often exceed IT budgets, PostgreSQL and MySQL have jumped into the forefront as low cost alternatives. Which one is the best? It depends on what you're hoping to accomplish.
There are three main areas of difference: licensing, technology and performance. This first article of four intends to highlight these differences; the following articles will further detail the specific areas mentioned. By comparing these key features between PostgreSQL and MySQL, the decision of which open source database is best for your organization should be made easier.
Whereas Postgres offers the royalty-free BSD license without limitation of use or distribution, MySQL AB provides users with two options: an open source license or a traditional commercial license. The open source license is the GPL extended with the right to use MySQL in conjunction with PHP. In the case that a MySQL user's usage is 100 percent GPL-compliant or includes PHP, the open source license can be used and the database is therefore free of charge. However, many corporate use cases do not meet these terms, especially ones that include distributing proprietary software between organizations. For example, an ISV shipping proprietary software with GPL components to a customer is not within the GPL. Similarly, an IT organization shipping an in-house application to a subsidiary in another country is also not in compliance since the home office and the sales subsidiaries are normally different legal entities despite being owned by the same parent company.
MySQL AB recommends for its customers to avoid GPL restrictions and uncertainty by purchasing its commercial license. Why? Because MySQL AB is essentially a traditional software company. It sells commercial licenses, and uses part of the proceeds to pay its developers. Although MySQL AB is clearly an open source supporter, it is in its best interest to sell licenses to organizations with closed source applications. Hence one of its many warnings on its Web site, "We recommend the commercial license to all commercial and government organizations.?
PostgreSQL, however, is different. PostgreSQL was originally created at the University of California at Berkeley and was released to the open source community under the BSD license. This license makes it royalty free and distributable without restriction. It does not matter whether the application being used with the database is closed source or whether the environment is commercial, or whether the database is embedded within another application or used in a stand-alone fashion. PostgreSQL is the product of a classical open source community and a single company doesn't control it.
Differences between PostgreSQL and MySQL usually trace back to different design goals. It owes much to its Ingres predecessor, which is actually the code parent of Microsoft SQL Server and Sybase ASE, among others, PostgreSQL was originally designed to be an enterprise-quality database and early development goals were to maintain the robustness of Ingres while adding advanced features such as extensible data types.
MySQL, on the other hand, does not benefit from such heritage. MySQL is based on a relational engine attached to an ISAM storage engine. This architectural history has both benefits and drawbacks. The benefit is a precise separation between the SQL processor and the storage engine. This enables MySQL to support multiple "table types" for storage, each one offering different features and benefits.
For example, the MyISAM table type is fast, but lacks transaction support, while the InnoDB table type supports transactions but at the sacrifice of execution speed. This adds unpredictable behavior (what happens when there is a transaction abort on a table that doesn't support transactions) and reduces reliability.
Essentially, PostgreSQL and MySQL are built for different tasks. MySQL is best suited for simple Web queries for dynamic Web sites. It is ideal to being the database underlying simple Web sites, as shown by its success in the LAMP stack (Linux, Apache, MySQL, PHP). PostgreSQL is designed for more complicated missions like those traditionally serviced by the enterprise databases. Those scenarios leverage its advanced features like a rich transaction model with triggers, stored procedures, and views. It is also ideal for driving Java Enterprise Edition (AKA J2EE) Web sites that use Enterprise Java Beans.
The performance characteristics of the different databases are too complicated to adequately cover in a paragraph or two. Each database will perform differently given different workloads. In general, MySQL will outperform when there are simple queries and few readers/writers. PostgreSQL will outperform under more demanding workloads.
The Bottom Line
Choosing the appropriate open source database begins by first determining what features are most critical to an organization's needs, then matching those requirements with a specific product's offerings. Cost, consulting services, and ongoing support are important metrics deserving equal attention.
Next month's article will detail the licensing and business model differences among PostgreSQL and MySQL that many people misunderstand ? and later realize - are vital in choosing the right product. Other articles will cover technology and performance.
For more information go to: http://www.pervasivepostgres.com/LP/csp
Link to article: http://www.pervasivepostgres.com/postgresql/the_source.asp
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com