Open Source Database Applications Comparison Matrix

  

 Email Article  

Tweet  

  

Use this comparison matrix to learn about the differences between - and relative benefits of - the popular open source databases MySQL, PostgreSQL, Derby and H2 Database Engine (H2DB).

Over the years, offerings in the sphere of Databases available from the open source community have become faster and more reliable and, with that, increasingly popular choices for enterprises globally and across a broad swath of industries. As with a number of other categories of project, open source databases now compete on equal footing with established proprietary offerings such as SQL Server and Oracle. In fact, studies and polls executed in the last few years consistently indicate that MySQL and PostgreSQL both rank in the top ten databases used by enterprises; MySQL frequently appears in the top three along with SQL Server and Oracle.

The following tables represent research completed in the fall of 2007 by OpenLogic. We went to the experts -- members of the OpenLogic Expert Community who are committers and expert users of the projects -- and asked them to answer a set of questions. Experts in MySQL, PostgreSQL, Derby and H2DB responded. Use the resulting information as a tool to plan your projects.

The five questions we asked the experts appear below. To view the detail on the projects* compared across each question, click the question.

1. What's the 'sweet spot' of your project? For what type of projects should users strongly consider it?

1. What type of scenarios does your project not fit into as well? Would you recommend another project in this scenario? If so, which one?

1. Of the projects included here, which have you tried? Of those, which ones did you like or dislike, and why?

1. What is the future of this project? What's coming that will ease development?

1. Are there myths about this project that you'd like to challenge?

For comprehensive information on each project, search the OLEX Open Source Library. For a list of the open source developers we interviewed, click here.

*While no version of the projects is specified, you can assume that the information relates to the latest version in our library at the time of the last update.

Summarized Response

What's your project's 'sweet spot'? For what type of projects should users strongly consider it?

This is a summary of the responses. For full detail, click here.

Project	Summarized Response
MySQL	It supports replication of primary-secondary out of the box. It provides good handling of national languages (you can set collation and encoding per database, table, column). It executes simple commands (select/insert/update/delete) quickly. It is multiplatform.
PostgreSQL	It's great for Web based applications doing either 'daily chores' or research projects. It's fast and easy to use. Updating is easy. Update and replication can be automated with tools. Good documentation.
Derby	Operates in both embedded mode and on a network server. Supports Java based stored procedures, and provides a good deal of flexibility in data manipulation. Tight security features distinguish it from other open source offerings. Good for CDC devices.
H2 Database Engine (H2DB)	A fast SQL database. It's good in Java unit test scenarios requiring a SQL database and accessing any SQL database with the JDBC API (using the H2 Console tool). Great documentation.

Return to the questions list.

What type of scenarios does your project not fit into as well? Would you recommend another project in this scenario? If so, which one?

This is a summary of the responses. For full detail, click here.

Project	Summarized Response
MySQL	Any scenario requiring logic in the database. Not so good for complicated queries. MySQL is not the best choice if the administrator needs to give hints to internal query planner (basically only USE INDEX). Triggers and stored routines have many restrictions (for example one cannot use transactions inside routines or use statements which returns resultsets).
PostgreSQL	One requiring a full journaling system. Use a lighter database system (like dbm) if all you need are very simple key/data pairs.
Derby	High availability and replication are not supported. It's not suitable for applications with hundreds of transactions per second or databases larger than 10 gigabytes. Not suitable for any kind of warehousing scenario, or a scenario which involves the manipulation of spatial data.
H2 Database Engine (H2DB)	Applications that need a large database. Scenarios in which many (3 or more) connections concurrently access the same database.

Return to the questions list.

Of the projects included here, which have you tried? Of those, which ones did you like or dislike, and why?

This is a summary of the responses. For full detail, click here.

