Size of h2 .db file keeps growing

[Qingyu Zhang]

Hi,

I have a Java application that uses h2 database (version 1.4.192). I noticed that the .db file grows bigger and bigger over time, although the number of records are stable. The application inserts and updates new records from time to time; it also deletes old records. Therefore the total number of records is not growing. With about 40,000 records, I noticed the file size has grown from 9MB to over 50MB in a day or two. When I added the option "DEFRAG_ALWAYS=TRUE" to h2 url, then the size could go back to 9MB after I stopped the java application. Is there something I can do to automatically "defrag" without restarting the application?  Should I update h2 version?

Thank you very much for the help!

Qingyu (Helen) Zhang

[lilia...@gmail.com]

I had met a same problem, and could not resolve it.

I just import data into H2 (embeded mode) through jdbc in first time, and following I will only execute jdbc query, but the   *.mv.db file size auto increased dramatically, from about 600M to 1.8G, if query without "union all", the db file size will increase to 1.1G.

I need a help, why this happen and is it will affect the performance?

[lilia...@gmail.com]

My solution is importing data to h2 normally, and the *mv.db file size increased dramatically, and then jdbc/execute! a sql to create a empty table with db url option ";DEFRAG_ALWAYS=TRUE", the size of db file dropped dramatically. the reason to do this is if jdbc/insert-multi! with ";DEFRAG_ALWAYS=TRUE", the efficiency of importing dropped.

At last jdbc/query with ";DEFRAG_ALWAYS=FALSE;ACCESS_MODE_DATA=r". the size didn't change anymore.

Is there anyone can tell me the reason about h2 db file frage? why is it so big? Is it a special attribute of h2 or is common for embedded database or database?