I have a spring boot app where I am using mysql or postgres database (based on client requirement). In database I have a table in which I have to store data for 3 months. The data gets populated in this table continuously through a kafka stream. So I have a scheduled spring task defines that runs a query on a regular interval to delete.
delete from table where creation_time < (current_time - 3 months)
But when this table has a lot of data this query takes a lot of time and gets stuck sometimes. What is the standard way to define a ttl on table data or mass delete it once it gets old?
PS: I have an index on creation_time and my primary key is of type uuid.