Project	Response
MySQL	PostgreSQL: liked In my opinion Postgresql is most the mature open source database engine. It's powerful, full-featured, efficient and robust. Of particular note are the following features: Procedural languages: postgresql makes it possible to write database functions in many languages like java, perl, python, php. It's very useful to explain analyze command, and it shows and measures queries via query planner. A query optimizer with a couple of optimization algorithms. A rich SQL dialect: foreign keys, views, procedures, triggers. Contrib package - extensions which are developed outside postgresql.
PostgreSQL	MySQL: didn't like I found it cumbersome. It lacks concise documentation. It may be faster than PostgreSQL in some circumstances, but if a developer needs to spend twice the time to make it all work, it is not worth it. From the tests I have seen, MySQL and PostgreSQL are pretty much equal in access speed. (Speed issues may arise depending on how you use the database and all systems have advantages and disadvantages in that area.) Especially annoying in MySQL are the documents that indicate functionality that doesn't exist or doesn't work as described. It's possible that the docs referred to a different version, but the appropriate version was not made clear. This made the docs unusable. Outside of that, the SQL commands are in general similar, though PostgreSQL has more functionality. This allows more granularity in your day to day programming of the database.
Derby	PostgreSQL: liked I vote for PostgreSQL when enterprise level features like clustering and high availability are required. Migration from PostgreSQL to ORACLE is very easy and it makes PostgreSQL a good choice for projects that may need to be ported to an ORACLE database.
H2 Database Engine (H2DB)	MySQL: liked Easy to use, very stable, fast. The licensing could be clearer and it's not Java. PostgreSQL: liked Easy to use, very stable, quite fast, clear license. Not Java. Derby: disliked Wish it were easier to use. Wish documentation were better. It was not as stable as others I have used and as its reputation led me to believe.

Return to the questions list.

What is the future of this project? What's coming that will ease development?

This is a summary of the responses. For full detail, click here.

Project	Summarized Response
MySQL	Falcon, a new storage engine.
PostgreSQL	Adherence to SQL standards. Faster queries. Simplified upgrades.
Derby	More efficient replication. Higher availability. Greater support for XML.
H2 Database Engine (H2DB)	Performance enhancements. Better documentation.

Return to the questions list.

Are there myths about this project that you'd like to challenge?

This is a summary of the responses. For full detail, click here.

Project	Summarized Response
MySQL	That it is dual licensed.
PostgreSQL	Because it is free, it must not be any good.
Derby	That it doesn't perform as well as other open source alternatives.
H2 Database Engine (H2DB)	That Java is slow. That transactions lack durability.

Return to the questions list.

Complete Responses

What's your project's 'sweet spot'?

This is the full response. For a summary of the response, click here.

Project	Response
MySQL	MySQL has the following strengths: It supports primary-secondary replication out of the box. It handles national languages well (you can set collation and encoding per database, table, column). It executes simple commands (select/insert/update/delete) quickly. It is multi-platform. Given these strengths, it's the best platform for use developing Web sites because these kinds of projects demand a database that can respond quickly. In Web environments, more than half of the operations required of the database involve reading data. Given that MySQL supports database replication out of the box, it makes scaling sites and Web services easier. The primary advantage of multi-platform support is scalability. I've worked in environments that began by installing MySQL on a single PC, then two, then a SUN SPARC server followed by several Intel IA-64 (Itanium) servers, and eventually two IBM PowerPCs with AIX; with all of them running MySQL as the database server. Additionally, some of the developers had a local copy of MySQL installed on theirs laptops/desktops. Very convenient.
PostgreSQL	Postgresql is also very well documented, with easy to read chapters, many examples that work, and information properly organized by version (7.4, 8.0, 8.2...).
Derby	Apache Derby is a pure Java RDBMS (Relational Database Management System). It can operate both in embedded mode, and as a network server. Derby supports Java based stored procedures, and provides a good deal of flexibility in data manipulation. The tight security features of Derby make it a unique open source database. In addition to regular SQL grant and revoke mechanisms, Derby allows you to configure several options to a determine connection authentication source (LDAP, Built-in, etc.), and also to utilize database encryption using JCE facilities. Derby supports four common isolation levels, and it operates by default in READ_COMMITED mode. In addition, it supports backup/ restore operations and crash recovery for transactions not committed, if for any reason the database crashes. Derby can be used on any J2ME enabled device with support for CDC/Foundation 1.1. Having different versions and modes makes Derby the most suitable database for many projects. For example: those designed for CDC devices, desktop applications which can use embedded mode, and server side applications with multi gigabytes databases that can utilize network server mode. All of these modes and versions have the same functionality, which makes the development cycle less complex.
H2 Database Engine (H2DB)	H2 Database Engine's sweet spot is Java applications requiring a fast or embedded (or both) SQL database. Another strength is Java unit test scenarios requiring a SQL database and accessing any SQL database with the JDBC API (using the H2 Console tool). Finally, the terrifc documentation makes it a good choice for developers unfamiliar with SQL.

Return to the questions list.

What scenarios does this project not work as well in?

This is the full response. For a summary of the response, click here.

