CODEWITHSUNDEEP
javadatabasehsqldb
Vignesh
Vignesh

Reputation: 101

HSQL DB size if huge after we implemented delete operations on large dataset

So we are working on a large dataset that will store 2-3 million records in the HSQL table and delete after every few minutes once a certain limit is reached, we observed that once we implemented this delete functionality the size of the database has increased from 450MB to 5GB approximately on the same dataset, is it possible that HSQL stores these deleted data for some time in the database? or maintain multiple copies? if so how to avoid it, once we delete the data we want the data to be removed from the database and reclaim the space. Is there any setting available? Our goal is to use as less disk space as possible. We will be working with only 1 connection to the database, so we don't need thread safety or MVCC and we are okay with occasional data corruption or data loss when the instance goes down, we also run all the connections with auto-commit mode.

Upvotes: 0

Views: 382

Answers (1)

fredt
fredt

Reputation: 24352

With older versions of HSQLDB you needed to execute the SQL statement CHECKPOINT DEFRAG in order to reclaim the space for deleted rows.

With the latest version 2.6.1 and later, there is a a new setting to manage the space reuse. You need to execute SET FILES SPACE TRUE. Then for each large table, you execute SET TABLE <tablename> NEW SPACE. Finally, execute CHECKPOINT DEFRAG. The space left from deleted rows is then reused automatically. See the Guide http://hsqldb.org/doc/2.0/guide/management-chapt.html#tspace_usage

If you are deleting ALL the rows in a table, you can instead execute TRUNCATE TABLE <tablename> AND COMMIT as a faster alternative.

Upvotes: 1

Related Questions