The open source Java database engine H2 (H2 stands for Hypersonic 2) has dropped the self-made ODBC driver and instead supports the PostgreSQL server side network protocol, so the H2 database can be accessed using the PostgreSQL ODBC driver and other PostgreSQL clients. H2 claims to be the fastest Java database, and is mostly compatible with its main competitors Derby (previously Cloudscape) and HSQLDB. It it written by the author of Hypersonic SQL (the predecessor of HSQLDB) and PointBase Micro. Since H2 was published in December 2005, many features were added and improved: CSV and XML functions, linked tables, views, online backup, full text search, two phase commit, compression, UUID and ARRAY support, encrypted databases, and clustering. H2 is the first database that supports protection against SQL injection. It is doing that using a feature called 'disabling literals'. [Editor's note: the key takeaway here is that PostgreSQL clients can work with H2, from my vantage point; any other significant changes or improvements offered by this change?]

It seems H2 only supports table-level locking (not row-level). is this true? If so, it's hard to imagine it scaling to any reasonable level for multiple users....

H2 only supports table-level locking

That's still true, table level locks. However 'read committed' transaction isolation is now supported, like in most other databases (first H2 only supported 'serialized'), and concurrency for medium sized databases is OK according to the benchmark ('BenchB' in the performance uses 10 threads). But of course table level locking is not enough for some use cases. The plan is to implement MVCC (multi version concurrency control).

any other significant changes offered by this?

No, except improved PostgreSQL compatibility. Quite a few changes were required, not all of them are documented yet (SET SEARCH_PATH, BEGIN, EXECUTE, $ parameters). In my view, compatibility is very important. Many developers know only one database, and many applications are written for exactly one database. Using another one usually requires big changes, except if compatibility is good.

The plan is to implement MVCC (multi version concurrency control).

that would be cool. Any idea when that might get into a beta stage ? thank you, BR, ~A

Very Good Tom... Keep it up. Thanks Nishant Saini

I once used it as a development platform, due to the problem of running into unresolved bugs in MySQL, H2 running in MySQL emulation mode fixed that one for me until I could find a workaround on native MySQL. I switched back to MySQL due to speed reasons (H2 was noticably slower than MySQL in InnoDB mode) unfortunately I was forced to use MySQL otherwise I would have chosen PostgreSQL anyway. Anyway, as development db H2 beats Hypersonic and Derby by miles, although it still currently only supports table level locking

Good work guys. Are any benchmark results for UNIX/Linux systems? I'm not sure there are many people that runs MySQL or PostgreSQL on Windows in production ;-) PS I'll try H2 as database for test. If it really works 2x times faster that postgresql we will switch to H2 for tests for sure! PPS Did anybody tried derby,hsql or h2 in production? What was database size? Is it stable enough to use?

Any idea when that might get into a beta stage?

At the moment it looks like at the end of this year.

H2 was noticeably slower than MySQL in InnoDB mode

I would be interested to know why. Was it H2 server mode? Embedded mode would be faster. Also, 'older' databases like MySQL have many optimizations for specific cases. It will take time until all those optimizations are built into H2. Maybe H2 didn't use an index?

Are any benchmark results for UNIX/Linux systems?

I currently don't have access to such a system. But running the benchmarks yourself should be easy, tell me if you need help!

Any idea when that might get into a beta stage?

At the moment it looks like at the end of this year.

H2 was noticeably slower than MySQL in InnoDB mode

I would be interested to know why. Was it H2 server mode? Embedded mode would be faster.

Actually it was only a handful of datasets and it was embedded mode. But there could be various reasons for this since I assumed myself it was not normal. a) I was using the jpa ri on top of everything in mysql mode b) The weaver was set over the entire app server (which is less than ideal) The app server was running on a windows system. Indexes were definitely not set at that stage due to its development state. (a handful of datasets etc..) I had to switch back the MySQL anyway, so it didnt matter to optimize more, I am sure there is lots of optimization possibilities.

Also, 'older' databases like
MySQL have many optimizations for specific cases. It will take time until all those optimizations are built into H2. Maybe H2 didn't use an index?

Are any benchmark results for UNIX/Linux systems?

I currently don't have access to such a system. But running the benchmarks yourself should be easy, tell me if you need help!

Thanks a lot, I was perfectly happy with H2 the way I used it and I consider it to be an excellent db. H2 since then definitely is my perfect choice if I need an embedded db, since I consider its production values way higher than any other java based db engine out there. Anyway if you want to do more investigation, I can provide the app, or access to it in h2 and mysql mode (I have to set it to h2 again but that should be doable - no benchmarks it was just noticable since the development state of everything was fluent I did not investigate into it too much) But as I said it didnt really matter, since there were so many sidefactors which could influence everything.

I'm having issue connecting to H2 using the PostgreSQL ODBC driver. I installed the ODBC driver and using the Microsoft ODBC Administrator to create the datasource. Here is the Error I get- "Could not connect to the server; No connection could be made because target m/c actively refused it;127.0.0.1:5432". I've also tried changing the port with no luck. Everything works with JDBC. I've no idea what am I doing wrong. Can someone help?

The default port number was the issue. It is 5435 by default for H2 and 5432 for Postgre ODBC DSN Setup.