Project	Response
MySQL	One which demands putting logic into a database (complicated stored procedures, triggers). For this kind of projects PostgreSQL or Oracle are a better fit. As mentioned before, MySQL is good for simple queries, but not so good for complicated ones (for example, a project with joins from many tables, sub-queries, grouping etc). This is especially true when the tables contain a large amount of data. In my opinion, it doesn't fit in environments like Data Warehouses or Data Mart. Some other MySQL drawbacks include: The administrator can't give hints to internal query planner (basically only USE INDEX). Triggers and stored routines have many restrictions (for example, one cannot use transactions inside routines or use statements which return resultsets). Higher level languages like plpsql or script languages like Perl or Python can not be used to program logic in a database.
PostgreSQL	Even really large projects can use PostgreSQL without much of a problem. The one thing that Postgresql itself does not offer is a full journaling system. Instead, it relies on your file system to ensure data integrity. This means PostgreSQL needs to run with ext3, NTFS or an equivalent file system. I do not know of a database system that replicates a full working journal. Yet if you need to use ext2, FAT or some other non-journaling file system, you would need such a system. On the other hand, these days you should not be using ext2 or FAT or any similar file system anyway. Again, I would recommend a much lighter database system (like dbm) if all you need are very simple key/data pairs. Thus, very small projects should use their own system.
Derby	The current version of Derby does not support high availability and replication, although in the next major version there will be out of the box support for replication and high availability. Derby is not suitable for applications with hundreds of transactions per second, or for databases larger than 10 gigabytes. Additionally, Derby is not suitable for any kind of warehousing scenario, or for any scenario which involves the manipulation of spatial data. That said, there are workarounds for some of these issues. Using Sequoia can resolve high availability and replication issues, for example. For all of the above scenarios, PostgreSQL is the most suitable project because of its stable engine, enterprise level features like clustering and high availability, and supports for spatial data.
H2 Database Engine (H2DB)	Applications that need a large database or scenarios in which many (3 or more) connections concurrently access the same database. Non-Java applications, of course, and instances when the processing of XML documents is required.

Return to the questions list.

Of the projects included here, which have you tried? Of those, which ones did you like or dislike, and why?

This is the full response. For a summary of the response, click here.

Project	Response
MySQL	PostgreSQL: liked In my opinion, Postgresql is the most mature open source database engine. It's powerful, full-featured, efficient and robust. Of particular note are the following features: Procedural languages: postgresql makes it possible to write database functions in many languages like java, perl, python, or php. It's very useful for explaining analyze command, shows and measure query via query planner. A query optimizer with couple optimization algorithms. Rich SQL dialect: foreign keys, views, procedures, triggers. Contrib package - extensions which are developed outside postgresql.
PostgreSQL	MySQL: didn't like I found it cumbersome. It lacks concise documentation. It may be faster than PostgreSQL in some circumstances, but it's not worth it if a developer needs to spend twice the time to make it all work. From the tests I have seen, MySQL and PostgreSQL are pretty much equal in access speed. (Speed issues may arise depending on how you use the database, and all systems have advantages and disadvantages in that area.) Especially annoying in MySQL are the documents that indicate functionality that doesn't exist or doesn't work as described. It's possible that the documents referred to a different version, but the appropriate version was not made clear, therefore the documents were unusable. Other than that, the SQL commands are similar, though PostgreSQL has more functionality. This allows more granularity in your day-to-day programming of the database.
Derby	PostgreSQL: liked I vote for PostgreSQL when enterprise level features like clustering and high availability are required. Migration from PostgreSQL to ORACLE is very easy and it makes PostgreSQL a good choice for projects that may need to be ported to an ORACLE database.
H2 Database Engine (H2DB)	MySQL: liked Easy to use, very stable, fast. The licensing could be clearer and it's not Java. PostgreSQL: liked Easy to use, very stable, quite fast, clear license. Not Java. Derby: disliked Wish it were easier to use. Wish documentation were better. It was not as stable as others I have used, and as its reputation led me to believe it would be.

Return to the questions list.

What is the future of this project? What's coming that will ease development?

This is the full response. For a summary of the response, click here.

