In-memory Java DB
Are there any DBs for Java that can be run in an embedded mode with some tables being stored in-memory while loading others from disk? H2 and JavaDB seem to be the two leaders for Java DBs and I know they both have an in-memory mode, but do they make you load the whole DB into memory or can you decide on a table-by-table basis?
Answer
This is the cached table FAQ mentioned in HSQL website.
•Does HSQLDB store all data in memory. Doesn't memory run out as a result?
•It stores all data in memory only if you want to. By default, CREATE TABLE results in a memory table, as this is the best type for smaller tables. For larger tables, use CREATE CACHED TABLE and adjust the cache size to suite your memory use requirements (as little as 8MB or so). See the System Management and Deployment Issues chapter of the Guide. There is no simple rule and no imposition on the part of HSQLDB as maximum flexibility is allowed using only a couple of settings. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.
I think cached table is already powerful enough to meet your need.
From http://hsqldb.org/web/hsqlFAQ.html
Comparation between cache table and memory table
MEMORY tables and CACHED tables are generally used for data storage. The difference between the two is as follows:
The data for all MEMORY tables is read from the *.script file when the database is started and stored in memory. In contrast the data for cached tables is not read into memory until the table is accessed. Furthermore, only part of the data for each CACHED table is held in memory, allowing tables with more data than can be held in memory.
When the database is shutdown in the normal way, all the data for MEMORY tables is written out to the disk. In comparison, the data in CACHED tables that has changed is written out during operation and at shutdown.
The size and capacity of the data cache for all the CACHED tables is configurable. This makes it possible to allow all the data in CACHED tables to be cached in memory. In this case, speed of access is good, but slightly slower than MEMORY tables.
For normal applications it is recommended that MEMORY tables are used for small amounts of data, leaving CACHED tables for large data sets. For special applications in which speed is paramount and a large amount of free memory is available, MEMORY tables can be used for large tables as well.
It seems this feature is not supported by H2 and Derby(JavaDB) yet. Correct me if it's not the fact.