I store 500.000 records each around 50 chars in a database. Every hour the database gets cleared and 500k records are inserted.
I usually prefer H2 but I do not understand why database.mv is 85 MB big (and after clearing and restoring even 200 MB). I also tried hsqldb, it just uses 35 MB all the time.
I use spring boots with the following properties: H2:
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:file:./h2
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
HSQLDB:
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver
spring.datasource.url = jdbc:hsqldb:file:data
spring.datasource.username=sa
spring.datasource.password=
What is your question?
You use a persistent database in H2. H2 has two storage backends. The default is MVStore-based one, it is much more suitable for multi-threaded applications with high data contention, but it also uses a copy-on-write data policy, and it has own cost. Massive updates can increase the size of database significantly. Of course, the space will be reused later. The legacy PageStore backend behaves better in such use cases, but it uses table-level locks and it does not execute multiple commands from different sessions in parallel. Both MVStore and PageStore can read data from the disk when necessary, they don't need a lot of memory for large databases.
HSQLDB holds everything in the memory by default, but its storage format is more compact. It also doesn't use additional space for indexes, because the table will be fully loaded in the memory anyway. Of course, you can't use the really large table in that way. HSQLDB also has normal on-disk tables, you can create such tables with CREATE CACHED TABLE and these tables are stored in entirely different format and use more space on disk than memory-based tables. However, such tables can still use less space than persistent tables in H2.
If you want to use the in-memory tables in H2, you need to create them with CREATE MEMORY TABLE, they use smaller amount of disk memory. H2 does not create such disk-efficient and memory-inefficient tables by default.