Deleting LOBs does not decrease Hibernate H2 DB size

Question

I am using H2 1.4.196. I have a Payload table that holds LOBs. I trigger a deletion of the entities within the table through a Java program, and verify via the H2 Console that the table is now empty.

However, the db.mv file does not decrease in size. Repeated creation and deletion of LOBs leaves the table empty, but the db.mv continues to grow indefinitely, and looking into the file I still see the LOB contents. Only upon DROP TABLE Payload does the size of the db.mv file actually decrease.

I had a theory that it could be an open transaction, but SELECT * FROM INFORMATION_SCHEMA.SESSIONS only showed the session created by the sessions sql statement.

What could be causing this inability to truly delete the LOBs?

Answer 1

Update: I found https://github.com/h2database/h2database/issues/681, and building from source to include the Nov 29 commit resulted in the DB file to successfully decrease. However, it took about 20 seconds for the deletion to propagate and reflect in the file size, and insertions within this timeframe resulted in the deletion never completing.

Similar issues have been around for years: https://groups.google.com/forum/#!topic/h2-database/CGXOfSx_Vq4

According to http://h2database.com/html/features.html#compacting, “Empty space in the database file re-used automatically. When closing the database, the database is automatically compacted for up to 200 milliseconds by default.” Haven’t really seen this to be the case with our LOBs as the DB grows every iteration of the rest mon task.

It suggests a manual SHUTDOWN COMPACT as a workaround to compact more

https://groups.google.com/forum/#!topic/h2-database/eXBzpF4WnNk: “Please note the database file doesn’t shrink if you delete data (but keep the database open). However, empty space within the file is automatically re-used. The database file only ever shrinks if you close the database (close all connections or run “shutdown”).”