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.
- What’s the ’sweet spot’ of your project? For what type of projects should users strongly consider it?
- What type of scenarios does your project not fit into as well? Would you recommend another project in this scenario? If so, which one?
- Of the projects included here, which have you tried? Of those, which ones did you like or dislike, and why?
- What is the future of this project? What’s coming that will ease development?
- 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.
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 |
|
| PostgreSQL |
|
| Derby |
|
| H2 Database Engine (H2DB) |
|
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 |
|
| PostgreSQL |
|
| Derby |
|
| H2 Database Engine (H2DB) |
|
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
|
| PostgreSQL | MySQL: didn’t like
|
| Derby | PostgreSQL: liked
|
| H2 Database Engine (H2DB) | MySQL: liked
PostgreSQL: liked
Derby: disliked
|
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 |
|
| PostgreSQL |
|
| Derby |
|
| H2 Database Engine (H2DB) |
|
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 |
|
| PostgreSQL |
|
| Derby |
|
| H2 Database Engine (H2DB) |
|
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:
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. |
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:
|
| 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. |
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
|
| PostgreSQL | MySQL: didn’t like
|
| Derby | PostgreSQL: liked
|
| H2 Database Engine (H2DB) | MySQL: liked
PostgreSQL: liked
Derby: disliked
|
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:
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:
|
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) |
|
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 |
