search

planetscale / database-js

A Fetch API-compatible PlanetScale database driver
https://planetscale.com/docs/tutorials/planetscale-serverless-driver
Apache License 2.0
1.16k stars 32 forks source link

PlanetScale doesn't support `EVENT`, so how can I make records subject to TTL? #120

Closed 205g0 closed 1 year ago

205g0 commented 1 year ago

Pretty much the title and typically not a big deal...

mattrobenolt commented 1 year ago

Can you explain what this means? Are you suggesting a feature where rows can be written with a TTL and they auto-delete? If so, this is firmly in the realm of MySQL would need to support this and outside of the scope of our driver. But in general there are lots of established patterns for doing this in relational databases, I'm just curious what you have in mind and maybe we can guide you to a solution.

mattrobenolt commented 1 year ago

Oh wow, I'm assuming you mean https://dev.mysql.com/doc/refman/8.0/en/create-event.html

I wasn't even aware of this being a thing. I haven't poked around, but I suspect then that this is lack of support in Vitess that we'd need.

iheanyi commented 1 year ago

Hey there! I was reading the MySQL documentation on this and you're right, this is something that we do not support. If a record is subject to a TTL, I'd suggest handling this at the application level. A pattern for doing this is to set a timestamp based on TTL on each row and having a scheduled job that runs a set interval of time to do what you need. This is a pattern that we use internally at PlanetScale for this type of thing and it works really well for us. I hope this helps out!

If that doesn't fit your use-case, I suggest opening an issue in the Vitess repository, where this is the root of the issue. Thanks!

CanRau commented 11 months ago

Extending on iheanyi you don't even have to remove those "TTL" columns on time, I do the same for sessions and the query just checks that the TTL is in the future and hasn't expired yet

205g0 commented 11 months ago

@CanRau but then the database fills up with data you won't need

@iheanyi it was clear to me how to do this manually. the point is that it's still some significant effort and you need a dedicated process vs setting some ttl (as with most other dbs) and call it a day

Also not sure if Vitess and/or PS can support this b/c this would require a long-running process

CanRau commented 11 months ago

Yea I see, my comment was meant like, the cron or whatever doesn't need to run as exact/often to ensure expired rows are deleted on time, it can "just" run every hour or whatever and remove everything already expired. But yea this requires some additional code/system. Just saw that EVENT is basically a cron like scheduler. Currently Vitess doesn't support stored procedures at all afaik, and this looks similar.

I accept some of those missing pieces for the benefits of the platform and personally need cron like stuff anyway, which I can then use to clean up old db stuff as well.