Built in retention policy

[mounte]

Is your feature request related to a problem?

Currently users of QuestDB are limited to external scripts for doing maintenance related to dropping old data.
In other db's there are built in "retention policies" that you can add to tables.

Describe the solution you'd like.

Introducing something like:
ALTER TABLE <name> SET RETENTION <condition>
I think having that as a basic feature with no advanced conditions to begin with would prove very useful.

In our cases having a simple INTERVAL or "OLDER THAN" x hours/days/... would cover 90% of what we do externally today.

Also, as mentioned in #1691 having a condition of dropping the oldest data if size of table gets bigger than X is probably a good condition.

Describe alternatives you've considered.

External bash scripts, python scripts etc...

Full Name:

Daniel Wedlund

Affiliation:

Mounte AB

Additional context

Mentioned this on slack 2023-07-27