Project	Response
MySQL	The most significant plans for MySQL involve a new storage engine called Falcon. This engine will likely replace innodb/myisqm. It's in an early stage of development, but it looks promising. If you're interested in why such a move is necessary, here's some background. Currently, MySQL version 5.1 supports several storage engines, or methods of storing data on disc drives. But usually only two types are used: MyISAM and InnoDB. MyISAM is the default engine. It's fast, architecture independent, and has full text search indexes. However, since it's not transactional, it's not ACID compliant. This disqualifies MyISAM in scenarios which demand data consistency. Also, it doesn't allow foreign keys. Every table in MyISAM is stored in separate files. This issue opens the possibility of data corruption. InnoDB is a transaction safe (ACID compliant) engine. It allows locking data on row level, foreign keys, constraints, etc. Tables and indexes are stored in tablespaces which can be partitioned. But InnoDB has drawbacks. It is slower than the MyISAM engine, it doesn't have full-text-search indexes, and there are some problems with AUTO_INCREMENT indexes. Possibly the biggest issue, however, is that InnoDB was developed by InnoBase, and InnoBase was acquired in 2005 by Oracle. Needless to say, the future of InnoDB in MySQL was uncertain. Hence the development of Falcon, a new storage engine developed by MySQL AB. The Falcon Storage Engine has been designed with modern database requirements in mind, and particularly for use within high volume Web serving, or other environments that require high performance. It's still in the alpha stage and still taking shape, but plans are in place for features such as: Optimizations for multiple CPU environments (multithreading). True Multi Version Concurrency Control (reading/storing data with efficient locking). Advanced indexes. Data compression stores. Intelligent tablespaces (disk management). Implicit save points (for keeping data integrity). From a legal and business perspective, MySQL is in no way obligated to Oracle if it switches to the Falcon project.
PostgreSQL	Postgresql is already in use by companies with databases of about 32 Tb with tables having million of rows; and it just works. What else do you need?! Seriously, like any complex system, it still has some gray areas. For instance, the project is investing energy into improving adherence to SQL standards. Another area of focus is the continued improvement of compatibility, and the filling in of holes in functionality. Still another is improvement of the search algorithm in order to speed up queries. Finally, we are working to ameliorate some issues with the update process. Right now it is classic, but cumbersome, particularly for large databases. The user must dump the database, stop the old system, start the new system and then restore the database in the new system. We are working on a tool named pg_update that would "simply" update an existing set of tables to a newer version of PostgreSQL.
Derby	There are several plans for further development of Derby. One of the most important features, which will make it easier to use and more efficient, is primary/secondary replication, and improvements to availability. Another area of development involves working on new XML processing features and enhanced support for XPath and XQuery over Derby XML type. Security enhancements are also on the way, as well as the  implementation of more features defined in SQL 2003 mandatory and SQL 99 core. The latter will make it easier to write complex SQL statements for Derby.
H2 Database Engine (H2DB)	Project developers are enhancing Derby and adding new features. Some of these are: Optimization for improved performance in larger databases. Improved concurrency. Various ease-of-use features. Documentation improvements. Improvements to extensibility.

Return to the questions list.

Are there myths about this project that you'd like to challenge?

This is the full response. For a summary of the response, click here.

Project	Response
MySQL	People tend to believe that MySQL is dual-licensed because it can be used as GPL software or as commercial software. MySQL is not dual licensed. Additionally, people sometimes misunderstand the GPL, and that frightens them off; but that's a separate topic.
PostgreSQL	Since PostgreSQL is free software, many people believe that it can't possibly be reliable, fast, fully featured etc. In fact, PostgreSQL has most of the features you can find in other DBMSs like Oracle and Microsoft SQL. Not only that, it also includes many very useful features that are specific to PostgreSQL, such as user defined types (not just a record... a full user defined type! With functions to execute when operators are used against variables of that type, etc.). In terms of security, Postgresql is one of the best databases I have seen. First of all, it runs as postgresql (not root as some others do).  Next, by default, the administrator user is not root or admin, but rather a common user of your choice and postgres. Once chosen, that user can do all the things you need a database administrator to do: create other users, databases, and tables;  and assign rights, roles, and groups to other users. The database system is reliable because the entire system is thoroughly tested for at least one month before any given release. As a result, we have only rarely encountered issues with reliability.
Derby	Derby's performance compared to other open source databases is much discussed, and was the subject of a talk at ApacheCON 2005. Viewing that - available on the project wiki - should put any questions to rest.
H2 Database Engine (H2DB)	That it is slow because Java is slow. That transactions in H2 are less durable than other in databases. In my view, this  is not true.

Return to the questions list.

Acknowledgments

OpenLogic would like to thank the following members of the OpenLogic Expert Community for their contributions to this effort and invite the community to email us (docs-at-openlogic-dot-com) if they'd like to augment, correct, update, refute or dispute any of the information included herein.

Project	Contributer
MySQL	Krzysztof Krzyzaniak
PostgreSQL	Alexis Wilke
Derby	Masoud Kalali
HQ2DB	Thomas Mueller