Open Source Databases Comparison

It is not true that Derby cannot run as a server while it's running in embedded mode. See Embedded server example in Derby Server and Administration Guide: http://db.apache.org/derby/docs/10.3/adminguide/radminembeddedserverex.html

It is also important to point out that Derby is a fully ACID-compliant database, whereas H2 lacks the D (durability). See for instance http://weblogs.java.net/blog/davidvc/archive/2007/05/that_little_iss.html

I find the performance comparison on the H2 page a bit biased. The total number shows a significant difference between H2 and HSQLDB. If you look closer at the numbers, this is a result of significant difference in performance in only one of the tests. For most tests, the performance is similar.

With respect to Derby performance, it looks from the numbers as it is done without reusing prepared statements. Derby is not the best at ad-hoc execution of statements, but much more efficient if the application is reusing prepared statements. Also, Derby guarantees durability while H2 does not. There is a performance penalty for that. The performance test is single-threaded. If you need to a database with multiple connections in parallel, you will see that Derby, PostgreSQL and MySQL is more suited than H2.

ACID-compliance: The problem is a bit more complex than. Apache Derby claims to be ACID compliant, but is in fact not. Apache Derby does not call FileDescriptor.sync() on each commit. See also http://www.h2database.com/html/advanced.html#durability_problems

> I find the performance comparison on the H2 page a bit biased.
If there is a problem with the algorithm or the test setup, please tell me! Apache Derby did get faster in the last release, the numbers will be updated in the next two weeks or so.

> It looks from the numbers as it is done without reusing prepared statements
No, PreparedStatement are use. See also the benchmark source code, and http://www.h2database.com/html/performance.html ("Wherever possible, the test cases use prepared statements.")

> There is a performance penalty for that
No, not the way Apache Derby does 'try to be' ACID compliant (it uses RandomAccessFile(.., "rws"). Apache Derby is slow for other reasons, mainly architecture.

> The performance test is single-threaded.
Some benchmarks yes, but not all (BenchB). In most situations, embedded databases will not only be accessed by one application, so it does make sense to mainly test with one connection.

> multiple connections in parallel
It is true that H2 does not yet support fine grained locking / MVCC like the others. But MVCC is already partially implemented (see docs), and it doesn't look like this will impact performance a lot (maybe 10% or so).

Thomas (maintainer of H2)

About the missing data in the matrix:

H2 Maximum Database Size: 256 GB for the data, excluding LOBs. (http://www.h2database.com/html/faq.html#size_limit)
(does not depends on the memory size)

H2 Maximum Number of Tables: correct (it does currently depend on the memory size, and unfortunately this was already a problem for somebody who tried to create 'tens of thousands of tables')

H2 Maximum Table Size: 256 GB, excluding LOBs.

H2 Maximum Row Size: 256 GB excluding LOBs.

H2 Interfaces: JDBC; others (ODBC, C, etc.) via PostgreSQL compatibility

H2 and HSQLDB Bind Variables: Yes

H2 and HSQLDB Row Level Locking: No (H2: table level locking, experimental MVCC)

H2 Scalability/SMP: Multithread listener, but execute queries one at a time by default (multi-threaded is not default yet)

Apache Derby Replication: Yes? (Experimental?)

H2 Cluster: Yes

One huge factor to take into consideration when choosing a database is the ease of finding people with expertise in that system. I think this is especially important for educators, who are not necessarily programmers/database specialists themselves, and must rely on others to do the dirty work. Whatever the advantages of the more obscure systems like H2, these must be weighted against the availability of people who are able to develop, administer, and support these databases.

My suggestion would be that if you are unsure about your ability to call upon people able to resolve your technical problems, try to stick to the most commonly used, generally accepted standards (in the case of databases, this being MySQL, and maybe HSQLDB/Derby in the Java world). What you sacrifice in performance or development integration, you gain in your ability to keep your application running down the line.

Any info available on Max Connections allowed for each DBS?

Thanks!

Bill

wep3@bellsouth.net

Apache Derby's performance is incredibly bad when compared to H2. I wouldn't even consider derby.

One annoying thing in Derby, it Does not support Boolean Data Type, while being more advanced than H2. I know some would debate on using Integer with 0's and 1's, but in doing that are'nt you increasing the code size.

I also had difficulty in Starting the Server with Specified Store Directory in HSQLDB while being embedded in the Application.

Good chart. Some updates that might change your mind regarding the order of your recommendations. Sun purchases MySQL, Michael Widenius, the original creator of the MySQL left the project over differences and started "Maria". Now Oracle has bought MySQL. PostgreSQL is looking pretty good now.