LOB Removal Does Not Reduce Hibernate H2 DB Size

Question

I am using H2 1.4.196. I have a payload table that contains a LOB. I run the deletion of entities in a table through a Java program and verify using the H2 console that the table is now empty.

However, the db.mv file is not reduced in size. Re-creating and deleting the LOB leaves the table empty, but db.mv continues to grow indefinitely, and while looking at the file, I still see the contents of the LOB. Only on DROP TABLE. The payload reduces the size of the db.mv file.

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

What could be the reason for the inability to truly remove the LOB?

Answer 1

Update: I found https://github.com/h2database/h2database/issues/681 , and building from source to enable commit in November 29 led to a successful reduction of the DB file. However, it took about 20 seconds for the deletion to propagate and be reflected in the file size, and inserts during this timeframe would cause the deletion to never complete.

Similar problems have existed for many 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 is automatically reused. When you close the database, the database is automatically used up to 200 milliseconds by default." Havent really saw that this is the case with our LOBs, as DB extends each iteration of the rest of the mon task.

It offers the SHUTDOWN COMPACT manual as a workaround for a more compact

https://groups.google.com/forum/#!topic/h2-database/eXBzpF4WnNk : "Please note that the database file is not reduced if you delete the data (but keep the database open). The space in the file is automatically reused The database file will only shrink if you close the database (close all connections or run "shutdown").