How can I alter the TTL of a database after the database has been created?

[besrabasant]

I want to alter the TTL of a database from 30 days to 90 days after it has been created.

How can I do it?

[killme2008]

Sorry, you can't do it right now. You have to create another database and restore the data from the old one. But we promise to implement this feature (including alter table TTL) in next major release.

I created an issue to track it. #4394

[Arshil-Akkala]

hey @killme2008 , can the TTL be set at database creation level or schema level?

[killme2008]

Of course you can, please read the doc https://docs.greptime.com/user-guide/administration/manage-data/basic-table-operations#create-a-database

CREATE DATABASE test with(ttl='7d');

[Arshil-Akkala]

So, to clean the existing data let's assume before 7 days, since TTL modification cannot be done as of now, what are the alternatives except DELETE command which is not supported yet?

[killme2008]

delete statement is already supported yet https://docs.greptime.com/reference/sql/delete/

[Arshil-Akkala]

By delete statement, my bad.. I meant this.
DELETE FROM ttl_test WHERE greptime_timestamp < NOW() - INTERVAL 7 DAY; let us say for instance this is the statement & my intention is to delete the older data & only keep 7 days' worth of data. Upon executing this, I am getting an unsupported error.

@killme2008

[killme2008]

I got you. Are u using the Prometheus remote write protocol? Looks like it writes the data with the metric engine, and the metric engine doesn't support the delete command right now. cc @waynexia

But I think you can delete the data from the physical table, the default physical table must be greptime_physical_table and it is mito engine-based.

[Arshil-Akkala]

I got some idea though, thanks for the insight. @killme2008