Our financial system heavily relies on H2 database. We choose H2 instead of Postgres as our intermediate financially storage because it's small and fast.
Our use case involves running a financial system on an H2 server in server mode. Our application will insert/delete entries from H2 every hour. This mission critical financial storage is expected to run without any downtime, since there are more than 30 servers rely on the storage. The major issue that we are encountering is the H2 DB performance degraded as the size of the DB grown over time. In order to regain the H2 performance our operation team needs to remove the database completely and let the application rebuild the database from scratch, two times a week. So I am investigating this issue from two direction, first, I am looking for a solution like AUTOVACUUM in Postgres that allows zero human intervention to compact the database at runtime. I noticed there is a priority 2 task as follow in the 1.3.x release that might be an alternative for AUTOVACUUM. # Compact databases without having to close the database (vacuum). I am wondering is there a date for the release? or a patch that I can apply immediately for addressing this issue? I am using H2 Version 1.2.135 that has the following feature to dealing with compacting database from my understanding. 1. Shrinks for up to 2 seconds by default when SHUTDOWN the database. 2. Fully shrinks the database when SHUTDOWN COMPACT the database. 3. Use SCRIPT/RUNSCRIPT to dump and restore the database. All above strategy involved closing the database. In our system we really needs the feature that compact database at runtime. Second, I am wondering how index works when we insert a new entry in a empty space. I understand 1.2.x version will re-use empty space in the database file at runtime. Does it needs to rebuild the index at some point as we insert the new data into the empty space? Is there any tool that we can use to rebuild the index at runtime? -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
