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?


One explanation is the case where lots of rows of data are created but later deleted by the application. If these rows happen to be in the memory cache throughout, they are never written to disk.

The space for deleted rows is reused for up to 500 most recent items, the rest discarded.

The CHECKPOINT DEFRAG command can be used from time to time to compact a database that has lots of empty spaces.

The database engine does not write zeros. The zeros in the file are there when some file space is allocated for use.

| improve this answer | |
  • That's a really interesting idea, so the data could be allocated but never written to disk, and the writes would therefore touch some space farther away instead? – JohnEye Jun 7 '16 at 18:08
  • Yes. It behaves that way with the given scenario. – fredt Jun 7 '16 at 18:15
  • Then I don't understand why those empty spaces are not filled with data later. Any ideas? – JohnEye Jun 7 '16 at 18:17
  • Keeping track was considered time consuming. Occasional CHECKPOINT DEFRAG to remove the spaces doesn't take too long. – fredt Jun 7 '16 at 18:26
  • Thank you, I will accept the answer. If you could add some more context to the answer (since you seem to know a lot about HSQL), it would be a great bonus. – JohnEye Jun 7 '16 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.