Relational Database with cache-like drop behaviour

Question

I am looking for a database system that should be a relational database. But a main feature has to be that it forgets entries to fit a certain condition, like 'least recently used' on a certain size of the DB. In other words, I want a kind of 'weak reference' in a DB.

Is there already such a solution?

What do you think about that idea: having a usual RDBMS, setting a time stamp for every entry and doing a periodic check for some constraint (like DB size). If needed, X of the oldest entries are removed and referencing entries will point to a special sentinel node from now on, to symbolize 'that entry is gone'.

Answer 1

It sounds like you are talking about archiving data in RDBMS. I have implemented this in RDBMS systems through ETL programs.

If you intend to remove the data, you are going to need a separate table that you will UNION with so that you can indicate in your app that the record(s) have been archived.

Some thoughts on this:

1. Ensure that you are not breaking any business models by archiving data.
2. Identify inactive business transactions that match your criteria. You may encounter several categories, each with their different types of archiving rules. Don't archive any open transactions - they are PITA to reconstruct.
3. Figure out the schedule of archiving the data
4. Ensure application transparency for users regardless of data location / archival
5. Implement this data retention policy tailored to each set of data
6. Usually archived data goes to a separate DB that may run on the same machine (recommended for license cost purpuses) or a different machine.
7. Even archival itself can be optimized by putting the Really Really old data into text dumps.
8. Backups, Backups, Backups - this is a very sensitive operation. Double, Triple check your backups.

All of this can be done with almost any ETL program on the market.