I'd only like to keep records in my table for a certain number of hours, or possibly only a certain number of records overall based on some threshhold. I'm no DBA, but I've heard and read that the way to usually do this is to keep data in partitions. For example, have hourly partions, keep 12 partions, and when you create a new one, delete the oldest partion.
After reading the HSQLDB docs and some googling I get the impression that HSQLDB doesn't support partitioning. The closest thing I saw to it was the little section in the Deployment page on Data Cache Memory Allocation. But it sounded like it would write the excess data to disk rather than just throwing it away.
What strategy would you recommend for HSQLDB data expiration? We don't want to keep the expired data around, just get rid of it as quickly as possible.
Simply delete the old rows.
This can be automated using an SQL insert trigger which deletes an old row after inserting a new one.
That sounds expensive. I don't want to incur the extra time it takes to do a lookup and delete everytime we insert a record because we'll be getting a high volume of inserts.
Maybe a script running on the server that calls SqlTool once every hour and deletes all the records older than 12 hours…
I was just looking for a suggestion on a good way to do it.