#1253 Defrag does not trigger properly at checkpoint


When I repeat the process of adding a million rows to a table, then truncating the table, the data file continues to increase in size when I have hsqldb.defrag_limit=5. My output is the following:

File size before truncate: 67108kb
File size after truncate: 67108kb
File size before truncate: 134217kb
File size after truncate: 134217kb
File size before truncate: 201326kb
File size after truncate: 201326kb
File size before truncate: 268435kb
File size after truncate: 268435kb
File size before truncate: 322961kb
File size after truncate: 322961kb

After the first run, the size of the data file is 67108kb, which should be the max size needed at any time because I am only adding a million rows and deleting them. After the second run, the size of the data file is 134217kb - twice the size of my data, which means 50% of the db file is wasted, so a defrag should be triggered at the next checkpoint. However as you can see, the defrag is not triggered.

When I reduce hsqldb.defrag_limit to 1, the defrag is triggered and the file size stays at 67108kb. But it should also trigger when the limit is 5.

Test code is attached. This was run against hsqldb 2.2.5.


  • Jessi Abrahams

    Jessi Abrahams - 2012-09-05
  • Fred Toussi

    Fred Toussi - 2012-09-07
    • labels: --> engine 2.0
    • milestone: --> current-release
    • priority: 5 --> 1
    • assigned_to: nobody --> fredt
    • status: open --> open-later
  • Fred Toussi

    Fred Toussi - 2012-09-07

    I think I have seen and fixed something similar to this issue since version 2.0.

    But your specific issue still remains. The TRUNCATE and DROP TABLE statements do not keep track of the empty space as this would take a lot of time. Furture versions could estimate the lost empty space.

    Currently the best option is to perform a CHECKPOINT DEFRAG programatically.

  • Jessi Abrahams

    Jessi Abrahams - 2012-09-07

    It seems that no matter how data is deleted from a table (DELETE, TRUNCATE, DROP TABLE) the space is not reclaimed (by reclaimed, I mean added back to the free list) until a defrag. Is there any way to delete where some of the space can be reclaimed without a defrag?

  • Fred Toussi

    Fred Toussi - 2012-09-07

    The space is added back to the free list when you use DELETE, but the free list is limited to 512 elements (can be extended to 4192). For large deletes, what you are observing is true.

  • Jessi Abrahams

    Jessi Abrahams - 2012-09-24

    Can you point me to some documentation regarding how to extend the size of the free list?

  • Fred Toussi

    Fred Toussi - 2012-09-24

    See the hsqldb.cache_free_count property in the Properties Chapter of the Guide.

    If you delete N rows within the limit, and commit, then up to N spaces will be available for use when you insert new rows.

    Management of empty spaces will probably improve in the next version, subject to SupporWare funding.

  • Fred Toussi

    Fred Toussi - 2013-01-30
    • status: open-later --> open-fixed
  • Fred Toussi

    Fred Toussi - 2013-01-30

    Version 2.3.0 records ALL the spaces that are released with DELETE or UPDATE and keeps a better record of of the free space size.

    There is also an option for a more advanced space management scheme which would reuse the spaces freed by dropping tables.

  • Fred Toussi

    Fred Toussi - 2013-08-14
    • status: open-fixed --> closed-fixed
  • Fred Toussi

    Fred Toussi - 2013-08-14

    Empty space management has been improved in 2.3.0, Still there is a limit on the number of spaces that can be reused.


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks