When I ask this question, I see that the current version of H2 database was released on 07-01-2011 (very recently). That is very good & healthy. Will this pace be kept? event if the pace of new releases are slow, will it be supported by opensource community for long term?
For cost reasons, I am currently considering using Postgresql for a high performance app and H2 database seems to have the right set of features (basically whatever I need). Am not just sure if it would be a right decision to use H2. Basically my decision should be based on the long term support be it subscription based or by community.
Answer
Just to provide some additional information, in 2010 we switched away from H2 to MySQL for our large[r] corporate database shards. The biggest reason was that the internal H2 engine is single threaded. When large, unoptimized queries are running across our databases with 100s of thousands or millions of rows, all other database operations would stop. See the H2 documentation for more info. The row locking of H2 seems relatively immature compared to MySQL or Postgres.
In addition, MySQL and Postgres also provide replication mechanisms instead of rolling your own. This also allows us to backup our database system "live" from the slave instead of blocking a H2 database while we dump it.
Lastly, although I've not run this performance test, I suspect that although H2 is lightening fast for small to mid sized databases, as you increase the database size and especially the number of concurrent queries, MySQL and Postgres will start to equal and then best its overall performance -- especially looking at query time distribution.
We continue to use H2 for boutique, in memory, and test databases very successfully. Thanks much to Thomas for it!