Experience using Derby or HSQL in production mode

Question

Anyone ever tried to use Derby or HSQLDB in a production environment? Any good, bad or ugly experiences?

Answer 1

There is a comparison of embedded databases on the H2 website (H2 is another embedded Java DB that is very fast, BTW):

• Feature comparison
• Performance comparison

This should give you some facts.

Answer 2

I used HSQLDB in production for reconciliation application. It scaled well till half a million records, DB size was nearly 2 GB.

Good
1) You can tune memory. Initially we were running with 2GB of heap and later increased to 4GB of memory. You can tune memory for CPU quite well. We had different configurations. 15 minutes of execution with 512MB and 2 minutes execution with 4GB of RAM.

2) Fall back to disk based. Later we switched to file based table, and we reduced our heap from 4GB to 512MB. But program doesn't require any change other than memory related configuration.

Nice to have. 1) Shutdown took extra time on windows when memory allotted was low. 2) There is little bit of black-magic with index. Once we added index on more than 6 columns on half a million records. Process was quite slow, we removed additional index. It didn't hurt us.

Overall I strongly recommend HSQLDB when number of records are less than few million, and DB size less than 10GB. It may work even for higher need, but I don't think they could be straight forward. May be additional document may help.

BTW, Fredt was very swift for my questions in mailing list.

Answer 3

See this post for HSQLDB Hibernate on hypersonic in production?