I have an application whose HSQL database grows awfully large for some users, but not others. When I received one example where this problem happenned, I discovered that the database is nearly empty - it's almost 16GB of zeroes and only a couple tens of megabytes of real data. Upon performing
CHECKPOINT DEFRAG, it shrinks to its real, small size.
Unfortunately, I'm not very familiar with this database engine and my online searches have produced little useful information. One interesting thing was that there is an option to automatically perform the defragmentation upon reaching a certain size, which I didn't use. However, I would expect the DB to reuse dead rows and not keep them around, and definitely not zeroed out as this must have a bad effect on performance.
Why does the database keep producing so much empty space, which is only interrupted by data here and there? How can I find out what's happening?