Hey everyone! I've been exploring the idea of using HSQLDB as an in-memory database for production, especially because it has an Oracle compatibility mode. This could be a huge advantage for us, as we have a lot of existing Oracle queries and rewriting them would be a hassle. I'm specifically curious about using it for OLAP or ETL scenarios. Has anybody tried this or heard about it? What's your experience with performance, reliability, and developer experience? I know DuckDB and SQLite are popular in this space, but as far as I know, neither offers Oracle SQL dialect compatibility. Is considering HSQLDB a bad move? Thanks for your insights!
2 Answers
Honestly, I wouldn’t completely trust the Oracle compatibility with HSQLDB. It might work until it doesn’t, and you need to consider how much data you're actually dealing with, since that impacts memory usage. If you're looking at scaling, that can be a problem too with an in-process DB.
For me, H2 is a solid option as an in-memory database for transient data. While HSQLDB has its perks, I wouldn’t rely too heavily on its Oracle compatibility. Just keep in mind that H2 doesn’t support full outer joins, which could be a downside compared to HSQLDB.
True, HSQLDB does handle full outer joins, but keep in mind H2 is still a worthy contender for most cases!