Database Comparison : H2, HSQLDB, DERBY, PostgreSQL, MySQL
Few days back I posted the comparison table which describes the various features/characteristcs of different free or open source databases, this would help us to understand the various free/open source database offerings. Could help us to decide the database selection for our next project.
I have used postgreSQL, MySQL and HSQLDB now looking forward to evaluate H2 and Derby for real world applications. First encountered open source database was postgresSQL in 2001, as I used it during my course-work in University of Illinois, Urbana Champaign. Then after serving as research associate in Indian Institute of Technology, New Delhi, I joined another company which was writing its own corporate messaging server and client. There I proposed managed to convince my management to use postgreSQL instead of MySQL. Though that time the configuration of postgreSQL was a nightmare for me!
But let’s talk about current developments in open source/free databases. Now several options are available but we need to choose the right database for our application based on the nature of our application. Now people are also pointing about the binaries size of offered databases.
Now we know that because of several enhancements in JRE java speed is on par with C/C++ (though depends on the kind of operation we are performing). There are several pure java based databases, so we should not be thinking as it would be slow, database system used to be IO bound not CPU bound. So the database performance would be directly depending on its implementation, how its in-memory caching and indexing is implemented.
Derby, HSQLDB and H2 are pure java databases; HSQLDB is choice of open source projects like Open Office software suite, JBOSS app server. Derby is supported by IBM and Sun Microsystems and also used by Sun’s application servers. Derby was released by IBM as fully relational Javaâ„¢database as open source to the Apache Software Foundation. for inormation could be found at http://www.ibm.com/developerworks/data/library/techarticle/dm-0410prial/ , now Sun has rebranded it as Java DB, though Cloudscape has been included in the J2EE SDK since 1.2.1, so it was always there, consistently ignored. for Java DB version faqs http://developers.sun.com/javadb/features/faqs.jsp and for java db performance http://jazoon.com/download/presentations/860.pdf
HSQLDB is fast but it has an 8 GB database limit and also it doesn’t support blobs bigger than 4kb. also there are some issues with Transactions,HSQLDB only supports transaction isolation level 0, which may be not sufficient for some projects, though Derby seems to be more powerful and more close to SQL standard (though I have used HSQL DB and its running nicely, once again it’s based on the projects).
The new player with great experience is H2, which is maintained by the original author of HSQLDB. According to H2 official website H2 is faster than HSQLDB, Derby, MySQL and PostgreSQL. Check out the following link
http://www.h2database.com/html/performance.html
As these databases could be embedded in the application, so they turn out to very fast as we get rid of the network-call overhead of calling external databases (like MySQL). I trust while choosing the open source project the community acceptance and activities also plays a great role.
References:
http://developers.sun.com/javadb/features/faqs.jsp#5q1
http://www.h2database.com/html/faq.html#size_limit
http://kasparov.skife.org/blog-live/src/java/h2-aci.writeback
http://developers.sun.com/javadb/features/faqs.jsp
http://www.h2database.com/html/performance.html
http://www.ibm.com/developerworks/data/library/techarticle/dm-0410prial/